MS Reporting Services – Dynamic SQL
Over the last couple of weeks, I have started to use and get my head around Microsoft Reporting Services. As with all new products, it takes a while to start to understand the feature-set of the product and how to work-around some of the limitations.
The power of SQL Reporting Services is the ability to create and deploy reports that end-users can consume and refine if necessary, without giving them direct access to the database. It also make sense that these same end-users can refine datasets as required before exporting to MS Excel.
In my case, I wanted to provide an easy way for users to be able to modify some of the search logic for a given report once published out i.e. Ability to select a search field, basic logic to search on and required search value. In order to do this, I had to convert the SQL within the dataset into ‘Dynamic SQL’. This small tutorial will take you through the steps of how to do this using the MS AdventureWorks Database.
On a side note, I am also testing out ScreenSteps Pro, which helps in the production of documentation and publication. It looks pretty good so far!
1. Create Datasource to Adventure Works
Start by setting up a connection to the Adventure Works Database
2. Create Parameter Variables
For this exercise, we create three parameter variables. I have named these as follows with the following attributes for the ‘General’ Screen
- QueryField | DataType – Text | Prompt – Search By
- QueryText | DataType – Text | Prompt – Search Value (no other parameter settings need to be defined.
- QueryLogic | DataType – Text | Prompt – Logic
3. Parameter Value Options – Query Field
Unfortunately ScreenSteps only allows for single images under each step, so I will not include screen shots of each parameter properties. Create fields as shown in the above image. The Label field is what will be displayed to the users. The Value is the name of the table columns we will be searching by.
On the default value screen, select LastName. This will simple pre-select the drop-down list for report viewer users to a default value.
4. parameter Value Options – Query Logic
This is where we add the SQL logic. I had difficulty trying to get report builder to evaluate ‘=’ as a value, so I resorted to the string equivalent. It will become apparent how this works later in the exercise. Once again, set the default value to Equal.
5. Creating Data-Set
For starters, we will create a simple dataset using the following SQL Query.
SELECT title,firstname,lastname,phone FROM [Person].[Contact]
Name the dataset DS_PersonRecords and Select OK.
6. Create & Bind Data-Set to Data-Table
Create a table and bind the dataset to it. I do this by dragging the columns from the Data-set we just created to the columns to the table.
Do a quick test by selecting the ‘Preview’ tab.
7. Test Report
We have now added the criteria to our report for which we want to search by i.e. Fieldname, Operator and Value. To proceed with the test, just enter a value into the Search Value field and press the ‘View Report’ button. Note: These parameters are not yet hooked up to our SQL query. We will do that next.
8. Creating the Dynamic SQL
Now that you have tested that the basic report is functioning, lets proceed with modifying the SQL to handle our search logic.
Re-open the DS_PersonRecords dataset.
Select the fx button to the right of the query pane and past the following SQL in.
= "SELECT title,firstname,lastname,phone FROM [Person].[Contact] WHERE " & Parameters!QueryField.Value & " " & iif(Parameters!QueryLogic.Value = "equal","= @QueryText","like @QueryText")
I will quickly explain how this works. We are now treating the following sql as a string expression. This is evaluated as such when the query starts with the equal sign. i.e. = ” ”
With the use of the variables and a little logic, we are creating a dynamic string. We can reference our parameter variables in two ways. i.e. Parameters!QueryLogic.Value or @QueryLogic. Referencing by the first method Parameters!QueryLogic.Value must be outside the string, so we close the string and concatenate using &.
Properties referenced by @ can be done directly within the string.
I have also added a simple if statement into the mix to test whether or not the QueryLogic is equal or like and display the necessary SQL as needed based on the outcome.
Once you have pasted in the SQL, select OK.
9. Binding Parameters
The last step of the exercise is to Bind the Parameters to the dataset. I have kept the Parameter Name and Parameter Value the same to avoid confusion.
Select OK once finished.
10. Preview Final Results
Go ahead and preview the final results. To test that our like operator is working, select Like from the list. Search By Surname and enter fra% in the search value, % being a wildcard.
This will pick up all records with a surname starting with the letters fra. I returned 2 results.
This is just a quick walkthrough of how you can go about adding in additional logic to create more useful and dynamic reports for end users. From here you can extend the capabilities further.
Let me know if you have come across any other methods to achieve similar results.
