11
Creating and maintaining conditions

Creating and maintaining conditions

This chapter explains how to implement conditions using Discoverer Administrator, and contains the following topics:

• "What are conditions?"

• "What are the different types of condition?"

• "How to create simple conditions"

• "How to create advanced conditions"

• "How to edit conditions"

• "How to edit condition properties"

• "How to delete conditions"

• "Example conditions"

• "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 (e.g. with row level security)"

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 2001, but not 1999 or 2000.

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 they can apply them in worksheets. This enables Discoverer end users to work efficiently.

Notes

• Discoverer Plus users can also create their own conditions.

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. This enables Discoverer users to 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 Application Server Discoverer Plus User's Guide).

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.

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 can not 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.

Notes

• 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.

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:

1. 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

2. Choose Insert | Condition to display the "New Condition dialog".

   Figure 11-1 New Condition dialog

   
   
   Text description of the illustration condnew.gif

   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.

3. (optional) Enter a name for the condition in the Name field.

   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.

4. (optional) To specify your own name for the condition:
   1. Clear the Generate name automatically check box.

   2. Enter a name for the condition in the Name field.

5. (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.

6. 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

7. Define the condition statement in the Formula area as follows:
   1. 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 10, "Creating and maintaining calculated items").

   2. 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.

   3. 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 8, "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.

8. 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'.

9. 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.

   Notes

   • When you create a condition based on an analytic function (i.e. 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.

   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:

   1. Create the first condition statement by following the instructions in "How to create simple conditions"

   2. To add more condition statements, click Advanced.

      Figure 11-2 New Condition dialog (Advanced)

      
      
      Text description of the illustration condadv.gif

   Discoverer adds Insert buttons for Add, And and Or. You use these buttons to create the advanced condition.

3. Build the advanced condition as follows:
   1. Click Add to insert a new condition statement.

   2. 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.

   c. Specify the condition and value for the new condition statement.

   d. 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 carry out 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 (i.e. nested condition statements are applied first).

4. 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.

   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:

   1. On the "Workarea: Data tab", select the condition that you want to edit.

   2. Choose Edit | Edit... to display the "Edit Condition dialog".

      Figure 11-3 Edit Condition dialog

      
      
      Text description of the illustration conded.gif

3. Edit the condition details as required.

4. Click OK to save the details and close the dialog.

The updated condition is displayed on the "Workarea: Data tab".

Notes

• You can also use the "Condition Properties dialog" to change conditions.

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 Chapter 3, "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:

1. On the "Workarea: Data tab", select the condition that you want to edit.

2. Choose Edit | Properties to display the "Condition Properties dialog".

3. Edit the condition properties.

4. Click OK to save the details and close the dialog.

The "Workarea: Data tab" is updated to reflect any changes made to the condition.

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:

1. On the "Workarea: Data tab", select the condition that you want to delete.

2. Choose Edit | Delete to display the "Confirm Delete dialog".

3. Click Yes at the confirmation screen to remove the condition permanently.

The condition is removed from the business area.

Notes

• 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.

Example conditions

The following examples show how conditions are used in Discoverer Administrator.

Notes

• For more examples of conditions, see the Oracle Application Server Discoverer Plus User's Guide.

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

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.

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")

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 "Join Wizard: Step 2 dialog".

• 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 20, "Discoverer registry settings".

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.

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

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

Notes

• 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.

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"

• "Create a new calculated item in the folder where you want to apply row level security"

• "Apply the list of values from the Username item (in the ALL_USERS table) to the new calculated item"

• "Create a mandatory advanced condition to define row level security in a folder for specified database users"

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:

1. Select the business area that contains the folder in which you want to create row level security.

2. Choose Insert | Folder | From Database to display the "Load Wizard: Step 1 dialog".

3. Select the Online dictionary check box and click Next to display the "Load Wizard: Step 2 dialog".

4. 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.

5. 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.

6. 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.

7. Click Finish.

   This loads the ALL_USERS view from the SYS table into the current business area.

8. (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:

1. Highlight the folder in which you want to create row level security (e.g. the Video Analysis folder).

2. Choose Insert | Item.

3. Type Username into the Name field.

4. Type USER into the Calculation field.

5. 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:

1. Highlight the folder in which you want to apply row level security (e.g. the Video Analysis folder).

2. Click Insert | Item Class to display the "Item Class Wizard dialog".

3. Select List of values check box and click Next to display the "Item Class Wizard: Step 2".

4. 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".

5. Select the calculated item Username (that you created in the previous step) from the Available items: list and drag it into the Selected list.

6. 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):

1. Highlight the folder in which you want to create row level security.

2. Choose Insert | Condition to display the "New Condition dialog".

3. (optional) Enter a description for the new condition.

4. Click the Type drop down list and choose Mandatory.

   The Type Mandatory specifies that a condition always applies to end users.

5. Click the Item drop down list and select the calculated item Username.

6. Click the Values drop down list and choose Select Multiple Values to display the Values dialog.

7. 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.

8. Click the Advanced button and then the Add button.

   This enables you to add a new condition statement to the current condition and specify a data condition to apply to the specified database user(s).

9. (example) Click the Item drop down list and select Store.Region.

   This data condition will be applied to the specified database user(s).

10. (example) Click the Values drop down list and select the region West.

    Note: The Username and Region condition statements must be grouped together using the AND clause in order to associate the database user(s) (Username) with the data condition (Region).

    Each Username/data condition statement must group using the AND clause. Pairs of Username/data condition statements, must group together 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).

    Figure 11-4 Condition where one group of database users sees data from the 'West' region and the other group sees data from the 'East' region

    
    
    Text description of the illustration condrw.gif

11. Click OK.

    This creates a mandatory advanced condition that applies row level security to the database user(s) specified (i.e. 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.

12. (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.

How to enable summary folders if a source folder includes a mandatory condition (e.g. with row level security)

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 (e.g. with row level security), you must carry out 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:

1. Create a summary folder based upon folders where no mandatory condition (e.g. row level security) has yet been set up.

   For more information about creating summary folders, see Chapter 13, "Managing summary folders" and Chapter 14, "Creating summary folders manually".

2. 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.

3. (optional) Set the summary folder properties Next Refresh and Refresh Interval to suitable values.

   For more information, see the "Summary Properties dialog".

4. 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 (e.g. row level security) to the view just created.

   For example:

   SQL> WHERE Userid='SMITH' AND Region='WEST'

5. Register the view in Discoverer as an external summary.

   For more information, see Chapter 14, "How to create summary folders based on external summary tables".

6. 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".

7. Create the required mandatory condition (e.g. row level security) in a folder in Discoverer Administrator (for more information, see "How to create row level security using a mandatory condition").

Notes

• You cannot use analytic functions in external summary folders (for more information about analytic functions, see "analytic functions").