We help IT Professionals succeed at work.

Crystal Reports "IN" function

Hi,
I'm using Crystal Reports for Visual Studio 2010. version 13.
SQL Server 2008 R2

I have a VB.Net user interface setup where the user can select a report from a listbox and then select the criteria they want to run that report for.  I want to allow the user to select multiple criteria for a single column, but I'm having no luck getting it to work.  Basically I want to pass criteria to the IN function like "where product IN('A','B','C')"

I can hardcode criteria into the IN function as a report formula and it works, but I haven't figured out how to pass a variable w/ comma seperated values and getting it to work. What can I do?

Thx,
Dave
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
You have to use [ ]

where product IN ['A','B','C']

How does the variable get its values?
Are you using a parameter that accepts multiple values or re you passing a single string?

What code do you use to set the value?

mlmcc

Author

Commented:
The values are selected using a multiselect listbox.

I've tried several different ways of passing the values. Right now I'm using the code below to build a string and then pass the param like this:
rpt.SetParameterValue("@Hol", gsHOL)

I've tried to pass the string to a stored procedure, but now I'm bringing back all the 'Holidays' and then trying to use a record selection formula to pass the string.
If lstRHol.SelectedIndices.Count > 0 Then
            For a As Integer = 0 To lstRHol.SelectedIndices.Count - 1
                'If lstRHol.Items(a).selected = True Then
                Dim dr As DataRowView = DirectCast(lstRHol.SelectedItems(a), DataRowView)
                If gsHOL = "" Then
                    'gsHOL = '"'" & dr("hol_cd").ToString & "'"
                    gsHOL = Trim(dr("hol_cd").ToString)
                Else
                    gsHOL = gsHOL & ",'" & Trim(dr("hol_cd").ToString & "'")
                End If
                'End If
            Next
        Else
            gsHOL = "ALL"
        End If

Open in new window

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try changing the parameter pass to

     rpt.SetParameterValue("@Hol", "[" & gsHOL & "]")

mlmcc

Author

Commented:
I made the change. Hadn't thought of doing it that way.

It's still returning everything though.  Here's what I have in the record select formula:
{qry_pc_holiday.hol} IN {?@HOL}
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you disply the parameter on the report?

Probably need to use a formula as

Join({?@HOL},", ")

mlmcc

Author

Commented:
The parameter displays like:
['00','02 ','03 ']

So it looks like it is putting a space after the value in the single quotes.

I put Join({?@HOL},", ") into the formula and it says @HOL should be a string arrray.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try

InStr({?@HOL},{qry_pc_holiday.hol}) > 0

mlmcc

Author

Commented:
I took care of the space. Had to move a paren inside the loop.
So now the param display looks like:
['00','02','05']

I tried changing the string gsHOL in VB to a string array. And I set the allow multiple values setting for the @HOL param to true.  The report wasn't different though.

Author

Commented:
It's still displaying all the records on the report.  It works fine when I preview the report, but when I try to view it from the app it's like it never sees the criteria.  The criteria is displayed at the top of the report and it looks like I think it should.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you upload the report?

mlmcc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
It looks like you have it correct.

Can you save it with data?

mlmcc

Author

Commented:
I checked the save data box and saved it. Is this what you wanted to see?
PC-Holiday.rpt

Author

Commented:
Here's a printout of the report I ran from the VB app.  Notice the criteria I picked display toward the top left. I picked 00,01,03.  00 and 03 are not in the database. Only 01 and 02 are in the DB right now. 02 displayed on the report even though I didn't pick it and it wasn't passed in the parameter.
Crystal-Reports.pdf
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't know.  I changed the selection formula in the report to

    InStr("['00','01','03']",{qry_pc_holiday.hol}) > 0

It seems to work.

The rpt file version has 02, 03 as the parameter.

Change it to '00','01','03' and run again.  Save with data and upload

mlmcc

Author

Commented:
I attached the file with single quotes around the parameters.  It works for me when I preview it, but when I stick it out in the path the app opens it from the parameters don't get passed to it correctly.
PC-Holiday.rpt
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Have you changed the code used to run the report?

mlmcc

Author

Commented:
I added the brackets around the holiday param.  Here's a shot of the code. The first code is in a click event on the main form.  The second block of code is from the load event of the report form that contains a report viewer control.
Private Sub btnRView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRView.Click
        'clear out the global variables to start fresh.
        gsAGIN = ""
        'gsHOL = ""
        gsLOGO = ""
        Dim sHOL As String = ""
        'get the selected report.
        gsReport = lstRptAccess.SelectedItem
        If gsReport = "" Then
            MessageBox.Show("Select a report to view.")
            Exit Sub
        End If
        'set start date back a day @ 11PM to capture 3rd shift. set end dt to 11PM on date selected for the end of 3rd shift.
        gdtStart = (Format(CStr(Format(DateAdd("d", -1, dtpRptStart.Value), "MM/dd/yyyy")) & " 23:00:00"))
        gdtEnd = (Format(CStr(Format(dtpRptEnd.Value, "MM/dd/yyyy")) & " 23:00:00"))
        gsAGIN = txtRAgin.Text

        'string together holiday
        If lstRHol.SelectedIndices.Count > 0 Then
            For a As Integer = 0 To lstRHol.SelectedIndices.Count - 1
                'If lstRHol.Items(a).selected = True Then
                Dim dr As DataRowView = DirectCast(lstRHol.SelectedItems(a), DataRowView)
                If sHOL = "" Then
                    'gsHOL = '"'" & dr("hol_cd").ToString & "'"
                    sHOL = "'" & Trim(dr("hol_cd").ToString) & "'"
                Else
                    sHOL = sHOL & ",'" & Trim(dr("hol_cd").ToString) & "'"
                End If
                'End If
            Next
        Else
            sHOL = "ALL"
        End If

        'gsHOL = New String() {sHOL}
        gsHOL = sHOL

        'string together logo
        If lstRLogo.SelectedIndices.Count > 0 Then
            For b As Integer = 0 To lstRLogo.SelectedIndices.Count - 1
                Dim dr As DataRowView = DirectCast(lstRLogo.SelectedItems(b), DataRowView)
                If gsLOGO = "" Then
                    gsLOGO = "'" & Trim(dr("logo_cd").ToString) & "'"
                Else
                    gsLOGO = gsLOGO & ",'" & Trim(dr("logo_cd").ToString) & "'"
                End If
            Next
        Else
            gsLOGO = "ALL"
        End If


        If F1 Is Nothing OrElse F1.IsDisposed Then
            F1 = New frmReports
        End If
        F1.Show()
        F1.Activate()
    End Sub

    Private Sub frmReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim rpt As New ReportDocument

        'The report type determines which criteria are needed to run the report.
        Dim sqlRptTyp As String = "SELECT rpt_typ FROM tbl_report WHERE rpt_name = '" & gsReport & "'"
        'Dim typAdapter As New OleDb.OleDbDataAdapter(sqlRptTyp, gsDbPath)
        Dim typAdapter As New SqlDataAdapter(sqlRptTyp, gsDbPath)
        Dim dtRptTyp As New DataTable
        typAdapter.Fill(dtRptTyp)

        Dim iRptTyp As Integer = dtRptTyp.Rows(0)("rpt_typ")

        rpt.Load("\\usoscs101\apps\SorterShow\SorterReports\" & gsReport & ".rpt")

        Select Case iRptTyp
            Case 1
                rpt.SetParameterValue("@Start_Dt", gdtStart)
                rpt.SetParameterValue("@End_Dt", gdtEnd)
            Case 2
                rpt.SetParameterValue("@AGIN", gsAGIN)
            Case 3
                'rpt.SetParameterValue("@Start_Dt", gdtStart)
                'rpt.SetParameterValue("@End_Dt", gdtEnd)
                rpt.SetParameterValue("@Hol", "[" & gsHOL & "]")
                'rpt.SetParameterValue("@Hol", gsHOL)
                rpt.SetParameterValue("@Logo", gsLOGO)

            Case Else
                MessageBox.Show("Report not valid. Contact Comp Ops", "rpt_typ missing")
        End Select

        crvReports.ReportSource = rpt
        crvReports.Refresh()

    End Sub

Open in new window

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try unchecking the SAVE DATA WITH REPORT option and save the report then rerun.

mlmcc

Author

Commented:
It was already unchecked, but I toggled it and reran then saved.  Still same result.

I have the datasource set to a query. Would it make a difference if I set it straight to the table?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
That shouldn't so long as  the query doesn't do any filtering.

Change your code that assigns the parameters to
            Case 3
                'rpt.SetParameterValue("@Start_Dt", gdtStart)
                'rpt.SetParameterValue("@End_Dt", gdtEnd)
                rpt.SetParameterValue("@Hol", "[" & gsHOL & "]")
      MSGBOX gsHol
                'rpt.SetParameterValue("@Hol", gsHOL)
                rpt.SetParameterValue("@Logo", gsLOGO)

mlmcc

Author

Commented:
The msg displays: '00','02'

Below is what the query looks like.
SELECT     TOP (100) PERCENT agin, stat_set_to, user_id, mod_date, pc_name, SUBSTRING(bin_nbr, 7, 5) AS Loc, SUBSTRING(bin_nbr, 3, 2) AS Rm, SUBSTRING(bin_nbr, 5, 2) 
                      AS MCC, hol, logo, rsn_cd, comments
FROM         dbo.tbl_agin_stat
WHERE     (SUBSTRING(bin_nbr, 11, 1) <> 'X')
ORDER BY hol, logo, Loc

Open in new window

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Do you select 00 and 02?

Is that what is displayed in the report?

mlmcc

Author

Commented:
Yes sorry I wasn't descriptive.
I selected 00 and 02 in that example.  What ever I select in the listbox is saved in the gsHOL string in the same format.  It is displayed in the messagebox the same way too (single quoted and comma sep).

Unfortunately that's not what displays on the report.  All the data from the query is displayed on the report.

I attached another printout of the report I ran through the app.  You can see the parameters I selected at the top left.  The holiday, which is the parameter, is what the report is grouped by.  You can see the 01 on the first page. I did not select 01 as a parameter.
Crystal-Reports.pdf
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I understand what the report was showing, I wanted to ensure that you were trying to pass the correct values to the main report from the application.

So the msgbox shows '00','03','04' and the report show '00','03','04' as the parameter value
but the report shows data for '01'

WHen you display the report can you hit the F5 (refresh) use the current values.
Does the report change?

If it doesn't change, try again but prompt for new parameter values.
Is the report correct?

mlmcc

Author

Commented:
When I'm in design view and I click on Preview Report, the report loads with whatever criteria I had previously put in as the parameter values.  When I hit refresh the report data stays the same if I don't change the parameter values.  When I hit refresh and change the param values the report changes to show the correct data.  So, when I preview the report (outside of the app) everything works as I expect it to.

When I run the app, select some param values and run the report it comes up with all the data from the database query.  When I click refresh and enter in the same params again the report comes back the same. When I refresh and change the parameters the report comes back with the same data. I even typed in some random alphas and it came back w/ the same data.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
In the application is this on the machine with Crystal loaded?

mlmcc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Do you have the final production version of CR4VS2010?
http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/22083

mlmcc
CERTIFIED EXPERT
Commented:
You've said that the report shows "all" of the data when run from your code.  It looks like you have, or maybe had, some other parameters.  Are those being ignored too, or just the Hol parameter?  If you had some other parameters, but took them out, what happens if you put them back in?  Are they used, or ignored too?

 Just a shot in the dark, but you could try putting the record selection formula on the report, to make sure that it's what you expect when the report is run from your code.  In the Field Explorer, under Special Fields, there should be "Record Selection Formula".  Put that in the report header or footer or wherever, set the field to "Can Grow", and see what it shows when you run the report from your code.

 James

Author

Commented:
I have SAP Crystal Reports, version for Visual Studio 2010.
version 13.0.1.220

I have created several other Crystal Reports on my same machine that are working fine and in production now.

Thanks for the suggestions. I'll try those out today and let you know what happens.
The report is working now!

There was another parameter that I was ignoring in the record selection formula.  I added it in, saved it and tried the report, but still had the same result.  When I click on refresh I noticed that I only got prompted for the holiday parameter and not the logo(second) parameter.

I went in to the Report Viewer control and changed the ToolPanelView prop. to parameterpanel so I could see the parameter. I hadn't messed w/ that before I just saw it when I was looking around the properties.

I also looked at the ReportSource property and noticed that all the reports created for this app were listed in the dropdown box.  I thought this was interesting since I just pass the report at runtime. I'm not sure how this works. So, I picked the Holiday report from the ReportSource dropdown. It displayed w/ dummy criteria when I did this. I took a look at it and then changed the ReportSource back to None since I don't want a default report to show.

I ran the app again and selected the report. This time it worked. So I'm not sure exactly what fixed but I've shut it down and ran it again a few times and it is working.  I also notice now on the ReportSource property for the Report viewer that in the "use an existing ReportDocument instance" node it lists PC_Holiday1. This is the only instance in that node, so I'm not sure if it's there b/c I selected it in the dropdown earlier or if there's some other reason it's there.

Any ideas which thing might have fixed the report?

Thanks,
Dave
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't have that version of Crystal or the viewer so I don't know.  I suspect it may be the setting to NONE.

mlmcc

Author

Commented:
Thanks for all the help mlmcc! And thanks for your suggestions as well James!

Author

Commented:
Well geez, I thought it would just close the question that way...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.