Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 1

In the next 3 posts I will show you a neat way to display your data with charts. By using drill-down menus we can show multiple data sets - all related in some way - in the same chart, with one data set showing at a time. Download the example file here and try it out yourself.

What you can see in "Dashboard" tab is one chart, with a drill-down button as shown below.

Clink on the button, and a drill-down menu will appear.

This drill-down menu provides the user with multiple options, from showing the sales data on the national level to showing same data on the district level. The chart will redraw automatically each time a different option item is clicked. Furthermore, headers in Range C1 as well as the textbox right above the chart will also change accordingly.

I see at least two big advantages to utilize such a drill-down menu:

1), The data is being presented in a way that is highly organized. By designing your own drill-down menus you can group related data sets together, making your data logically structured and easy to follow.

2), By using one chart area for multiple data sets you also save space, and your report will look so much more clean and usable.

I will only show you the key sections of Macro in my posts and you can always refer to the Macro in its entirety in the example file you just downloaded.

Print Reports into PDF Files

Let's do something fun: print all our territory-level call panel worksheets into PDF files. It has become more and more popular among biotech managers to distribute their reports in PDF format to avoid any formatting discrepancies as well as potential data tempering. This is suitable for any reports that do not require user-interaction, such as IC Payout Scorecard, Goal Attainment Scorecard, etc.

Before we can programatically do this, we need to install a free software called "PDFCreator" on the report creator's machine (not necessary on end-users' computers). The download link is here. After proper installation, go to "VBA Editor" in Excel, choose "Tools" -> "References", and select "PDFCreator", click "OK". This allows VBA access to all the properties and methods of PDFCreator library.

The code to print all 99 territory-level workbooks into PDF files is shown below. Note that for demonstration purpose we will only print out the first two pages for each file. You can download the example file here.

Change the "mkdPDFName" and "mkdPDFPath" variables to your specifications. If you would like to print out all pages (not just the first two), remove "From" and "To" parameters in the above "ActiveSheet.PrintOut" line.

Combining what we have done in this post and the past one, we can easily print out Excel worksheets as PDF files, then immediately email them to various recipients via Outlook.