To add a filter to an existing BI query:
1.Open Business Intelligence and Reports from the Desktop
2.Select the Business Intelligence tab.
TheBusiness Intelligence|Search page appears.
3.Search for and select the business intelligence (BI) query that you want to work with.|
The Business Intelligence|Results page appears.
4.Select the Filters tab.
The Business Intelligence|Filters page appears.
5.From the pick filter section, select the category that contains the criteria that you want to work with.
Once you select a category, it expands to display all related criteria that you can use in your filter. This is illustrated in the image below. Each category option (listed in the pick filter section) contains related criteria from which you can choose when creating your filter.
You can also search for specific filters using the 'Search' option.
|
Information The Formula filter option is only available if a formula has been configured on the Business Intelligence|Filters page. |
6.Select the criteria that you want to work with.
The set filter section populates with fields as illustrated in the image above. Based on the criteria selected from the pick filter list, you can then choose how you want to filter the resulting members.
Once you have selected a field name related to the chosen category, you then chose from the filter criteria types found in the set filter section.
7.Use the table below to complete the fields in the set criteria section.
Field |
Description |
Type |
Enables you to select between: •Has a Value: Return all rows that contain data in the selected filter field. •Is Blank: Return all rows that do not contain data in the selected filter field (blank). •Match Condition: Return all rows that contain data in the selected filter field that matches the value(s) specified in the 'Values' field. •Match Condition (include blanks): Return all rows that contain data in the selected filter field that matches the value(s) specified in the 'Values' field as well as those that do not contain data (blank). •Match Condition (session): Return all rows that contain data in the selected filter field that matches session variables selected in the list in the 'Values' field. Whenever a user logs in, session variables are set based on the user’s current role. Thus session filtering makes the query dynamic based on which user is running the query. For example, you could create a customer BI to filter for those customers created by the current user. For this example, refer to Example Session Filter – Filtering Customers By Create User. You can view a list of session variables by opening the Registry and navigate to the Registry::EN::Session node. |
Operator Type |
If you are creating multiple filters, you can specify whether you want the filters joined by an AND or an OR operator: •AND: All of the And filters will be combined and results returned when all of the AND conditions are true. •OR: All of the Or filters are combined and results returned when any of the OR conditions are true.
When the filters are combined along with any criteria, the resulting expression appears as follows: (all criteria) AND (([and filters]) OR ([or filters]))
When users run the query, all the filters are always applied along with any criteria specified. Consider an example where you want to restrict results to only those events in Venue A, but want to enable users to search for events by date range. The query results would display those events that match the date range specified by the user and are in Venue A: (event date range) AND (Venue A)
It is not recommended to use aggregate functions and Or filters in the same BI query. |
Function |
The functions available depend on the 'Type' selected and whether the filter field is a GUID, string, date, or number. If the 'Type' selected is Has a Value or Is Blank, the 'Function' dropdown is disabled. For definitions of functions by data type, refer to Functions by Data Type. |
Condition |
Enables you to apply one of the following conditions to the filter, type and function selected: •Greater than: Greater than value in 'Values' field. •Greater than or equal to: Greater or equal to value in 'Values' field. •Less than: Less than value in 'Values' field. •Less than or equal to: Less than or equal to value in 'Values' field. •Does not Equal: Does not equal value in 'Values' field. •Equals: Equals value in 'Values' field. •Between (including both values): In between range of values in 'Values' field, inclusive. •Not in Range: Not in range of values in 'Values' field. •Begins with: Begins with value entered in 'Values' field. •Ends with: Ends with value entered in 'Values' field. •Contains: Contains value in 'Values' field. If the Type selected is 'Has a Value' or 'Is Blank', the 'Condition' dropdown is disabled. |
Values |
The specific data that the type, function, and condition is being applied to. For example, to find all customers with the last name Smith, the value would be Smith. For text fields, you can use wildcard characters. For more information, refer to Using Wildcard Characters to Perform a Search. To add additional entry fields, press CTRL+TAB (or CONTROL+OPTION+TAB) once you have made an entry in a field. When using multiple fields against a single filter, resulting record data is returned when it matches any of the values entered. If the 'Type' selected is Has a Value or Is Blank, the 'Values' field is disabled. |
8.For each filter you want to include in query, repeat from the process Step 5.
9.To preview your changes, select the Preview tab.
The Business Intelligence|Preview page appears.
10.To extract to Microsoft Excel or PDF, refer to Running a BI Query.
11.If you want to save your changes, expand the save lists and extracts section of the Business Intelligence|Results page and save the BI query.
