In this article, we will see how to create a SSRS report with parameters. We will create the parameters and then add these parameters to our reports, in different ways. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.
This article is a continuation of ‘Designing Basic Report using SSRS 2008 and VS 2008 BI Studio’. If you have not gone through it, I will highly recommend you to go through it first and then start with this article.
In my previous article, we have seen how to create a simple drill down report which is fetching the data from SQL Server 2008 database engine from the database ‘AdventureWorks’. So let’s open the solution ‘AdvReports’ which we created during our last demonstration and preview the report.
Now let’s open the ‘Report Data’ window. Right click the Dataset1 from ‘AdventureWorksDS’ and go to ‘Dataset Properties’ as shown below –
This will open a Dataset Properties Window. If you observe the window, it shows you multiple options as described below –
- Query – from the ‘Query’ section, you can modify the query of the report. You can change the name of the Dataset. You can even change the Data source or create a new Data source from Data Source dropdown list or by clicking a ‘New’ button respectively. You can even import a query from ‘.SQL’ file or ‘RDL’ file.
- Parameters – from the parameters section, you can add new parameters to the report or change the existing parameters.
- Fields – from the fields section, you can change the display name of the field or do the mapping of the field with the source field.
- Options – this section allows you to change collation, change case sensitivity and other options.
- Filters – from this section, you can add filters to your report.
Now let’s get back to Query section and click on ‘Query Designer’ button as shown below –
Once you open the ‘Query Designer’ window, let’s modify our query to add the parameter(s). To add the parameter(s) to our report, replace the existing query by the query shown below which has parameter declaration.
Modified Query –
SELECT Sales.SalesTerritory.Name AS Region,
Production.ProductCategory.Name AS Category,
Production.ProductSubcategory.Name AS SubCategory,
SUM(Production.Product.ListPrice * Sales.SalesOrderDetail.OrderQty) AS Sales,
SUM(Production.Product.StandardCost * Sales.SalesOrderDetail.OrderQty) AS Cost
FROM Sales.SalesOrderHeader
INNER JOIN Sales.SalesOrderDetail
ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
INNER JOIN Production.Product
ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
INNER JOIN Production.ProductSubcategory
ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory
ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID
INNER JOIN Sales.SalesPerson
ON Sales.SalesPerson.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID
INNER JOIN Sales.SalesTerritory
ON Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID
INNER JOIN Sales.Customer
ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
WHERE (Sales.SalesTerritory.[Group] = @Territory)
AND (Sales.Customer.CustomerType = 'S')
GROUP BY Sales.SalesTerritory.Name,
Production.ProductCategory.Name, Production.ProductSubcategory.Name
Now if you observe the above query and compare it with our previous article, we have made a small modification. The modification is that instead of using Sales.SalesTerritory.[Group]=’North America’ we are using Sales.SalesTerritory.[Group]=@Territory, where ‘@Territory’ is a parameter.
After replacing your old query with the new query shown above, click on the ‘Run’ button to test the query. It will show you a ‘Query Parameter’ dialog box. Fill the value of the parameter as ‘North America’ and click ‘OK’ button as shown below –
Now you will see the result shown below in a ‘Query Designer’ window. Click ‘OK’ button on the query designer window and check your ‘Parameters’ section in ‘Dataset Properties’ window. You will now see the parameter ‘@Territory’ in the parameters section as shown below –
Now click ‘OK’ button of ‘Dataset Properties’ window. Build your report by going to ‘Build’ menu and ‘Build AdvReport’ as shown below –
Now hit ‘F5’ and run your report. Your report will be displayed in a ‘Report Viewer’ with a parameter ‘Territory’ and a ‘Textbox’ where we need to pass the value of the parameter. So let’s pass ‘North America’ and click a ‘View Report’ button and see how the report is getting displayed. The report is shown below –
So this is how you create a simple parameterized report in SQL Server Reporting Services 2008.
Choose a Parameter using a DropDown List
But now if you observe the report parameter, it is asking you to write a name of the ‘Territory’. What if your customer does not want to remember the names of all the ‘Territories’? Can we make our customer’s life much easy by providing a list of all the ‘Territories’? This way our customer will see the report as per his/her convenience by selecting a ‘Territory’ of his/her own choice.
To make our customer’s life much simpler and to give a professional approach to our report, let’s design our parameter in a different way and add it to our report.
To do this, first of all open ‘Report Data’ window. Right click ‘AdventureWorksDS’ and create a new Dataset as shown below –
Now once you click the ‘Add Dataset’ menu, you will see a ‘Dataset Properties’ window as shown below –
Change the name of the dataset as ‘AllTerritories’. Make use of the ‘AdventureWorksDS’ data source. At the end, write the following query in the ‘Query’ section –
SELECT distinct [Group] FROM Sales.SalesTerritory
You can test your query by clicking the ‘Query Designer’ button as shown in the step earlier. Now click the ‘OK’ button of ‘Dataset Designer’ window and check your ‘Report Data’ window. You will now see the new dataset ‘AllTerritories’. Now expand the Parameters section from ‘Report Data’ window. Right click the ‘Territory’ parameter and click on ‘Parameter Properties’ menu as shown below –
It will open a ‘Report Parameter Properties’ window. From that window, go to ‘Available Values’ Section and choose ‘Get values from a Query’. Then choose the dataset ‘AllTerritories’ from the dropdown list. After that, select the value field ‘Group’ and Label field ‘Group’ as shown below –
Click on the ‘OK’ button. Build your report and hit ‘F5’ to see the report in ‘Report Viewer’ tool. Now if you observe your report in report viewer, you will see a dropdown list instead of a ‘Textbox’. When you click on the dropdown list, you will see all the available ‘Territories’ which the customer can choose as per his/her convenience and see the report. The report is as shown below –
In this article, we have seen how to create an SSRS report with parameters and let the user supply the value of the parameter.
No comments:
Post a Comment