Report Studio

Creating a Detail or Summary Filter

To add a filter:

On the Insertable Objects pane, on the source tab , drag the filter you want to the report.

You must drag the filter to an object that is linked to a query. If there is more than one query defined in the report, drag the filter to an object linked to the query you want.
To create your own filter, from the Data menu, click Filters and click Edit Filters.

If you have more than one query defined in the report, you must first click an object linked to a query. If there is more than one query defined in the report, click an object that is linked to the query you want.

Tip: You can also define filters in Query Explorer .
In the Filters dialog box, decide what type of filter you want to create:
To add a filter that will apply to detail values, click the Detail Filters tab.
To add a filter that will apply to summary values, click the Summary Filters tab.

Click the add button .
In the Create Filter box, decide what conditions to include in the filter:
To create a simple filter condition based on only one data item, click Custom based on data item, select a data item from the query, and click OK.
To combine multiple filter conditions, click Combined, and click OK.
To create a filter condition that uses advanced calculations, click Advanced, and click OK.
If you create a simple filter condition based on one data item:
In the Filter Conditions dialog box, specify the details for the condition.
If you want to parameterize the filter so that users are prompted when they run the report, select Prompt for values when report is run in viewer.
If you want the filter to include null values, select Include missing values (NULL).
Click OK.
If you create a Combined filter:
In the Create Filter dialog box, select the data item for the first filter condition and click OK.
In the Filter Conditions dialog box, specify the details for the first condition and click OK.
Click the new condition button and in the Create Filter dialog box, select the data item for the second filter condition and click OK.
In the Filter Conditions dialog box, specify the details for the second condition and click OK.
In the Combined Filter dialog box, use the AND, OR and NOT operators to combine the filter conditions. Select the conditions you want to combine, and click an operator. To change an operator, double-click the operator to toggle among available ones or select the operator and then select a different one from the list.
Click OK.
If you create an Advanced filter condition, in the Filter Expression dialog box, define the filter expression:
To add data items that are not shown in the report, on the Source tab, double-click data items.
To add data items that are in the report but not necessarily in the model (such as calculations), on the data items tab , double-click data items
To add data items from a specific query in the report, on the queries tab, double-click data items.
To add functions, summaries, and operators to the filter expression, on the functions tab, double-click elements.
Notes:
You can insert only functions that return a boolean value. For example, you cannot insert the function topCount because it returns a set of data. Filter expressions must resolve to a boolean in order to be valid.
The eProject functions are used internally in the Data Presentation Model and are not intended for use by customers.
To include a value that is derived from a parameter, on the parameters tab, double-click a parameter.
Parameters are used to define prompts, drill-through reports, and master-detail relationships.
To add a macro, on the macro tab, double-click the parameter maps, session parameters, or macro functions you want to add to the macro expression.

Note: Use Insert macro block to get your started with creating the macro expression. Insert macro block inserts number signs in the expression. Make sure that the macro expression you create is between the number signs.
Click the validate expression button and click OK.
Click OK.

In the Usage box, specify whether the filter is required, optional, or not to be used.
If you create a detail filter, in the Application box, click one of the following options:
To apply the filter to individual records in the data source, click Before auto aggregation.
For example, you want to filter out individual orders of a specific product type within a product line that generated less than one thousand dollars in revenue.
To apply the filter to data after the query has grouped and summarized at the lowest level of detail, click After auto aggregation.
For example, you want to filter out product types within a product line that generated less than ten thousand dollars in revenue.

For example, you have a list that contains the data items Product line, Product type, and Revenue. The revenue values you see are aggregated to the product type level. If you create detail filter on Revenue and you choose to filter values before auto aggregation, you are filtering non-aggregated revenue values. If you choose to filter values after auto aggregation, you are filtering revenue values aggregated to the product type level.

If you are filtering a summary, under Scope click the ellipsis points (...) and select the grouping level for which you want to apply the filter.
For example, a revenue report is grouped on product line and product type. You can choose to filter total revenue for either the product line or product type level.

If the query in which you are creating the summary filter is linked to more than one data container, the data item that you select as the grouping level must be grouped in all data containers linked to the query. Otherwise, the report will not run successfully.

Note: To filter at the overall level, do not click a level. For example, if a report has an overall footer that shows the total revenue for all products, by not choosing a level you will apply the filter to the footer. In addition, if you are using a dimensional data source, excluding a parent level excludes its children, and excluding all children excludes the parent.