Skip Headers
Oracle® Argus Insight BusinessObjects Administrator's Guide
Release 7.0.1

E28483-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Using Advanced Conditions to Create Queries

The advanced conditions querying tool is designed to allow the greatest flexibility in designing the most advanced queries. You can use advanced conditions to create complex queries that involve Boolean and set operations and use Structured Query Language (SQL).

You create advanced conditions in the Advanced Conditions Editor, which employs a spreadsheet-like approach for adding query conditions (fields and their values) and conditional operators (Boolean or set).

Note:

The labels for the advanced conditions field are displayed as per the field labels configured in Argus Safety.

This chapter includes the following topics:

In addition to advanced conditions, Argus Insight provides the following query tools:

Creating a New Advanced Condition

To start a new advanced condition:

  1. Navigate to Create Query, Advanced Conditions, and select New. The ADVANCED CONDITIONS EDITOR page opens.

    Surrounding text describes activeadv.gif.

    Note:

    You can also start an advanced condition by converting a QBE or a filter value set to an advanced condition. See "Converting a QBE to an Advanced Condition" and "Converting a Value Set to an Advanced Condition" for details.
  2. Enter the attributes (fields and values) for the advanced condition.

  3. Click the context menu icon. In the context menu, the first seven categories (Activities, Additional Info, Analysis, Events, General, Patients, and Products) represent the Argus case form tabs. In these categories, the various data mart fields that pertain to case information are organized in this hierarchy:

    • Argus case form tab

    • Sections within the tab

    • Fields within the section

    You can browse through the appropriate hierarchy and select the required field as an advanced condition attribute.

    The eighth category Reports contains attributes that pertain to the reporting information of a case. You can browse through the Reports category and select the required advanced condition attribute.

    The ninth category AC Library lists all the advanced conditions stored in the system. You can browse through the AC Library category and select a stored advanced condition as an attribute for the new advanced condition.

    Surrounding text describes actadv.gif.

  4. Browse through the appropriate category hierarchy and select the required attribute. The selected attribute appears as a row in the editor.

    Surrounding text describes actadva.gif.

    Note:

    The user defined fields in Argus which have been converted as a look up are also available as attributes for the advanced condition search.
  5. In the Value field, enter the value for the selected attribute. Depending on the type of attribute you selected, the system lets you populate the Value field by using a:

    • Drop-down list to select from a set of predefined values

    • Text box to enter text strings or numerals

    • Date field to enter dates

    • Browser to select MedDRA dictionary terms, company products, WHO drugs, clinical study IDs, Drugs in Study (incl. Comparators) and Investigational Drugs.

      To use a browser to populate the Value field, click the ellipsis button next to the Value field. From the context menu, select the option corresponding to the browser you want to start. For example, you can start the MedDRA Browser and enter criteria to search for a preferred term based on a specific code or description. You can automatically add a selected term to the Value field.

  6. Click the Conditions field and select a condition for the attribute value. See Table 5-1 for a description of each condition. The conditions available to you depends on the attribute you selected.

    Table 5-1 Conditions for Defining an Advanced Condition

    Condition Select this option if you want to…

    equal to

    Retrieve cases where the selected attribute's value is equal to what the Value field specifies

    not equal to

    Retrieve cases where the selected attribute's value is not equal to what the Value field specifies

    greater than

    Retrieve cases where the selected attribute's value is greater than what the Value field specifies

    greater than or equal to

    Retrieve cases where the selected attribute's value is greater than or equal to what the Value field specifies

    less than

    Retrieve cases where the selected attribute's value is less than what the Value field specifies

    less than or equal to

    Retrieve cases where the selected attribute's value is less than or equal to the Value that the field specifies

    missing

    Retrieve cases where the selected attribute's value has not been specified

    exists

    Retrieve cases where the selected attribute has any value

    begins with

    Retrieve cases where the selected attribute's value begins with what the Value field specifies

    contains

    Retrieve cases where the selected attribute's value contains what the Value field specifies

    does not contain

    Retrieve cases where the selected attribute's value does not contain what the Value field specifies

    in

    Retrieve cases where the selected attribute's value exists in what the Value field specifies

    not in

    Retrieve cases where the selected attribute's value does not exist in what the Value field specifies


    Tip:

    To change the structure of the query by changing the order of the attribute rows, select a row and then click the arrow icons to move the row up or down. To delete a row from the editor, select the row and click the Delete icon.
  7. Specify the operator. Use the Operator drop-down list to specify the Boolean or set operator through which you want to join the attribute with another attribute.

  8. Add more attributes to your advanced condition. Click the icons on the left bar to insert another attribute above or below the existing attribute.

  9. Specify runtime parameters, as appropriate.

    Note:

    Argus Insight lets you create advanced conditions which when executed, ask for user-specified values for certain attributes to generate the case series. For example, you may create an advanced condition in which the Case Number or the Product name may be specified only at the time of execution. In this case, the advanced condition would only retrieve those cases where the Case Number/Product Name is as specified at the time of execution.

    Creating an advanced condition using same field name multiple times requires to change the Parameter name so that while executing the advanced condition different parameters values can be entered.

  10. Identify the attribute row where you want to specify runtime parameters.

  11. Click the ellipsis button next to the Value field, and then select Parameters from the menu.

    Surrounding text describes advd.gif.
  12. Click Parameter. The Parameter dialog box opens.

    Surrounding text describes param.gif.

  13. Select the Multi Select check box if you want to pass multiple runtime values for a single attribute. For example, you might want to pass more than one case number or product name as runtime parameters.

  14. Click OK. The field you configured as a runtime parameter is displayed in the Value text box enclosed within % symbol. For example, %Product Type% If you configured the field to accept multiple runtime parameters, the field name appears in the Value text box enclosed within %% symbol. For example, %%Product Type%%.

  15. Type parentheses in the ( and ) columns to determine the order of execution for the selected advanced conditions, as appropriate.

  16. Examine the advanced condition results.

  17. Click Execute. While the system searches for matching cases, the following dialog box is displayed.

    Surrounding text describes exec.gif.

    Tip:

    To cancel the query execution at this point and return to the Advanced Conditions Editor, click Cancel Query.
    • If you configured any runtime parameters, the Parameters dialog box opens. Specify the parameter values by using the options in the dialog box. Next, click Execute; the system retrieves the matching cases from the data mart and displays them in the ACTIVE CASE SERIES page.

      Tip:

      If you select one runtime parameter and execute the search, you must enter a field.

      If you select multiple runtime parameters and execute the search, you have the option to ignore the runtime parameters.

    • If the system does not find any cases that match the querying criteria, the following screen is displayed. Click View Query in this screen to return to the Advanced Conditions Editor, modify the criteria and execute again.

      Surrounding text describes ncf.gif.

    • If the system finds cases that match the query criteria, the ACTIVE CASE SERIES page displays the list of such cases.

  18. Examine the case series. If the case series is too large, you may want to modify the advanced condition by specifying additional attributes or changing values. Alternatively, if you find the case series to be appropriate, you can save the advanced condition to the system.

    The system retrieves the matching cases from the data mart and displays them in the ACTIVE CASE SERIES page.

Returning to the Advanced Conditions Editor

To return to the Advanced Conditions Editor:

  1. Click View Query in the ACTIVE CASE SERIES page. The ACTIVE ADVANCED CONDITIONS EDITOR page opens.

    Note:

    When you save the modifications to an advanced condition or execute it, the system assigns the active status to the advanced condition. Therefore, when you return to the editor after executing the advanced condition, the page title changes to ACTIVE ADVANCED CONDITIONS EDITOR. The field values you specified in the editor before executing are retained. See "Working with the Last Modified or Saved Advanced Condition" for details.
  2. If required, modify the advanced condition and examine the results again or proceed to save the advanced condition.

  3. Save the advanced condition.

Assigning a Category to an Advanced Condition

Use the Category drop-down list to assign a category to the advanced condition. A category indicates the reporting aspect to which your advanced condition pertains: Case Processing, Compliance, Configuration, General, Management, or Pharmacovigilance. Specifying the category also helps you in searching the relevant advanced conditions from a list of all the advanced conditions saved in the system.

  1. Type a description of the advanced condition in the Description field. For example, you can describe the type of cases the advanced condition retrieves.

  2. Click Save on the ACTIVE ADVANCED CONDITIONS EDITOR page. The Advanced Condition - Save dialog box opens.

    Surrounding text describes save_adv.gif.
  3. Type the name of the advanced condition in the Name field.

  4. Click OK to save the advanced condition and refresh the ACTIVE ADVANCED CONDITIONS EDITOR page. Note that:

    • The Active Query Name field in the upper-right corner of the page now displays the name of the advanced condition you specified while saving the advanced condition.

    • The Save As, View SQL, and Permissions buttons are now enabled.

      Note:

      The ADVANCED CONDITION LIBRARY page lists all the advanced conditions saved to the system. See "Working with Saved Advanced Conditions" for more information.

      Tip:

      If you modify the field values after saving the advanced condition to the system, click Save to save the changed field values.
  5. Click View SQL to view the underlying SQL query for the advanced condition.

    Surrounding text describes viewsql.gif.
    1. Modify the SQL, if necessary.

    2. Click Save SQL after editing the query.

      Note:

      When using the Save SQL functionality, the advanced condition can be executed only from the ADVANCED CONDITIONS LIBRARY page and the QUERY LIBRARY page.

      Tip:

      Revert to Original lets you to revert back to the original advanced condition.
    3. Click Close.

  6. Click Permissions to assign group-level permissions on the saved advanced condition. The system opens the Permissions dialog box and displays the names of all the groups (except the Administrator group) that the system administrator has created.

    Surrounding text describes perm_adc.gif.
  7. Use the drop-down list next to a group name to assign permissions to the group members on the advanced condition you have created. You can select from these options:

    Permission Description
    R/W/D/P Group members will be able to view, modify, delete, and assign permission on the advanced condition.
    R/W/D Group members will be able to view, modify, and delete the advanced condition.
    R/W Group members will be able to view and modify the advanced condition.
    R Group members will be able to only view the advanced condition.
    No Access (Default) No group members will be able to access the advanced condition.

    Note:

    The author of the advanced condition always has the highest level of permission (R/W/D/P) on it. For example, suppose you belong to the Data Entry group and you assign the No Access permission level to the Data Entry group on an advanced condition that you create. In this case, you will continue to have the highest level of permission on your advanced condition; other members in your group cannot access your advanced condition.
  8. Click OK. The system saves the permission settings.

  9. Click Execute to generate the case series.

    • The system retrieves the matching cases from the data mart and displays them on the ACTIVE CASE SERIES page.

    • If you configured any runtime parameters, the Parameters dialog box opens. Specify the parameter values by using the options in the dialog box. Next, click Execute; the system retrieves the matching cases from the data mart and displays them on the ACTIVE CASE SERIES page. See Chapter 7, "Working with Case Series" for more information.

Using and Ordering Advanced Condition Operators

Use the following order of precedence for the various operators you can select when creating an advanced condition.

Order Operator Use
1 AND Use with all fields available in an advanced condition.
2 OR Use with all fields available in an advanced condition.
3 INTERSECT Use between various queries (AC Library Attributes)
4 MINUS Use between various queries (AC Library Attributes)
5 UNION Use between various queries (AC Library Attributes)

Using an INTERSECT, MINUS, or UNION operator results in the creation of two separate select clauses. Therefore, use these operators between different queries.

Be sure to use correct PL/SQL syntax:

SELECT

table1.common_column

FROM

Table1, table2, table3

WHERE

Table1.column2 = table2.column2

AND

Table2.column3 = table3.column3

INTERSECT

SELECT

Table4.common_column

FROM

Table4, table5, table6

WHERE

Table4.column2 = table5.column2

AND

Table5.column3 = table6.column3

Advanced Condition Example

Suppose you want to create an advanced condition as follows:

WHERE Country = "Australia" AND Co-Drug Code w Study = "LAS + TAB" OR Co-Drug Code w Study = "LAS+" AND Overdose = "Yes" AND Interaction? = "Yes"

AND

You want to INTERSECT the results of this advanced condition with the existing advanced condition:

WHERE Country = "United States" OR Country = "Australia"

Table 5-2 lists the values that you enter to create the advanced condition specified in the previous example. Figure 5-1 shows the same example advanced condition in Argus Insight.

Table 5-2 Values Entered for the Advanced Condition Example

( Attributes Condition Value ) Operator

(

Country

equal to

AUSTRALIA

 

AND

(

Co-Drug Code w Study

equal to

LAS + TAB

 

OR

 

Co-Drug Code w Study

equal to

LAS+

)

AND

 

Overdose

equal to

Yes

 

AND

 

Interaction?

equal to

Yes

)

INTERSECT

 

COI USA OR AUSTRALIA

 

(Advanced Condition)

   

Figure 5-1 Advanced Condition Example in Argus Insight

Surrounding text describes Figure 5-1 .

You do not need to include the parentheses if the PL/SQL syntax and the order and precedence of operators are correct.

Sample SQL Generated

The following SQL is generated for the previous example:


SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case, rpt_product
WHERE v_rpt_case.case_id = rpt_product.case_id
AND ( (v_rpt_case.country_id = 13)
AND ( UPPER (rpt_product.co_drug_code) = 'LAS+TAB'
OR (pat_exposure IN (
SELECT product_id
FROM lm_product
WHERE UPPER (lm_product.drug_code) ='LAS+TAB')))
OR (UPPER (rpt_product.co_drug_code) = 'LAS+'
OR (pat_exposure IN (SELECT product_id FROM lm_product
WHERE UPPER (lm_product.drug_code) ='LAS+')))
AND (rpt_product.is_overdose = 1)
AND (rpt_product.interaction = 1))
INTERSECT
SELECT DISTINCTv_rpt_case.case_id
FROM v_rpt_case
WHERE v_rpt_case.case_id IN (
SELECT DISTINCT v_rpt_case.case_id
FROM v_rpt_case
WHERE (v_rpt_case.country_id = 13)
OR (v_rpt_case.country_id = 223))

Working with the Last Modified or Saved Advanced Condition

The system assigns an active status to a advanced condition when you modify and save it or when you execute it. Unless you save the modifications to another advanced condition or execute another advanced condition, the last advanced condition you modified or executed remains active. This is helpful in situations when you want to access a frequently used advanced condition.

To view an active advanced condition:

  1. Navigate to Create Query, Advanced Condition, and select Active. The ACTIVE ADVANCED CONDITIONS EDITOR page displays the advanced condition you executed or modified last.

    Surrounding text describes activeadv.gif.

  2. If the active advanced condition was saved to the system before executing, the name of the advanced condition appears next to the Active Query Name and Name label. However, if the active advanced condition was not saved to the system before executing, the text <Not Saved> is displayed next to the Active Query Name label.

    From the ACTIVE ADVANCED CONDITION page, you can perform the following tasks:

    Task Description
    Modify the attributes in the Advanced Conditions Editor If required, you can modify the attributes (fields and values) in the Advanced Conditions Editor. See "Creating a New Advanced Condition" for more information.

    Click Save to save the changed field values. This button is only available for a saved active advanced condition.

    Save Active Advanced Condition with another name Click Save As to save the active advanced condition by a different name.

    The ADVANCED CONDITIONS LIBRARY page lists all the advanced conditions saved to the system. See "Working with Saved Advanced Conditions" for more information.

    View the Advanced Condition in SQL Click View SQL to view the underlying SQL query for the advanced condition.

    In this SQL, manually replace the V_RPT_CASE with RPT_CASE. This is required to execute the query in the Oracle database.

    Assign Permissions Click Permissions to set the group-level access permissions on the advanced condition. See "Creating a New Advanced Condition" for more information.

    The Permissions button is only available for saved active advanced conditions.

    Change the Description of the Active Advanced Condition You can change the description of the active advanced condition by modifying the text displayed in the Description field.

    Click Save to store the changed description.

    Execute the Active Advanced Condition Click Execute to generate a case series by using the active advanced condition.

Note that modifying the field values in the Advanced Conditions Editor for an active advanced condition or saving the active advanced condition by a different name changes the active advanced condition to reflect the most recent changes. The active advanced condition also changes in case you modify the field values in the Advanced Conditions Editor and execute the advanced condition without saving the modifications.

Working with Saved Advanced Conditions

The ADVANCED CONDITIONS LIBRARY page lists all the advanced conditions saved to the system.

To access this page, navigate to Create Query, Advanced Conditions, and select Library. The ADVANCED CONDITIONS LIBRARY page opens. See Figure 5-2.

The ADVANCED CONDITIONS LIBRARY page displays the following information about each saved advanced condition:

Figure 5-2 ADVANCED CONDITIONS LIBRARY Page

Surrounding text describes Figure 5-2 .

Searching Saved Advanced Conditions

To search for a saved advanced condition:

  1. Specify the search criteria, as appropriate:

    • To search for an advanced condition by its name, type the advanced condition name in the Name text box.

    • To search for an advanced condition by its description, type the first few words of the description in the Description text box. The system searches for the specified search string in all advanced condition descriptions.

    • To search for an advanced condition by its date of modification, enter the modification date in the first Last Modified date field. You can specify a date range by typing the start and end dates in the first and second date fields, respectively.

    • To search for an advanced condition by its author, select the author name from the User Full Name drop-down list.

    • To search for an advanced condition by its category, select the category from the Category drop-down list.

  2. Click Search. Based on your search criteria, the system displays the search results in a list.

    • To sort the search results, click the sort icon next to the column headers in the list.

    • To clear the search results and display all the advanced conditions in the list, click Clear.

Creating a New Advanced Condition

To create a new advanced condition, click New in the ADVANCED CONDITIONS LIBRARY page.

See "Creating a New Advanced Condition" for details.

Modifying an Advanced Condition

To modify an advanced condition:

  1. Navigate to the ADVANCED CONDITIONS LIBRARY page.

  2. Select the advanced condition you want to modify.

  3. Click Modify.

  4. Make your modifications to the advanced condition, as appropriate.

  5. Click Save to save your changes.

    Alternatively, click Save As to save the modified advanced condition by another name.

Modifying the SQL of an Advanced Condition

To modify the SQL of an advanced condition:

  1. Navigate to the ADVANCED CONDITIONS LIBRARY page.

  2. Select the advanced condition you want to modify.

  3. Click Modify. The ACTIVE ADVANCED CONDITIONS EDITOR page opens.

    Surrounding text describes actadva.gif.
  4. Click View SQL. The Advanced Conditions SQL dialog opens.

    Surrounding text describes viewsq.gif.
  5. Modify the SQL as per your requirement.

  6. Click Save SQL to save your changes.

  7. Click Close to return to the ADVANCED CONDITIONS LIBRARY page.

  8. Select the modified advanced condition.

  9. Click Execute. The system displays the results based on your modified SQL.

    Note:

    You cannot modify an advanced condition once you have added user defined SQL in that, you can modify SQL but cannot add new items in advanced condition.

    If you want to add new items, you have to revert the SQL to original by clicking Revert to Original.

Deleting an Advanced Condition

To delete an advanced condition:

  1. Navigate to the ADVANCED CONDITIONS LIBRARY page.

  2. Select the advanced condition you want to delete.

  3. Click Delete. The system prompts for confirmation.

  4. Click OK to delete selected advanced condition.

Associating a Report Group with an Advanced Condition

Report packaging is the concept of grouping reports together. You can define multiple reports that are always or often executed at the same time into a group. A set of reports run on a weekly or periodic basis can be scheduled and run as a package.

In Argus Insight, you can associate a report group and the report scheduling to a power query (QBE, Filter Value Set, and Advanced Conditions) or to a case series.

Argus Insight lets you associate the report group, schedule a time to generate the reports, and automatically send the completed reports through email to one or more users that you specify.

Associating a report group with an advanced condition is helpful in situations when you need to generate the latest case series and run a report on it each time the data mart is refreshed. Instead of manually executing the advanced condition to generate the latest case series and then running the report, you can use the report association functionality of Argus Insight to accomplish the task automatically.

For detailed procedures and more information about associating report groups, see "Scheduling and Associating Reports Against a Query or Case Series".

Note:

To be able to schedule reports, you must have an enterprise login configured in the Business Intelligence tool (either BusinessObjects or Cognos) you are using with Argus Insight.

Executing an Advanced Condition

To execute an advanced condition:

  1. Navigate to the ADVANCED CONDITIONS LIBRARY page.

  2. Select an advanced condition.

  3. Click Execute. The system executes the advanced condition; the ACTIVE CASE SERIES page opens.

    Surrounding text describes acs3.gif.

  4. If a report is associated with the saved advanced condition, click Execute. The ACTIVE CASE SERIES page and PRE FILTER page of the associated report is displayed.

  5. Enter the prompts value in the PRE FILTER page.

  6. Click Execute to generate the report.

After generating the case series, you can manually modify it or save it. See Chapter 7, "Working with Case Series" for more information.

Executing a Saved Advanced Condition on the Active Case Series

Just as the system assigns the active status to an advanced condition when you save modifications to it or execute it, the last saved or generated case series becomes the active case series.

You may want to run an advanced condition on an active case series.

To narrow down the list of cases in the active case series:

  1. Generate the case series on which you want to run another Value Set:

    • You can generate the case series by either using QBE, filters, or advanced conditions. This case series automatically becomes the active case series and remains so until you execute another query to generate a different case series.

    • Alternatively, if the case series on which you want to run another Value Set is already saved in the system, navigate to Query Results, Case Series, and select Case Series Library. When the CASE SERIES LIBRARY page opens, select the case series and click Make Active.

  2. Navigate to Create Query, Advanced Conditions, and select Library. The ADVANCED CONDITIONS LIBRARY page opens.

  3. Select the advanced condition you want to run on the active case series you generated in Step 1.

  4. Select the Limit Query to Active Case Series check box.

  5. Click Execute. The system runs the selected advanced condition on the active case series and displays the ACTIVE CASE SERIES page. Note that the case series you just generated becomes the active case series.