Avatar of thayduck
thayduck
Flag for United States of America asked on

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

In my SSRS report, I can tell if a user selected all the choices in the drop down parameter by doing:

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

Then in my report, I would show the word 'ALL' somewhere in the report instead of listing out all the individual choices.

Now, my question is:

How can I pass back to my Stored procedure, via a parameter, the letter 'A',  so I know that the user chose all the choices from the drop down parameter ?

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.
SSRS

Avatar of undefined
Last Comment
thayduck

8/22/2022 - Mon
Koen Van Wielink

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

Open 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.
Arifhusen Ansari

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
thayduck

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Arifhusen Ansari

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.
thayduck

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

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Arifhusen Ansari

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, ", ")))
thayduck

ASKER
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, ",")
Arifhusen Ansari

yes it will pass all the selected values to the sp
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
thayduck

ASKER
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.
thayduck

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

use this

=IIF(COUNTROWS("SC_procRptLstBusinessUnit").Equals(Parameters!BusinessUnit.Count), "All ", "")
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
thayduck

ASKER
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.
thayduck

ASKER
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.
Arifhusen Ansari

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
thayduck

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

ASKER
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.
Arifhusen Ansari

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", "" )
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
thayduck

ASKER
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
Arifhusen Ansari

ok. mean while i am also thinking for the other solution.
ASKER CERTIFIED SOLUTION
Arifhusen Ansari

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
thayduck

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
thayduck

ASKER
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.