March 6, 2012 at 9:06 am
The report I have developed has a main report and four subreports.
The main report receives three parameters from a user. Each of these parameters is allowed multiple values ... one or more or even all available values.
Two of those parameters are passed to each of the subreports.
My problem is this. If I select two values each for those two parameters, the subreports work right. But if I select more than two values then the subreports return only it's heading ... not the data even though two of the values are the same ones for which the subreport formerly returned data. (And if I knock out the third value then the subreports return that data again.)
March 6, 2012 at 1:17 pm
Several things to try:
Run a profiler trace to see what is being passed in to the query(be careful if on a prod server).
Can you run the subreports standalone with 3 values and see if they return anything?
What size is your parameter set to in the SQL query - could you be cutting off the last part of the parameter list?
Do you have any filters or visibility settings on the resulting dataset or matrix that may come into play?
Let us know what you find out.
MWise
March 6, 2012 at 2:00 pm
Thankyou. I'll see about getting a translator for terms I don't understand yet, and a local expert to help me answer the questions. I think the only question I know the answer to is that I had created my subreports as stand-alone reports before making them subreports. While they were stand-alone, I did test them out and they worked perfectly with all the multi-value parameters.
As part of that morphing I was told to remove all the Default options (value "None") and likewise the Available values lists. Until I did those steps I was getting no results at all from the subreports.
Today I was doing further analysis of my test results, and discovered that when I specify only a single value for each parameter the subreport will return correct data (assuming the parameter values are such that data should be returned) but when I specify multiple values for one or more of the parameters the results are inconsistent. So obviously there's some other anomaly going on, and I'll pursue that locally before going back to SQLSERVERCENTRAL ... just in case somebody here will have a AHA moment.
March 6, 2012 at 8:45 pm
In the report that is calling the subreport, check the value that is being passed in the parameter. By default, you get the first value only. The value of the parameter parameter will be something like
Parameters!ParameterName.Value(0)
Remove "(0)" and you will get all of the parameter value passed (not just the first)
March 8, 2012 at 8:14 am
Thank you, SSCrazy. That did the trick.
Curiously, though, when I deleted the “(0)” a little drop-down menu appeared which includes an option called “IsMultiValue”. I tried that and that had even worse results. Don’t you think that’s odd?
March 10, 2012 at 7:05 am
Not all that surprising, really. What reporting services is really doing is allowing you to access the parameter object for the parameter you are using. This object has a number of properties including "IsMultiValue".
The Value property (Parameters!ParameterName.Value) is can be accessed as an array. THe first element of the array is Parameters!ParameterName.Value(0) which is why you would have been getting the first of the selected values passed to the subreport. If you don't specify the array index, you actually get the entire parameter object - the subreport is happy to get this if the parameter defined in the subreport is a multi value parameter.
Understanding of what reporting services is doing is a little easier if you have been programming in one of the .net languages (or even something like VB6).
March 12, 2012 at 4:42 am
Understanding of what reporting services is doing is a little easier if you have been programming in one of the .net languages (or even something like VB6).
Very true!!!
Under the covers, a SSRS report is effectively two containers: a) XML, b) VBA. The standard VB code that managess the report is hidden from you, but you can add your own code into the VB container. Knowing this means you know about the underlying architectural constrains of SSRS, and can help you work out why some of the things in SSRS happen as they do.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply