Passing parameter values to multiple sub-reports based on command objects

[nscheaffer]

I would like to create a report that contains 4 sub-reports all with the same date range criteria values.  How to I pass the parameter values to the sub-report and then into the command object of each sub-report?

Thanks,

Noel

[abhilash_kumar · Accepted solution]

Hi Noel,

If you're trying to pass static date values to the 'from' and 'to' parameters in the Command Object, you can create two separate formulas in the Main Report.

@From_Date:

cdate(2012,01,31)

@To_Date:

cdate(2013,03,14)

You then need to right-click each Subreport > Select Change Subreport Links > Move the From_Date formula field to the "fields to link to" pane on the right and then from the drop-down that says "Subreport Parameter field to use" choose the 'from' parameter in the Subreport.

Do the same for the To_date field as well.

If you have a Date-range parameter in the Main report instead and you wish to pass this into the Subreport, then you need to make sure the Subreport has separate parameters for the 'from' and 'to' dates. Since this is a Command Object, I'm sure they are separate.

The From Date formula in the Main report in this case would be like this:

Minimum({?Date_range_parameter})

Whereas, the 'To' date formula would be like this:

Maximum({?Date_range_parameter})

You can then use the Change Subreport links dialog box to pass both these fields into the Command Object parameters.

Hope this helps!

-Abhilash

[nscheaffer · Accepted solution]

I am still having trouble making sense of this.  Let me try and explain my situation more fully.

I have two parameters that I want to be able to input when I run the report.  They are StartDateOffset and EndDateOffset.  They each represent the number of days to go back from the current date for my  @StartDate and @EndDate SQL variables.  The reason I am doing it like this is that I want to be able to schedule this report.

I want to be prompted for those offset values once and pass them into each of the four sub-reports which are each based on a command object.  Here is how the beginning portion of the SQL of the command object for each of my sub-reports looks...

DECLARE @StartDate DATE,
                @EndDate DATE
SET @StartDate = DATEADD(dd, {?StartDateOffset}, GETDATE())
SET @EndDate = DATEADD(dd, {?EndDateOffset}, GETDATE())

The Change Subreport links... dialog box is absolutely perplexing to me.

What selections should I be making in the bottom portion?

Noel

[abhilash_kumar · Accepted solution]

At the bottom left where it says "Subreport parameter field to use", choose the ?StartDateOffset parameter from the drop-down. This is the parameter from the Subreport's datasource.

The drop-down will list all the parameters with similar datatypes as the field/parameter in the "Fields to link to" pane.

-Abhilash

[nscheaffer · Accepted solution]

Ok, I think I figured it out.  It seems that you cannot pass a parameter value directly through to the command object of a sub-report.  Instead I had to create StartDate and EndDate variables in my main report base on my parameter values as follows...

StartDate ==> CDate(ToText(DateAdd("d", {?StartDateOffset}, CurrentDate), "MM/dd/yyyy"))

EndDate ==> CDate(ToText(DateAdd("d", {?EndDateOffset}, CurrentDate), "MM/dd/yyyy"))

I then changed the queries in those SQL in the command objects of the sub-reports to use the start and end dates rather than recalculating them in each sub-report based on the offset values.  Here is what my Change Subreport Links dialog looks like now...

Things are working as I had hoped.

Thanks Abhilash!

[abhilash_kumar · Accepted solution]

Nice! Glad I could help Noel.

Have a great day.

-Abhilash