Working with the SQL Template Designer > Advanced SQL Report Template Option - Drilldowns
  
Version 10.0.01
Advanced SQL Report Template Option - Drilldowns
Just as the out-of-the-box reports contain links that drill down to other reports, Custom Reports can be configured to include drilldowns.
Fields from the query can be used in the drill down syntax.
In the SQL Template Designer, in the Formatting window:
1. Select the field that will become the drill down link.
2. Click Advanced to launch the Drilldown window where you will enter the syntax required to drilldown to the report.
3. In the Drilldown window, enter the details that the Portal will need to link to the report. The following example and the accompanying descriptions illustrate the required components for a drilldown specification.
Examples
systemName=displayServerDetail&serverId=${row[‘server_id’].data}
templateInstanceId=400&serverId=${row['server_id']}
Where:
systemName
Drilldowns should start with a systemName or templateName parameter and then provide additional optional parameters to the target report. The fields from the query can be accessed using the ${} format to access the "row" context variable. e.g. ${row['field_name'].data} will substitute the field_name into the drilldown.
System names for reports can be obtained by pressing CTRL+ALT+T while the report is in the active tab.
Use either systemName, templateInstanceId, or templateName in the syntax, based on the following use cases.
systemName - The required prefix for drilldowns to factory-shipped, out-of-the-box reports that cannot be customized with either the SQL Template Designer or the Dynamic Template Designer.
templateInstanceId - The required prefix for drilldowns to Report Templates created with the Dynamic Template Designer.
templateName - The required prefix for drilldowns to Report Templates created with the SQL Template Designer.
<displayReportName>&<fieldName>
Insert the specific report systemName, templateInstanceId, or templateName along with the &<field Name> that is to be supplied by the parent report.
To identify the system name or ID of an existing report, generate that report and in the active browser window type:
CTRL+ALT+T
Two types of names are displayed:
System Name (systemName): For factory-shipped reports that cannot be customized, use this name for the displayReportName.
Dynamic Template ID (templateInstanceId): For Report Templates created with the Dynamic Template Designer, use this number in place of displayReportName.
Note that for a Report Template that was create with the SQL Template Designer, CTRL+ALT+T does not display the templateName. A Template Name must be configured, as described in Advanced SQL Report Template Option - SQL Template Name for Drilldowns.
=${row[‘<field_name>’]}.data
The syntax required for the field name specification.
4. Click OK in the drill down window.
Advanced SQL Report Template Option - SQL Template Name for Drilldowns
Report Templates that have been created with the SQL Template Designer can be configured to be the target of a drill down. In this example, a Report Template is created with the SQL Template Designer to serve as the parent report. Another Report Template is then created with the SQL Template Designer to be the drill down details.
1. Using the SQL Template Designer, create a parent report that includes a query that lists a group of hosts, using the apt_v_server published view (example query: select * from apt_v_server). For the purpose of this example, we’ll call this the Parent Report.
2. Using the SQL Template Designer, create a second report that will be the Drilldown Report. For the purpose of a simplified example, you can use the same query that you used for the Parent Report.
3. Customize the Drilldown Report and before saving it, click Advanced at the top right of the SQL Template Designer window, as shown below.
4. In the Advanced window, enter a template name, for this example: Drilldown_Report, that will be used to configure the drill down in the Parent Report:
5. Customize the Parent Report and in the Formatting tab, select the hostname field and click the Advanced button at the bottom of the SQL Template Designer window. See also, Advanced SQL Report Template Option - Drilldowns.
Enter the following syntax in the Drilldown field and click OK before saving the template: templateName=Drilldown_Report&hostname=${row['hostname']}.data
Advanced SQL Report Template Option - Mouse-over Hover
Custom Bar Charts, created with the SQL Template Designer, can be configured to have a mouse-over on the bars to display the charted values.
1. From the Portal Menu Bar, select Tools > Templates > SQL Template Designer to configure the mouse-over option.
2. Click the Formatting tab in the SQL Template Designer window.
3. Select the field that will become the displayed as a bar in the chart.
4. Click Advanced to launch the Drilldown and Hover window where you will enter the syntax required for the mouse-over option.
5. In the Hover input box, enter the syntax for the value.
The fields from the query can be accessed using the ${} format to access the row context variable.
EXAMPLE
${row['field_name'].data} will supply the field_name value for the mouse-over.
Edit a Custom Report Template
Once you have saved a custom-designed report template, you can return to it to edit its configuration. For example, you can modify the SQL query or share it with additional users.
To edit a custom report template:
1. Select the report template in the Reports tab.
2. Click Customize on the action bar. The relevant Template Designer is displayed to edit the template.
3. Refer to these sections for details about the various designers: Working with the Dynamic Template Designer and Working with the SQL Template Designer.