Home > SSRS > SSRS – Data driven report

SSRS – Data driven report

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.SalesOrderNumber,
        FIS.UnitPrice,
        FIS.TotalProductCost AS [ProductCost]
FROM    dbo.FactInternetSales AS FIS
WHERE   SalesOrderNumber = @SalesOrderNumber

-- Sales Details Report Parameters query
USE [AdventureWorksDW2008R2]
SELECT DISTINCT
        SalesOrderNumber
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

Advertisements
Categories: SSRS Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: