Getting Down to the Nitty-Gritty

On my daily commute, after one of Michigan’s most brutal winters, I have the opportunity to encounter multiple pot holes. Those nasty, alignment-altering holes that go deeper and deeper from the surface until they get to the nitty-gritty layer that was once the original surface.

Drilldowns in a dashboard work much in the same way. In a drilldown, you are moving from one layer to another, ultimately working your way down to the nitty-gritty information you are looking for.

In creating a successful drilldown, there are a number of things to consider.

Start with the source chart for your drilldown. Let’s say we start with a column chart showing sales figures tallied by country. When designing the drilldown target, which will show Sales by City for the selected Country on the source chart, I will start with the original source chart side-by-side, because I will use many components of the source chart for the drilldown target.

In what will be the target chart, I right click, choose Chart Data and Cut and Paste my original x-axis as a new y-Axis label. I will use this label copied to the clipboard again shortly. I now put in my new x=axis label of City, as shown in Image 1.

 

With the new x-Axis, it is a quick trip through my data source connections (assuming we’re working from one data source) to the point where I now map my new x-axis to City and my newly created y-Axis to Country. I also now create a pivot on Country as shown in Image 2.

 

 

Once the target chart is created I like to change the chart to differentiate it from the source to give the visual appearance of change. This chart will ultimately be a chart filtered by City for the selection made at the Country level.

 


At this point I save the new chart by right clicking and choosing Save Chart As to choose the new chart name. I save the chart with a Sales by City name, and make the chart title Sales by City from ${value:Country} (this is where I can paste the filtering selection from my original cutting of the x-axis label from earlier). That label from the pivoted y-axis needs to match the Value Macro in order for it to work in the title of the target chart.

The final step in creating the filtered drilldown is to go into the Chart Properties on the source chart and choose the Drilldown tab and the Drilldown to Chart option from that tab. See Image 4.

 

 

Note the simplicity of creating a drilldown. The application understands which axis values need to be sent to the target chart automatically. The x-axis of Country is automatically selected when the drilldown is chosen.

Now, when the chart is saved and the drilldown selection is made, the target chart appears and is filtered for the selection that is made (Image 5). Also, note that the Country label is shown in the title, so we know which country we selected by looking at the filtered chart.

 

 

At this point we can do one more level of filtering to get to our granular details of knowing how many units have been sold per city (the nitty-gritty details!).

 

 

We see that again in the chart title, we’ve pulled in the selected values for both Country and City. This has been accomplished by using the following syntax in the chart title – Units Sold  – ${value:Country}, ${value:City}.

It’s no surprise that drilldowns are invaluable in the world of iDashboards. This was after all only one of the many type of drilldowns available. It’s a matter of how you want to show your filtered metric. We could have just as easily set up a drilldown to a dashboard, to a report, or to a website. It all depends on how you wish to see the nitty-gritty details of your data.

Jerry Stowe -Senior Technical Consultant

Leave a Reply

Your email address will not be published. Required fields are marked *