All tabular reports are created from the Report tab of the Report Designer, as shown in the following figure. The Report tab allows you to add break levels to your report, add aggregate functions and formulas on column values, choose the style of your report, and customize the look of your report.
|
Before you start to configure your report using this tab you must plan the layout of your report. There are nine types of tabular report available. The report format must match the requirements of the chosen report type.
This section contains examples of each report type. Each example also describes the report formatting necessary for the chosen type. All of these example reports are based on the report configuration in Figure 86 Report Tab on page 171.
The report types are set using the Report Properties window. You can open this window using the Properties button on the Report tab. The Report Properties window with the Report Style tab selected is shown below:
|
The two tables used in these example reports are joined using the Severity column. This has been done in order to display the severity name in place of the severity number. The displayed column names have been changed on the Columns tab, and there is no sort criteria specified in the Order By tab.
To reduce the size of the images, the example reports do not include report titles and the none of the header properties have been enabled.
The following sections describe the layout of the Default reports.
When you preview a tabular report without report formatting and using the default report style, the following page is displayed.
|
|
Figure 88: Example HTML Report Without Formatting |
When you add break levels to a report using the default report type, the selected columns are grouped and duplicate entries are removed. For example, you can add break levels to the Report tab of the example report, as shown below.
|
|
Figure 89: Report Format Field Showing a Break Level |
This formatting changes the example as shown below.
|
|
Figure 90: Example Report Showing a Break Level |
You can add two types of aggregate functions to a report: Report and Break.
When you add a Break function to a report a new row is added at the end of each break level. When you add a Report function to a report a new row is added to the end of the report.
These rows contain an aggregate value for the column. The default aggregate function is the sum of the values in the selected column.
Note:
A column cannot be used for both a break level and an aggregate function.
For example, you can add a level and a break function to the Report tab of the example report, as shown below.
|
|
Figure 91: Report Format Field Showing a Break Level and an Aggregate Function |
This formatting changes the example as shown below.
|
|
Figure 92: Example Report Showing a Break Level and an Aggregate Function |
You can add more than one break function and report function to a report. You can also add break functions to any of the break levels in the report.
A drill-down report presents the data as a series of linked pages, arranged according to break levels of the report. The first page shows the top level of the report. It contains links to the next level of detail, and displays the break functions assigned to the column on this level.
You can move down through the hierarchy of detail information by clicking the linked values on each page. To go back to the next highest level, click the link in the column heading. Report Designer allows up to 29 levels in a drill-down report.
To create a drill-down report, you must first create a report with break levels. You can then select the drill-down report type from the Report Types tab on the Report Properties window.
For example, you can create a drill-down report by adding the following break levels to the Report tab of the example report, as shown below.
|
|
Figure 93: Report Format Field Showing Break Levels |
This formatting, and the selection of the Drill-Down type, generates a set of three report pages, as shown below.
|
|
The first page is shown on the left of the figure. When you click the Clear link, the second break level page is displayed. When you click the Link2 link, the third break level page is displayed.
The form report displays each row of results in a vertical column. Each result is displayed in its own HTML table.
Aggregate functions are not displayed in this type of report. Applying a break level only changes the sort order of columns.
For example, the following form report is created from the example report with no formatting. To create the report, select the Form report type from the Report Types tab on the Report Properties window.
|
|
Figure 95: Form Report |
The label report displays the results from each row in a single HTML table cell. The cells are displayed in three columns across the page.
Aggregate functions are not displayed in this type of report. Applying a break level only changes the sort order of the cells.
For example, the following label report is created from the example report with no formatting. To create the report, select the Label report type from the Report Types tab on the Report Properties window
|
|
Figure 96: Label Report |
The break form report displays each report in its own table. Each column you add to a break level appears in its own column in the report. All columns that are not added to a break level appear as rows in the last column of the report.
For example, you can create a break form report by adding the following break levels to the Report tab of the example report, as shown below.
|
|
Figure 97: Report Format Field Showing Break Levels |
This formatting, and the selection of the Break Form type, generates the report shown below.
|
|
Figure 98: Break Form Report |
The master-detail reports display the break levels as headings. The remaining columns are displayed in a table below these headings.
You can select two types of master-detail reports on the Report Properties window, in the Report Types tab. The Master-detail I type displays the headings in a single row. The Master-detail II type displays the headings one above the other.
For example, you can create a master-detail report by adding the following break levels to the Report tab of the example report, as shown below.
|
|
Figure 99: Report Format Field Showing Break Levels |
This formatting, and the selection of the Master-detail I type and the Master-detail II type, generates the reports show below.
|
|
Figure 100: Master-Detail I and II Reports |
The drill-chart report combines a chart with a drill-down report. The report generates a chart as the first page. If you double-click an element in the chart, a drill-down report table is displayed in a second page. This table displays the data for the next break level or the data for the remaining columns if there are no lower break levels (Note that the first chart page is formatted in HTML, and the drill-down table can be in HTML, CSV or Excel format, depending on your selection. For more information, refer to Note on Drill-Chart Report on page 252).
The report format must contain at least one break level and a sum() or count() aggregate function, before you can run the report.
For example, you can create a drill-chart report by adding the following break level and aggregate function to the Report tab of the example report, as shown below.
|
|
Figure 101: Report Format Field Showing a Break Level and an Aggregate Function |
This formatting, and the selection of the Drill-chart type, generates two pages. The first page of the report is show below.
|
|
Figure 102: Page One of a Drill Chart Report |
This chart uses the default chart formatting (in HTML format). If you double-click the Major bar in this chart, the second page is displayed, as shown below.
|
|
Figure 103: Page Two of a Drill Chart Report |
This second page is either in HTML, PDF, CSV, or Excel, depending on your selection of the output type in the
The top level of an N-tier report is always the same as a drill-down report. The second and subsequent levels take the format specified in the Break Level Substyle window. To open the Break Level Substyle window, double-click the LEVEL 2 text in the Report Format field.
For example, you can create an N-tier report by adding the following break levels to the Report tab of the example report, as shown below.
|
|
Figure 104: Report Format Field Showing a Break Level and a Break Level Substyle |
This formatting, and the selection of the N-Tier type in the Report Properties window, generates the following page.
|
|
Figure 105: N-Tier Report, Level One Page |
When you click the Major link the second level page is displayed. The following page is displayed when the Default report type is selected in the Break Level Substyle window.
|
|
Figure 106: N-Tier Report, Level Two Page with a Default Break Level Substyle |
Creating breaks in a report improves its readability by eliminating repeating values in columns and by sorting rows of similar data into groups.
If you choose a date column for a break level, the date format is not applied and the date always appears in the default format: day-month-year.
To add the first break level to your report:
1. Select the column on which you would like to base the first break level from the Selected Columns: list.
2. Select the Append New Level radio button.
3. Click the Add button. The selected column is added to the Report Format: list with the heading Level 1.
To add a column in a new break level:
1. Select the column from the Selected Columns: list.
2. Select the Append New Level radio button.
3. Click the Add button. The selected column is added to the Report Format: list with the heading Level X, where X is the number of the new break level.
To add a column to an existing break level:
1. Select a column from the Selected Columns: list.
2. Select the Add to Existing Level radio button.
3. Select a break level heading, or a column under a break level, in the Report Format: list.
4. Click the Add button.
To delete a break level, or one of the columns in a break level:
1. Select the column name or the break level heading in the Report Format: list.
2. Click the Delete button. Deleting a column only removes the selected column. Deleting the level heading deletes all columns for that level.
Creating a break report specifies how data is to be sorted in the tabular report. This overrides the sort order specified on the Order By tab. If you want to disable the sorting function for a column, double-click the column name. A red icon, shown below, appears next to the column name.
|
|
Figure 107: Disable Sort Icon |
When the sorting function has been disabled, you can sort the column using the Order By tab, as described in Sorting Data on page 152.
If Report Designer attempts to sort the data in a column using the sort criteria specified in the Order By tab and the sort criteria of the break level, the Report Designer displays an SQL error.
You can add aggregate functions to display subtotals for the break levels and grand totals for the entire report. Report Designer supports multiple levels of report breaks and can organize complex data into an easy to read report with powerful analytic functions.
Note:
This guide does not include descriptions of the aggregate functions. For descriptions of these functions, refer to an SQL reference document for your database.
The aggregate functions supported are:
• r_sum
• r_avg
• r_count
• r_distinctcount
• r_min
• r_max
• r_formula
The r_formula option opens the Formula window, described in Adding Formulas on page 185. You can use this window to write a complex formula before applying it to a break level sub-total or as a grand total.
To add an aggregate function to a break level:
1. Select the column on which you would like to base the function from the Selected Columns: list. You cannot select a column that has already been selected as a break level.
2. Select the Break checkbox. If you want to use the column for a break level function and a report function, you can also select the Report checkbox.
3. Select the function type from the drop-down list in the Add Function section. If you select r_formula you must follow the additional steps in Adding Formulas on page 185.
4. Click the Add button in the Add Function section. The aggregate function is added to all break levels.
To add an aggregate function to the end of your report:
1. Select the column on which you would like to base the function from the Selected Columns: list. You cannot select a column that has already been selected as a break level.
2. Select the Report checkbox. If you want to use the column for a break level function and a report function, you can also select the Break checkbox.
3. Select the function type from the drop-down list in the Add Function section. If you select r_formula you must follow the additional steps in Adding Formulas on page 185.
4. Click the Add button in the Add Function section. A new group is added to the bottom of the report format list with the heading Report.
You can customize the way aggregate functions display in reports, for example, you can customize header text font, colors, data font, and many others.
1. The Report Format: list displays all the break levels and summaries you have created. Select the summary you want to edit and click the Edit button in the Report Format: section. This opens the Summary Properties window, as shown below.
|
|
Summary Properties Window |
2. There are two tabs in this window: Header Format and Data Format.
Header Format-- Used to customize visual effects of displaying headers of aggregate functions, such as font color, width and height, and so on.
Data Format-- Used to customize visual effects of displaying the data cell of aggregate functions, such as data font size, hoz. and ver. alignment, and so on.
The following two tables describe the properties options in the Header Format and Data Format tabs.
|
Header Format Properties |
|
Property |
Selected Values |
Description |
|
Header No Wrap |
TRUE, FALSE |
Specifies whether long header text wraps or gets truncated.
|
|
Header Horiz. Alignment |
LEFT, CENTER, RIGHT |
Specifies where the header appears, horizontally. |
|
Header Vert. Alignment |
TOP, MIDDLE, BOTTOM |
Specifies where the header appears, vertically. |
|
Header Width |
None |
Specifies the header width in pixels. You can also type in a value to set the header width to a fixed value. The value are limited from 1 to 1000. |
|
Header Height |
None |
Specifies the header height in pixels. You can also type in a value. The value are limited from 1 to 1000. |
|
Header Background Color |
None |
Selects the desired background color for displaying the header. |
|
Header Font |
None |
Specifies whether a particular font can be displayed or not. Browser dependant. |
|
Header Font Size |
None |
Specifies whether a particular font size can be displayed or not. Browser dependant. |
|
Header Font Color |
None |
Selects the desired font color for displaying the header. |
|
Header Style Class |
None |
Specifies the name of the header style class from your HTML style sheet. |
|
Data Format Properties |
|
Property |
Selected Values |
Description |
|
Data No Wrap |
TRUE, FALSE |
Specifies whether the text in cells wraps or gets truncated.
|
|
Data Horiz. Alignment |
LEFT, CENTER, RIGHT |
Specifies the horizontally alignment of the text in a cell. |
|
Data Vert. Alignment |
TOP, MIDDLE, BOTTOM |
Specifies the vertically alignment of the text in a cell. |
|
Data Width |
None |
Specifies the width of cell in either pixels or percentage of the entire browser window. You can also type in a value. |
|
Data Height |
None |
Specifies the height of cell in either pixels or percentage of the entire browser window. You can also type in a value. |
|
Data Background Color |
None |
Specifies the color of the background. Choose from predefined colors, or create your own custom colors. Values are unlimited, dependent on your browser. |
|
Data Font |
None |
Specifies whether a particular font is displayed or not. Browser dependant. |
|
Data Font Size |
None |
Specifies whether a particular font size is displayed or not. Browser dependant. |
|
Data Font Color |
None |
Specifies the color of the data. Choose from predefined colors, or create your own custom colors. Values are unlimited, dependent on your browser. |
|
Data Style Class |
None |
Specifies the Data Style Class name from your HTML Style Sheet. |
To delete a function from the Report Format: list, select the function then click the Delete button in the Add Function section.
Some functions, such as r_sum or r_avg, can give column titles in the final report that are difficult to read.
To create a new heading for the function, click the function within the break. Over-type the heading in the Function Heading: field with the replacement heading.
You can create custom formulas out of aggregate functions.
Follow the steps in the previous sections to add an aggregate function to your report and select r_formula from the drop-down list in the Add Function section. When you click the Add button the Formula window appears, as shown below.
|
Double-click AVAILABLE COLUMNS to expand the list of columns in the Available Columns: list. Select aggregate functions and columns to create your formula and click the OK button.
You can enter the displayed name for your formula in the Alias: field.
To add a report title, enter the title in the Edit Report Title: field. This title is displayed above the column headings on the first page of the report.
If you have a parameterized report, you can make the parameter value appear in the report title. Type the name of the parameter exactly as you entered it on the Conditions tab prefixed with the & character.
For example, if the name of your parameter is NODE, type &NODE in the title where you want the parameter value to appear. When you run the report, the parameter value that you use when you run the report appears in the place of the parameter name in the report title.
You can add one or more parameters in the title, including those that take multiple values.
Advanced users can also enter HTML to format the way the report titles are displayed. For example, enter <I>Custom Heading</I> into the edit field to italicize the heading.
To run a report and display it on the screen, click the Preview button on the Report tab. The Report Generating message is displayed in the lower left corner of the tab. The Cancel window is displayed with the message Query is running. Press Cancel to cancel running the query.
When the first page of the report has been generated, Report Designer displays the report in a new web browser window. In the case of long queries, subsequent pages may take a while to build and the Cancel window is displayed again.
Long reports may be displayed on multiple pages according to the settings in the Report Option window. You can access the different pages of a long report by clicking the Next, Previous, First, and Last buttons. These buttons do not appear for reports that appear in one window.
Wide reports cause a scroll bar to appear at the bottom of the report window. Use these bars to scroll horizontally to view the entire width of the report. The width of each column in the report display is determined by the length of the column heading and values displayed in the column.
You can modify your report and preview the report again. If you closed the previous report window, a new one is created. If you did not close the report window the new report is displayed in the same window.
When an existing window contains the new report it is not brought to the front over the Report Designer window. You must move to that window to view the new report.
When you click the Preview button while previewing a parameterized report, the Edit Parameter Values window is displayed to allow you to choose the values of the parameters in your report query.
You can click the Reset to predefined values button to use the values set in the Define Parameters window. You can also type specific parameter values in the Additional values field. Click the OK button to preview the report.
For a description of the Edit Parameter Values window, see Creating Parameterized Reports on page 144.
For drill-down reports it is important that your web browser is configured to always read the latest version of a report and not use an older version in the web browser cache.
For information on changing your web browser cache settings, see your web browser documentation.
To save a snapshot of the report, click the Save Snapshot button. The Save window is displayed. Enter the report file name then press OK to continue.
When naming your file do not include a file extension. You must not include spaces or any special characters, for example @,#,$,%.
The Report Designer runs the report and display it in a new window. The report is also saved as an HTML file in your Inbox.
When you click the Save Snapshot... button for a parameterized report, the Enter Parameter Values window is displayed after the Save window. After selecting the required parameter values the report is displayed and saved.
For a description of the Enter Parameter Values window, see Creating Parameterized Reports on page 144.
You can save the Report Designer settings using the Save Definition button on the Report tab or the menu option FileÆSave Definition.
If the report conditions do not contain parameters, the settings are saved as a report with the file extension.rpt. If the report condition does contain parameters, the settings are saved as a parameterized report with the file extension.par.
For additional information on loading and saving Report Designer settings, see Loading and Saving Report Definitions on page 167.
To change the properties that control the report output, click the Report Properties button on the Report tab (you can also access the report properties by clicking the icon in the toolbar). The tabbed Report Properties window is displayed, as shown in Report Style Tab on page 172, which lets you change the display settings.
The Report Style tab is used to select the output format and the style of the report.
The output format is set using the Output Format drop-down list. The formats available depend on the type of report selected. The default report type is HTML. For details of all report types, see Report Style Tab on page 250.
The different report styles are described in Choosing the Tabular Report Type on page 171.
The other Report Properties window tabs are described in the Tabular Report Properties on page 250.
The Custom Call feature allows external actions to be triggered upon report creation. Click the Custom Call button on the Report tab to display the Custom Call window.
For a description of the custom calls, see Creating a Custom Call on page 242.
If you have selected the N-Tier report type, you can distribute different break levels of break reports to different groups within your organization instead of running separate reports.
An example N-Tier report is shown in N-Tier Report on page 181.
To create an N-Tier report:
1. Create a report with at least two break levels.
2. Click the Properties... button.
3. In the Report Properties window, select the N-Tier report style and click the OK button.
4. On the Report tab, double-click the break heading at Level 2 or a lower level. The Break Level Substyle window is displayed, as shown below.
|
|
Figure 109: Break Level Substyle Window |
5. Select a report style for this and lower break levels.
6. If you want to prevent the recipient of the sub-level report opening the top level page or a page for a higher break level, select the Yes radio button in the Secure Hyperlink section. The hyperlinks to the higher levels are not displayed on the report page.
7. Click OK to close the Break Level Substyle window. The break level heading now includes a green arrow icon, as shown below.
|
|
Figure 110: Break Level Substyle Arrow |
8. On the Report tab, click the Save Snapshot button and save a snapshot of the report. The Report Designer saves each break level report page as a separate snapshot. The highest level of report snapshot is named reportname.html, and lower level report snapshots are each named reportname_breakcolumnvalue.html.
For example, a report called Example with two break levels creates the files Example.html, Example_1.html and Example_2.html.
You can publish these different snapshots to different users and groups using the Publish Folder Content window, as described in Publishing Files on page 42.