DevPinoy.org
A Filipino Developers Community
αρχάριος » The “ALL” Parameter In SQL Server Reporting Services 2005
   
The “ALL” Parameter In SQL Server Reporting Services 2005

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.
[A Cross-Post from my other blog http://dbalink.wordpress.com ]
-Marlon Ribunal

kick it on DotNetKicks.com

 

Posted 10-18-2008 6:58 PM by marl
Filed under: , ,

Comments

keithrull wrote re: The “ALL” Parameter In SQL Server Reporting Services 2005
on 10-20-2008 9:11 AM

Hmmm... The better approach would be to have an ISNULL check on your WHERE clause:

WHERE Name = ISNULL(@NameParameter, Name)

This way the Name field always matches the Name value if @NameParameter is null and you don't need to write the same query over again.

;)

marl wrote re: The “ALL” Parameter In SQL Server Reporting Services 2005
on 10-20-2008 1:27 PM

@Keith

Thanks for the tip there! That will be helpful in my other tasks.

The data source of my drop down column is from "Select Distinct" statement (which is returning 3 columns). I do not have the "ALL" word in my column; hence, the union all:

Select 'ALL' as column1, NULL as column2, Null as column3

UNION ALL

Select Distinct column1, column2, column3

The post above pertains only to that one dropdown parameter I have in that report. So I do not need the "Where" clause because I only need the list that I needed for the parameter.

For optionality, which I will discuss later, I use COALESCE rather than ISNULL.

-Marl  

keithrull wrote re: The “ALL” Parameter In SQL Server Reporting Services 2005
on 10-20-2008 4:19 PM

Hi Marl,

I try not to add a parameter called 'ALL' in my reports. I learned from experience that users tend to think that they are selecting everything if they don't specify any parameter thus i make use of a NULL check on my scripts.

I agree with you that COALESCE is better than ISNULL. What i pointed out on my previous comment is just to show how to do this without 'ALL'-like feature without using a UNION ALL.

Thanks,

Keith

marl wrote re: The “ALL” Parameter In SQL Server Reporting Services 2005
on 10-20-2008 11:16 PM

@Keith

"I try not to add a parameter called 'ALL' in my reports. I learned from experience that users tend to think that they are selecting everything if they don't specify any parameter thus i make use of a NULL check on my scripts."

Lol! You're right! I changed the "ALL" into "<None>" in the production report. <None> is still lame, but it is more "politically" correct than "ALL".

Please expound more on "eliminating ALL-like feature". What I originally wanted to do was just to deal with it will NULL checkbox; but I lost the checkbox when I added a "From Query" values to the parameter.

Your thoughts on this?

Thanks.

Regards,

Marl

- See you at the Code Camp @ USC


Copyright DevPinoy 2005-2008