Subreports not accepting parameters as expected
-
Alright, short form, long story:
I've been arguing with the fact that SSRS doesn't allow direct WYSIWYG development of a repeating section, so I've turned to heavy subreporting. After abusing my way out of newbie-ville, primarily driven by the fact that I haven't touched SSRS in over 2 years, I've come to the following dilemma.
I have a 3 layer report setup. Layer 1 is the primary report and simply returns a list of the primary key as its dataset to send down to the subreport. Layer 1 has a table attached to this dataset and includes 3 columns during testing but will eventually just have one, the subreport.
The two fields consisting of my key are returned from the procedure (all data being pulled is setup as procedures in the data tab) are INT and DATETIME, respectively. It's a repeated identity from the origination table and an audit date to isolate the row.
Now, this ships down to the subreport both of these columns. After banging my head on SSRS 2k5 and its removal of milliseconds in datetime parameters/pieces (joy!) which screwed me up awhile, everything's working as strings in the parameters for the datetimes.
This subreport has another subreport that goes and gets further data. Layer 1-2 is the part that's malfunctioning. I've got layer 2 and 3 behaving themselves and I can get Layer 2 and it's subreport (layer 3) to behave just fine when I enter in parameters directly for layer 2's preview.
The values in question are 356949 for the INT and 2012-05-22 17:15:53.023 for the Datetime (string Parameter). This passes just fine from 2 to 3.
I've confirmed that Layer 2's parameters (in Report-Report Parameters) are set as INT and String.
Now, when I try to get Layer 1 to cough up the subreport, the parameters are failing the passthrough. I've even tried to directly input a set of parameters:
I'm at the end of my wits here with this thing, and am getting to more dakka territory. I've tried forcing the datetime through as a string with =FORMAT() and that flailed miserably. To wit, with those exact settings directly entered as parameters to Layer 2, Layer 2 and 3 work just fine. Layer 1 I cannot get to talk to it, I keep getting the error that we're not returning rows for the data set.
As a tertiary note, if I default the values in the subreport, it'll appear just fine when called from Layer 1, but it will always use the default and not use the passed values. Not defaults in the parameter settings but when I originally set this up calling the proc as a string call with default values in the parameter pass in the data tab. (IE: EXEC proc @p1 = 123, @p2 = '1/1/1900' vs. just proc)
Ignoring the fact that I'm going for beauty over optimization (I'm well aware I'm going to be calling a proc for EVERY row in Layer 2 and 3), what the heck am I doing wrong here?
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
I've created my own workaround but I still don't know what the problem was with the direct entry parameters above.
My workaround was to alter the PK generating script in Layer 1 to kick the AuditTimestamp out as a converted varchar string (type 121 for ODBC canonical) and it flows through just fine. Still don't know why the parameter pass was busted though.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
I don't have the answer, but did you try outputing the 1->2 values on report 1 to verify you are getting the results expected and display the parameter on report 2 to verify that what report 1 had was being passed (kind of like a debug output string on both ends)? Maybe use profiler to see exactly what is being passed in the queries to make sure the select is being created as expected and run that select in SSMS to ensure that the results are what was expected as well? Beyond that, I don't have any suggestions. 🙁
Chad
-
I don't have the answer either. I think I have accomplished what you are trying to accomplish with subreports by nestin tables. It has been a couple years since I've really been in SSRS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Chad Crawford (5/24/2012)
I don't have the answer, but did you try outputing the 1->2 values on report 1 to verify you are getting the results expected and display the parameter on report 2 to verify that what report 1 had was being passed (kind of like a debug output string on both ends)? Maybe use profiler to see exactly what is being passed in the queries to make sure the select is being created as expected and run that select in SSMS to ensure that the results are what was expected as well? Beyond that, I don't have any suggestions. 🙁Chad
I tried a blind display on report 2 but because the dataset was returning 0 rows it was making all sorts of a mess.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply