The “ALL” Parameter In SQL Server Reporting Services 2005
October 18, 2008
One of the most common business requirements for SQL Server Reporting Services is that an SSRS Report should be dynamic. Dynamic in a sense that the report consumer can query the report in a variety of ways. In this post, I will demonstrate how you can add a parameter value “ALL” to return all of the desired result.
I have four parameters in this report; all of them are optional (can be NULL). I will demonstrate the optionality in a different post. For the mean time, let me demonstrate how to add “ALL” in a parameter.
My parameter, which we alias here as “param1″, is optional. Here is how I created my statement in TSQL and wrapped it in a Stored Procedure:
As you can see, a simple “Union ALL” statement can do the job. I can include NULL value in param1 because that parameter is meant to be optional in the report.
Here is the result and the intended purpose of the value “ALL” in param1.
-Marlon Ribunal
6 Comments
leave one →
Reblogged this on Marlon Ribunal's Note On SQL Server and commented:
Adding “ALL” Parameter in SSRS (Reblog)
Hello I am working on a report where I have a column that has a bunch of populated fields and a bunch of null fields. I want to pass a parameter that will let the user choose to see only the null fields or only the populated field or both the populated fields along with the null fields. (This can also be done with three checkboxes. I am kind of new to sql reporting and I am running into a lot of trouble with this as I cannot even show the null fields. I tried , =”Null”, leaving it blank, and just about anything else I could think of. I am in desperate need of assistance and I was hoping you could provide me with some guidance.
Thanks,
Cameron
i am writing ssrs report in this report i have different departments
Dept1
Dept2
Dept3
Dept4
I am using these as a parameter dropdown.
Now when user clicks Dept1 in second dropdown employee of that department will come
like:
All
Tom
Jerry
Herry
similarly when user clicks on Dept2
dropdown will look like:
All
Rita
Sita
Gita
Now every thing works fine when i click one employee at a time it gives me correct report
but when i click “All ” it gives me report for “All ” the employees in “All” the departments .
I want “All” for each department .Like if i hit “All” for “Dept1″ then it should give me report
only for “All” the employees in “Dept1″ which are
Tom
Jerry
Herry
similarly “All” in “Dept2″ should give me reports for “All” employess of “Dept2″
which are:
Rita
Sita
Gita
please someone suggest how is this possible?
Thanks!
Define separate datasets for each dept. That’s how I would do it. Hope that helps.
i am writing ssrs report in this report i have different departments
Say:Dept1
Dept2
Dept3
Dept4
I am using these as a parameter dropdown.
Now when user clicks Dept1 in second dropdown employee of that department will come
like:
All
Tom
Jerry
Herry
similarly when user clicks on Dept2
dropdown will look like:
All
Rita
Sita
Gita
Now every thing works fine when i click one employee at a time it gives me correct report
but when i click “All ” it gives me report for “All ” the employees in “All” the departments .
I want “All” for each department .Like if i hit “All” for “Dept1″ then it should give me report
only for “All” the employees in “Dept1″ which are
Tom
Jerry
Herry
similarly “All” in “Dept2″ should give me reports for “All” employess of “Dept2″
which are:
Rita
Sita
Gita
please someone suggest how is this possible?
Thanks!