Like with reports, it is also possible to add layers of drill-downs to charts. All of the drill-downs added will be applied to both the data points on the plot area and their respective fields in the legend. Using chart drill-down, you can create a top-level chart that displays summarized data and allow users to click through specific data points to see underlying details.
The charting engine in EspressReport contains several different built-in drill-down mechanisms that allow you to create only one chart template for each level of drill-down. All of the drill-down options are available from the Drill-Down menu in Chart Designer.
It is important to note that you can only use drill-downs in charts when the chart is deployed independently from the report (either in the Chart Viewer, in its own servlet, etc). If a chart template that includes drill-down is placed in a report, only the top-level chart will display. Users will not be able to click through to the lower-level charts.
Data drill-down allows you to group and display information that is based on a single data source. The advantage to this form of drill-down is that it works with any data source. However, it does not allow you to display loosely related information because all levels of drill-down will share the same value column from the input data.
For example, assume you have the following table as the chart data:
| Category | Product | Sales |
|---|---|---|
| Chairs | Elm Arm Chair | $8,216 |
| Chairs | Pine Side Chair | $7,611 |
| Chairs | Redwood Arm Chair | $8,625 |
| Tables | Elm Round Table | $10,241 |
| Tables | Pine Oval Table | $9,663 |
| Tables | Oak Oval Table | $11,261 |
| Dressers | Oak Single Dresser | $16,442 |
| Dressers | Elm Double Dresser | $17,148 |
Using this data, you could create a top-level chart that displays total sales for each distinct product category and then create a lower-level chart that shows the individual sales for each product in a category. The number of drill-down levels available depends on the number of groupings that are present in the input data.
To add layers of data-drill down to a chart, you must first create the top-level chart. In the above example, we would create a chart with “Category” mapped to the category axis and “Sales” mapped to the value axis. In a column chart, it would look something like this:
Top-Level Chart
To add a layer of drill-down, select → . This will bring up a dialog prompting you to specify the aggregation that you would like to use for the value axis. For example, selecting sum will display the total for each category in the top-level chart. Available aggregate functions are sum, minimum, maximum, average, and count.
Aggregation Dialog
After you select the aggregation that you would like to use, click . A new dialog will appear prompting you to specify a name for the drill-down chart. This name will be assigned to the chart templates that are created by the drill-down process. Note that all sub-level drill down templates are saved in the /drilltemplates/ directory.
Drill-Down Name Dialog
Once you have specified the name that you would like to use, click . You will then be prompted to specify the chart type and data mapping for the sub-level chart.
Data-Drill Down Mapping Dialog
The options in the dialog are similar to those for normal data mapping. The major difference is that the first option allows you to select a chart type. Available chart types for data-drill down are column, bar, line, stack column, stack bar, pie, area, overlay, radar, and dial. The data mapping options will change depending on the type of chart that you select.
Once you have finished specifying the mapping options, click 'Done' and you will go back to the Chart Designer where you can customize and modify your sub-level chart. You can add additional layers of drill down by selecting → again.
You can navigate to a particular drill down chart by selecting → or → or by double clicking a data point. The Previous selection takes you up a level, while the Next selection goes down a level using the left-most category as the data to be drilled on. You can also go from any drill-down chart to the top-level chart by selecting → . You can customize (i.e. assign colors, add title, axis labels, background image, ...etc.) the drill-down chart in the same manner as the top-level chart. Everytime you change and leave a level, you will be prompted to save the chart. Make sure that you answer if you want the attributes of the drill-down level to be saved. Otherwise, your changes for that level will be lost.
You can insert a drill-down chart between two other drill-down charts by going to the higher level drill-down chart and selecting → . The drill-down chart that you create will be inserted between the two previous drill-down charts.
You can remove a level of the drill-down by navigating to that level and selecting → . The entire drill down chart can also be deleted by selecting → . Selecting → brings the drill-down chart to a higher level, which is the same as right clicking and selecting Back from the pop-up menu. The Next selection brings the drill-down chart to a lower level, which has the same function as double clicking on an item in the chart.
Once you have finished creating and editing all the levels of the drill-down chart, you can save it by navigating to the top-level chart and selecting → or → .
Normally with data drill-down, the mapping and drill options are fixed at design time. Dynamic drill-down is an additional option that allows the end user to select the mapping. All that is specified at design time is the top-level chart and the aggregation.
To create a chart with dynamic drilldown, first create the chart that you want to use as the top-level chart (for example, you can create the same top-level chart as before). Next, select → . A dialog will appear allowing you to enable dynamic drill-down.
Enable Dynamic Drill-Down Dialog
Check the Enable Dynamic Drill Down box and a new dialog will surface prompting you to select the aggregation.
Aggregation Dialog
Options for this dialog are the same as for regular data drill-down. Once you have specified the aggregate you would like to use, click . Another dialog will surface and allow you to specify a template to use for the sub-level charts.
Select Dynamic Drill-Down Template Dialog
If you do not select to use a template then the sub-level charts will be generated using default appearance properties. After you have finished selecting these options, your chart will change to display aggregated data on the value axis. You can change the option by selecting → again and then clicking on the button.
Dynamic drill-down charts can only be viewed in the Chart Viewer applet. In the Chart Viewer, you can configure the next drill-down chart setting by right clicking the desktop area to bring up the pop-up menu. If dynamic drill-down is enabled and the chart still has some unused fields (columns) to plot, the item Drill Down will be displayed in the pop-up menu. Upon selecting Drill Down in the pop-up menu, you can view the current setting for next drill-down. If Category is None it means you have not configured the next drill-down chart yet. To configure the drill-down chart for next level, you must select Category. (Type, Series and SumBy can be selected once Category has been set). After you created a drill-down chart, you can navigate a drill-down chart in the Chart Viewer by left clicking on a data point to go down a level, by right clicking and selecting Back from the pop-up menu to go up a level, or by repeating the previous step to create another drill-down chart for next level.
![[Tip]](../../../images/tip.png) | Tip |
|---|---|
After traversing to a lower-level of drill-down, right clicking on a data point will navigate you back to the top-level chart. To bring up the pop-up menu, right click on the chart canvas away from the chart data points. |
Dynamic Data Drill-Down in Chart Viewer
The third type of drill-down that you can do with charts is parameter drill-down. Parameter drill-down is the most flexible implementation because you can relate the data between the drill-down levels any way you like. Specifically, you do not need to use the same value element for each level. Rather, parameter drill-down uses the parameterized query feature to relate the various chart levels. Because parameter drill-down uses queries, the data source for the sub-level charts must be a database or parameterized class file.
For example, using our previous scenario, say rather than always looking at sales, you wanted your top level chart to look at aggregated sales by category (same as before), but then on the next level, you wanted to look at sales volume for each product. And from there, you wanted to look at inventory levels for each product by region. This can be accomplished with parameter drill-down.
With parameter drill-down, the category value of the element you click to drill on will be passed to the sub-level chart as the parameter value. Hence, if you click on the “Chairs” column, the value of “Chairs” would be passed to the query. Therefore, anything in the database that could be filtered by category name could be retrieved.
To add and edit the various layers of parameter drill down, select → . This will bring up a navigation window that shows the various levels of drill-down.
Parameter Drill-Down Navigation Window
The left-hand side of the navigation window displays the hierarchy of drill down levels. The “ROOT” node is the top-level chart. The level that you are currently editing is marked with **. To edit a different level, select it and click the button on the right-hand side. That chart will then open in the Designer allowing it to be modified. Levels of drill-down can also be removed by selecting the node in the Navigation window and clicking the button.
To add a new level of drill-down, select the chart under which you would like to add the layer (if you have only the top-level chart then only the “ROOT” node will be visible), and click . You will then be prompted to create a new chart or to use an existing chart for the drill-down layer. You can use any existing chart; however, any chart for a drill-down layer must have a parameterized query as the data source. If you select to create a new chart, the Data Source Manager will re-open, allowing you to select a data source for the chart and to continue through the Chart Wizard steps.
The next step is to map the category and/or series columns from the top-level chart to the query parameter(s) in the sub-level chart. You will be prompted to do this when you select an existing chart for the drill-down layer or when you select the data source for a new chart for the drill-down layer. In either case, a dialog will appear prompting you to map the fields.
Parameter Mapping Window
The options in that are available in the drop-down menus are based on data type. For example, if your parameterized query takes a string as a parameter, only fields containing string data can be mapped. Hence, it is important to consider what type of data will be passed from the top-level chart to the lower-level charts because if your category or series are not of the correct data type or if you do not have enough fields to pass to the lower-levels, the drill down will not work correctly.
Once you have correctly specified the parameter mapping, you will be prompted to specify a display name for the drill down level. After a name is selected, the sub-level chart will appear in the Designer allowing it to be customized. You can navigate through the layers of drill-down using the Navigation window, or like data drill down, you can double click on a data point to go down a level and right click and select Back from the pop-up menu to go up a level.