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
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