Posts Tagged ‘Data driven report’

SSRS – Data driven report

November 6, 2011 Leave a comment

Data driven reports are most widely used reports in the reporting environments as it enables features of report dataset dynamically using the data from the user without using the SQL injection.
Data driven reports are used to created many reports as
1. Select and search report
2. Linked report
3. Sub report
Here, developing simple data driven report
Step 1: Source query for the data set and parameter as

-- Sales Details Report Dataset query
USE [AdventureWorksDW2008R2]
SELECT  FIS.ProductKey,
        FIS.TotalProductCost AS [ProductCost]
FROM    dbo.FactInternetSales AS FIS
WHERE   SalesOrderNumber = @SalesOrderNumber

-- Sales Details Report Parameters query
USE [AdventureWorksDW2008R2]
FROM    dbo.FactInternetSales

Step 2: Developed a simple report with Dataset and Parameter Dataset as

Step 3: The Dataset of the report with the filter condition as this condition works as parameter to get the data into the report

Step 4: The Parameter Dataset to get the unique record set from the database, this will be used to inject the filter condition to the Report Dataset

Step 5: Set the properties of the Parameter as

Step 6: Set the parameter values properties as

Step 7: Run the report, select any value from the Parameters and upon selecting the value, the value will be send to the Report query as the filter condition to retrieve the data into the Report

Step 8: Report generated based on the selected parameter value as

Step 9: In many cases, we need to get the report result for more than one parameter so select the parameter properties to select multiple input values as

Step 10: Run the report, it will allow multiple values as parameters to the report

Step 11: On running the report, we have errors as the multiple parameters are not supported at the report query due to the equal operator in the report dataset query

Step 12: Update the Report query to accept the multiple values as parameters from the parameters selection process using the IN operator as

Step 13: Run the report, we have got the report results as

Step 14: Report view at web server, here I have updated the report view with fill colours to a better view

Points to consider:
1. Initial plan to integrate this report to another report as linked report so I presented a simple scenario of a particular dataset
2. Formatting the cells and presentation of content are just for the consideration of user experience but I need to accept that it is not as production standards
3. I haven’t discussed the issues with SQL injection as the parameter value

Categories: SSRS Tags: