Feb 12, 2014

Concerns in passing a parameter to a sub report

In most cases we need to pass parameters to sub reports in SSRS. If parameter is not available in certain scenarios it can raise issues. I think it’s always good to do the queries in below manner when it comes to datasets in sub report.

In my example I am passing a company name as a parameter which is not mandatory one. Typically my data set returns a Brand name.  In this case, if parameter is null, relevant data fields in the sub report will show a label called “Brand not available”.

IF (@companyName is NOT NULL)
BEGIN

    SELECT new_brandname as BrandName
    FROM ....
    WHERE new_companyName = @companyName  

END

ELSE

BEGIN

   SELECT 'Brand Name not Available' as BrandName

END

A simple trick.