[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2978
  • Last Modified:

VB:Crystal Reports Record Selection Formulas

How do you set the record selection formula at run time for a crystal report designer(Crystal Reports 7) used within visual basic (VB6) while using variables in the formula?  I'm trying to filter out records under a certain dollar amount and/or quantity based on user input at run time.
0
CrazyFox
Asked:
CrazyFox
1 Solution
 
Annette RussellCommented:
Try this if your report was created using ADO, OLEDB, RDO.
1.  Create a sql statement filtering out the records with the dollar amount and/or quantity from the variable. (ie "SELECT * FROM TABLE where Amount = " & sDollar = sSQL

2.  With the crystalobject set the  following properties:

frmForm.CrystalReport1.SQLQuery = sSQL
frmForm.CrystalReport1.connect = "dsn="& databasename.

The connect property may be different based on the type of database you are using.  Hope this helps .. Good Luck.

If not using ADO, OLE DB, or RDO, try using the SelectionFormula property.
0
 
CrazyFoxAuthor Commented:
I'm using ADO in an n-tier environment. But the way the server program is set up restricts what type of queries I can write.  I need to change or set the CrystalReport1.RecordSelectionFormula property.
0
 
CareyMBilyeuCommented:
CrazyFox:

Are you hitting an Access database or SQL Server. If you are hitting a SQL Server database, consider creating a Parameter Stored Procedure based on the filtered record. Then in Crystal Reports, create a parameter based on the field you want to filter, (Dollar Amount).

Assuming you are using the Crystal ActiveX Control, place some code like below. The parameters in SQL Server are the txtBeginningDate and txtEndingDate.

There are a few parameters in the Crystal Report that are not included in the SQL Server, such as txtHighlight.

Carey

Sub CrystalReportConnect(mCrystalFormName As Form)

Dim SelectionFormula As String
Dim strSubHeading As String
Dim mres As String

    If Not IsDate(txtFromDate) Then
        MsgBox "Invalid Date. Please input a valid date.", vbOKOnly + vbCritical, "Print Report"
        txtFromDate.SetFocus
        Exit Sub
    End If

    If Not IsDate(txtToDate) Then
        MsgBox "Invalid Date. Please input a valid date.", vbOKOnly + vbCritical, "Print Report"
        txtToDate.SetFocus
        Exit Sub
    End If

    If CDate(txtFromDate) > CDate(txtToDate) Then
        MsgBox "The Start Date must be on or before the End Date.", vbOKOnly + vbCritical, "Print Report"
        txtFromDate.SetFocus
        Exit Sub
    End If
 
    CrystalReport1.Reset 'Resets the report data

    If txtHighlight = "" Then
    '    CrystalReport1.ParameterFields(0) = "Highlight;0;TRUE"
    'Else
    '    CrystalReport1.ParameterFields(0) = "Highlight;" & txtHighlight & ";TRUE"
    End If  'txtHighlight = ""

    If DrillDown Then
        CrystalReport1.WindowShowGroupTree = True
        CrystalReport1.WindowTitle = "Check Out Those Sales Man!!!"
    Else
        CrystalReport1.WindowShowGroupTree = False
        CrystalReport1.WindowTitle = "Check Out Those Sales Man!!!"
    End If

        strSubHeading = "'" & txtFromDate & " through " & txtToDate
        strSubHeading = strSubHeading & ", Sorted By " & cboGrouping
   
    If txtHighlight = "" Then
    '    strSubHeading = strSubHeading & ", No Highlight'"
    Else
        strSubHeading = strSubHeading & ", The Highlight Value Is $" & txtHighlight & ".00 and Greater.'"
    End If
   
        CrystalReport1.Formulas(1) = "Sub Heading=" & strSubHeading
   
    Select Case cboGrouping
       
        Case "City"
            CrystalReport1.GroupCondition(0) = "GROUP1;{SalesVolume.City};"
           
        Case "Store"
            CrystalReport1.GroupCondition(0) = "GROUP1;{SalesVolume.Store};ANYCHANGE;A"
       
        Case "Publisher"
            CrystalReport1.GroupCondition(0) = "GROUP1;{SalesVolume.Publisher};ANYCHANGE;A"
   
    End Select
   
    With CrystalReport1

    With CrystalReport1
    .Connect = "driver={SQLServer};server=ServerName;uid=sa;pwd=;database=databasename"

         
    .ReportFileName = "C:\My Documents\VBTraining\TestParameters.rpt"
    .ReplaceSelectionFormula "{SalesVolume.OrdDate} in Date(" & _
     Format(txtFromDate, "yyyy,m,d") & ") to Date(" & _
     Format(txtToDate, "yyyy,m,d") & ")"
    .Destination = crptToWindow
         
    mres = .PrintReport
        If mres <> 0 Then
              MsgBox "printer error on Crystal report :" & .LastErrorString
        End If
    End With
 
End Sub
Private Sub cmdPrint_Click()

   CrystalReportConnect frmSalesVolume


End Sub

Private Sub Form_Load()
Dim Today As Date
Today = Format(Now, "mm/dd/yyyy")
txtToDate = Today
cboReport.Text = "Please Select A Report"
cboGrouping.Text = "Please Select A Group"


End Sub


0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CrazyFoxAuthor Commented:
It's an Access database.  
0
 
CareyMBilyeuCommented:
You can still use parameter "queries" (instead of Parameter Stored-Procedures). On the query builder, right click and click on the Parameters option, set your parameters based on your filtering criteria. Note, you still have to go through the Date formatting as Crystal Reports has its own date format, therefore you need to supply the data to Crystal reports as you normally would with SQL Server.

Carey
0
 
chewhoungCommented:
Hi, I'm using VB5 and CR4.6 and Access 97, I think there no much different:
example:    
    cryReport.ReportFileName = "C:\mob\Reports\branchdaily.rpt"
    cryReport.SelectionFormula = "{BranchDailyTotal.BranchCode} like '" & frmCriteria1.cboBranchCode & "' and {BranchDailyTotal.Date} = Date(" & Mid(frmCriteria1.mskDate, 7, 4) & ", " & Mid(frmCriteria1.mskDate, 4, 2) & ", " & Mid(frmCriteria1.mskDate, 1, 2) & ")"
    cryReport.UserName = gsUserName
    cryReport.Password = gsPassword
    cryReport.SortFields(0) = "+{BranchDailyTotal.BranchCode}"
    cryReport.SortFields(1) = "+{BranchDailyTotal.TransactionType}"
    cryReport.SortFields(2) = ""
    cryReport.PrintReport


be careful when you build up your sql for selectionformula. add ' for string in front and behind.
0
 
CrazyFoxAuthor Commented:
Thanks!  That works with only a minor change. It's cryReport.RecordSelectionFormula in Crystal Reports 7 instead of cryReport.SelectionFormula.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now