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
dsClassicCarsdata source. - Create a new dataset called
employeeSalesusing the following query:select CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER, CLASSICMODELS.EMPLOYEES.LASTNAME || ', ' || CLASSICMODELS.EMPLOYEES.FIRSTNAME name, sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH) sales, rtrim(char(year(CLASSICMODELS.ORDERS.ORDERDATE))) || '-' || rtrim(char(month(CLASSICMODELS.ORDERS.ORDERDATE))) orderDate from CLASSICMODELS.EMPLOYEES, CLASSICMODELS.CUSTOMERS, CLASSICMODELS.ORDERS, CLASSICMODELS.ORDERDETAILS where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER and CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER = CLASSICMODELS.CUSTOMERS.SALESREPEMPLOYEENUMBER and CLASSICMODELS.ORDERS.CUSTOMERNUMBER = CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER and CLASSICMODELS.ORDERS.ORDERDATE between ? and ? group by year(CLASSICMODELS.ORDERS.ORDERDATE), month(CLASSICMODELS.ORDERS.ORDERDATE), CLASSICMODELS.EMPLOYEES.EMPLOYEENUMBER, CLASSICMODELS.EMPLOYEES.LASTNAME, CLASSICMODELS.EMPLOYEES.FIRSTNAME
- Link the two dataset parameters to the report parameters,
startDateandendDaterespectively.
- Drag the
employeeSalesdataset to the report designer.
- Delete the last two columns from the table.
- Create a group called
GroupByEmployeein the table and set it to group on theEMPLOYEENUMBER.
- Create another group below
GroupByEmployeeand call itgroupByDate. Set it to group on theORDERDATEfield.
- Merge all of the cells in the
orderDatedetails row into one large cell. Be sure to delete any data items that are still present such as theNAMEandEMPLOYEENUMBERitems.
- 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 to PNG.
- 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 Scale button.
- Enter 0 for the Min value and 5000 for the Max value. We have now set up the scale.
- On the Edit Chart tab, under the Legend section, uncheck the Visible checkbox.
- Select Chart Area.
- Click the Interactivity button.
- Select Mouse Click from the Event listbox and select Hyperlink from the Action listbox.
- 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 preceding chapter. - Select the
startDateandendDateparameters under the Parameters drop down, which selects the parameters in the target report. Under Values, enterparams["startDate"].valueandparams["endDate"].value, or whatever names that have been used used for the current report'sstartDateandendDateparameters.
- Under Step 2, select Target Bookmark. From the drop-down list, select
row["EmployeeNumber"]. - 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 BIRT that 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
EMPLOYEENUMBERdata element.
- Drag the
EMPLOYEENUMBERcolumn from the Data Explorer, next to theOrderDatereport 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.
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.
