Passing Query driven parameter to subreport not working

ccaines
ccaines used Ask the Experts™
on
Hi,

I have an SSRS 08 Report Server I am designing in VS08 and this report has a subreport which takes two dates as parameters (A start date and end date) and returns a table of results.

Firstly, the main report is passed a user selected parameter which defines a date range. For this example, we'll say "Last Month" = 5

The main report has a Dataset which takes this parameter and calculates and returns the first and last date between a particular timespan, and returns those dates. So again, we would feed the 5 in this example to this query and it would return Start_Date, "2010-07-01 00:00:00.000" and End_Date, "2010-07-30 00:00:00.000"

I know this works, as if I run the main report with a textbox returning the results of that query I get the dates expected, so know they're being generated.

The Subreport takes two parameters @SD and @ED, which are unsuprisingly the start and end date of it's query and returns a list of appointments between those dates.

The problem is that when I pass the two dates to the subreport, it returns nothing (or if I disallow blanks/nulls, give the "subreport is missing a parameter" error).

If I test the subreport directly by hardcoding in the date returned by the Main Report query then it works and, most importantly if I pass the parameters with hardcoded values in, again this works, so I know it's not a problem with the Subreport.

In the parameter setting for the Subreport I am using this expression (in the case of SD):

=Format(First(Fields!start_date.Value, "DateRange"), "yyyy-MM-dd HH:mm:ss.FFF")

however, just using =First(Fields!start_date.Value, "DateRange") yields the same results.

Looking for advice as I can't work out why these query results are getting to a textbox I place on the main report, but not into the parameter fields for the subreport.

Many thanks.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

  Try to print parameter values in the subreport whether it is getting the values or not.

Just take 2 labels(header) and display the parameter values. If empty, problem sending from main report.

Author

Commented:
hi Rjavja,

As stated I know there's a problem with them being sent. If I hardcode them in then it works fine, which makes me believe it's not a fault with the subreport.

What I don't know is what that problem is.


Commented:
Hi,

  what I mean is try to display the parameter values and check what value and format. It might be passing values but query doesn't return results!

Or try sql profiler a nd check what query it is executing...
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
I see what you mean. If I show the parameters in the subreport, they are empty .. nothing is being sent to it.

I'll take a look at profiler, but I've not used it extensively enough to know what i'm doing with it.

Commented:
If parameters are empty, check whether you are mapping the correct values to the parameters.

instead of using field values, pass directly current report parameters to sub-report's parameter by seeting the value

Parameters!StartDate.Value
instead of
First(Fields!start_date.Value, "DateRange")

Author

Commented:
karthitron - Unfortunately I can't do that, there is no parameter to pass to them, it's the results of a query. When you send the main report the parameter "5", it runs it through a Dataset which returns the start_date and end_date values, which I need to pass onto the subreport.

Ok, after some more extensive testing, the fault is caused exclusively by using this Expression in the Subreport parameter properties:

=Format(First(Fields!start_date.Value, "DateRange"), "yyyy-MM-dd HH:mm:ss.FFF")

And this information is empty, as I get the error "(Instance: 30iS4): One or more parameters were not specified for the subreport, 'Subreport1'"  

However, if I use exactly that Expression in a text box on the main report, it returns the date as expected, so that expression is returning data, it is simply not being passed onto the Subreport when it is used in the parameter properties.

I've attached an image of what I'm using and where for clarity.
pexample.JPG
ok, I found the reason, if we use First() method, it will return the dataset's first records value. So to pass current record's value use Fields!start_date.Value property. This is working in my system properly with this property instead of using First() method.

Author

Commented:
If I try to do that it'll return the error (quite rightly)

"The Value expression for the subreport ‘Subreport1' refers to the field ‘end_date’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope."

As it's a subreport setting, it's not linked to any dataset (nor can it be like other items).

I've gone for another fancy photoshop to see if I can explain any clearer the sequence of events.


pexample2.JPG
Sr. Sw Eng
Commented:
If DateRange is the Data-set that get returns in main report,
then create one Table in body of main report.
Set its dataset to "DateRange".
then add this sub-report inside the cell. Then set the report parameter directly.
You does not require to format it via expression.
You are making this thing way to hard.
If you are using a begin and end date parameter in your main report.
Then you have to pass the same parameter to the subreport This will give you the same answer as what you are trying to do from your statements above.

Author

Commented:
planocz - I'm afraid I don't understand your suggestion.

I'm not using a begin and end date parameter in my main report, just a single parameter (date_limit_code) to define which period we want to look at (i.e. last month = 5, this month = 6, next three months = 7 etc..)

This is then passed to the DateRange dataset to calculate the begin and end dates of that period. I then want to pass these two dates to my subreport(s), so it can work with them.

I could, and do, pass the date_limit_code parameter to my subreport, however this means I have to have a dataset in each subreport which calculates the begin and end date every time, something which seems very inefficent if I'm only need to work it out once on the Main report.

Author

Commented:
Thanks astroutkarsh, that works perfectly.

Still at a loss as to why it doesn't work the other way but that's something to ponder.

Many thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial