With the next chart, the guage chart, we will expand on the interactivity a bit and
demonstrate how drill-downs work. In addition to viewing the static chart, we want
the user to be able to click on the chart and have it pull up an external report with the
details for a particular user. This is called a drilldown.
The following meter chart will demonstrate an employees sales vs. a target amount
per month, let’s say 3000, on a 5000 dollar scale. This will be broken out and grouped
monthly in the query statement. When the user clicks on the chart, it will bring them
to the detail report we created last chapter for Employee sales.
- Create a new report titled Employee_Sales_Guage.rptDesign.
- From the library, drag over the dsClassicCars data source.
- Create a new dataset called employeeSales using the following query:
- Link the two dataset parameters to the report parameters, startDate and
- Drag the employeeSales dataset to the report designer.
- Delete the last two columns from the table.
- Create a group called GroupByEmployee in the table and set it to group on
- Create another group below GroupByEmployee and call it groupByDate. Set
it to group on the ORDERDATE field.
- Merge all of the cells in the orderDate details row into one large cell. Be
sure to delete any data items that are still present such as the NAME and
- In the new large cell, create a chart element.
- Select Meter as the type, and select SuperImpose Meter as the chart type. If
we need to know which type of chart we are picking, hover the mouse over
the chart for a second and its description will pop up. Set the Output Format
- In the Select Data tab, create another dial by selecting the Meter Value
Definition drop-down list and selecting <New Series..>.
- For the expression for Dial 1, select row["SALES"] by either dragging the
column over from the Data Preview pane or by using the Expression Editor.
- The value for Dial 2 should be set to 3000.
- As Category Definition, use row["NAME"] by either dragging the column
over from the Data Preview section or using the Expression Editor.
- In the Format Chart tab, under the Value Series section, click the
- Enter 0 for the Min value and 5000 for the Max value. We have now set up
- On the Edit Chart tab, under the Legend section, uncheck the
- Select Chart Area.
- Click the Interactivity button.
- Select Mouse Click from the Event listbox and select Hyperlink from the
- Click the Add Button.
- In the Name field, insert NewHyperlink.
- Click the Edit Base URL… button.
- Select Drill-through as the hyperlink type.
- For the Report Design, navigate and select the report Employee Sales
Details Report.rptDesign. This report was created in the
- Select the startDate and endDate parameters under the Parameters drop
down, which selects the parameters in the target report. Under Values, enter
params["startDate"].value and params["endDate"].value, or whatever
names that have been used used for the current report’s startDate and
- Under Step 2, select Target Bookmark. From the drop-down list, select
- Under Step 4, select New Window.
- We can skip Step 5 as we are sticking to our default output format, which is
HTML. We could have the target report open in any format that we have an
emitter registered for. Emitters are plugins for BIRTthat produce output in
different formats such as HTML, PDF, Microsoft Excel, or Microsoft Word.
- Click OK to exit the dialog and save the report.
- Delete the table header.
- Delete the EMPLOYEENUMBER data element.
- Drag the EMPLOYEENUMBER column from the Data Explorer, next to the
OrderDate report item.
- Now run the report. When we click on any of the charts, a detailed report
will open and the control will automatically jump to the employees section,
as specified in the Bookmark in the target report.
CLASSICMODELS.EMPLOYEES.LASTNAME || ‘, ‘ || CLASSICMODELS.
rtrim(char(year(CLASSICMODELS.ORDERS.ORDERDATE))) || ‘-’ ||
CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.
and CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER = CLASSICMODELS.
and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS.
and CLASSICMODELS.ORDERS.ORDERDATE between ? and ?
Of course, this example can be heavily modified to simplify things. The charts could be
consolidated into single chart, with more needles for each month instead of separate
charts for each month, by moving the chart into the EmployeeNumber group’s header
or footer row and adding a group by date in the chart editors dialog under the Select
Data tab. It is also possible to make the needles interactive and to filter down to a
specific user and date range by modifying the bookmarks in the target report, but I
will leave that up to the reader to discover. The following is a modified version of the
the report that has created different series in the chart itself for the date groupings.
Latest posts by Krishna Srinivasan (see all)
- 5 New Features in HTML5 - November 29, 2013
- How To Set Tomcat JVM Heap Size in Eclipse? - November 28, 2013
- How To Resolve “Resource Is Out Of Sync With The Filesystem” Error in Eclipse? - November 28, 2013