Saturday, 22 August 2015

How to Set Default parameter value as 'ALL' in SSRS

This article giving how to set default value 'ALL' for parameter in ssrs report.
I am going to create one procedure for dataset and another dataset query for parameter mapping dataset.

------------------------Main dataset............................
Create procedure [dbo].[SalesDetails](@Category varchar(100))
as
begin

IF @Category  = '0'
  BEGIN  
   SET  @Category   = NULL  
  END


SELECT        PC.Name AS Category, PSC.Name AS Subcategory, P.Name AS Product, SOH.OrderDate,datepart(yy,orderdate) Orderyear,
SOH.SalesOrderNumber, SD.OrderQty, SD.LineTotal
FROM            Sales.SalesPerson AS SP INNER JOIN
                         Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID INNER JOIN
                         Sales.SalesOrderDetail AS SD ON SD.SalesOrderID = SOH.SalesOrderID INNER JOIN
                         Production.Product AS P ON SD.ProductID = P.ProductID INNER JOIN
                         Production.ProductSubcategory AS PSC ON P.ProductSubcategoryID = PSC.ProductSubcategoryID INNER JOIN
                         Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
                       
                         where
                       
                         ISNULL(PC.name,'') =  ISNULL(@Category  ,ISNULL(PC.name,''))
                       
end


---------------------------Parameter query---------------------




select '0' Name,'ALL' catgeroryname
union all

SELECT

DISTINCT Name ,name catgeroryname
FROM            Production.ProductCategory



1)Create SSRS report and create two datasets one dataset for tablix data second for parameters dropdown.







2)maap two datasets for tablix and parameter




3)set dafault value as Null.


4)Result



No comments:

Post a Comment