Passing Query driven parameter to subreport not working
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
however, just using =First(Fields!start_date.V
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.
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
however, just using =First(Fields!start_date.V
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.
ASKER
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.
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.
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...
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...
ASKER
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.
I'll take a look at profiler, but I've not used it extensively enough to know what i'm doing with it.
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.Va
Parameters!StartDate.Value
instead of
First(Fields!start_date.Va
ASKER
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
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, after some more extensive testing, the fault is caused exclusively by using this Expression in the Subreport parameter properties:
=Format(First(Fields!start
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.
ASKER
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
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
ASKER
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.
Still at a loss as to why it doesn't work the other way but that's something to ponder.
Many thanks.
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.