Drill down on chart

This user manual defines how to set and use drill down on chart.

Case summary Department head would like to analyse sales value by receiver, department and document type. In this section he would like to see what was the sales value within the individual receiver by department and document type. To preform such an analysis, he has to prepare a component SQL query in a specific way. Dashboard component preparation Use of drill down on chart

1. Dashboard component preparation

To prepare such an analysis, SQL query has to contain list of parameters and has to end wit a value.

e.g.:

select acReceiver as Receiver, M.acDept as Department, SDT.acName as DocType, SUM(M.anValue) as Value from tHE_Move M JOIN tPA_SetDocType SDT on M.acDocType = SDT.acDocType where SDT.acDocType like '32%' group by M.acReceiver, M.acDept, SDT.acName  

where Receiver , Department and DocType are parameters and Value is numberic value at the end.  When you are structuring a SQL query like this, link data has to be established between first parameter and value (if horizontal chart type is selected) and checkbox Enable drill down has to be selected.  

Hint If you are using more sophicticated SQL query, you should structure it in a subquery and add simplified verstion on top to extract data.

2. Use of drill down on chart

When we add such a chart on dashboard, data will be sumarized by first parameter, in our case by Receiver.

By clicking on the individual column on chart (e.g. "End customer"), chart will show next layer with sales value by department for receiver End customer.

By clicking again on the individual column on chart (e.g. "Retail"), chart will show next layer with sales value by document type for receiver End customer and department Retail.

By clicking on the individual column of the final parameter (e.g. "POS retail"), chart will show the value of sales for receiver End customer, department Retail and document type POS retail.​

Hint SQL query can be structured with unlimited number of layers where drill down always follow the order of parameters in SQL query. If our sales manager would like to see the analysis first by department and then receiver, he would have to change an order in SQL query on dashboard component.

When the second (or any additional layer) is clicked a back button  appears on the chart. Click on this button will return the chart to the beginning (not the previous layer) and drill down has to be preformed from the beginning.