A while ago I published an article on how to create custom Service Manager reports –> see here. This post is a follow up article that explains how to use report parameters to filter the displayed results. This can be useful if you want to have filters to only display specific records in your report. Read on to see how this works.
I will again use SQL Report Builder V3 for this. First create a new report that displays some information. In this example I have a very simple reports that displays Domain User information directly from the CMDB. One column shows the domain name where the user accounts lives.
I created a simple query directly for the table where the domain users are stored and get all the records with all the attributes. Normally you would optimize the query a little bit and user views instead of tables, but for a quick example this approach is fine. The records are then displayed in a simple table.
Now let’s add a new parameter to the report, define a name for it, define the data type and some other options. In this case I want to allow the report user to select multiple values if needed.
For the selectable values we have two options. Either we can specify manual values or use a query that gives us back available values. I will show how to define values manually and come back to the query-based approach later. So I just enter some static values that will be selectable when executing the report. If needed, a default value can also be set.
After the completion of the wizard, the new parameter is displayed.
Now we have to reconfigure the table in the report to use the parameter as a filter. By right clicking on the table I can select the table properties.
For defining the filter, I add a new entry and configure the expression. I select the domain field, define the operator (in this case “in” because I defined that multiple values can be selected) and then define the value. The value will be the user selection. Using the button, the parameter can be selected (double click).
When the report is run, the parameters are available and can be selected as needed.
The available report parameter values are working great, but the values are static. There may be times where you want to have dynamic values available to select. This is indeed possible by using queries to populate the selectable values. To make this work, we need to add another data set that gets the available values from somewhere. In this example I want to get all the available domains directly from the CMDB. So I prepared a query that gets those.
By using a “Select Distinct” I make sure that every value is only returned once.
When the data set is ready, it’s now time to reconfigure the Parameter. Instead of using static values, I now change the configuration so that the data set records are available as selectable values.
When now running the report again, the returned values from the query are available and can be used to filter the returned records. But this time it’s dynamic and therefore (most of the time) more useful.
When the report design is complete, deploy the report as explained in the previous post.