Skip to content

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

kick it on DotNetKicks.com

Advertisements
6 Comments leave one →
  1. Cameron permalink
    January 11, 2012 3:18 PM

    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

  2. hanuman permalink
    January 20, 2010 3:53 PM

    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!

  3. hanuman permalink
    January 20, 2010 3:52 PM

    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!

Trackbacks

  1. How To: Report Authoring in SSRS 2005 – Part V (Pimp Your Parameters) « Marlon Ribunal's Note On SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives


  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: