How To Create Dynamic Columns In Rdlc Report

By: | Updated: 2018-11-06 | Comments (39) | Related: > Reporting Services Dynamic Reports

Problem

When SQL Server Reporting Services (SSRS) is used for retrieving data from a database a report is created. Even for a simple SELECT statement, a new report definition file is created and run against a particular data source. There is a generic problem with this approach. As business requirements increase to obtain different datasets, the number of reports increase as well. Also, there are times when users need to query reporting data and the only interface available for them is SSRS. The end user will have no choice other than creating a new report definition and running it on an SSRS server.

Solution

The solution to this problem is to create a single report which allows a user to pass a query as a report parameter and the output of query is presented as a report in a tabular form. Using this approach, the number of reports can be substantially reduced. Multiple subscriptions can be created against a single report to produce entirely different datasets. And using the SSRS automated email feature, different email alerts can be created from a single report.

Creating a Dynamic SQL Report in SQL Server Reporting Services

This solution appears to be working perfectly when a SELECT query or a combination of select queries such as CTE (common table expression query) is run against a set of tables or views.

The main components of this report are:

A multi-line text parameter which accepts a query as input
A dynamic SQL query in the dataset, which transforms the output of a query to only three columns i.e. RowId, ColumnName and Value
An SSRS matrix which transforms data from ColumnName and Value pair combinations to a tabular structure.

We will work through a step-by-step approach to create a dynamic SQL report.

Creating a Multi-line Text Parameter

Add a new parameter to your empty report as shown below:

Adding SQLTxt parameter to the report

Details of SQLTxt parameter

The report parameter name used in this example is SQLTxt, its prompt is "SQL Text", data type is text, "Allow multiple values" checkbox is checked and "Select parameter visibility" is set to visible. When this parameter is created it will appear in the parameters list as shown below.

SQLTxt appearing in report parameters

Creating a dataset for the dynamic SQL query

Here is the code for the dynamic SQL query which resides in the dataset query text.

SET NOCOUNT ON  BEGIN TRY    DECLARE @dbCursor CURSOR     DECLARE @sqlQuery NVARCHAR(MAX) =@SqlTxt    DECLARE @tblColumnDetails TABLE (ordinal_position INT, column_name VARCHAR(255))    DECLARE @column_name VARCHAR(255) , @ordinal_position INT , @column_characteristics_flags INT , @column_size INT , @data_type_sql INT , @column_precision INT , @column_scale INT , @order_position INT , @order_direction VARCHAR(255) , @hidden_column INT , @columnid INT , @objectid INT , @dbid INT , @dbname VARCHAR(255)    DECLARE @queryDynamicColumnsDec NVARCHAR(MAX)=''    DECLARE @queryDynamicColumnsLst NVARCHAR(MAX)=''    DECLARE @queryDynamicColumnsLstDec NVARCHAR(MAX)=''    DECLARE @queryDynamicTable NVARCHAR(MAX)=''     SET @sqlQuery = 'DECLARE  query_cursor CURSOR FOR '+@sqlQuery    EXEC sp_executesql @sqlQuery    DECLARE @ResultSet CURSOR    EXEC master.dbo.sp_describe_cursor_columns @ResultSet OUTPUT,@cursor_source=global,@cursor_identity=N'query_cursor'      FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname;     WHILE (@@FETCH_STATUS <> -1)      BEGIN         INSERT INTO @tblColumnDetails(ordinal_position,column_name) VALUES(@ordinal_position,ISNULL(@column_name,'Col_'+CAST(@ordinal_position AS VARCHAR(6))))       FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname       END        SELECT @queryDynamicColumnsDec += '['+column_name+'] NVARCHAR(MAX),', @queryDynamicColumnsLstDec+='@var_'+CAST(ordinal_position AS VARCHAR(6))+' NVARCHAR(MAX),', @queryDynamicColumnsLst+='@var_'+CAST(ordinal_position AS VARCHAR(6))+','  FROM @tblColumnDetails ORDER BY ordinal_position    SET @queryDynamicColumnsDec= LEFT(@queryDynamicColumnsDec,LEN(@queryDynamicColumnsDec)-1)    SET @queryDynamicColumnsLst= LEFT(@queryDynamicColumnsLst,LEN(@queryDynamicColumnsLst)-1)    SET @queryDynamicColumnsLstDec= LEFT(@queryDynamicColumnsLstDec,LEN(@queryDynamicColumnsLstDec)-1)    SET @queryDynamicTable = 'DECLARE @dynamicTable TABLE('+REPLACE(@queryDynamicColumnsDec,'@','')+')'      DECLARE @sql1 NVARCHAR(MAX) =    'DECLARE '+@queryDynamicColumnsLstDec+CHAR(13)+    @queryDynamicTable+CHAR(13)+    'OPEN query_cursor        FETCH NEXT FROM query_cursor INTO '+@queryDynamicColumnsLst+'    WHILE(@@FETCH_STATUS<>-1)    BEGIN       INSERT INTO @dynamicTable('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+') VALUES ('+@queryDynamicColumnsLst+')       FETCH NEXT FROM query_cursor INTO '+@queryDynamicColumnsLst+'    END     SELECT RowId,ColumnName,Value FROM (    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))[RowId],'+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+' FROM @dynamicTable    )tblBase    UNPIVOT    (    Value FOR ColumnName IN ('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+')    )tblUnPivot    ORDER BY RowId    '    EXEC sp_executesql @sql1      IF CURSOR_STATUS('global','query_cursor')>=-1    BEGIN       DEALLOCATE query_cursor    END   END TRY  BEGIN CATCH    SELECT 1 RowId,'Error'ColumnName,ERROR_MESSAGE()[Value]     IF CURSOR_STATUS('global','query_cursor')>=-1    BEGIN       DEALLOCATE query_cursor    END END CATCH

Note that the report parameter created in the previous step is passed as a parameter to the dynamic SQL query in line number 4. The input query from the report parameter is run as a cursor using sp_executesql stored procedure. The column details of this cursor is obtained by using stored procedure master.dbo.sp_describe_cursor_columns and then the details of the columns are stored in the @tblColumnDetails table variable. Up to this point, the dataset and names of the columns are obtained. When the information for the column names and values are available, sp_executesql is used again with the UNPIVOT operator to transform the query output from multiple columns to three only columns: RowId, ColumnName and Value. This whole dynamic SQL code is wrapped in TRY CATCH blocks for exception handling.

Create a new dataset using dynamic SQL code

One of the steps when adding a new dataset

In the dataset properties set the query type to text. Copy and paste the dynamic SQL query into the dataset query textbox as shown below:

Query tab of dataset properties

Set the timeout value to a reasonable number, for example 600 seconds.

Go to the Fields tab of the dataset properties for manually adding the Fields Name and Fields Source. If these fields appear automatically as shown in the screenshot below, then it's fine otherwise create as suggested.

Fields tab of dataset properties

Another important aspect in this dataset is to control parameters so that they won't be created incorrectly. Go to the Parameters tab of dataset properties and check whether you have @cursor_source and @cursor_identity appearing in the list of dataset parameters. They appear from dynamic SQL code and are not needed for dataset parameters. If they appear, then please ensure that @cursor_source and @cursor_identity are deleted. To do that select each of these parameters and hit delete.

Showing two parameters that need to be deleted if they are showing up.

After deleting @cursor_source and @cursor_identity parameters. The dataset properties parameter tab will look like this figure below.

@SqlTxt is a dataset report parameter

Now we need to control the parameter value. In the parameters tab of the dataset properties, set the expression of @SqlTxt parameter by clicking on fx button and adding the following line of code to expression.

=JOIN(Parameters!SQLTxt.Label,CHR(13))

This expression will combine different lines into a single string

This expression will turn a multiple value parameter to a single string with multiple lines in it. That's what we will pass, a single SQL query with multiple lines in it.

Screenshot after setting up expression

Click OK to create the dataset.

A couple of important points to note:

If a dataset is created before deleting the unwanted parameters from the dataset parameters tab, then those parameters will automatically be added to report parameters. If it happens, then remove @cursor_source and @cursor_identity from the dataset parameters and report parameters as well.
Another important point to understand is that the queries which are going to be run through this report are not known at this stage, that's why we need to manually add RowId, ColumnName and Value fields to the dataset properties. We cannot expect that dataset fields will be automatically created.

Dataset with three columns RowId, ColumnName, Value

Creating an SSRS Matrix

From the toolbox, add a new matrix in the report. A new matrix will look like this:

A blank matrix in an SSRS reprot design.

From the dataset created in the previous step, drag and drop each field from the DataSet as marked below:

Dataset with three columns

SSRS Matrix showing where to drop fields from dataset.

After dragging and dropping the dataset fields, the matrix will look like this screenshot below:

SSRS Matrix after drag and drop of fields of

It is possible that in the older versions of Business Intelligence Development Studio that the Value appears with an aggregate function, for example SUM([Value]). To avoid this, right click on the Value textbox and set the expression to =Fields!Value.Value.

Screenshot of expression of Value

At this state the dynamic SQL report is functionally complete. Save the work and preview, it should show output against the different SQL queries.

Correcting Column Sequence

If you preview the report now, you might be able to notice that the sequence of columns from left to right is in alphabetical order A-Z, not in the order specified by the SELECT query. That's due to the default sort order of a group in a matrix. To change this sequence, select matrix and go to groups. You'll see two types of groups: 1) Row Groups and 2) Column Groups. In column groups, go to the properties of the ColumnName group and then select the sorting tab. Change the sort by option to RowId. As shown in the figures below.

Steps towards changing group properties

Sorting is changed to RowId instead

Changing the SQL Server Reporting Services Report Appearance

From an appearance point of view, we will make a few minor changes. First of all, we will hide the RowId column because there is no need to display it. To do this, right click, select Textbox Properties and then select visibility tab, and chose "Hide" in the visibility option.

Screenshot of hiding RowId column

Repeat this for the RowId header and RowId details textboxes.

The RowId columns are now hidden and will not appear when the report is run. The next step is to make RowId column small enough so that it will not occupy space. Move the matrix to top left corner and click to adjust the width of RowId to its minimum by moving it to the left as shown below:

Screenshot when size is being changed

After reducing the size of RowId width

Let's change the default colors and font of the textboxes in the matrix. Right click on the textbox, chose properties and then select the Font tab. In the following example, Calibri font of size 11pt is selected with Bold style.

Changing Textbox properties.

This will change appearance of the foreground text. To change the background color, select Fill tab in the same Text Box Properties window. As shown in the example, the fill color is Cornflower Blue.

Backgroud color being filled up

Select the color and click on OK button to close the dialog box.

The report is now complete and is ready for testing.

Using the Dynamic SQL Server Reporting Services Report

Here are some examples of using the dynamic SQL report in SQL Server Reporting Services 2016 which is connected to the Adventure Works 2016 database.

Example of query 1

Example: Output of query 1

Example of query 2

Example: of Output of Query 2

Next Steps

Check out these resources on MSSQLTips.com:
- Getting Started with Report Parts in SQL Server Report Builder 3
- Report Builder for Deploying Reports
- SQL Server 2017 Reporting Services Setup
- SQL Server Reporting Services 2017 Data Sources
- Execute Dynamic SQL Commands in SQL Server
- SQL Server Common Table Expression CTE Usage and Examples
- USE SQL Server's UNPIVOT Operator to Help Normalize Output