How to add a Filter to a Report?
search cancel

How to add a Filter to a Report?

book

Article ID: 179981

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

 

Resolution

Question
How to  add a filter to a report and allows narrowing the report results to contain just the resources in the Filter.

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

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

Setting up the Report for the Filter selection

Locate the default filter to use in the report

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

 

Go to Manage > Filters in the console.

Drill into Filters > Computer Filters > Windows and right click and select Properties for '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 use any filter 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

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

Select Add > New Parameter.

Create a Report Parameter using the values below:

Parameter
Name: ReportFilter (This is the label/name of the parameter for use 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: Leave at default values (Label Visible and Show selector control in the parameter pane checked)

Set up default filter information within the report - Query Parameters

Navigate to 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 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:

 

Joins: 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 (the ResourceGuid should be kept. You can modify the [Computer].[Guid] to suit the report and resource type you're working with).

Now the Query builder window should look something like this: 

 Select the "Add Filter Expressions" link or the "Filter Expressions" tab:

 

 For the Join Filter Expression drop down select "Equals":

 

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.

Save/apply your changes and the report will now use a Filter for narrowing.