CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2
Arockia Nirmal
Arockia Nirmal

Reputation: 787

Selecting either one or ALL the values in a parameter in SSRS 2008 R2

I am working on a reporting project in SSRS. I have a parameter called 'customer'. The values for this parameter are now populated with the sql query. I want to restrict this parameter such that the user should be able to select either one customer or all the customers. There should not be any possibilities to select either 2 or 3 customers.

Upvotes: 0

Views: 696

Answers (1)

iamdave
iamdave

Reputation: 12243

Have an option in your parameter list called All Customers that is ordered so it sits at the top of the parameter list.

If you are manually adding parameter options, this ordering is easy. If it is data-driven you can do a union all in your parameter value dataset to get the correct order:

select <Unique value that matches your customer ID type> as Value
      ,'All Customers' as Label
      ,1 as SortOrder

union all

select CustomerID as Value
      ,CustomerName as Label
      ,2 as SortOrder
from CustomerTable
order by SortOrder
        ,Label

and then in your query you just need to add the logic that handles this new All value:

select Columns
from Tables
where CustomerID = @Customer
   or @Customer = <Unique value that matches your customer ID type>

Upvotes: 2

Related Questions