Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Create Dynamic SSRS Reports Using a Query as an Input Parameter


By:   |   Last Updated: 2018-11-06   |   Comments (7)   |   Related Tips: > 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:

  1. A multi-line text parameter which accepts a query as input
  2. A dynamic SQL query in the dataset, which transforms the output of a query to only three columns i.e. RowId, ColumnName and Value
  3. 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) [email protected]
   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 '[email protected]
   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 '[email protected]+CHAR(13)+
   @queryDynamicTable+CHAR(13)+
   'OPEN query_cursor
   
   FETCH NEXT FROM query_cursor INTO '[email protected]+'
   WHILE(@@FETCH_STATUS<>-1)
   BEGIN
      INSERT INTO @dynamicTable('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+') VALUES ('[email protected]+')
      FETCH NEXT FROM query_cursor INTO '[email protected]+'
   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


Last Updated: 2018-11-06


next webcast button


next tip button



About the author
MSSQLTips author Laeeq Hamid Laeeq Hamid has more than 10 years of experience working with SQL Server database application design, development and support for Australian government agencies.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, November 16, 2018 - 2:10:38 PM - Laeeq Hamid Back To Top

Hi Endrit,

Would you be able to tell me what SQL query you trying to pass as a paraneter. It will be great if you could  remove any sensitive infirmation from your query before sharing it.

Thanks

Laeeq


Thursday, November 15, 2018 - 12:29:12 PM - Endrit Back To Top

I have a problem with Cursor showiing me error like :

The variable '@ResultSet' does not currently have a cursor allocated to it.


Thursday, November 08, 2018 - 5:55:57 AM - Laeeq Hamid Back To Top

Further to my previous comments

Prevention from SQL injection has been discussed widely and there are some good articles on prevention and recovery from SQL injection available at reputable websites including MSSQLtips.com.

Here are a few of them

https://www.mssqltips.com/sqlservertip/3637/protecting-yourself-from-sql-injection-in-sql-server--part-1/
https://www.mssqltips.com/sqlservertip/3638/protecting-yourself-from-sql-injection-in-sql-server--part-2/
https://www.mssqltips.com/sqlservertip/1559/recover-from-a-sql-injection-attack-on-sql-server/


Also, there are TSQL codes available to verify if an SQL query contains SQL injection or not.

Thanks
Laeeq



Wednesday, November 07, 2018 - 7:12:48 AM - Laeeq Hamid Back To Top

Hi Tibor, Olu & Adrian


Thanks for your appreciation. With regards to security, the first is that reporting user should have read-only access to the database, they should not have access to updating or deleting data. secondly, I would create an access list and verify authorised users before letting them run a dynamic SQL report.


Thanks
Laeeq


Tuesday, November 06, 2018 - 9:16:46 AM - Adrian Hernandez Back To Top

 Hello,

Very good article. This can be very useful, but, as others have mentioned there are security concerns that need to be addressed. And even after modifying underlying code the fact that it runs Dynamic SQL is always a concern. Dynamic SQL in my experience should rarely be used because of potential security issues.


Tuesday, November 06, 2018 - 6:09:38 AM - Olu Back To Top

 Hi

Thanks for the article, this would have been heaven sent as I am looking to implement a solution like this. However it does not prevent SQL injection attacks. There is nothing stopping a user from doing something like this: 

   DECLARE @sqlQuery NVARCHAR(MAX) ='Select top 1 contact_id from contact;  Update contact set address1 = ''MyAdress1'' where contact_id = 0'

   SET @sqlQuery = 'DECLARE  query_cursor CURSOR FOR '[email protected]

   EXEC sp_executesql @sqlQuery

 

Any thoughts \ suggestions on this?

 

Thanks

 


Tuesday, November 06, 2018 - 3:55:52 AM - Tibor Nagy Back To Top

Hi,

Interesting solution but I have some doubts about the security of this approach...


Learn more about SQL Server tools