Configure SQL Template Scope Selector Components
When you create a report template, you must also determine what elements should be provided in the report’s Scope Selector—the interface that enables the user to specify the scope of the data to be displayed in the report. Each time a report is generated from the report template, the Scope Selector enables the selection of parameters to be used for report generation.
Date Range
Check this component to include a a Time Period and Data Range selector in the report’s Scope Selector that is displayed when generating the report.
1. Check Date range.
2. Select the Date range row and click Configure to display the Date range window.
3. Configure the Date component to include the time with the date range. Choose Yes or No to specify if the time will be shown along with the date range in the Scope Selector. This configuration results in a report Scope Selector that includes the following:
Host Groups and Client Scope
This Report Designer component enables you to customize how the report will display the host groups’ data.
Use this configuration to specify if the cascade to sub-groups option is available and, if available, the default setting—Checked or Unchecked.
Custom Text Fields
In the Template Designer pane, a user can define fields that can have values substituted into a query. When the report is generated, the Scope Selector window presents the fields that can be selected, thus providing dynamic input for the report.
This option enables you to create up to three free-form text fields, where you can specify a field that you want to use in your query. For example, you might enter Host Name, so that when you form the query, you can specify a host name to be inserted into the query.
Example of Custom Text Field Configuration in a SQL Template
The following steps illustrate the advantage of Custom Text Fields.
1. In the SQL Template Designer, select the Custom Text Fields component and click Configure.
2. In the Custom Text Fields window, define a field named Host ID and click OK.
3. To use this newly configured Custom Text Field, be sure to select the checkbox.
4. In the Query window, enter the following SQL query by typing a partial statement and then double-clicking to select fields:
select * from aps_v_host_volume where host_id > ${freeText1}
This query can be constructed by combining typing with double-clicking selections in the window, as shown in the following example:
The resulting query will be:
select * from aps_v_host_volume where aps_v_host_volume.host_id < ${freeText1}
5. Click Validate Query.
6. In the Formatting window, select all the fields for a table.
7. Save the report template with a name and a menu group.
8. Generate the report from this report template and in its Scope Selector window, provide a value for the Host ID field. This value will be passed to the query.
Static Custom Combo Box
The Report Designer Static Combo Box component enables the flexibility of offering the selection of various characteristics when the report is generated—similar to the way the out-of-the-box reports handle options, such as Event Type or Job Type. A Combo Box becomes available in the Scope Selector, enabling a user to select items from a drop-down list. This is particularly useful in environments where custom attributes have been defined for objects, enabling the user to select specific attributes at runtime.
Specify a heading, along with a list of values that will be displayed as a drop-down selection.
To include a blank or no choice option, specify the list of values in the form:
,option 1,option 2
If this no choice option is selected when the report is generated, an empty string will be passed to the SQL expression.
Example of a Combo Box Configuration in a SQL Template
1. Create a Custom Combo Box to enable the user to select all hosts for a particular Make:
2. This Combo Box can be used in a report template query, such as:
select * from apt_v_server where apt_v_server.make = '${freeCombo1}'
Note: In this example, the Combo Box variable is a string, so it must be enclosed in single straight quotes to be evaluated as a text field, as shown in the above example.
3. Format this report as a table.
4. Save it as List Hosts by Make in a Menu Group.
5. Generate a report from this newly saved report template.
In this example, a Combo Box heading was specified with a list of values that will be presented in a drop-down list in the scope selector, when the user generates the report:
6. Select a Make from the drop-down list and click Generate.
Query Custom Combo Box
The SQL Template Designer offers a feature to design a combo box that is populated with the results of the query. In addition to configuring the Query Custom Combo Box, a relevant report template query must be constructed to enable accurate report filtering using the combo box.
1. In the Template Designer component, double-click Query custom combo box to access the configuration window.
2. In the Heading field, enter the heading that will appear in the report template’s scope selector with the drop-down list.
3. In the Custom Combo Box query field, supply a query with the following components and then click Validate.
SQL command | SELECT DISTINCT |
Key field, Field value | The comma-separated key-value pair is derived from a published view. For example: Key: storage_array_id Value: array_name The Key in the key-value pair is what will substitute the ${queryCombon} variable and the Value is shown in the Combo box. See the Hitachi Storage Viewer Database Programmer’s Reference Guide for descriptions of the published database views, including the available fields. In addition, these views and fields are available via the SQL Template Designer Query component. See Construct the SQL Query. |
Published view | End the SELECT statement with FROM and the published view, with optional list criteria. For example: FROM aps_v_array_group ORDER BY array_name ASC |
Sample Query Custom Combo Box Queries
SELECT DISTINCT server_id, server_name FROM apt_v_job
SELECT DISTINCT client_id, client_name FROM apt_v_job
SELECT DISTINCT product_type, product_type_name FROM apt_v_job
SELECT DISTINCT job_type, job_type_name FROM apt_v_job
SELECT DISTINCT vendor_status, vendor_status_name FROM apt_v_job
SELECT DISTINCT policy_id, policy_name FROM apt_v_nbu_job_detail
SELECT DISTINCT policy_type, policy_type_name FROM apt_v_nbu_job_detail
SELECT DISTINCT media_server_id, media_host_name FROM apt_v_nbu_job_detail
SELECT DISTINCT storage_array_id, array_name FROM aps_v_array_group
4. In the Query tab, enter a SQL query that will use the value selected in the populated combo box. For example:
Note: If the Combo Box variable is a string, it must be enclosed in single straight quotes to be evaluated as a text field. The above example is a numeric, so single quotes are not used.
Example of a Query Custom Combo Configuration in a SQL Template
The following query produces the Ultimate NetBackup Job Status Report.
In the SQL Template Designer Query Custom Combo box, enter: select window_group_id, window_group_name from apt_v_date_window order by window_group_name
Then, in the Query, use the following query to report Ultimate Job Status:
SELECT
a.nbu_job_id,
a.client_id,
b.client_host_name,
b.policy_name,
b.job_type_name,
decode(a.overall_status,0,'Success',
1,'Partial',2,'Queued',3,'Running','Failed') overall_status,
a.start_date,
a.finish_date,
a.kilobytes,
file_pathlist,
decode(was_restarted,1,'Y','N') was_restarted
FROM table (nbu_rtd.listJobSummaryAfterRestart (
${startDate},
${endDate},
${queryCombo1},
${spHosts},
null,
null,
null)) a
, apt_v_nbu_job_detail b
where a.job_id = b.job_id
