After you have selected your tables for the report you must select the columns to use. The Columns tab, shown below, allows you to select columns from tables that you selected on the Tables tab.
|
|
Figure 64: Columns Tab |
Before you can select a column you must use the Tables: drop-down list to select the table that contains the column. If the table you want is not in the drop-down list, select the table using the Tables tab, as described in Selecting Tables on page 127.
To select a column, highlight the column name in the Available Columns: list and click the Add> button. The column name is copied to the Selected Columns: list. The data type of the column is displayed in the Column Data Type: field.
To remove a column from the Selected Columns: list, highlight the column and click the <Remove button.
To select all of the columns click the Add All>> button. To remove all of the columns click the <<Remove All button.
If you want your report to remove all duplicate rows automatically and display only unique rows, you must select the Unique Values checkbox.
Note:
Removing duplicate rows can slow down the data retrieval process and should only be used when you definitely want to eliminate any duplicate rows of data.
You can change the column headings that appear in a report or chart. Select the column name in the Selected Columns: list, and edit the name in the Column Heading: field.
You can also enter HTML script to format the way the column headings are displayed in a report. For example, entering <I>Custom Heading</I> italicizes the column heading.
To move a column up or down in the Selected Columns: list, highlight the column and click the Move Up or Move Down buttons, as shown below.
|
|
|
Figure 65: Move Up and Move Down Buttons |
You can change the display format of a number of columns types. For example, the number display format options include 9999.99, and 9,999.999. Character columns cannot be reformatted without corrupting the data.
The Display Format: drop-down list displays the default display format for the column currently highlighted in the Selected Columns field. Expand the drop-down list and select an alternative format.
Click the More... button to attach a suffix or prefix to the value of the column.
To add a column expression, click the Add Expression… button. The Expression window is displayed, as shown below.
|
|
Figure 66: Expression Window |
An expression can include database columns, arithmetic operators, database functions, or a combination of these. You can create an expression by typing directly in the Expression: field.
To insert a column in the expression, ensure the cursor is at the required position in the Expression: field and double-click the column name in the Tables and Column: list.
To insert a function in the expression, ensure the cursor is at the required position in the Expression: field and double-click the function in the Functions: list.
To insert an arithmetic operator in the expression, ensure the cursor is at the required position in the Expression: field and click the required operator button.
Some functions return a value for every row. For example, you can use the function ROUND() to round off a number. Some functions summarize or aggregate the values of the rows. For example, you can use the function SUM() to add up all of the values in a column for a group of rows.
When you use a function that summarizes rows into groups, Netcool Reporter automatically adds clauses to your query to group the data correctly. This enables the Group By... button at the bottom of the Columns tab. The Group By... button is described in Editing Group By Clauses on page 132.
When you have created the expression that you want to add to the query, click the OK button. The expression is added to the Selected Columns: list in the Columns tab.
To edit an expression in the Selected Columns: list, highlight the expression and click the Edit Expression... button. The Expression window appears with the expression displayed.
By clicking the Group By... button, you can override the automatic Group By clauses created when you added an expression containing a summary function. The Group By button is grayed out when no expressions exist. When you click the Group By... button, the Group By window appears, as shown in the following figure. All columns in the query appear in the Available Columns: field. All of the columns in the query that are not in an expression that uses a summary function appear in the Group Columns: field.
|
|
Figure 67: Group By Window |
The columns in the Group By window control how the rows selected by the query are summarized. Some databases require that every column that is not part of a summary expression be included in the columns. Other databases allow you more control over how the results are summarized.