Creating Conditions

Conditions narrow the scope of the data that is returned from the database. Conditions describe the criteria the data must meet in order to be included in your report. It is very important when you are working with a large database to define meaningful conditions before you run a query so that you do not bring back too much or unwanted data.

The Conditions tab allows you to create three types of clauses:

•   Where clauses

•   Having clauses

•   Union/Set clauses

 

   Note:


    The Union/Set clause tab is a Report Designer configurable option and may not appear on your Conditions tab.


Report Designer allows you to create conditions using a point-and-click interface or by typing the conditions directly. This section describes how to create the most common conditions. Refer to your database reference manual if you want to create more complicated conditions.

An example Conditions tab is shown below.

 

   Figure 69: Conditions Tab

Writing a Condition in the New Condition Field

Before you can enter a new condition, you must first construct it in the New Condition: field.

You can enter your condition directly in the New Condition: field or build your condition using the table, function and operator buttons and lists.

Adding a Column to the Condition

To add a column name to your condition:

1.   Position the cursor in the New Condition: field where you want to add the column name.

2.   Select a table from the Tables: drop-down list.

3.   Double-click the required column name to add the column to the condition.

Adding a Function to a Condition

To add a function to your condition:

1.   Position your cursor in the New Condition: field where you want to add the function.

2.   Double-click a function in the Functions: list to add the function to the condition.

 

   Note:


    Consult a manual of SQL functions for descriptions of specific functions.


Adding a Comparison Operator to a Condition

To add a comparison operator to a condition:

1.   Position the cursor in the New Condition: field where you want to add the comparison operator.

2.   Click the required comparison operator button. Operator buttons include: +, =, <>.

Adding a Column Value to a Condition

To add a column value to your condition:

1.   Position the cursor in the New Condition: field where you want to add the column value.

2.   Select a table from Tables: drop-down list.

3.   Click the required column name to highlight it.

4.   Click the Lookup button. The Report Designer queries the selected column and returns all of the values in the column. These values are listed in the Report Designer Select Values for Lookup window, as shown below.

 

   Figure 70: Report Designer Select Values for Lookup Window

5.   Select the required value and click OK. The window is closed and the value is entered in the condition.

Adding a Parameter

When you add a parameter to a condition using the Add Parameter button, the report opens the Enter Parameter Values window before generating the report. The report uses the values you select or enter as part of its database query.

Parameters are described in Creating Parameterized Reports on page 144.

Adding and Managing Conditions in the Conditions Field

Once you have written a condition in the New Condition: field, you must transfer it to the Conditions: list before it can be used by the report. The Conditions: list contains three tabs and you must select the required tab you transfer the condition.

Selecting the Conditions: List Tab

The Conditions: list contains the Where, Having, and Union/Set tabs. These tabs define the the type of database query the report is to send. You can only select one tab for the report query.

Where Tab

The Where tab is selected by default. When this tab is selected the condition limits the number of rows returned. Only those rows conforming to the condition are returned. For example, WHERE REPORTER_STATUS.SEVERITY = 5.

Having Tab

The Having tab filters the groups returned, and displays only those records containing data that meets the condition. For example, HAVING REPORTER_STATUS.TALLY > 50.

Union/Set Tab

The Union/Set tab is only available when you have this Report Designer option enabled. It provides a set of alternative operators for creating composite queries.

Transferring a New Condition to the Conditions: List

Once you have constructed a condition in the New Condition: field, click the Add button to move the condition to the Conditions: list. The new condition is always added at the bottom of the list of conditions.

There is an additional step if you have selected the Union/Set tab.

The Union/Set Tab

When you select the Union/Set tab and click the Add button, the Add Union Set window is displayed, as shown below.

 

   Figure 71: Add Union Set Window

Select one of the radio buttons in the Set Operators section. The radio buttons are:

•   UNION

•   UNION ALL

•   INTERSECT

•   INTERSECT ALL

•   EXCEPT

•   EXCEPT ALL

•   MINUS

You can also enter an SQL expression in the SQL Expression field. Click OK to transfer the condition.

Creating Multiple Conditions

Before you add a new condition to a multiple condition report, you must select either the AND or OR radio button.

You must use the AND button when the two connected conditions must both be true for the row to be returned in the results.

You must use the OR button when at least one of the two connected conditions must be true for the row to be returned in the results.

 

   Note:


    If you are not sure how to create a condition, your database reference manual can give you more examples and a complete description of the supported functions and correct syntax for conditions.


Editing a Condition

To edit a condition in the Conditions: list, click the condition to highlight it then click the Edit button. The Edit Condition window is displayed, as shown below.

 

   Figure 72: Edit Condition Window

Change the condition as required and click the OK button to apply the changes and close the window.

Deleting a Condition

To delete a condition in the Conditions: list, click the condition to highlight it then click the Delete button.

Creating Parameterized Reports

Replacing a fixed value with a parameter in your report condition adds flexibility by letting you choose different comparison values every time you run the report.

When you run the report the Enter Parameter Values window is displayed. You are prompted to select one or more values from a list or to enter your own values. The report is only run when you have entered values for all of the report parameters.

Adding parameters to a condition makes it easier to reuse and share queries since you can define the query once and use it to access different sets of data based on new input each time the query is run.

For example, you may want to create a report that asks you to select a node on your network and then runs the report against this node. Typically a parameter is used to replace the value in a condition. In this example, the condition might look like:

REPORTER_STATUS.NODE = &NODE

A parameter can be used anywhere in a condition, providing the values you enter or select at run time create a valid condition format recognized by the database.

There are two limitations with parameters. You can use no more than eight parameters per report and a parameter name must contain only alphanumeric characters without any blanks or special characters, for example @,#,$,%.

 

   Note:


    If you are not sure how to create a valid parameter, your database reference manual can give you a complete description of the correct syntax for conditions. For example, when you are creating a parameter based on Date column, you must make sure that both sides of the parameter are of the same type and same format, otherwise no data may be returned. Taking an Oracle database as an example, you need to format the parameter value with either to_date() function or the to_char() function on the date column, such as, where to_char(ENDDATE, 'DD-MON-YYYY') in (&ENDDATE).


Adding a Parameter

To insert a parameter in a condition:

1.   Position your cursor in the New Condition: field where you want to add the parameter.

2.   Select a table from the Tables: drop-down list.

3.   Double-click the name of the column that the parameter is to use for its data values, and the column name appears in the New Condition: field (for example, DEPT.DEPTNO).

4.   Click the Add Parameter button. The Define Parameter window is displayed, as shown below.

 

   Figure 73: Define Parameter Window

5.   By default the Parameter Name: and Description: fields contain the column name. You can edit these values as required.

The description in the Description: field appears as the prompt question in the Enter Parameter Values window when the report is run.

The name in the Parameter Name field must not include spaces or any special characters, for example @,#,$,%.

6.   To create a selection list to display in the Enter Parameter Values window, click the Lookup button. The data values in the selected column are displayed in the Available Values: list. Select the values you require in your selection list and click the Add> button. These values are added to the Selected Values: list.

You can also add all of the values in the Available Values: list and remove one or all of the values from the Selected Values: list.

7.   Select the Allow Multiple Value Select checkbox if you want your report user to be able to choose more than one value for each parameter. For example, you may choose to run a report returning data for both REPORTER_STATUS.NODE = ’Development’ and REPORTER_STATUS.NODE = ’Finance’.

8.   Select the Add Single Quote for String or Date checkbox if you want the report to accept string or date values. Report Designer wraps the string or date in single or double quotes depending on the requirements of the database. When available, this checkbox is selected by default. If you deselect the checkbox you can only enter numeric values.

9.   Select the Allow Manual Entry checkbox to allow the report user to manually enter new parameter values when the report is run. If the checkbox is deselected, the report user can only select from the values in the Enter Parameter Values window Values: selection list.

10.   Deselect the Restrict Value to Predefined List checkbox to allow the report user to click the Load all values from database button on the Enter Parameter Values window to add all values to the Values: selection list. This list can be filtered using the SQL filter query.

If this checkbox is selected the Load all values from database button on the Enter Parameter Values window is disabled.

11.   You can use the SQL Filter... button to define a SQL query to limit the parameter values retrieved from the database. The returned parameter values are displayed in the Enter Parameter Values window Values: selection list when the Load all values from database button is clicked. For details of the SQL query, see Restricting Parameter Values on page 149.

12.   Click the OK button to add the parameter to the condition in the New Condition: field.

 

   Note:


    You must save the report definition using the Save Definition button on the Report tab to be able to run a parameterized report.


Entering Parameters When You Run a Report from the Netcool Reporter Application

When you run a parameterized report the Enter Parameter Values window is displayed, as shown below. You must enter values for each parameter before the report is generated.

 

   Figure 74: Choosing Parameter Values at Runtime

Select each parameter in turn from the drop-down list. If the parameter contains predefined values these appear in the Values: list.

If the parameter configuration has the Allow Multiple Value Select checkbox selected in the Define Parameter window, you can select one or more values from the Values: list.

If the parameter configuration has the Allow Manual Entry checkbox selected in the Define Parameter window, you can add a value to the Value: list. Enter the new value in the Additional value: field and click the Add to list button. You can now select this new value.

If you click the Reset to predefined list button, then the Values: list returns to its original settings.

If the parameter configuration has the Restrict Value to Predefined List checkbox deselected in the Define Parameter window, you can click the Load all values from database button to retrieve values for this parameter. If there is a SQL filter query defined, these values will be filtered by the query.

When you have chosen the values for each parameter, click the OK button.

Entering Parameters When You Run a Report from the Netcool Reporter Database

When you run a report directly against the Netcool Reporter database without using the Netcool Reporter application, you must enter the parameter values in the Parameter Value Selector window. An example window is shown below.

 

   Figure 75: Parameter Value Selector Window

If the report contains more than one parameter, each parameter appears as a new row on the window.

If the parameter configuration has the Allow Multiple Value Select checkbox selected in the Define Parameter window, the Listed Values column appears as a list of values, allowing you to select one or more of these values (by Ctrl/Shift-clicking).

If the parameter configuration has the Allow Manual Entry checkbox selected in the Define Parameter window, you can enter your own value into the User Values field and click Add to add it into the Listed Values list.

The Load all data from DB button is used to load values from the database into the Listed Values list. Note that if there is a SQL filter query defined in the parameter, these values are the result after filtered by the query.

When you have chosen the values for each parameter, click the OK button.

Restricting Parameter Values

The Values: selection list in the Enter Parameter Values window displays the selected values when the report is run. If you want to see additional values you must click the Load all values from database button in this window.

By default the Load all values from database button displays a list of all values returned from the database column. You can restrict this list by editing the parameter SQL query.

To edit the SQL query, click the SQL Filter... button on the Define Parameter window. This button opens the SQL Query Statement window, as shown below.

 

   Figure 76: SQL Query Statement Window

When you create a new parameter the SQL Query Statement window contains a simple select/from query. You can change this query to modify the data returned.

For example, if you want the Load all values from database button to display discrete network node names that all start with the characters dev, you can use the SQL query shown below.

select

distinct ( REPORTER_STATUS.NODE )

from

REPORTER_STATUS

where

    NODE like’dev%’

Using the CURR_USER Variable

Netcool Reporter populates the CURR_USER variable with current user’s login name. You can use this feature to filter the parameter values according to the user running the report.

The following example shows how you can use the CURR_USER variable to prevent any user other than ADMINISTRATOR from displaying all of the node names when they click the Load all values from database button.

select

distinct ( REPORTER_STATUS.NODE )

from

REPORTER_STATUS

where

    %CURR_NAME%=’ADMINISTRATOR’

If a database column you have selected for your parameter contains Netcool Reporter usernames you can use the CURR_USER variable to create a filter that displays only data linked to the username of the reader of the report.