In the last part of this installment, I will show an example of SSRS report using the data we parsed in part 2 and extracted from SSMA in part1. For this report, I would like to list all projects and its aggregate conversion rate, number of object, and number of errors. If there are multiple runs of assessment for a given project, I only want to show the latest result.
Example of the report is as follows:
clicking on “orcl_OE” project, would take you to the detail report:
For each of the report above, I created a stored procedure to define the query. I also created two views as follows:
vwLatestSSMAProjectSession identifies the latest session for each project. vwSSMAReport is the base query that join the data between the two tables created in part 2 and the view we just created. All the reports will use the same base query defines in this view to ensure consistency.
After the views are created, we can create the stored procedures:
Specify "execute sproc_getReportSummary" as the query string
Accept the default Tabular report type:
Move all the fields to the "Details" section:
Choose the Table Style:
Name your report and click finish:
From the design window, drag "Data Bar" from the toolbox window onto ConversionRate cell.
Select the Data Bar Type
Click on the bar to display chart data dialog box then click on the arrow next to the ConversionRate. Select "Show Data Labels"
To create the Project Detail report, repeat the process and use the following as query string: "execute sproc_getProjectDetail @project"
Select the Report Type:
Move ObjectCategory, CountObject, CountError, and ConversionRate to the Details section:
Select the Table Style
Name your report and click Finish:
Return to the Report Summary, right click on the Project cell and select Text Box Properties:
Go to Action tab, select "Go to report", select the report name, and add parameter as follows:
Click the preview tab on the Project Summary report and click one of the project:
The detail for the selected project should appear:
This conclude our 3 part series on creating custom SSMA report. Please check out the previous related posting on this topic: