By: Laeeq Hamid | Comments (45) | 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:
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.
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
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:
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.
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.
After deleting @cursor_source and @cursor_identity parameters. The dataset properties parameter tab will look like this figure below.
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 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.
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.
Creating an SSRS Matrix
From the toolbox, add a new matrix in the report. A new matrix will look like this:
From the dataset created in the previous step, drag and drop each field from the DataSet as marked below:
After dragging and dropping the dataset fields, the matrix will look like this screenshot below:
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.
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.
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.
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:
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.
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.
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.
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
About the author
Laeeq Hamid has more than 10 years of experience working with SQL Server databases. Laeeq gets involved in database application design, monitoring, development, data migration, performance tuning and ETL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
