12 Creating and Maintaining Conditions
This chapter explains how to implement conditions using Discoverer Administrator, and contains the following topics:
-
"About outer joins and the DisableAutoOuterJoinsOnFilters registry setting"
-
"How to create row level security using a mandatory condition"
-
"How to enable summary folders if a source folder includes a mandatory condition"
12.1 What are conditions?
Conditions filter worksheet data, enabling Discoverer end users to analyze only the data they are interested in. For example, you might want to give Discoverer end users access to data for 2007, but not 2006 or 2005.
You also use conditions to restrict access to sensitive data. By imposing mandatory conditions, only non-sensitive data is made available to Discoverer users (see "What are the different types of condition?"). You can make sure that Discoverer end users only see the data that you want them to see.
As a Discoverer Administrator, you can anticipate commonly used conditions and make them available to Discoverer end users so that they can apply them in worksheets, and work efficiently.
Note: Discoverer Plus users can also create their own conditions.
12.1.1 About condition categories
Conditions are categorized as follows:
-
Simple conditions - these contain a single condition statement. For example, 'display data where year = 2001'.
-
Advanced conditions - these contain two or more condition statements. For example:
display data where year = 2001 AND quarter = 1 AND region = south.
-
Nested conditions - these contain condition statements that are defined within other condition statements. For example:
display data where year = 2001 AND (region = north OR region = south)
here, the OR clause is nested within the AND clause.
-
Advanced nested conditions - these contain two or more condition statements and also include condition statements defined within the advanced condition. For example:
display data where year = 2001 AND quarter = 1 AND region = south AND (region = north OR region = south)
here as in the nested example, the OR clause is nested within the AND clause)
As an alternative to creating advanced conditions, you might want to create two or more single conditions and apply them at the same time; Discoverer users can then be more selective about which parts of the condition they use.
Note: There can be subtle differences between applying advanced conditions and equivalent multiple single conditions (for more information, see Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus).
12.1.2 How do conditions work in Discoverer?
Conditions work in Discoverer by matching condition statements against worksheet data so that:
-
data matching your condition statements is displayed in workbooks
-
data not matching your condition statements is not displayed in workbooks
For example, you might want to limit the display of data to the last two years of sales. Or, you might want to see the data for only two types of sales items. Each of these tasks involves filtering the data to find information that meets the conditions.
12.2 What are the different types of condition?
There are two types of condition:
-
mandatory condition
-
optional condition
You create mandatory and optional conditions in the same way. However, note the following:
-
mandatory conditions are always applied to a worksheet that contains one or more items from the folder that contains the condition. Discoverer Plus users are not notified of mandatory conditions and cannot turn them off.
For example, you might want to assign a mandatory condition to sales data for regional sales managers, limiting their view of sales to the region for which each manager is responsible.
-
optional conditions can be turned on or off as required by Discoverer Plus users. They can also view a condition's formula, but they are not allowed to edit the formula.
For example, a Vice President responsible for all sales regions should be able to see all of the sales data, and also be able to apply conditions to see sales data relating to specific sales regions.
The table below shows further differences between mandatory and optional conditions:
| Mandatory condition | Optional condition |
|---|---|
| Always applied to the results of a folder. | Only applied to the results of a folder if selected in Discoverer Plus. |
| Used by the Discoverer manager to permanently restrict the rows returned by a folder. | Provided by the Discoverer manager as a shortcut to help users build conditions more easily. |
| Invisible in Discoverer Plus. | Visible (but not editable) in Discoverer Plus. |
| When created in a complex folder, can reference items in the source folders. | When created in a complex folder, can only reference items in the complex folder. |
| Affects the result set (in the database) of the folder definition in the EUL. | Does not affect the result set (in the database) of the folder definition in the EUL (because an optional condition is only used when applied in Discoverer Plus). |
| When added, changed, or deleted, cause any summaries based on the folder to become invalid, because their result set no longer matches that of the folder.
These summaries are set to “Refresh required” and must be refreshed to be made available again. |
When added, changed, or deleted have no affect on summaries based on the folder. |
Note: If you create a complex folder from items in a folder that contains a mandatory condition, the results in the complex folder are restricted by the mandatory condition on the original folder. You can see any mandatory conditions that impact a complex folder using the Components tab of the complex folder's Properties dialog.
12.3 How to create simple conditions
You create a simple condition when you want Discoverer end users to filter worksheets in a new way. You add the condition to a folder so that Discoverer end users can apply the condition when using workbooks based on the folder.
For example, you might want to create an optional condition that filters data to display results for the current year, because Discoverer end users might only be interested in data for that year. Or, you might want to restrict access to sensitive data by imposing a mandatory condition on a particular area of data (see "What are the different types of condition?").
To create a single condition:
-
On the "Workarea: Data tab", do one of the following:
-
select the folder in which you want to create the condition
-
select the item that you want to form part of your condition
-
-
Choose Insert | Condition to display the "New Condition dialog".
Note: If you choose Insert | Condition without first selecting a folder or item, Discoverer prompts you to choose a folder or item before displaying the dialog.
-
(optional) To have Discoverer generate a name for your conditions automatically, select the Generate name automatically check box.
Note: By default, Discoverer Administrator creates a default condition name for the condition, based on the condition itself. However, you can specify your own name for the condition.
-
(optional) To specify your own name for the condition:
-
Clear the Generate name automatically check box.
-
Enter a name for the condition in the Name field.
-
-
(optional) Enter a brief description of the condition in the Description field.
The text entered here is displayed when the condition is edited in Discoverer Administrator or when the condition is highlighted by end users.
-
Specify the type of condition from the Type drop down list as follows:
-
select Optional if you want Discoverer end users to be able to turn the condition on or off
-
select Mandatory if you want the condition to always apply
-
-
Define the condition statement in the Formula area as follows:
-
Choose what item or calculation you want to filter the data on using the Item drop down list. For example, choose Year if you want to display data for a particular year. To create a calculation that you want to filter data on click Create Calculation... (for further information, see Chapter 11, "Creating and Maintaining Calculated Items").
-
Choose how to match data against the item using the Condition drop down list. For example, choose the greater than (>) symbol to filter data where the item value is greater than a particular number.
-
Define what data or items you want to match against using the Values field. For example, enter 2002 to look only at data for the year 2002. If you created a list of values for the item, these items appear in the drop down list (for more information about lists of values, see Chapter 9, "Maintaining Items and Item Classes"). To create a calculation that you want to filter data on click Create Calculation.... To choose an item that you want to match against click Select Item... and select an item from the list.
-
-
Specify whether to match the case of text data using the Match Case check box as follows:
-
select the Match Case check box to make the condition case sensitive
-
clear the Match Case check box to make the condition case insensitive
For example, when the Match Case check box is selected, the match value 'New York' would not return data for 'new york'.
-
-
Click OK do save the details and close the New Condition dialog.
The new condition is displayed on the "Workarea: Data tab". When Discoverer users access this business area, they will see this condition item in a folder. Note that seeing a condition in the folder does not mean that the folder is filtered. The end user must select and use the condition in a workbook.
Note: When you create a condition based on an analytic function (a function that computes aggregate values based on a group of rows), you must designate the condition Type as optional (for more information, see "What are the different types of condition?"). If you choose mandatory, a message is displayed informing you that analytic functions are not allowed in mandatory conditions.
12.4 How to create advanced conditions
Advanced conditions are conditions that contain more than one condition statement. For example, if you want to filter data for the city New York in either 1999 or 2000, you might create the condition City = New York AND (Year = 1999 or 2000). You could then nest an existing condition within the existing advanced condition. For example, where Department = Video AND Rental Profit > $100. In Discoverer, it is easy to add as many condition statements as you want, enabling you to build powerful condition items.
To create an advanced condition:
-
Create the first condition statement by following the instructions in "How to create simple conditions"
-
To add more condition statements, click Advanced.
Discoverer adds buttons for Add, Delete, And, Or and Not. You use these buttons to create the advanced condition.
-
Build the advanced condition as follows:
-
Click Add to insert a new condition statement.
-
Specify the item for the new condition statement as follows:
-
Select the drop down list in the Item column and choose an item from the list of items displayed.
-
Select the drop down list in the Item column and choose the Create Calculation option to create a calculation to include in the advanced condition.
-
Select the drop down list in the Item column and choose the Copy Condition option to include a copy of an existing condition in the advanced condition. The copy can be edited as it is a copy of the original condition.
-
Select drop down list in the Item column and choose the Select Condition option to include an existing condition in the advanced condition. If you select an existing condition, it cannot be edited here.
-
Specify the condition and value for the new condition statement.
-
Specify how you want to combine the condition statements.
Note: You can use the handles next to each condition statement to highlight a condition statement and perform the following actions:
-
click a group and click AND to specify that all condition statements must be met for the condition
-
click a group and click OR to specify that one of the condition statements must be met for the condition
-
click a group and click NOT to specify that no condition statements must be met for the condition
-
click Delete to remove condition statement lines from the condition.
-
drag and drop a handle (next to a condition statement) to reposition the condition statement in a nested hierarchy.
-
double click a group to change from AND to OR or vice versa
Note: If you reposition a condition statement, it can affect the order in which the condition statement is applied within the advanced condition (that is, nested condition statements are applied first).
-
-
When you have finished, click OK to save the advanced condition and close the dialog.
The new condition is displayed on the "Workarea: Data tab". When Discoverer users access this business area, they will see this condition item in the business area folder.
12.5 How to edit conditions
You edit a condition to change the way that it behaves. For example, you might want to:
-
change the name of a condition
-
change the data being returned by the condition
-
create a more advanced condition
To edit a condition:
-
On the "Workarea: Data tab", select the condition that you want to edit.
-
Choose Edit | Edit... to display the "New Condition dialog".
-
Edit the condition details as required.
-
Click OK to save the details and close the dialog.
The updated condition is displayed on the "Workarea: Data tab".
Note: You can also use the "Condition Properties dialog" to change conditions.
12.6 How to edit condition properties
You edit a condition's properties to change the way that it behaves, or change its identifier (for more information, see "What are identifiers?"), which is a unique Discoverer identification label. You can also change the way that a condition behaves by editing the condition itself (for more information, see "How to edit conditions").
To edit condition properties:
-
On the "Workarea: Data tab", select the condition that you want to edit.
-
Choose Edit | Properties to display the "Condition Properties dialog".
-
Edit the condition properties.
-
Click OK to save the details and close the dialog.
The "Workarea: Data tab" is updated to reflect any changes made to the condition.
12.7 How to delete conditions
You delete a condition when you want to remove it permanently from the business area. For example, you might have previously filtered data relating to the year 2000, and would now like Discoverer users to access data relating to all available years.
To delete a condition:
-
On the "Workarea: Data tab", select the condition that you want to delete.
-
Choose Edit | Delete to display the "Confirm Delete dialog".
-
Click Yes at the confirmation screen to remove the condition permanently.
The condition is removed from the business area.
Note: When Discoverer users next re-open workbooks that use a deleted condition, or refresh the worksheets using the deleted condition, previously filtered out data will be available to the Discoverer end users.
12.8 Example conditions
The following examples show how conditions are used in Discoverer Administrator.
For more examples of conditions, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.
12.8.1 Example 1: Using a condition to find data for the year 2002
To create a condition that returns only data for the year 2002, enter the following in the Formula area of the New Condition dialog:
| Item | Condition | Value |
|---|---|---|
| Year | = | 2002 |
12.8.2 Example 2: Using a condition to find sales in the last seven days
To create a condition that only returns the sales in the last seven days (using the calculated item, “Transaction Age (in Days)”), enter the following in the Formula area of the New Condition dialog:
| Item | Condition | Value |
|---|---|---|
| Transaction Age (in days) | < | 7 |
Note that the Transaction Age calculated item has the following formula
FLOOR (SYSDATE - Transaction Date)
This type of condition is sometimes described as a “rolling window” condition because the “window” of rows it returns changes from day to day.
12.8.3 Example 3: Using a condition to find shipments in Quarter 3
To create a condition that only returns shipments made in quarter 3 (Q3) regardless of year (using the calculated item, 'Ship Quarter'), enter the following in the Formula area of the New Condition dialog:
| Item | Condition | Value |
|---|---|---|
| Ship Quarter | = | 'Q3' |
Note that the Ship Quarter calculated item has the following formula:
EUL_DATE_TRUNC(Ship Date, “Q”)
12.9 About outer joins and the DisableAutoOuterJoinsOnFilters registry setting
If you define an outer join between two tables, make sure you are aware of how conditions (filters) and the DisableAutoOuterJoinsOnFilters Discoverer registry setting can combine to affect the rows of data returned by an end user query.
You define an outer join between two tables to display:
-
rows in a master table for which there are no corresponding rows in a detail table
-
rows in a detail table for which there are corresponding rows in a master table
For example, you might want to display:
-
department names and their employees, but also include the department names that have no employees
-
employee names and their departments, but also include the employee names that do not belong to a department
In SQL, the outer join is signified by the plus (+) symbol.
Discoverer includes outer joins in SQL:
-
when explicitly defined in Discoverer Administrator
For more information, see the "Edit Join dialog: Options tab".
-
automatically under some circumstances
For example, Discoverer automatically creates outer joins in the SQL for end user queries that contain conditions.
When running a query that contains a condition, users will sometimes want the results to:
-
include values from one table that have no corresponding values in a table to which it is joined by an outer join
-
not include values from one table that have no corresponding values in a table to which it is joined by an outer join
The DisableAutoOuterJoinsOnFilters registry setting enables you to disable the use of automatically generated outer joins when conditions are used in end user queries. The table below summarizes the examples that follow:
| Example No. | Condition applied? | Value of DisableAutoOuterJoinsOnFilters | All values displayed |
|---|---|---|---|
| Example 1 | No | 0 or 1 | Yes |
| Example 2 | Yes | 0 | No |
| Example 3 | Yes | 1 | Yes |
The following examples illustrate how outer joins, conditions, and the value of the DisableAutoOuterJoinsOnFilters registry setting can affect the rows of data returned from an end user query.
For more information about Discoverer registry settings, see Chapter 21, "Discoverer Registry Settings".
12.9.1 Example 1: No condition applied
This example illustrates the results returned when you execute a query against two tables, where the master and detail tables are joined with an outer join.
Discoverer displays:
-
rows of data from the master table (dept) and detail table (emp)
-
rows of data from the master table (dept) where no data exists for the detail table (emp)
The query is defined using the following SQL statement, where the outer join is signified by the plus (+) symbol:
select dname, ename, job from dept, emp where dept.deptno = emp.deptno(+);
| DNAME | ENAME | JOB |
|---|---|---|
| SALES | GRIMES | DIRECTOR |
| SALES | PETERS | MANAGER |
| SALES | SCOTT | CLERK |
| SUPPORT | MAJOR | MANAGER |
| SUPPORT | SCOTT | CLERK |
| ADMIN | ||
| MARKETING | ||
| DISTRIBUTION |
The results returned from the query above will not change whether you switch the DisableAutoOuterJoinsOnFilters registry setting on or off.
12.9.2 Example 2: Condition applied to query and DisableAutoOuterJoinsOnFilters set to 1 (switched off)
This example applies a condition to the query in Example 1 and the DisableAutoOuterJoinsOnFilters registry setting is switched off.
Discoverer displays:
-
the data specified in the condition
Discoverer does not display:
-
master rows for which there is no detail data
The following SQL statement is used, where the outer join is signified by the plus (+) symbol:
select dname, ename, job from dept, emp where dept.deptno = emp.deptno(+) and job = 'CLERK';
| DNAME | ENAME | JOB |
|---|---|---|
| SALES | SCOTT | CLERK |
| SUPPORT | SCOTT | CLERK |
12.9.3 Example 3: Condition applied to query and DisableAutoOuterJoinsOnFilters set to 0 (switched on)
This example applies a condition to the query in Example 1 and the DisableAutoOuterJoinsOnFilters registry setting is switched on.
Discoverer displays:
-
the data specified in the condition
-
master rows for which there is no detail data (Null values)
The following SQL statement is used, where the outer join is signified by the (+) symbol:
select dname, ename, job from dept, emp where dept.depno = emp.deptno(+) and job = 'CLERK';
| DNAME | ENAME | JOB |
|---|---|---|
| SALES | SCOTT | CLERK |
| SUPPORT | SCOTT | CLERK |
| ADMIN | ||
| MARKETING | ||
| DISTRIBUTION |
Note: The database supports placing outer joins in IS NULL and IS NOT NULL clauses, but does not support placing outer joins in IN and OR clauses.
12.10 How to create row level security using a mandatory condition
You might want to restrict the data that end users can see in Discoverer workbooks.
For example, you have a single table with profit data for all regions. Each row of profit data applies to a transaction in a single region. You would like a manager in the West region to only access the rows with profit data for the West region.
| REGION | PROFIT | DATE |
|---|---|---|
| East | $100 | 8/7 |
| West | $50 | 8/7 |
| South | $65 | 8/10 |
| North | $100 | 8/6 |
To create row level security you must complete the following tasks:
Load the ALL_USERS table from the SYS view into the business area that contains the folder in which you want to apply row level security
This task enables you to obtain a list of all the database users to which you can subsequently apply conditions and achieve row level security.
To load the ALL_USERS table into the business area where you want to apply row level security:
-
Select the business area that contains the folder in which you want to create row level security.
-
Choose Insert | Folder | From Database to display the "Load Wizard: Step 1 dialog".
-
Select the Online dictionary check box and click Next to display the "Load Wizard: Step 2 dialog".
-
Select the SYS user from the Select the users whose tables you want to load list box and click Next to display the "Load Wizard: Step 3 dialog".
The SYS user contains a view that holds the names of all database users.
-
Expand the SYS user in the Available list box and drag the ALL_USERS view into the Selected list box and click Next to display the "Load Wizard: Step 4 dialog".
This loads the ALL_USERS view into the current business area. The ALL_USERS view contains the names of all database user accounts.
-
Select the List of values for items of type check box and the Character check box, leaving all the other check boxes in this area cleared.
This will create a list of values of the names of all the database users.
-
Click Finish.
This loads the ALL_USERS view from the SYS table into the current business area.
-
(optional) Edit the folder properties of ALL_USERS and set the Visible to user property to No (for more information about how to edit folder properties, see "How to edit folder properties").
This makes sure that Discoverer does not display the ALL_USERS folder to end users.
Create a new calculated item in the folder where you want to apply row level security
You create a calculated item so that you can subsequently apply the list of values item class of all the database users from the SYS table.
To create a calculated item in the folder where you want to apply row level security:
-
Highlight the folder in which you want to create row level security (for example, the Video Analysis folder).
-
Choose Insert | Item.
-
Type Username into the Name field.
-
Type USER into the Calculation field.
-
Click OK to create the new calculated item.
Apply the list of values from the Username item (in the ALL_USERS table) to the new calculated item
To apply the list of values item class to the calculated item created in the previous task:
-
Highlight the folder in which you want to apply row level security (for example, the Video Analysis folder).
-
Click Insert | Item Class to display the "Item Class Wizard: Step 1 dialog".
-
Select List of values check box and click Next to display the "Item Class Wizard: Step 2 dialog (select the item that generates the LOV)".
-
Select the Username item from the All Users table that you loaded previously into the business area and click Next to display the "Item Class Wizard: Step 3 dialog (enter name and description)".
-
Select the calculated item Username (that you created in the previous step) from the Available items: list and drag it into the Selected list.
-
Click Finish to apply the list of values to the calculated item 'Username'.
Create a mandatory advanced condition to define row-level security in a folder for specified database users
You create a mandatory advanced condition so that you can apply data conditions to specified database users.
You must create a mandatory advanced condition that includes both:
-
a condition statement defining the database user(s)
-
one or more condition statements restricting data access to the specified database user(s)
To create a mandatory advanced condition to define row level security for the specified database user(s):
-
Highlight the folder in which you want to create row level security.
-
Choose Insert | Condition to display the "New Condition dialog".
-
(optional) Enter a description for the new condition.
-
Click the Type drop down list and choose Mandatory.
The Type Mandatory specifies that a condition always applies to end users.
-
Click the Item drop down list and select the calculated item Username.
-
Click the Values drop down list and choose Select Multiple Values to display the Values dialog.
-
Select a check box for each database user that you want row level security to apply then click OK.
Discoverer displays the selected database user(s) in the Values field.
Note: You have now created a mandatory simple condition specifying the names of one or more database users. However, before you can apply row level security to the database user(s) in the current folder, you must specify the data conditions that you want to apply to the specified database user(s).
The remaining steps describe how you can apply row level security to the specified database user(s) so that they can see only data from the West region.
-
Click the Advanced button and then the Add button.
-
(example) Click the Item drop down list and select Store.Region.
This data condition will be applied to the specified database user(s).
-
(example) Click the Values drop down list and select the region West.
Note: To associate the database users (Username) with the data condition (Region), the Username and Region condition statements must be grouped together by using the AND clause.
Each Username/data condition statement must group using the AND clause. Pairs of Username/data condition statements, must group with other pairs using the OR clause. By grouping the pairs of Username/data condition statements using the OR clause ensures that each condition statement pair can be applied (see figure below).
-
Click OK.
This creates a mandatory advanced condition that applies row level security to the database users specified (that is, binding a group of users either to the West or the East region). In the example above, the database user ADMTEST can view data from the West region only.
-
(optional) Edit the Properties of the new condition and set the Visible to user property to No (for more information about editing condition properties, see "How to edit condition properties").
This ensures that Discoverer does not display the condition to end users, but it is always enforced.
12.11 How to enable summary folders if a source folder includes a mandatory condition
When you create a mandatory condition in a folder, database user queries must not use a summary folder that is based upon the folder that contains the mandatory condition. This is because the data in the summary table will be only for the database user that created the summary folder.
To enable database user queries to use summary folders where the source folders use mandatory conditions (for example, with row level security), you must perform the following steps before you create the mandatory condition.
To enable summary folders for database user queries where the source folders contain a mandatory condition:
-
Create a summary folder based upon folders where no mandatory condition (for example, row level security) has yet been set up.
For more information about creating summary folders, see Chapter 14, "Managing Summary Folders" and Chapter 15, "Creating Summary Folders Manually".
-
Set the summary property Available for Queries to 'No'.
This summary folder references data for the database user that created it. You must set this property to No to prevent end user queries from accessing this summary folder.
-
(optional) Set the summary folder properties Next Refresh and Refresh Interval to suitable values.
For more information, see the "Summary Properties dialog".
-
Create a database view against your summary folder.
Ask your database administrator for more information as this is done outside Discoverer.
Use a WHERE clause to apply the mandatory condition (for example, row level security) to the view just created.
For example:
SQL>
WHERE Userid='SMITH' AND Region='WEST' -
Register the view in Discoverer as an external summary.
For more information, see "How to create summary folders based on external summary tables".
-
Set the summary property Available for Queries to Yes.
You must set this property to Yes to enable database users to access this summary folder.
The Next Refresh and Refresh Interval summary folder properties should be set to Never in Discoverer.
For more information, see the "Summary Properties dialog".
-
Create the required mandatory condition (for example, row level security) in a folder in Discoverer Administrator (for more information, see "How to create row level security using a mandatory condition").
You now have two summary folders, one based on the data folder without the mandatory condition and one based on the view. By adding the mandatory condition to the folder after the first summary folder was created, subsequent queries will be rewritten to use the view-based summary folder, rather than the folder-based summary folder. For more information about summary rewrite, see Chapter 16, "Additional Information about Summary Folders".
Note: You cannot use analytic functions in external summary folders (for more information about analytic functions, see "analytic functions").