Monday, September 19, 2011

Reporting Services Parameters: Adding “All” item.

There are some instances we have to create parameters with multiple items, including an item representing all items in the list, mostly called as “All”. There are various ways of doing it. If the list is dynamic and data source is OLAP, you get this item automatically. If it is not OLAP, and loading from OLTP database, this item has to be manually added and programmatically handled.

Image1

Here is an easy way to handle it. Have this additional item in a new query and use UNION for joining it to main query.

SELECT  'All Years' AS OrderYear
    , 0 AS OrderYearValue
UNION
SELECT DISTINCT 
    CONVERT(varchar(100), YEAR(OrderDate)) AS OrderYear
    , YEAR(OrderDate)  as OrderYearValue
FROM Sales.SalesOrderHeader
ORDER BY 2

Now data source contains the item. In order to get this handled with query parameter, follow below code;

SELECT {your columns}
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = @Year
    OR @Year = 0

Happy Coding Smile.