How can I add a filter to a report?

book

Article ID: 179682

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
I am need to add a Filter to a Report allowing me to narrow the Report results to contain just the resources in the Filter, how can this be done?

Answer
There are two types of reports that you would want to filter on.

One type of report would be the built in resource reports such as a computer report, which will be used in this example. Another type of report would be a Raw SQL report.

Parts of the methodology to set up the filter for selection as part of the report as well as the parameters passed to the query are the same. The differences will be outlined and demonstrated where applicable.

Setting up the Report for the Filter selection

Locate the default filter to use in the report

First we will pick a filter that will be used as the default filter. For this example we will use the 'Windows Computers' filter.

Go to Manage > Filters in the console. Now drill into Filters > Computer Filters > Windows and right click and select properties on 'Windows Computers';

As highlighted above, you will see the GUID (unique identified number) for the filter, that we want to use as the default filter selection. In this case the GUID is e3a71b08-1612-44a6-9f71-7d359d5475b4. You can modify this as needed, to reflect the default you want, as long as you make sure it's a valid GUID for a valid filter.

Set up default filter information within the report - Report Paremeters

In this example we've created a new (default) "Computer Report" to use as a base. For other report types these steps are similar.

Add a Report Parameter for the filter by selecting the "Report Parameters" tab. Here select Add > New Parameter.

For the Parameter Enter the following information;

Parameter
Name: ReportFilter (This is the label/name you give the parameter, when used/selected as a Query Parameter)
Description: Select Report Filter (This is the description of the parameter you see in the Report)
Type: Basic GUID Parameter (The input we want to pass is a GUID, which identifies our selected filter)
Default value: e3a71b08-1612-44a6-9f71-7d359d5475b4 (The GUID of the default filter we want to use - as found above)
Test value: e3a71b08-1612-44a6-9f71-7d359d5475b4 (Using the same as in Default value, but you can change for testing / preview of results)

Value Provider
Name: Filter Chooser Parameter Control (Choose the Filter Chooser Parameter Control, allowing you to select a filter in the report)
Configuration: Left at default values (Label Visible and Show selector control in the parameter pane checked)

Set up default filter information within the report - Query Paremeters

Now select the Query Parameters tab (Note; To navigate to this you may need to select the Data Source tab and then the Query parameters tab).

The ReportFilter Report Parameter you set up above should now be available as a Query Parameter. Select Add > Select ReportFilter. This adds in your Report Filter as a query parameter and thus exposes it to the report query. However, at this time for both reports built in the report builder interface completely as with Raw SQL reports, you will need to take the following additional steps to perform the filtering on your report.

Adding Filter to Query Builder Report (Computer Report)

Go back to the Query tab, and if using a default Computer Report you should see the following;

Click the "Create Joins" link and the following dialog should appear, enter in the information as shown;

Join (first dropdown) : Select INNER join for the join type
For the table (second dropdown) : Select CollectionMembership (this table contains all filter memberships mapping them to resources)
On: [Computer].[Guid] = ResourceGuid for the last two dropdown menus (the ResourceGuid should be kept, you can modify the [Computer].[Guid] to suit the report and resource type you're working with).

When done with the above your Query builder window should look something like this;

Now select the "Add Filter Expressions" link or the "Filter Expressions" tab and you should see something like this;

For the Join Filter Expression drop down select "Equals", and you should see a dialog like this;

For the {0.EN_US} operand select "Field" and then [CollectionMembership].[CollectionGuid], this is easiest located by typing on collectionmembership and waiting for the selection values to load.
For the {1.EN_US} operand select "Parameter" and then ReportFilter as the value.

At this point you can save/apply your changes and your report will now use a Filter for narrowing.

Attachments