Creating Custom Service Manager Reports with Parameters

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.

image

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.

image

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.

image

image

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.

image

image

After the completion of the wizard, the new parameter is displayed.

image

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.

image

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).

image

image

When the report is run, the parameters are available and can be selected as needed.

image

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.

image

image

By using a “Select Distinct” I make sure that every value is only returned once.

image

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.

image

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.

image

When the report design is complete, deploy the report as explained in the previous post.

cheers
Marcel

This entry was posted in SCSM and tagged , , , , . Bookmark the permalink.

7 Responses to Creating Custom Service Manager Reports with Parameters

  1. Pingback: Creating Custom Service Manager Reports | SCSMfaq.ch

  2. Vinh says:

    Hi Marcel,
    Your tutorial works out great in our environment. When you get a chance, could you please provide suggestions on how to resolve the FONT size issue. When I deploy the report in SM, the font size gets seems to get really big unless I change the font size to 100% from the drop-down. Is there a way to set the default font size somewhere so that we don’t encounter such issue? Thank you,
    Vinh

  3. Mark says:

    Dear Marcel,

    Thanks for the effort in explaining this. It helped us a lot in figuring out many other things. I have a situation and wonder if you came across such a scenario. I am trying to create a new report of extended work item where that report should be the same as the related original OOTB work item report with addition of the extenstion reflected as a new column. When I try to copy the orginial .rdl file and then try to run it in SQL reports builder I can see that the list of parameters is way more than what I have seen in the original report. Why I do not just get the list of the same parameters during run time. If you go on this gentleman blog http://social.technet.microsoft.com/wiki/pt-br/contents/articles/13305.customizing-scsm-reports-using-extended-fields-on-an-management-pack.aspx
    you could see on step 5 the extra parameters that I was talking about. I just wanted to add one more parameter but I am ended with many more. I wonder if copying would not copy the parameters properties set already which made those parameters hidden or captioned differently in the original reports.
    Any help would be much appreciated

    Cheers
    Mark

  4. atmayoni says:

    Hi Marcel,
    How we can can put Start and End Date parameters to a form so that we can easily filter according to the created date of incidents?

  5. Pingback: Create a report for “Support Group Changes” | marcelzehner.ch

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s