Pass data from SSRS report back to SSRS reports main Stored Procedure

I suspect you're trying a very complex thing to do something relatively straightforward, so let me summarize what I think you're trying to achieve:

- You have a report with a multiple select parameter where the user can select 1, more, or all values;
- If the user selects all values, you wish to display this somewhere on the report for the report reader's reference, instead of listing all the report values.

My standard workaround for this problem is to add an option "All" to the list of parameters, displayed at the top of the selection list. If I understand your information correctly your parameter list is populated by the procedure SC_proc_RptLstBusinessUnit, so you'd modify that procedure to add an option "All" (usually a simple "union all" statement will do).

Once you have this available, you modify your main report procedure, starting it with an If statement:

If @param = 'All'

Run query for all selections

Else

Run query for selected values only

End

Select allOpen in new window

This way you don't have to apply any logic in your report itself as to what parameters the user selected, it's all handled by the stored procedures. You can just display the selected choice.
I hope I understood your problem correctly. If not, please let me know so I can try and update my answer.

Hi,

Rather then using the "All" as an Option in the Drop down you can go with your approach.
Issue with adding "All" is if you are selecting your parameter values using SP. I would be difficult to run the second SP based on the Value "All" because it won't be clear that what are the values in the "All".
Other issue is even if you select the "All" report parameter will display "Select All" option, which will me misleading for the Users.

Instead go with your code and just do some modification as i did in the rdl file attached.

I took the example from the AdventureWorks2012 database.

Base on the CustomerID data is populating. Parameter in retrieved using "DataSet1".

"Matrix_Case" used the values in the CustomerID parameter. Just edit the dataset Property-> Parameter Tab and - > Exp

Also check the text box i have added to display when "Select All" is selected.

let me know if you have any question.
Parameter-To-Other-Dataset.rdl

I know that the user selected all choices in the report. I want to pass something back to the SP (via a parm) so I know that the user selected All choices from the dropdown. I will then use that information to do something in the SP.

I really do not want to add a extra choice to the drop down called All. The Drop Down already has a (Select All) choice by default.

I am not sure how to get that .rdl to run so I can see what you are trying to explain.

Just open an rdl and just see the how the parameter is Passed to the query used in the "Matrix_Case" Dataset.
 
This query is built up on the AdventureWokrs2012 db .

Let me know if you have further questions.

Sorry, never used Adventure Works.
So I need Adventure Works before I can see your .rdl ?

No just add the rdl in report solution.
If you get any popup related to connection not found or anything just click on cancel. You will then see the datasets and their property.

open the rdl and just see the

"Dataset1" this is used to get the values for the parameter Customer_id.

now the values which user will select is passed to  
"matrix_case" dataset.

Go to matrix case dataset property and -> parameter section. See the configuration of parameters.

let's make it simple.

rather then using

=IIF(COUNTROWS("SC_procRptLstBusinessUnit").Equals(Parameters!BusinessUnit.Count), "All ", (Join(Parameters!BusinessUnit.Label, ", ")))

for the parameter config for the sp u are calling.

Write

=Join(Parameters!BusinessUnit.Value, ",")
This will be passed to your calling sp

Now to show the selected parametet in the report somewher

Use

=IIF(COUNTROWS("SC_procRptLstBusinessUnit").Equals(Parameters!BusinessUnit.Count), "All ", (Join(Parameters!BusinessUnit.Value, ", ")))

So, will this pass something back to the SP saying that the user chose all the drop down selections ?  That's all I want to do.

=Join(Parameters!BusinessUnit.Value, ",")

yes it will pass all the selected values to the sp

If there are 20 dropdown choices and the user checked all 20, then I want to pass something back to the SP (via a parm) saying that for this dropdown, the user chose all of the available choices.

I do not want to pass selected values back, I just want to know if all dropdown choices available were selected by the user.

use this

=IIF(COUNTROWS("SC_procRptLstBusinessUnit").Equals(Parameters!BusinessUnit.Count), "All ", "")

In the SP, if I know that the user chose all choices for that parm, there are certain things in my Where Clause I will not do.

ARIF:

Tried that already.

I tried passing back a parameter (@BusinessALL) to the SP but this code was not allowed in my parameter expression:

 =IIF(COUNTROWS("SC_procRptLstBusinessUnit").Equals(Parameters!BusinessUnit.Count), "A", Nothing)


 A aggregate or Lookup function are not allowed in a query parameter function.

Do you want to pass the values selected and use those values in the where clause to filter some data right??


if answer is yes.
you can then pass the selected values as a "," separated and use the  split function to split the value in the table to filter the data.

No, just want to know if user selected all choices available.

In the report, you can tell if the user chose all available choices, is there someway to pass that
back to the SP, that the user chose All.

We have to do a trick for that i think.
the query used to get parameters add the column with count. this count will have count of values for the parameter.
 so if 4 parameters are there then the count will be 4.
e.g

paramval.           valuescount
 1                            4
2                            4
3                             4
4                           4


When you configure your parameter
Set paramval for the lable and valuescount as value.


Now in the othet sp where you want to pass the value "All" if all ate selected.

= iif ( Parameters!param.Count = Parameters!param.Value , "All", "" )

I cannot change the original parameter because the label and value are needed as is.
That means I would have to create another parameter (hidden) and change my original query to include a record count. Then only access the record count from the hidden parameter.
That might work, the only issue is, I would have to run that query twice, because it will run once for the original parm and once for the hidden parm.

I do not want to have to run queries twice since there are 6 parms in this report that I will want to do this for. And 4 of these queries run over big tables. This means the report will take longer to process. But I will look into this option.

Maybe someone else can come up with another solution

ok. mean while i am also thinking for the other solution.

All of your suggestions will work, but I cannot use them because they mean I have to execute queries twice, once for the real parameter and once for the hidden parameter. Since some of the queries I use to create parameters take longer than usual I cannot use that approach.

Your other approach was to put the total count into the Value of a parameter. Cant use that since I use both the Label and Value to contain specific data.

Koen Van Wielink

Did not want to use your suggestion because it would have meant that the word ALL would appear twice in the drop down if I understood it right.

I did that once a while back and the users did not like it.