Creating a Generic SSRS Report

Creating useful reports is part art and part science. On one end of the spectrum, you have visually appealing and highly customized reports and dashboards that are truly works of art. These often illicit “oohs” and “aahs” at first glance, and can help to creatively tell the story of the underlying data. On the other end of the spectrum is the boring classic reports that present data very simply, in monochromatic rows and columns. While it’s a lot more fun to work on the former, often the latter is all that is required to present raw information to data consumers.

When a simple presentation of data is needed, SQL Server Reporting Services (SSRS) is a common tool of choice. Creating simple tabular, row-and-column type reports can be done relatively quickly and easily in SSRS. But what happens if you need a lot of these reports? If you need to present dozens or even hundreds of reports to end users, building even a simple report for each one can take time. Unfortunately there’s no Biml-like automation tool for SSRS, so this work must typically be done by hand.

However, it is possible to create a generic report in SSRS that can dynamically present information from tables or views. In this post, I’ll share my implementation of this.

Building a Generic SSRS Report

The methodology I used for this report is as such:

• Create a generic report wrapper stored procedure that uses dynamic SQL to return query results.
• Create a stored procedure that returns the column names for the table or view.
• Create an SSRS report that uses the above stored procedures as data sources.
• Render the report using the data returned from the first stored procedure, and modify the report appearance using data from the second stored procedure.

For this example, I’ll use my Dunder Mifflin database as a source.

Create a Generic Data Retrieval Stored Procedure

For the first step, I created a stored procedure renders dynamic SQL to retrieve data for a specified table. This sproc expects parameters for the schema name and table name. The results are returned with generic column names such as Column001, Column002, etc. In the stored procedure, you’ll note that I’ve limited my results to a maximum of 25 columns.

Create a Stored Procedure to Retrieve Column Names

Next, we’ll get the column names for this table, which we’ll combine with the above data to properly render the report. The stored procedure below accepts the same two parameters, and returns a list of column names along with the associated generic column name for the specified table.

Create the SSRS Report

Now it’s time to build the SSRS report. I created a very simple SSRS report, and added a data set for each of the stored procedures listed above. I also created report parameters for Schema Name and Table Name. Additionally, I used a separate data set (TableList) to let me specify via query the list of tables that can be presented on this report, and I use this as the list of available values for the TableName parameter. As shown below, these are the moving parts that will drive the report data.

Now that I’ve got data to present, I’ll update the report to display the data in the ReportData data set. I’ll use a matrix, specifying the ReportData data set as the data source, and include all columns in the output.

You’ll notice that we’re still using generic column names. This isn’t really helpful since we don’t know what each column is, so we’ll tweak the report to display the correct column name.

Add Dynamic Behavior to the Rendered Report

This is the time-consuming part. We need to do the following for each column:

• Dynamically replace the generic column name with the actual column name
• Set the visibility of each column to hide columns that are not used

I’ll start with the first one of these. For each of the column headers, I’m using the SSRS function named LOOKUP to find the correct column name. As shown below, the LOOKUP function makes a call back to the ReportSchema data set to look up the column name corresponding to Column001.

The above expression will be used to substitute for the static Column{N} text in each column header, replacing “Column001” with the respective column name for that column. (I told you this part would be time-consuming.)

Next, we’ll dynamically hide the columns not in use. Since SSRS expects that the report structure to be defined at design time, we can’t just arbitrarily drop columns out of the report. However, we can set columns to be hidden if they don’t contain any data. As shown below, right-clicking on the header of a column shows the option to set Column Visibility.

On the Column Visibility window that appears, select Show or hide based on an expression. In the expression window, I used the following expression for the second column, which sets that column to be hidden if the number of columns in the metadata is less than the number of this column:

Note that you’ll need to change the less-than value in the expression above to the number of the column. For example, the above expression is for Column002. For Column003, the expression would indicate “< 3”, and so on. Note that you won’t have to set this expression on the first column, since each table would have to have at least one column of data to display to be valid.

Finally, I made the report easier to read by changing the title to use the parameter values so the user knows which table they are viewing.

The Final Product

With the above changes made, I can now browse the report and display data for each of the tables from the included list. You’ll notice that the column names are dynamically rendered (even though the underlying report columns are generically named), and if the table has fewer than 25 columns, those “extra” SSRS columns are hidden from the report.

Changing the source table name will change not just the data but the column headers and the number of visible columns as well.

There are some notable limitations to this generic setup:

• The number of columns that can be presented is limited to the maximum number of columns set in your underlying stored procedure and in the report design. Because all of the tables I have in this sample database have fewer than 25 columns, I set that to the maximum number. You can add more columns to your implementation, of course – just remember to modify the data retrieval stored procedure and add the extra columns to the report (setting visibility and column header expressions for each).
• You may notice a performance impact for large sets of data. Because the data retrieval query will return the maximum number of columns even if the source table has just a few columns, there will be a small amount of extra unused data in memory while the report is rendered.
• You’ll want to limit the list of tables that can be presented in this way. I configured this example to constrain the available tables to a specific list to limit the risk of bad or malicious input.

Conclusion

As I mentioned earlier, this is a very minimalistic approach to reporting, so this output won’t win any accolades for visual design. However, for situations in which you need to present several tables to end users in a simple, row-and-column format, this generic SSRS approach works well.