[RESOLVED] Using Parameters w/Crystal Reports XI?

[blakemckenna]

I am trying to pass a parameter to a Crystal Report from a VB.Net app. Based on the parameter value, it will either print detail lines or sub-total/summary lines.

I created a Parameter Field in the CR IDE. In the "Section Expert", I selected "Detail" and clicked on the "Suppress (No drill-down) checkbox. I then clicked on the Formula Editor icon next to it to enter the Formula Editor. I double-clicked on the Parameter Field and entered the following line for the formula:

{?ReportType} = "D"

My VB Code looks like this:

    Public Function CreateSettlementReport() As Exception
        Try
            Dim crSettlement As New rptSettlement
            Dim diskOpts As DiskFileDestinationOptions = ExportOptions.CreateDiskFileDestinationOptions()
            Dim exportOpts As ExportOptions = New ExportOptions()

            crSettlement.SetParameterValue("@ReportType", strReportType)
            crSettlement.SetDataSource(DS)

            exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat
            exportOpts.ExportDestinationType = ExportDestinationType.DiskFile

            strSettlementReport = "\Settlement" & strReportDate & ".PDF"

            diskOpts.DiskFileName = strPath & strSettlementReport
            exportOpts.ExportDestinationOptions = diskOpts

            crSettlement.Export(exportOpts) 'Exporting report to a PDF format

            crSettlement = Nothing
            Return Nothing

        Catch ex As Exception
            strProcedure = "CreateSettlementReport()"
            strMsg = ex.Message
            WriteToErrorFile(strMsg, strProcedure, strProgram, intRecordCount)
            Return ex
        End Try

My app is bombing on the highlighted line of code giving me the following error:

"Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"

I have know idea what this means. The variable strReportType is the Command Line parameter.

Please help!

Thanks,

[brucevde]

The parameter name "@ReportType" does not exist. Try "?ReportType".

[blakemckenna]

still the same error Bruce...

[GaryMazzone]

I would just do ReportType as that is the parameter name right?

[SunshineBH]

I know its more work but the following always seems to work for me. Maybe you could try this:

 Dim pfs As ParameterFields
 Dim pf As ParameterField
Dim pdv As ParameterDiscreteValue

 pf.Name = "@ReportType"
 pdv.Value = strReportType
 pf.CurrentValues.Add(pdv)
pfs.Add(pf)

and then call your report using "pfs"

Its a bit more code but, to be honest with you, since i started setting the parameters up this way i have never had any problems.

Hope this helps

[blakemckenna]

Gary,

Not to sound to stupid but how do I call my CR using the "pfs" Object?

Thanks,

[GaryMazzone]

Blake

Here is what I do to call (displaying on a View)

Public Sub DisplayReport(ByVal mRepName As String, ByVal mstrW As String, Optional ByVal strPars As String = "")
        Dim mRep As New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo
        Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue
        Dim currValue As New CrystalDecisions.Shared.ParameterValues
        Dim mySubReportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject
        Dim mySubRepDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim i, x, z As Integer 'Some Counters
 
        Try
            ' Load the report
            'Need to add the report dll yet Nothing Works
            Dim rep As New RSMReports.clsReports(mRepName)
            mRep = rep.returnRep()
            mRep.RecordSelectionFormula = mstrW
 
 
            'SQL Server 
            ConInfo.ConnectionInfo.UserID = MyUserID Here
            ConInfo.ConnectionInfo.Password = My Password Here
            ConInfo.ConnectionInfo.ServerName = mdlGeneral.serverName 'Stored Server name taht the database is on (using SQL Server)
            ConInfo.ConnectionInfo.DatabaseName = mdlGeneral.dbName  '(Database to use on the Server)
 
            For i = 0 To mRep.Database.Tables.Count() - 1
                mRep.Database.Tables(i).ApplyLogOnInfo(ConInfo)
            Next i
 
            'Check for Subreprots
            For i = 0 To mRep.ReportDefinition.Sections.Count - 1
                For x = 0 To mRep.ReportDefinition.Sections(i).ReportObjects.Count - 1
                    With mRep.ReportDefinition.Sections(i)
                        If .ReportObjects(x).Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
                            mySubReportObject = CType(.ReportObjects(x), CrystalDecisions.CrystalReports.Engine.SubreportObject)
                            mySubRepDoc = mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
                            For z = 0 To mySubRepDoc.Database.Tables.Count - 1
                                mySubRepDoc.Database.Tables(z).ApplyLogOnInfo(ConInfo)
                            Next z
                        End If
                    End With
                Next x
            Next i
 
            ' Set the report source for the crystal reports viewer to the report instance.
            Me.crViewer.ReportSource = mRep
            If strPars.Trim() <> String.Empty Then
                Dim arPars As String() = strPars.Split("~")
                Dim strVal As String()
                For xx As Integer = 0 To arPars.GetUpperBound(0)
                    strVal = arPars(xx).Split("=")
                    paraValue.Value = strVal(1)
                    currValue = mRep.DataDefinition.ParameterFields(strVal(0)).CurrentValues
                    currValue.Add(paraValue)
                    mRep.DataDefinition.ParameterFields(xx).ApplyCurrentValues(currValue)
                Next
            End If
            ' Zoom viewer to fit to the whole page so the user can see the report
            Me.crViewer.Zoom(100)
            'Me.crViewer.ShowRefreshButton = False
            Me.crViewer.DisplayGroupTree = False
        Catch crExp As CrystalDecisions.CrystalReports.Engine.LoadSaveReportException
            MessageBox.Show("Error printing Report " & mRepName & System.Environment.NewLine & _
                "The error number is: " & Err.Number.ToString() & System.Environment.NewLine & _
                "The error Message is: " & System.Environment.NewLine & crExp.Message.Trim(), "Print Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch ex As Exception
            MessageBox.Show("Error printing Report " & mRepName & System.Environment.NewLine & _
                            "The error number is: " & Err.Number.ToString() & System.Environment.NewLine & _
                            "The error Message is: " & System.Environment.NewLine & ex.Message.Trim(), "Print Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
 
    End Sub

[blakemckenna]

Gary,

I played around with your previous code and understand it a little better now. I used in my procedure where I call my report. In my CR, I have defined a Parameter field called "ReportType". I know when a parameter field is reference within a CR it's format is ?ReportType. I'm not sure if that needs to be reflected in VB code when creating a parm field. See below

    Public Function CreateSettlementReport() As Exception
        Try
            Dim crSettlement As New rptSettlement
            Dim diskOpts As DiskFileDestinationOptions = ExportOptions.CreateDiskFileDestinationOptions()
            Dim exportOpts As ExportOptions = New ExportOptions()

            Dim pf As New ParameterField
            Dim pdv As New ParameterDiscreteValue

            pf.Name = "ReportType"
            pdv.Value = strReportType     'The value is "D"
            pf.CurrentValues.Add(pdv)

            strSettlementReport = "\Settlement" & strReportDate & ".PDF"

            exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat
            exportOpts.ExportDestinationType = ExportDestinationType.DiskFile

            diskOpts.DiskFileName = strPath & strSettlementReport
            exportOpts.ExportDestinationOptions = diskOpts

            With crSettlement
                .Load(strSettlementReport)
                .SetDataSource(DS)
                .ParameterFields.Add(pf)
                .Export(exportOpts)
            End With

            crSettlement = Nothing
            Return Nothing

        Catch ex As Exception
            strProcedure = "CreateSettlementReport()"
            strMsg = ex.Message
            WriteToErrorFile(strMsg, strProcedure, strProgram, intRecordCount)
            Return ex
        End Try
    End Function

When the highlighted line of code is executed...it errors out with the message

"Not Supported".

I think I'm getting closer.

[GaryMazzone]

In the code I showed I build the paramert set in and other sub and they are passed into the procedure (strPars).

The part that builds the parameters is based on the particular report. Say I have two parameters in the report MyTestRep with the name Text1 and Text2

I build a string (lets call it strTempPars) like this
"Text1='" & someTextHere & "'~Text2='" Someother text here & "'"

No I call my procedure I pass the report name any selection statement (strW) and the parameter string (strPars)
this section of code process the strPars and breaks them down to a pair of text string. (the parameter name and the parameter value)

If strPars.Trim() <> String.Empty Then
                Dim arPars As String() = strPars.Split("~")
                Dim strVal As String()
                For xx As Integer = 0 To arPars.GetUpperBound(0)
                    strVal = arPars(xx).Split("=")
                    paraValue.Value = strVal(1)
                    currValue = mRep.DataDefinition.ParameterFields(strVal(0)).CurrentValues
                    currValue.Add(paraValue)
                    mRep.DataDefinition.ParameterFields(xx).ApplyCurrentValues(currValue)  'This actuall sets the parameter field in CR
                Next
            End If

[blakemckenna]

Gary,

I'm not the best programmer around and unfortunately there just isn't enough code there to help me understand...sorry!

[GaryMazzone]

OK

So strPars = "Text1=I want this is in my First Parameter Place holder on the report~Text2=I Want this in the second parameter"

In CR on the report I add two Parameters Named Text1 as Text and Text2 as Text.

Now the code piece where you apply parameters.

Look at strPars if it is not empty then we start
Dim arPars as String() This will hold the Parameter Name and Value Pairs
Dim strVal As String() This will hold each Pair as we split then and apply then to the report.
arPars = strPars.Split("~") Ok this just split the parameters apart into Pairs of Name and value
strVal = arPars(postion in the array we are wrking with).Split("=") OK we now have splut apart the Name/Value Pair with the parameter Name in strVal(0) and the value to apply in strVal(1)


paraValue and currVal were defined at the beining of the sub
(Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue Dim currValue As New CrystalDecisions.Shared.ParameterValues)

We set the parValue.Value (the stuff we want sent into the report to be displayed) to strVal(1)

paraValue.Value = strVal(1)

We define the parameter field we want to work with and set it to strVal(0) (the parameter name)

currValue = mRep.DataDefinition.ParameterFields(strVal(0)).CurrentValues

Now we add the text to that parameter we just defined

currValue.Add(paraValue)

Now we apply the whole thing to the report

mRep.DataDefinition.ParameterFields(xx).ApplyCurrentValues(currValue)


We enter a loop for 0 to the upperbound of arPars
We now split the Pair apart geting the Parameter Name and the Parameter Value

[blakemckenna]

Ok,

Now it's making a little more sense. But I want to use my code, applying your example. So with the following code the only problem I can see is the highlighted line of code. When I tried entering that line...there doesn't appear to be an "ApplyCurrentValues" Method. I even tried setting a variable as in your example and it still didn't have that Method.

            pf.Name = "ReportType"
            pdv.Value = strReportType
            pf.CurrentValues.Add(pdv)

            strSettlementReport = "\Settlement" & strReportDate & ".PDF"

            exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat
            exportOpts.ExportDestinationType = ExportDestinationType.DiskFile

            diskOpts.DiskFileName = strPath & strSettlementReport
            exportOpts.ExportDestinationOptions = diskOpts

            With crSettlement
                .Load(strSettlementReport)
                .SetDataSource(DS)
                .DataDefinition.ParameterFields(pf, strSettlementReport)
                .ParameterFields.Add(pf)
                .Export(exportOpts)
            End With

[blakemckenna]

Forget the last post Gary. I got it working syntactically speaking anyway.

[blakemckenna]

Gary,

I got the VB portion working (I think...I'm not getting any runtime errors). Now, in my CR, I've defined a Parameter Field named "ReportType". Now, take a look at the 2 screenshots and see if they look right.

Thanks,

[GaryMazzone]

I think so.

[blakemckenna]

I would like to create a Formula field like this:

WhilePrintingRecords;

If {?RecordType} = "S"
    HIDE DETAIL SECTION
End If

But I'm not sure how to write code where I can control the sections.

[GaryMazzone]

If {?RecordType} = "S" Then True Else False

Use the Section Expert Click the Formula button next to Suppress (No Drill-Down). Enter that formula. IF S is passed in the Parameter then The secion is Suppresed (Hidden) other wise the section will show.

[blakemckenna]

Time to party!!!

That worked Gary...thanks for all your help!