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.

at

2 comments:

  1. DamithSeptember 19, 2011 at 8:32 PM

    hi dinesh,
    that i have a req. in ssrs. i have several reports that have the same title and footer value. So i want to took both of these params in one location(as in config file).Are there any method of doing that??

    thanks

    ReplyDelete
  2. Dinesh PriyankaraSeptember 19, 2011 at 10:20 PM

    Hi Damith,

    SSRS R2 supports a feature called Report-Parts sharing. If you use R2, make a report with header and footer and publish both header and footer as Report Parts, then you can use them when you create reports... but remember, you cannot create reports using BIDS, have to use Report Builder 3.0.

    Other solution is, have a report with header and footer and save it is as a template. When you create reports, you can use the template which has header and footer.

    ReplyDelete