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.

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 .