MI2Wheels

I'm having a problem with a linked subreport that I can't get around, so I'm going to finally throw up the flag and ask for help.

I'm using VS.NET 2005, and the Crystal Report was originally written in 8.5, but has been upgraded to CR 10 via the Crystal Reports developer edition.

I have a windows service that check a report table for a run flag, and when it finds the flag, loads the report into a ReportDocument object (the UNC path of the .rpt file is passed into the class):

Code Snippet

Public Sub OpenReport()

Dim crConnectionInfo As ConnectionInfo

Dim crSection As Section

Dim crSections As Sections

Dim crReportObject As ReportObject

Dim crReportObjects As ReportObjects

Dim crSubreportObject As SubreportObject

Dim subRepDoc As New ReportDocument()

Dim crTables As Tables

Dim crTable As Table

''crReportDocument.FilePath = myReportLocn

crReportDocument.Load(myReportLocn)

'Setup the connection information structure to be used

'to log onto the datasource for the report.

crConnectionInfo = New ConnectionInfo()

With crConnectionInfo

.ServerName = myReportDBServer

.DatabaseName = myReportDBName

If myReportDBUID.Trim.Length > 0 Then

.IntegratedSecurity = False

.UserID = myReportDBUID

.Password = myReportDBPwd

Else

.IntegratedSecurity = True

End If

.Type = ConnectionInfoType.SQL

End With

crTables = crReportDocument.Database.Tables

For Each crTable In crTables

Dim myTableLogonInfo As TableLogOnInfo = crTable.LogOnInfo

myTableLogonInfo.ConnectionInfo = crConnectionInfo

crTable.ApplyLogOnInfo(myTableLogonInfo)

If crTable.Location.IndexOf("Proc(") > -1 Then

crTable.Location = crConnectionInfo.DatabaseName & "." & myReportDBObjectOwner & "." & crTable.Location.Substring(crTable.Location.IndexOf("Proc(") + 5, crTable.Location.LastIndexOf(";1)") - 5)

Else

crTable.Location = crConnectionInfo.DatabaseName & "." & myReportDBObjectOwner & "." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)

End If

crTable.LogOnInfo.ConnectionInfo.ServerName = crConnectionInfo.ServerName()

myTableLogonInfo = Nothing

Next

crSections = crReportDocument.ReportDefinition.Sections

For Each crSection In crSections

crReportObjects = crSection.ReportObjects

For Each crReportObject In crReportObjects

If crReportObject.Kind = ReportObjectKind.SubreportObject Then

'If you find a subreport, typecast the reportobject to a subreport object

crSubreportObject = CType(crReportObject, SubreportObject)

'Open the subreport

subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

'crDatabase = subRepDoc.Database

crTables = subRepDoc.Database.Tables

'Loop through each table and set the connection info

'Pass the connection info to the logoninfo object then apply the

'logoninfo to the subreport

For Each crTable In crTables

Dim myTableLogonInfo As TableLogOnInfo = crTable.LogOnInfo

myTableLogonInfo.ConnectionInfo = crConnectionInfo

crTable.ApplyLogOnInfo(myTableLogonInfo)

If crTable.Location.IndexOf("Proc(") > -1 Then

crTable.Location = crConnectionInfo.DatabaseName & "." & myReportDBObjectOwner & "." & crTable.Location.Substring(crTable.Location.IndexOf("Proc(") + 5, crTable.Location.LastIndexOf(";1)") - 5)

Else

crTable.Location = crConnectionInfo.DatabaseName & "." & myReportDBObjectOwner & "." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)

End If

crTable.LogOnInfo.ConnectionInfo.ServerName = crConnectionInfo.ServerName()

myTableLogonInfo = Nothing

Next

End If

Next

Next

crConnectionInfo = Nothing

crSection = Nothing

crSections = Nothing

crReportObject = Nothing

crReportObjects = Nothing

crSubreportObject = Nothing

subRepDoc = Nothing

crTables = Nothing

crTable = Nothing

End Sub

Once the report has been loaded, parameters are passed in via another SQL table and this routine is run to export the report to a PDF file:

Code Snippet

Public Sub CreateReport(ByVal TerminalList As String)

Dim crMainReportParameterFieldDefinitions As ParameterFieldDefinitions

Dim crParameterFieldDefinitions As ParameterFieldDefinitions

Dim crParameterFieldDefinition As ParameterFieldDefinition

Dim crParameterDiscreteValue As ParameterDiscreteValue

Dim crParameterValues As ParameterValues

Dim drReportParamList As DataRow

Dim mySQLConn As New SqlConnection(My.Settings.ConnectStringSQL)

Dim cmdReportParamList As New SqlCommand("usf_GetReportParameters", mySQLConn)

With cmdReportParamList

.CommandType = CommandType.StoredProcedure

.Parameters.Add(New SqlParameter("@ParameterID", SqlDbType.Int))

.Parameters("@ParameterID").Value = ParameterID

End With

'' create the DataAdapter

Dim myDataAdapter As New SqlDataAdapter(cmdReportParamList)

'' create the DataSet

Dim dsReportParamList As New DataSet

'' fill the DataSet

myDataAdapter.Fill(dsReportParamList)

'' close the connection

mySQLConn.Close()

dsReportParamList.Dispose()

myDataAdapter.Dispose()

crParameterFieldDefinitions = crReportDocument.DataDefinition.ParameterFields

Dim dtReportParamList As DataTable = dsReportParamList.Tables(0)

For Each drReportParamList In dtReportParamList.Rows

crParameterFieldDefinition = crParameterFieldDefinitions.Item(drReportParamList.Item("ParameterOrder") - 1)

crParameterDiscreteValue = New ParameterDiscreteValue()

Select Case drReportParamList.Item("ParameterTypeID").ToString

'' string

Case "1" : crParameterDiscreteValue.Value = drReportParamList.Item("ParameterValue").ToString

'' date offset (past)

Case "2" : crParameterDiscreteValue.Value = Format(DateAdd(DateInterval.Day, drReportParamList.Item("ParameterValue") * -1, Date.Now), "MM/dd/yyyy")

'' date offset (future)

Case "3" : crParameterDiscreteValue.Value = Format(DateAdd(DateInterval.Day, drReportParamList.Item("ParameterValue"), Date.Now), "MM/dd/yyyy")

'' current terminal

Case "4" : crParameterDiscreteValue.Value = TerminalList

End Select

crParameterValues = crParameterFieldDefinition.CurrentValues

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

gLogFile.LogEvent(crParameterFieldDefinition.Name.ToString + " value set to <" + crParameterDiscreteValue.Value.ToString + ">")

Next

If File.Exists(DiskFileName) Then

File.Delete(DiskFileName)

End If

crReportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, DiskFileName)

gLogFile.LogEvent("Report " & DiskFileName & " created.")

cmdReportParamList.Dispose()

crParameterFieldDefinitions.Dispose()

crParameterFieldDefinition.Dispose()

crParameterDiscreteValue = Nothing

crParameterValues = Nothing

drReportParamList = Nothing

dtReportParamList.Dispose()

End Sub

For most of what I do, this works great, but...

I have one report that has a linked subreport in the group header of the main report. The subreport is linked on the value of the group field rather than a parameter of the main report. So rather than having the main report parameter linked to a sub-report parameter (i.e. Field(s) to link to: @QueryDate and Subreport parameter field to use: @QueryDate), the link is Field(s) to link to: ReportSegArrLateDetail;1.SegDest and the Subreport parameter field to use is @SegDest.

When I run the report, I get the error:

CrystalDecisions.ReportAppServer.DataSetConversion

at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)

at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.Export(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToDisk(ExportFormatType formatType, String fileName)

at CrystalReporting.CRReport.CreateReport(String TerminalList) in C:\Temp\CrystalReporting\CrystalReporting\CRReport.vb:line 396

at CrystalReporting.Form1.Button1_Click(Object sender, EventArgs e) in C:\Temp\CrystalReporting\CrystalReporting\Form1.vb:line 108

Boolean ThrowDotNetException(System.Exception)

Query Engine Error: 'ADO Error Code: 0x

Source: Microsoft OLE DB Provider for SQL Server

Description: Procedure or function 'ReportSegArrLateDetailSummary' expects parameter '@SegDest', which was not supplied.

SQL State: 42000

Native Error: '

Error in File C:\DOCUME~1\kbullen\LOCALS~1\Temp\ArrivalPatterns_NET_10 {6247521A-AD81-45F1-8C3B-B23DD6CD83D5}.rpt:

Query Engine Error

This is the stored procedure (ReportSegArrLateDetailSummary) for the sub report. It's like Crystal is not passing the parameter to the sub report.

If I try changing the subreport link to for the parameter in question to: ReportSegArrLateDetail;1.SegDest and the Subreport parameter field to use is Pm-ReportSegArrLateDetail;1.SegDest and check the Select data in subreport based on field and select the ReportSegArrLateDetail;1.SegDest field from the drop down box, I get this error:

CrystalDecisions.ReportAppServer.DataSetConversion

at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)

at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.Export(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToDisk(ExportFormatType formatType, String fileName)

at CrystalReporting.CRReport.CreateReport(String TerminalList) in C:\Temp\CrystalReporting\CrystalReporting\CRReport.vb:line 396

at CrystalReporting.Form1.Button1_Click(Object sender, EventArgs e) in C:\Temp\CrystalReporting\CrystalReporting\Form1.vb:line 108

Boolean ThrowDotNetException(System.Exception)

Missing parameter values.

The funny thing is, the report currently works fine under VB6, CR 8.5.

Does anyone have any ideas how to get this to work

Thanks,

Kevin

kevin.bulllen@_removethisjunk_usfc.com



Re: Crystal Reports for Visual Studio Crystal Reports.NET - Linked subreports

MI2Wheels

Wow,

Lots of lookers but no takers.

Fixed it by upgrading to CR XIR2. Once I installed the upgrade and upgraded the project, worked like a charm.

I love Crystal Reports.





Re: Crystal Reports for Visual Studio Crystal Reports.NET - Linked subreports

captJackSparrow

I too love CR but i dont think it worth the price BO ppl are selling it for, its too much.

They say it can do anything but still we are using workaround Big Smile for thing that are required in real time requirements.