The SQL tab contains an SQL statement that matches the configuration of the Tables, Columns, Joins, Conditions, and Order By tabs. You can use this tab for the following operations:
• Exporting the SQL statement for an existing report.
• Importing an SQL statement into a new report.
• Writing an SQL statement for a new report.
• Creating a basic tabular report using the SQL statement.
Although it is possible to create a basic tabular report using only the SQL tab, you can also import or write an SQL statement and change to any other report type. The report configuration is retained when you change report types.
The SQL tab does not appear by default when you select a new tabular report, chart or other report type. Click the SQL button or select the menu option ModeÆSQL to display the tab. An example SQL tab is shown below.
|
|
Figure 81: SQL Tab |
As you complete the configuration of the Tables, Columns, Joins, Conditions, and Order By tabs an SQL statement is written by the Report Designer. This statement is used to query the database for the data to use in the report.
When you open the SQL tab for the first time the SQL Statement to Generate Tabular Report: field contains the SQL statement for the current report.
If you change the configuration on the other tabs and return to the SQL tab, the statement does not automatically update. You must click the Refresh button to replace the displayed statement with a new statement created from the other tabs. The Report Designer asks you to confirm the operation.
To save the SQL statement click the Export... button. The Export Query window is displayed. Enter a file name and click OK. You can only use alphanumeric characters without any spaces or a file extension for the file name. The SQL statement is saved in the $INFOHOME/websqlbr/sql directory.
You can import the configuration for any report type by importing an existing SQL statement.
To open an existing SQL statement, click the Import... button. The Import a SQL Query window is displayed listing all of the previously saved SQL statements. These files are stored in the $INFOHOME/websqlbr/sql directory.
Select a statement and click OK. The SQL statement appears in the SQL Statement to Generate Tabular Report: field.
To transfer the SQL statement configuration to the Tables, Columns, Joins, Conditions, and Order By tabs, click the Parse/Edit button.
Note:
Note that this Parse/Edit feature is not designed to be capable of handling complex SQL. Only a limited range of parsing rules are incorporated into this feature, for example, the imported SQL must be given in fully-qualified format. Due to the limitation of parsing rules, after an SQL statement is parsed, you may need to check the Tables, Columns, Joins, Conditions, and Order By tabs and manually correct any mistakes that the parser did not parse correctly.
If the SQL statement includes a condition with a parameter, the SQL Saver window is displayed, as shown below.
|
You must associate each parameter listed in the Parameters list with a report table and column. Select each parameter and type the name of the associated table and column in the Associated Column field. You must also select the correct column type for the parameter.
Once the import is complete you can select one of the tabs and complete the configuration of the report. Alternatively you can create a basic tabular report using the SQL tab, as described in Creating a Basic Tabular Report Using the SQL Statement on page 161.
You can either directly type long queries or cut and paste from any text editor into the SQL Statement to Generate Tabular Report: field. To transfer the SQL statement configuration to the Tables, Columns, Joins, Conditions, and Order By tabs, click the Parse/Edit button.
Note:
Some queries may be too complex to be handled by the Parse/Edit feature as only a limited range of parsing rules are incorporated into this feature. Due to the limitation of parsing rules, after an SQL statement is parsed, you may need to check the Tables, Columns, Joins, Conditions, and Order By tabs and manually correct any mistakes that the parser did not parse correctly.
Netcool Reporter requires the SQL to be given in fully-qualified format. For example, to indicate the Severity column of the REPORTER_STATUS table, enter REPORTER_STATUS.Severity. If you enter a non-qualified name, Netcool Reporter tries to render it in fully-qualified form, but it may not do so correctly.
Once an SQL statement has been entered or imported, you can go to any tab to add the customization that is not available in the SQL statement.
If you have entered a parameter for a parameterized report you must associate the parameter with a column. When you click the Parse/Edit... button the SQL Saver window is displayed. Use the SQL Saver window to associate the parameter with a column, as described in Importing an SQL Statement into a New Report on page 159.
Once you have completed your SQL statement using the SQL tab, and transferred it to the other tabs using the Parse/Edit... button, you can create a basic tabular report.
This section describes the processes involved in creating a basic tabular report.
You can enter the report title in the Report Title: field. This title is displayed at the top of the first page of the report.
To open the SQL Report Properties window click the Properties button on the SQL tab. The window opens with the Report Style tab selected. On this tab, you can only select the Default report type. The report output selection is limited to HTML, CSV (Comma Separated Values) and Excel 97.
For an example of a default tabular report, see Default Reports on page 172.
Configure the remainder of the report properties in the General, Auto Format and Advanced tabs. For information on these properties, see Tabular Report Properties on page 250.
The Preview button generates a tabular report based on the SQL displayed and the setting in the SQL Report Properties window. When you click the Preview button, a Report Generating message is displayed in the lower left corner of the SQL tab.
When the first page of the report has been generated, Netcool Reporter opens the report in a new web browser page.
You can keep the report window open and return to the SQL tab to modify your report. Click Preview to generate the updated report in the open report page. If a new page does not open when you click the Preview button, look through your open web browser pages to see if another report window already exists.
If you want to save a fixed image of the report output, click the Save button. The Save window appears prompting you to select a location to save the file and enter the report file name. Enter a name and click OK to continue.
Report Designer runs the report and displays it in a new window. The report is saved in HTML file format.
If your SQL statement includes a parameter, a Parameter Info window opens when you click the Preview or the Save Snapshot... button. The Parameter Info window is identical to the SQL Saver window, shown in Figure 82: SQL Saver Window on page 160.
You must specify the table column associated with each parameter (in the form table.column_name) and the data type of the column, and click OK.
For example, for a parameter called NODE you could enter REPORTER_STATUS.NODE.
The SQL statement defines the report type. The Report Properties window displays the appropriate tabs for this report type. For more information on report properties, refer to the properties sections for each report type in Chapter 6: Customizing Reports on page 247.