Build an Excel Dashboard Report with Monarch Data

Worldwide Quantity Shipments

Recently, I looked at how Monarch builds range names when exporting to Excel, and then some examples of how we can use those range names in Excel functions. Today we’ll look at the process of having Monarch create an Excel dashboard report workbook and how we can begin to easily add dramatic functionality to that workbook.

1. Build the required summaries

Again, for this demonstration I’m using the supplied sample reports as the data source (with permission from Datawatch). I have a few “secrets” for building summaries that will be used for the purpose of building Excel dashboard reports, including:

• Don’t use subtotals for the key fields. They’re not necessary for the purpose of developing dashboard style reporting.
• Don’t suppress duplicate key field values. For many reasons, we want each row to behave like a regular database record, with values in each key field.
• Limit the summary to very few measure values. Often one measure field is sufficient.
• Limit the number of key fields whenever possible. We’re after a simple, easy to query layout and multiple key fields adds to the overall size of the Excel file unnecessarily, and excessive or redundant data will negatively impact the speed and performance of the file.
• Never set a summary to use the “default filter”. You want to be absolutely certain of the data that you’re funnelling into the Excel file, and remember that Monarch will use the currently active filter in the Table as the “default filter” for that summary.
• Include grand totals only when necessary. Further, always use the same wording for the grand totals. By using the same wording, one Excel formula will be able to extract the total amounts for any summary.
• Name the summaries as descriptively as possible. You’ll probably have quite a few of them and it helps immensely to know the content just by the name. Short names are convenient to work with, but have limited usefulness.
• Build one field summaries. Summaries consisting of only a single key field, while seemingly simplistic and of no analytic use, are absolutely critical to building useful dashboard report systems.

Worldwide Shipping Revenues

Customer Shipments

Customer Revenues

Media List

Customer List

Country List

Top 10 Customer Revenues

Top Revenue Products

Top Revenue Products by Country

2. Create the Excel Workbook

Since this will likely be a process that we’ll repeat on a regular basis to update the file with current data, we’ll create a project export in Monarch. For all of the specific Monarch work here I’ll be referencing Monarch v10, though the process will be similar if not identical for all versions.

Define a new project export. Name it All Summaries, and choose to export summaries. From the Summary Name list, select the “All summaries” item. Elect to use Automatic Naming by tables, not files. Do not apply any additional filtering to the export process at this stage.

Now this next part of the project export definition process is really important: supply a file name using the “.xlsm” extension, not the “.xlsx” extension. This is because you’re going to add one single critical line of Visual Basic program code to the file (more if you’re up to it) and you cannot add that to an “.xlsx” file. I’ll supply that code later.

Typically when we export from Monarch to Excel files, especially reused files, it’s very common to overwrite the previously exported Excel file with a new export, but we don’t want to do that when developing dashboard report files. Instead indicate that you want to add data to the file. It’s only when specifying what you want Monarch to do with the individual tables that get created in the Excel file that you want to select the overwrite option.

Finally, to create the fastest, most responsive Excel file, do not add any further features to the exported tables such as titles, outlines, or formulas.

Run the project export to create your new dashboard report Excel file.

3. Add devices to facilitate dashboard report development.

First up is a little bit of housekeeping that is required due to a bit of a bug in Monarch Excel file creation process. When Excel files that Monarch updates are opened, Excel often requires that you force a manual recalculation. You can doing this simply by pressing the F9 function key, but it’s easy to overlook and that can cause problems. So we’ll add a line of program code to have Excel recalculate the workbook immediately when you open it.

In Excel, hold down Alt and hit F11. This will open Excel’s development window. You should see the current file listed sheet name by sheet name in the left side of the window in a Project window. Double click the item for the ThisWorkbook item. Now at the top of the window, you’ll see a drop down list with a (General) label. Select the Workbook item from this list. Note that Excel automatically creates a new subroutine for you:

To that add a single line of code:

Excel Workbook Open Event

Close the programming window and return to your Excel workbook.

For our dashboard report of the Classical Music Distributors data, we want to be able to spotlight data for activity in specific countries, media types, and customers. To do that, we’ll add drop down lists for each of those data items to the dashboard. The values for each of those lists will be automatically populated by our Monarch exports, but the lists need a little help to get exactly the right data into them. Let’s tackle the countries item first.

Go to the Country_List sheet, and define a new range name. Name the range “Countries”, and assign it the formula “=OFFSET(Country_List!$A$2,0,0,COUNTA(Country_List!$A:$A),1)”. With this formula, “Countries” will refer to every country name in the list, avoiding the field name in the first row. Because we often want the values in the heading row, we don’t have the luxury of exporting not including the field names in only selected sheets, but using this approach it’s easy to skip the heading.

Use the same approach to create range names for “Customers” (“=OFFSET(Customer_List!$A$2,0,0,COUNTA(Customer_List!$A:$A),1)”) and “Media” (“=OFFSET(Media_List!$A$2,0,0,COUNTA(Media_List!$A:$A),1)”).

Now we can add a new blank worksheet into the file. Rename the sheet to something more descriptive.

To quickly see how our new range names are behaving, add a data validation rule to a cell. Select “List” from the Allow list, and set the Source box to =Customers. Note that you must include the equals sign otherwise you’ll see only the text “Customers” in your drop down list. While this works well, there is a drawback to using the data validation list approach in that the font size of the list items can get a little small and hard to read depending upon the screen resolution. There’s a better mechanism available, but we’ll save that for later.

The attentive will have noticed that our range names are each returning one extra row with a blank value. This will become apparent later when we look at how the selected values are used to display information in the dashboard.

We’ve made some good progress on a dashboard reporting system today. To learn more details of how to build these types of reporting systems, be sure to pick up a copy of “Dashboard Reporting with Excel”. I’m positive that as you read it you’ll envision plenty of new ways to excel with Monarch.