Link to home
Start Free TrialLog in
Avatar of cueshot
cueshotFlag for United States of America

asked on

Crystal Reports: Programmatically setting parameter values in a subreport

Hi all,

Tech specifics: Visual Studio 2008, Crystal Reports 10, Visual Basic, ASP.NET 3.5

I've been searching here and there for a definite solution for my problem and have yet to find it. I have a report designed via Visual Studio. This report consists of the main report and 2 subreports. The main report and each subreport retrieve data from a stored procedure. The main report and each subreport use the exact same parameters (7 parameters). The values of all parameters, with the exception of one, would be given to the main report and passed to both subreports. The main report and each subreport retrieve data from the same stored procedure. This stored procedure returns 3 recordsets. I use a "@RecordSet" input parameter in the stored procedure to indicate which recordset I want returned. This is the one parameter that is different between the main report and each subreport.

I have the appropriate parameters in each subreport linked to the corresponding parameters in the main report via "Change Subreport Links..." option, with the exception, of course, of the @RecordSet parameter. This works perfectly in the Main Report Preview in Visual Studio. I give values of the common parameters once and the appropriate flag value for the @RecordSet parameter for main report and each subreport. The preview displays the appropriate data for the main report and the subreports, exactly the way I want it. At runtime, however, different story.

When the report attempts to load, I get the "Missing parameter values." error.

I have given my VB code below. I have also tried rdo.SetParameterValue("@RecordSet", 2, "Detentions") in place of rdoDetentions.DataDefinition.ParameterFields("@RecordSet").CurrentValues.AddValue(2) (same for "Comments") with the same results. What am I missing or doing wrong?

I would very much appreciate any help I can get with this.

Thanks in advance
Dim sFile As String = "crpdf" & Date.Now.Ticks.ToString & ".pdf"
 
Dim rdo As New ReportDocument()
rdo.Load(Server.MapPath("~/Reports/ShiftReport.rpt"))
 
Dim rdoDetentions As New ReportDocument()
rdoDetentions = rdo.OpenSubreport("Detentions")
 
Dim rdoComments As New ReportDocument()
rdoComments = rdo.OpenSubreport("Comments")
 
' Set the DB source for main report and each subreport
SetCrystalDBSource(rdo)
SetCrystalDBSource(rdoDetentions)
SetCrystalDBSource(rdoComments)
 
If Not IsDate(sStart) Then
  sStart = "01/01/1970"
End If
 
If Not IsDate(sEnd) Then
  sEnd = "01/01/1970"
End If
 
' Set main report params
rdo.SetParameterValue("@Start", sStart)
rdo.SetParameterValue("@End", sEnd)
rdo.SetParameterValue("@Location", sLocation)
rdo.SetParameterValue("@Voyage", sVoyage)
rdo.SetParameterValue("@Vessel", sVessel)
rdo.SetParameterValue("@ViewAndEmail", 0)
rdo.SetParameterValue("@RecordSet", 1)
 
' Set Detentions subreport @RecordSet param
rdoDetentions.DataDefinition.ParameterFields("@RecordSet").CurrentValues.AddValue(2)
 
' Set Comments subreport @RecordSet param
rdoComments.DataDefinition.ParameterFields("@RecordSet").CurrentValues.AddValue(3)
 
' Export to PDF
CrystalToPDF(rdo, sFile, sFormat) ' sFormat determined by user; could be Excel or PDF

Open in new window

SOLUTION
Avatar of mdougan
mdougan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
forgot a line that would probably go under the first line at the top of the procedure...

          rpt = New MyMainReport

Avatar of cueshot

ASKER

Thanks for your reply! I'll consider what you're suggesting. However, based on your reply I suppose I should add a couple of tidbits.

In my case, the subreports are not pre-designed as rpts. I'm designing each subreport as part of the design of the main report. But I think the principle in what you're saying about designing them separately is the same.

I'm not using a the viewer control, but are you suggesting by your code that when using your suggestion I should for a specific purpose?

The recordset for the main report has a mixture of static and dynamic columns (a subset of the columns will have a variable number of columns). This I know will be another issue I will have to address. Cross-tabs may be an option for that, but I was going to take care of that after I got this subreport issue resolved. Point is, though, I'm thinking that throws a wrench in your suggestion.

I appreciate your suggestion, and it's a good one. It does give me another angle I can look at for accomplishing the task at hand. But, it seems to me I should be able to simply set the values on the subreports in the same manner as I would the main report. If I can just solve that problem, then I can move on to dealing with the dynamic columns in the main report.

Any other thoughts?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cueshot

ASKER

It's all food for thought. I will try this new sample code you've provided when I'm back in the office Monday, and respond then with a yea or nay. Thanks again for your response.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cueshot

ASKER

Unfortunately, it's a nay for both of your last 2 snippets. The syntax is no longer valid for the first one, although I tried something similar, so instead of:

rdoDetentions.ParameterFields("@RecordSet").AddCurrentValue (2)

I tried

rdoDetentions.ParameterFields("@RecordSet").CurrentValues.AddValue(2)

I get a Not Supported exception with that.

With the second one I tried adding the parameters to the subreport's ParameterFields instead of to a viewer parameter fields (since I'm not using a viewer) and I get the Not Supported exception there as well.

It appears the only way to add or set a value in a subreport is through the DataDefinition as I was doing originally. I do not get a Not Supported exception then. Instead I get the "Missing parameter values" error. Apparently I'm missing something there. There must be something else I need to do to truly set the value to be used appropriately by the subreport.

Avatar of cueshot

ASKER

I'm afraid your first suggestion is not working for me either. Here's my code now:

Dim DS As New DataSet
Dim Conn As New SqlConnection(GetConnectionString)
Dim Cmd As New SqlCommand("dbo.UP_rpt_ShiftReport", Conn)

If Not IsDate(sStart) Then
      sStart = "01/01/1970"
End If
If Not IsDate(sEnd) Then
      sEnd = "01/01/1970"
End If

Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.AddWithValue("@Start", sStart)
Cmd.Parameters.AddWithValue("@End", sEnd)
Cmd.Parameters.AddWithValue("@Location", sLocation)
Cmd.Parameters.AddWithValue("@Voyage", sVoyage)
Cmd.Parameters.AddWithValue("@Vessel", sVessel)
Cmd.Parameters.AddWithValue("@ViewAndEmail", 0)
Cmd.Parameters.AddWithValue("@RecordSet", 0)

Conn.Open()

Dim SA As New SqlDataAdapter
SA.SelectCommand = Cmd
SA.Fill(DS)

If Conn.State = ConnectionState.Open Then
      Conn.Close()
End If

Cmd.Dispose()
Conn.Dispose()

Dim sFile As String = "crpdf" & Date.Now.Ticks.ToString & ".pdf"

Dim rdo As New ReportDocument()
rdo.Load(Server.MapPath("~/Reports/ShiftReport.rpt"))

Dim rdoDetentions As New ReportDocument
rdoDetentions = rdo.OpenSubreport("Detentions")

Dim rdoComments As New ReportDocument
rdoComments = rdo.OpenSubreport("Comments")

rdo.SetDataSource(DS)
rdo.Database.Tables(0).SetDataSource(DS.Tables(0))

rdoDetentions.SetDataSource(DS)
rdoDetentions.Database.Tables(0).SetDataSource(DS.Tables(1))

rdoComments.SetDataSource(DS)
rdoComments.Database.Tables(0).SetDataSource(DS.Tables(2))

CrystalToPDF(rdo, sFile, sFormat)

I no longer get the "Missing parameter values" error, though. The report does load, but only the main report gives me data. I have confirmed that with the test criteria I'm using some data is returned from the stored proc in all recordsets. The subreports are not displaying any of their data. I have also confirmed that the column names in DS.Tables are exactly what they should be and match the fieldnames in the main report and subreports.

The more and more I try all these different things, not just stuff you've suggested, the more it appears this is just not gonna work in any way. But there's got to be some way to do it!!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It sounds like in the last thing you've tried is the original technique I suggested.  If so, make sure that you remove any sub-report parameter linking that you may have set-up in previous tries... I think there is some option that you set that says this sub-report will be linked to the main report by this particular field.

Check the output of all of the select to see if there are any NULL values being returned... sometimes Crystal will blank out an entire section if it encounters a NULL value.

That technique, setting the individual datasource for each report and bypassing Crystal's internal parameters is the way I do all my reports and I know it works well.
Avatar of cueshot

ASKER

On that last snippet, I can't make out what mcolSubreports is. Even reading through the thread, I can't. The SubReports property of ReportDocument does not have a Values property.

As for your last reply, I have confirmed that there are no NULLs in the returned data. I have also removed all linkage between the reports. I even just removed the subreports and re-added them and did not touch the linking part. I still get data in only the main report displayed. Subreports are not displaying data at all.
mcolSubreports appears to be a collection of SubreportItems, but I'm unfamiliar with what that is.  At first, I thought it was unimportant, because it looked like they were just using it to get the subreport name to open the sub-report.  And, if you hard-coded the name in your case, you wouldn't have to bother... however, I see that they are doing an lSubreport.Values.Add(lpdValue) at the end of that... I know when I've added parameter values to a subreport, I did it to the object that was in the Open statement... so, I would have expected:

 crSubReportDoc.Values.Add(lpdValue)    

though, I'm pretty sure in my case there was a word parameter in there somewhere.

Whenever I'm trying to find out the proper syntax for something I can't find a sample for, I use the Object Browser extensively to see what properties and methods are available in the objects I'm working with... so, in your case, check out the properties for ReportDocument to see if there is any sort of parameters collection.  Or, maybe you'll find something in the object browser hierarchy at the same level as ReportDocument called something like SharedParameters. It would also be a good idea to lookup the properties of that SubreportItems object.

What I do is look at any possible property or method that might be involved, then I google that and I typically can find sample syntax.

I know I've had problems with sub-reports not displaying too, which was unrelated to the data, but I'm going to have to ponder it for a while to see if I can remember what the reason was.  NULLs was one of the reasons, but I think there were others.

One thing you can try, is to run the individual subreports as a stand-alone report just to see if you can get it to appear with all of the data... then, at least you'll know that the subreport is working on its own.

Other things to check, see if the section where you are placing the subreport has any sort of conditional display formula... such as surpress if xyz = false.  And/or check the section paging format... will the section with the subreport start a new page before etc.

I'm slowly running out of other ideas!
One other thing, I see you're going directly out to PDF... if you can, try to display the reports, or write them out to a different format such as Excel...

One thing I've found is that the PDF export will sometimes show blank if it doesn't like the font that you used in that subreport!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad to hear it!  No worries, have them close the question and refund your points.  You might have them archive it, though, as opposed to deleting it, as your solution will be helpful for someone else.

Cheers!
Mike