To add criteria to an existing BI query:
1.Open Business Intelligence and Reports from the Desktop
2.Select the Business Intelligence tab.
The Business 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 Criteria tab.
The Business Intelligence|Criteria page appears.
From the pick criteria section, select the category that contains the criteria that you want to work with. Alternatively, you can search for criteria.
Once you select a category, it expands to display all related criteria that you can use in your query. This is illustrated in the image above. Each category option (listed in the pick criteria section) contains related criteria from which you can choose when creating your query.
|
Information The Formula filter option is only available if a formula has been configured on the Business Intelligence|Formulas page. |
5.Select the criteria that you want to work with. The set criteria section populates with fields as illustrated in the image above. Based on the criteria selected from the pick criteria list, you can then choose how you want to filter the resulting members further. Once you have selected a field name related to the chosen category, you then choose from the filter criteria types found in the set criteria section.
6.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 criteria field. •Is Blank: Return all rows that do not contain data in the selected criteria field (blank). •Match Condition Return all rows that contain data in the selected criteria field that matches the value(s) specified in the 'Values' field. •Match Condition (include blanks): Return all rows that contain data in the selected criteria field matching 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 criteria 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. |
Function |
The functions available depend on the 'Type' selected and whether the criteria 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 criteria, type and function selected: •Greater than: Greater than the value in the 'Values' field. •Greater than or equal to: Greater or equal to the value in the 'Values' field. •Less than: Less than the value in the 'Values' field. •Less than or equal to: Less than or equal to the value in the 'Values' field. •Does not Equal: Does not equal the value in the 'Values' field. •Equals: Equals the value in the 'Values' field. •sBetween (including both values): In between the range of values in the 'Values' field, inclusive. •Not in Range: Not in the range of values in the 'Values' field. •Begins with: Begins with the value entered in 'Values' field. •Ends with: Ends with the value entered in 'Values' field. •Contains: Contains the value in the '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 are 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 criterion, 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. |
6.To specify the position of the criteria when viewing the BI query, enter the numeric position in the 'Load Order' field in the current criteria section. Zero places the criteria closest to the top. This is useful if you are saving the BI query for others to use. Place criteria most frequently used closer to the top of the page or related criteria close to each other.
7.For each criterion you want to include in a query, repeat the process from Step 5.
8.To preview your changes, select the Preview tab.
The Business Intelligence|Preview page appears.
9.To extract to Microsoft Excel or PDF, refer to Running a BI Query.
10.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.
Functions by Data Type
Data Type |
Function |
Description |
GUID |
Count |
Counts the instances of the selected criteria. |
String |
Avg as Number |
Treats the data as a number instead of a string, and calculates the average. |
Cast as Number |
Treats the data as a number instead of a string. |
|
First Word |
Matches the first word of a string. The first word is considered to be everything preceding the first space or dash. This allows you to match based on the first part of a postal code. |
|
Max |
Determines the maximum value of the selected criteria. |
|
Max as Number |
Treats the data as a number instead of a string, and determines the maximum. |
|
Min |
Determines the minimum value of the selected criteria. |
|
Min as Number |
Treats the data as a number instead of a string, and determines the minimum. |
|
Sum as Number |
Treats the data as a number instead of a string, and determines the sum. |
|
To Lowercase |
Ignores uppercase letters and matches lowercase text entered in the 'Values' field. |
|
To Uppercase |
Ignores lowercase letters and matches uppercase text entered in the 'Values' field. |
|
Date |
Age in Years |
Matches the number of elapsed years since the supplied date. |
Count |
Counts instances of the selected criteria. |
|
Date |
Matches the date portion (not date and time) of the date/time selected in the 'Values' field. |
|
Day |
Matches the day of the month of the date/time selected in the 'Values' field. |
|
Day of Week |
Matches the day of the week of the date/time selected in the 'Values' field. |
|
Day of Year |
Matches the current number of the day from the supplied date based on the calendar year. |
|
Days to Anniversary |
Matches the number of days until the next time the supplied date comes around. |
|
Hour |
Matches the hour of the date/time selected in the 'Values' field. |
|
Max |
Matches the maximum date/time selected in the 'Values' field. |
|
Min |
Matches the minimum date/time selected in the 'Values' field. |
|
Month |
Matches the month of the date/time selected in the 'Values' field. |
|
Time |
Matches the time of the date/time selected in the 'Values' field. |
|
Week of Year |
Matches the current number of the week (Week 1, Week 52) based on the supplied date. |
|
Year |
Matches the year of the date/time selected in the 'Values' field. |
|
Number |
Average |
Matches the average value of the selected criteria. |
Count |
Matches the count of the selected criteria. |
|
Max |
Matches the maximum value of the selected criteria. |
|
Min |
Matches the minimum value of the selected criteria. |
|
Sum |
Matches the sum of the selected criteria. |
