Oracle® Argus Insight User's Guide Release 7.0.2 E38588-01 |
|
|
PDF · Mobi · ePub |
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:
QBEs (see Chapter 3, "Creating Queries by Example")
Filters (see Chapter 4, "Using Filters to Create Queries")
Library (see Chapter 6, "Working with Libraries")
To start a new advanced condition:
Navigate to Queries, Advanced Conditions, and select New. The ADVANCED CONDITIONS EDITOR page opens.
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.Enter the attributes (fields and values) for the advanced condition.
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.
Browse through the appropriate category hierarchy and select the required attribute. The selected attribute appears as a row in the editor.
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.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.
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.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.
Add more attributes to your advanced condition. Click the icons on the left bar to insert another attribute above or below the existing attribute.
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.
Identify the attribute row where you want to specify runtime parameters.
Click the ellipsis button next to the Value field, and then select Parameters from the menu.
Click Parameter. The Parameter dialog box opens.
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.
Click OK. If you configured the field to accept single runtime parameter, 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%%.
Type parentheses in the ( and ) columns to determine the order of execution for the selected advanced conditions, as appropriate.
Examine the advanced condition results.
Click Execute. While the system searches for matching cases, the following dialog box is displayed.
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.
If the system finds cases that match the query criteria, the ACTIVE CASE SERIES page displays the list of such cases.
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.
To return to the Advanced Conditions Editor:
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.If required, modify the advanced condition and examine the results again or proceed to save the advanced condition.
Save the 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.
Type a description of the advanced condition in the Description field. For example, you can describe the type of cases the advanced condition retrieves.
Click Save on the ACTIVE ADVANCED CONDITIONS EDITOR page. The Advanced Condition - Save dialog box opens.
Type the name of the advanced condition in the Name field.
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.Click View SQL to view the underlying SQL query for the advanced condition.
Modify the SQL, if necessary.
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.Click Close.
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.
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.Click OK. The system saves the permission settings.
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.
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
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) |
You do not need to include the parentheses if the PL/SQL syntax and the order and precedence of operators are correct.
The following SQL is generated for the previous example:
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:
Navigate to Queries, Advanced Condition, and select Active. The ACTIVE ADVANCED CONDITIONS EDITOR page displays the advanced condition you executed or modified last.
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 in the Name field. 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.
You can enter the name of the advanced condition in the Name field and click Save.
You can also rename an already saved advanced condition, by entering the new name in the Name field and clicking Save.
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.
The ADVANCED CONDITIONS LIBRARY page lists all the advanced conditions saved to the system.
To access this page, navigate to Queries, 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:
Name of the advanced condition.
Description of the advanced condition.
Date when the advanced condition was last modified.
Full name of the user who created the advanced condition.
Category assigned to the advanced condition.
Name of the fields for which runtime parameters need to be specified while executing the advanced condition.
An icon in the Associated Report column if the advanced condition has a report associated with it. See "Associating a Report Group with an Advanced Condition" for more information.
To search for a saved advanced condition:
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.
Note:
If you search for a text comprising an underscore, it is treated as a wildcard and is replaced by a letter in the displayed results. For example, If you have queries with names - CURE, CORE, and CARE and you search for C_RE, all three queries are displayed in the result.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.
To create a new advanced condition, click New in the ADVANCED CONDITIONS LIBRARY page.
See "Creating a New Advanced Condition" for details.
To modify an advanced condition:
Navigate to the ADVANCED CONDITIONS LIBRARY page.
Select the advanced condition you want to modify.
Click Modify.
Make your modifications to the advanced condition, as appropriate.
Click Save to save your changes.
Alternatively, click Save As to save the modified advanced condition by another name.
To modify the SQL of an advanced condition:
Navigate to the ADVANCED CONDITIONS LIBRARY page.
Select the advanced condition you want to modify.
Click Modify. The ACTIVE ADVANCED CONDITIONS EDITOR page opens.
Click View SQL. The Advanced Conditions SQL dialog opens.
Modify the SQL as per your requirement.
Click Save SQL to save your changes.
Click Close to return to the ADVANCED CONDITIONS LIBRARY page.
Select the modified advanced condition.
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.
To delete an advanced condition:
Navigate to the ADVANCED CONDITIONS LIBRARY page.
Select the advanced condition you want to delete.
Click Delete. The system prompts for confirmation.
Click OK to delete selected 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 you are using with Argus Insight.To execute an advanced condition:
Navigate to the ADVANCED CONDITIONS LIBRARY page.
Select an advanced condition.
Click Execute. The system executes the advanced condition; the ACTIVE CASE SERIES page opens.
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.
Enter the prompts value in the PRE FILTER page.
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.
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:
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 Case Series, Open Case Series, and select Library. When the CASE SERIES LIBRARY page opens, select the case series and click Make Active.
Navigate to Queries, Advanced Conditions, and select Library. The ADVANCED CONDITIONS LIBRARY page opens.
Select the advanced condition you want to run on the active case series you generated in Step 1.
Select the Limit Query to Active Case Series check box.
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.