Hoping someone can help figure out what I am doing wrong.
I have a form that has combobox controls to allow the user to select PROGRAM and SHIP. These are stored in variables - me._rptProgram and me._rptShip. The user also selects a report name from a combobox control. This populates me._reportName. These are being successfully populated. No problem here
I have a "ViewReport" function that receives the location and name of my .rpt file along with the parameter names and values. When I step through this code - all variables, etc are being correctly populated
Friend Function ViewReport(ByVal sReportName As String, Optional ByVal sSelectionFormula As String = "", _Optional ByVal param As String = "") As Boolean
Dim retval As Boolean = True
'to hold the number of parameters in the report definition for the report name passed 'in by the caller
Dim intCounter As Integer = 0
'to hold which table (stored procedure) currently
'looking at for subreport(s) (if report has any subreports)
Dim intCounter1 As Integer = 0
'create a new Crystal Reports report document object
Dim objReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
'create a logon info object for the report
Dim conInfo As New CrystalDecisions.Shared.TableLogOnInfo
'parameter value object of report
'parameters used for adding the value to parameter
Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue
'current parameter value object (collection) of cyrstal report parameters
Dim currValue As CrystalDecisions.Shared.ParameterValues
'sub report of this Crystal Report
Dim mySubReportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject
'sub report document of this Crystal report
Dim mySubRepDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
'to hold the parameters passed in by the caller
Dim strParValPair() As String
'to hold the "current" parameter, used to process parameters one at a time
Dim strVal() As String
'to hold which parameter are we currently looking at and
'also used to hold which subreport section we are looking at
'(later in the code)
Dim index As Integer
Try
'load the report
objReport.Load(sReportName)
'check if there are parameters in the report
intCounter = objReport.DataDefinition.ParameterFields.Count
'Because parameter fields collection also picks the selection
'formula which is NOT a parameter, if total parameter
'count = 1, then we check whether it is a parameter or
'selection formula. If selection formula, set count to 0 (no parameters)
If intCounter = 1 Then
If InStr(objReport.DataDefinition.ParameterFields(0).ParameterFieldName, ".", CompareMethod.Text) > 0 Then
intCounter = 0
End If
End If
'if there are parameters in report (as designed) and user has passed them
'when calling this function, then split the parameter string and
'apply the values to the concurrent parameters
If intCounter > 0 And param.Trim <> "" Then
strParValPair = param.Split("&")
For index = 0 To UBound(strParValPair)
If InStr(strParValPair(index), "=") > 0 Then
strVal = strParValPair(index).Split("=")
paraValue.Value = strVal(1)
currValue = objReport.DataDefinition.ParameterFields(strVal(0).ToString.Trim).CurrentValues
currValue.Add(paraValue)
objReport.DataDefinition.ParameterFields(strVal(0)).ApplyCurrentValues(currValue)
End If
Next
End If
'set the connection information to ConInfo object
'so we can apply the connection information on each
'sp in the report
conInfo.ConnectionInfo.UserID = <substitute real user id here>
conInfo.ConnectionInfo.Password = <substitute real password here>
conInfo.ConnectionInfo.ServerName = <substitute real ServerName here>
conInfo.ConnectionInfo.DatabaseName = <substitute real DatabaseName here>
For intCounter = 0 To objReport.Database.Tables.Count - 1
objReport.Database.Tables(intCounter).ApplyLogOnInfo(conInfo)
Next
'loop through each section on the report then look through each object
'in the section, if the object is a subreport, then apply logon info
'on each sp of the sub-report
For index = 0 To objReport.ReportDefinition.Sections.Count - 1
For intCounter = 0 To objReport.ReportDefinition.Sections(index).ReportObjects.Count - 1
With objReport.ReportDefinition.Sections(index)
If .ReportObjects(intCounter).Kind = ReportObjectKind.SubreportObject Then
mySubReportObject = CType(.ReportObjects(intCounter), CrystalDecisions.CrystalReports.Engine.SubreportObject)
mySubRepDoc = mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
For intCounter1 = 0 To mySubRepDoc.Database.Tables.Count - 1
mySubRepDoc.Database.Tables(intCounter1).ApplyLogOnInfo(conInfo)
Next
End If
End With
Next
Next
'if there is a selection formula passed to this function
'then use it
If sSelectionFormula.Length > 0 Then
objReport.RecordSelectionFormula = sSelectionFormula
End If
'Reset the control
CrystalReportViewer1.ReportSource = Nothing
'set the current report object to the viewer
CrystalReportViewer1.ReportSource = objReport
'Show the report
CrystalReportViewer1.Show()
Catch ex As Exception
<stuff will be here>
End Try
Return retval
End Function
Here is a piece of the code that calls this function. I only have one report so far, so the rest of the sub is skeleton and will fill in as I figure out where I've gone wrong.
Private Sub cmdRptExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRptExecute.Click
Try
Select Case Me._reportName
Case "Not Qcd"
Case "Requested vs Actual"
Me.ViewReport("C:\Projects\PriyaDNEHelper\Projects\PriyaDNEHelper\Reports\InspReqVsActual.rpt", , "@Program=" & Me._rptProgram.Trim & "&" & "@Ship=" & Me._rptShip.Trim)
Case "Summary By Est"
Case "Summary By Variety"
Case Else
MessageBox.Show("Please select a report")
End Select
Catch ex As Exception
<stuff will be here>
End Try
End Sub
Here is the code for the stored procedure which returns the data for the first report I am currently testing. I have verified that the sp returns the correct data as I change the values of the parameters, testing where each parameter has a value and also null. All possible combinations of values/null for parameters are returning the correct desired results.
CREATE PROCEDURE dbo.DNE_InspRpt_ReqvsActual(
@Program VARCHAR(2) = NULL,
@Ship VARCHAR(50) = NULL
)
AS
<code details left out to save room>
Finally, I have the design of the report itself. I have to admit here, I am NOT a fan of visually designed stuff, I prefer CODE. Having said that, here is what I did to design the report:
Project....Add New Item....Crystal Report <fill in correct name of report>.....Click ADD button. Using Report Wizard is selected and Standard is selected....Click OK button. Under Available Data Sources I open the name of the Database Server and expand the name of the Database and expand dbo and expand Stored Procedures and SELECT the name of the stored procedure that I want. I click the > button to put that stored procedure under the Selected tables list and click the NEXT button. The names of the two parameters are correctly populated in the "Enter Parameter Values" dialog box. Set To NULL value is automatically selected. (I have tried BOTH leaving it selected and unselecting and I don't get any different result, currently, this is selected for my report). I click the OK button. Then click the NEXT button. I transfer all Available Fields to the Fields to Display box and click the NEXT button. I group by Ship and Exporter and click the NEXT button. I remove the SUM fields as they are not relevant to this report and click the NEXT button. For the FILTERING I have tried not selecting any fields and selecting the Program and Ship field. Either way, same final result, so current report has Nothing selected. Click the NEXT button. Select any report format and click the FINISH button.
Now, here's what happens when I "run" the report. I select the Program, Ship, and Report Name from the appropriate drop downs. I click the command button and step through the code. As I step through the ViewReports I am verifying that the parameters seem to be setting up correctly with the name and values. So far so good. However, when the report displays in the control, it shows ALL the data, not just the data for the selected parameter values.
Now, I make a change to the report itself in design view. I have read that I need to do one more step in the design of the report. So...I use the SELECT EXPERT button and set up a "Record Selection" formula where the resulting expression is as follows {nameOfStoredProdedure:1.Program}={?@Program} and {nameOfStoredProcedure:1.Ship}={?@Ship}. Now, I run the program again and make my Program, Ship, and report Name selections, click the command button to run the report and THIS TIME I get an Exception with the following:
"This field name is not known. Error in File C:\<location>\<nameOfReport>.rpt: Error in formula <Record Selection>. {<nameOfStoredProdedure;1.Program}={?@Program} and .....This field name is not known. " I have Googled for a day and a half with none of the results helping.
Can anyone tell me where I have gone wrong? Thanks in advance for possible solution/suggestion.
Most humbly,
Patricia
Edited by NorrisPC - 14 Apr 2011 at 9:03am
IP Logged
