Pre-Filtering Microsoft SSRS Data (Dataset Example)

When sourcing data from Microsoft SSRS for Metrics, Reports, External Reports, Dimensions, and Datasets, you can pre-filter your data before fetching it. This function allows to focus on the slice of data that you really need and exclude those values that are currently irrelevant for you and your research.

PREREQUISITES:

Establish Connectivity to Microsoft SSRS

1. FINDING FILTER (PARAMETER) NAMES IN MICROSOFT SSRS

Most of the data in Microsoft SSRS can be filtered by different criteria.

Filters (aka Parameters) are available in the Report header, like "Year" and "Product" in this example. Drop-downs include Filter Values. 

2. Define a Source Object for Data Collection

Start by creating an element or Dataset. Once you get to the process of Data Collection, define the following:

  1. Choose Data Source that connects Microsoft SSRS and Metric Insights. For more information, see: Establish Connectivity to MS SSRS
  2. Choose Data Collection Trigger that is going to initiate updating information in this Element
  3. Choose Element from the pop-up list of available MS SSRS objects that can be a source of data
    • Each item in the list is represented as the path (hierarchy) to the respective Report in MS SSRS.
    • If the required item is not displayed, click the Refresh icon at the upper right corner of the pop-up

3. Manage Filters in MI

3.1. Add MS SSRS Filter to Metric Insights

Once filters are added to a Metric/Report or External Report for the first time, they are going to be automatically added to all new respective elements with the same Data Source/Report.

NOTE:

  • External filters are tied to MS SSRS Reports, not Metric Insights' elements. This allows Filters to be reused for multiple elements (without creating new Filters every time an element is created in Metric Insights.
  • If there are more External Filters or Filter Values that you would like to use for the current element, you can always set the redundant ones to "ignore".

When creating a Metric/Report/External Report from MS SSRS, after you define the Report that should serve as a Data Source, you may pre-filter information that is going to be fetched.

Under Manage Filters > [+Filter], you can choose one of the four options for adding filters individually. Sections below provide information on how to do this.

3.1.1. Dataset

If you have already created Datasets from MS SSRS in Metric Insights, you can choose one for pre-filtering:

  1. Enter Microsoft SSRS Filter Name
  2. Choose the corresponding option for filter creation
  3. Select Dataset & View
  4. Specify Value Column
  5. [Save]

NOTE: Datasets used here must have Values that exactly match the Filter Values in MS SSRS (if the Values do not match, the Filter will not work).

3.1.2. Map to Dimension Values

If you have already used MS SSRS filters to create Dimensions in Metric Insights, you can quickly choose which Dimension Values you want to use for pre-filtering:

  1. Enter Microsoft SSRS Filter Name
  2. Choose the corresponding option for filter creation
  3. Select Dimension
    • All Dimension Values are going to be loaded to the Values list automatically
  4. [Save]

NOTE: Dimensions used here must have Values that exactly match the Filter Values in Microsoft SSRS (if the Values do not match, the Filter will not work).

3.1.3. Enter Manually

  1. Enter Microsoft SSRS Filter Name
  2. Choose the corresponding option for filter creation and [Save] to display the [+Add Value] button
  3. [+ Add Value] and add the name of the filter value
  4. [Save]

3.1.4. Date

  1. Enter Microsoft SSRS Filter Name
  2. Choose the corresponding option for filter creation
  3. Select Date Format used in the MS SSRS Report
  4. [Save]

3.2. Deleting Filters

To delete a filter, go to Manage Filters and click the Trashcan icon next to the filter.

3.3. Add Filters to a Results Set from Microsoft SSRS

  1. Click the Pencil icon in the filter row
  2. Choose how you want to use the filter
  3. [Save]