Creating Generic SSRS Reports

We can design generic SSRS reports in such a way that we end up creating less number of reports then what are mensioned in Business Requirement Document. This will help us reduce burden with regards to maintainance and administring reports.

Please following below steps for creating generic reporting project for your set of requirements.

Step 1: Analyze Unique/Distinct UI patterns in your reports & And assign unique ID to each pattern called PatternID.

For Example:

• Bar chart on top and Table below.
• Pie Chart and Table.
• Two Barcharts Side By Side. etc.

Step 2: Create Pattern MetaData Table with name "tblPatternMetadata" which will contain information about our patterns like.

• Pattern ID.
• Pattern Report URL
• Etc

This table will be used by calling application or master report through which user will access reports.

 Step 3:  Start Designing Patterns.

• Each pattern will support defined set of table columns or Bars.
• With an option to hide/Show columns as required.
• Such that we can have different Table/Column headers or Report/Table/Chart Names for each reports.
• And this can be done by creating one more table named "tblReport_Metadata" with columns.
• ReportID
• ReportName
• PatternID
• Table1_Name
• Table1_Col1_Name
• Table1_Col2_Name
• Table1_Col3_Name
• Table1_Col4_Name
• Table1_Col5_Name
• Table1_Col1_Format
• Table1_Col2_Format
• Table1_Col3_Format
• Table1_Col4_Format
• Table1_Col5_Format
• Chart1_Bar1_Name
• Chart1_Bar2_Name
• Chart1_Bar3_Name
• etc

• Now, We will create two stored procedures named "sp_GetReportData" and "sp_GetReportMetadata"  for getting Data and Metadata respectively.
• sp_GetReportData stored procedure will take ReportID as parameter and will fire query to database to get data that report but aliasing fields as
• Table1_Col1_Data
• Table1_Col2_Data
• Table1_Col3_Data
• Table1_Col4_Data
• Table1_Col5_Data
• Chart_Bar1_Data
• Chart_Bar2_Data
• Chart_Bar3_Data
• etc
• Whereas, sp_GetReportMetadata Stored takes ReportID as parameter and Queries "tblReport_Metadata" table always resulting in single or No record.

Step 4: Create master Report for allowing navigation between reports.

Step 5: Update Metadata for each report and add query for each report in sp_GetReportData Stored procedure.