Working with the SQL Template Designer > Format the SQL Template Output
  
Version 10.1.01
Format the SQL Template Output
1. Format fields in a report within the Formatting tab.
Check the query fields to select the fields to be displayed in the report.
Move fields up and down to position them in the report’s output.
Modify the Label for the displayed fields to something more relevant to your needs.
Modify the Number, Size, Date, and Time Formatting.
Modify the Alignment, Aggregation, Bar Type, and Bar Type Color.
Modify the Area or Column Sparkline Format for a SQL Tabular Report Template
2. Choose a Report Type:
Bar Chart: Configure a Bar Chart SQL Template
Donut Chart: Configure a Donut Chart SQL Template
Gauge Chart: Configure a Gauge Chart SQL Template
Area/Stacked Area Chart: Configure an Area Chart SQL Template
Horizontal Bar Chart: Configure a Horizontal Bar Chart SQL Template
Horizontal Stacked Bar Chart: Configure a Horizontal Stacked Bar Chart SQL Template
Pie Chart: Configure a Pie Chart SQL Template
Pivot Table: Configure a Pivot Table SQL Template
Stacked Bar Chart: Configure a Stacked Bar Chart SQL Template
Table: Configure a Table in the SQL Template Designer
Number, Size, Date, and Time Formatting
The following field formatters are provided:
Number: This format is based on the Java class NumberFormat. This formatter ignores the Pattern listed in the SQL Template Designer. See also, Examples of Negative Value Formatting.
Custom Number: This format is based on the Java class NumberFormat. Unlike the Number formatter, this Custom Number formatter takes into account the Pattern when formatting the value. See also, Examples of Negative Value Formatting.
Decimal: This formatter is based on Java decimal formatters, DecimalFormat.
Date: This formatter is based on the Java class SimpleDateFormat. Note that the Date Formatter is available for templates developed in earlier StorageConsole releases, however, it is no longer supported in the SQL Template Designer because the format of the date is controlled by the user’s profile. For special cases, where custom date formatting is required, use the Oracle TO_CHAR function in the query. This function converts a date or interval value to a character data type in the specified format: Date, Timestamp.
File Size: This formatter formats the file size with the unit of measure that is most applicable to the field value. For example, 2048 MB would become 2 GB. The pattern field takes the following values: Bytes, KB, MB, GB, and TB (or the Kibibyte multiples: KiB, MiB, GiB, and TiB). This is the unit of the data returned from the Portal database.
Percentage Bar: The Percentage formatter results in a horizontal bar, representing the value in a tabular report column. For example, 50:75 results in a color designation, where a value < 50 renders green, 50-75 renders yellow, and > 75 renders red.
Status Icon: Colored circle icons, relevant to a value, will render in a tabular report column. A Pattern must be configured to render the relevant color. For example, 0|Green,2|Red,1|Yellow,*|White, where 0 renders as green, 1 renders as yellow, 2 renders as red, and any other value causes the icon to remain white. Typically, this format is used to display status, such as backup status.
Area Sparkline: See Area or Column Sparkline Format for a SQL Tabular Report Template.
Column Sparkline: See Area or Column Sparkline Format for a SQL Tabular Report Template.
Truncate: Truncates a string with ellipsis. The maximum size of the string is 28 characters. Use the pattern to override the maximum size. When using the Truncate formatter, it is recommended to use the Advanced dialog to create a hover tool tip. See Creating Mouse-over Hovers.
Examples of Negative Value Formatting
Since the number format is based on Java formatters, you can use a semi-colon (;) sub-pattern boundary to represent negative numbers so that they stand out differently from positive values.
The following examples show how to use the semi-colon to represent the negative value, -1234.56.
#,##0.00;(#,##0.00) displays the value, -1234.56, as (1,234.56)
#,##0.00;'<font color=red>'(#,##0.00) displays the value, -1234.56, in red as (1,234.56)
Alignment, Aggregation, Bar Type, and Bar Type Color
Select the Field and click Formatting (at the bottom of the window) to view the formatting options.
Data alignment - Left, Center, or Right.
Aggregation - Sum, Average, Minimum, Maximum
Bar type - Bar or Line (for bar charts only)
Color - Bar or Line (for bar charts only)
 
1. At the bottom of the Formatting window, use the Move Up and Move Down buttons to organize the fields in the order that you want them to appear in a tabular report. Be sure to select a row before shifting the fields.
2. In the Save & Share window, complete the appropriate text boxes to save and share the report, then click OK. You can choose to share with one or more users and user groups. See Save and Share Report Templates. You can also assign configurations for the Inventory. This determines how the report templates will be classified and displayed in the Inventory. See Inventory Configuration
The saved report template will be listed in the report group that you select.
Configuring a Header and Footer in a Custom Report Template
At the bottom of the Formatting window in the SQL Template Designer, click the Header/Footer button to launch the window where you can define the text and variables that will be displayed when the report is generated.
Any variables used in the query can be included in a header or footer, as shown in the above example.
Area or Column Sparkline Format for a SQL Tabular Report Template
Sparklines render as small charts embedded in each row of a tabular report, presenting an at-a-glance visualization of data trends. You can display a number series as an Area Sparkline or a Column Sparkline in a column of a tabular report to illustrate spikes in data that may indicate issues that require attention. For details on how to configure a sparkline in a SQL Template, see Example of a Sparkline Query in a SQL Template. This section covers only the SQL Template formatting selections required to render the data as a sparkline.
To format a table column as a sparkline chart, take the following steps.
1. In the SQL Template Designer Formatting tabbed section, select the field to be rendered as a sparkline chart.
2. From the Formatter list, select either Column Sparkline or Area Sparkline.