Home > Dashboard Digest > Setup > 

IncSight DB Appendix:

How to Link Your Excel Dashboard
Report to PivotTables & Data Tables

Your Excel dashboard reports can be live-linked to data in your data warehouse, to
Access, or to any other data that you can explore with PivotTables. Here's how.

by Charley Kyd
February, 2009

One of the advantages to linking an Excel dashboard report to an Excel database is that you can link your database, indirectly, to your data warehouse. This means that your Excel dashboard can be linked, indirectly, to your data warehouse.

Excel provides at least three ways to do this: You can use PivotTables; you can use Data Tables; and you can manually import your data using CSV files. The figure provides an overview of the process. (The dashed line for the CSV Data indicates that it isn't returned by a live link.)

As this figure illustrates, three steps are required to use this approach.

Step 1 is to create a data table or a PivotTable linked to your corporate data. You also could import a CSV file manually. You'll have to set up these tables on your own, because I don't have access to your data and I haven't worked with relational databases in years. (I used to consult about OLAP databases, which is a different technology.)

Step 3 is to use formulas in your Excel dashboard to return data from your Excel database. Plug-N-Play #2 already provides the Excel formulas to do this. And Dashboard Reporting With Excel explains these formulas in detail.

Step 2 is the missing link in this process. You'll need to write formulas in your Excel database to return data from your tables shown for Step 1. This article explains the process.

Fair Warning

I warn you, this process isn't for beginners. First, you'll need to set up your PivotTables or your data tables on your own. Or generate your own CSV files. I won't be able to help you with this step, so please don't ask. (Your IT department might be able to help with this step.)

Second, I won't take the time in this article to explain every new concept. But this shouldn't be a problem for Excel users who no longer are beginners.

(I'll probably offer video training later this year. Some videos will explain Excel dashboards in detail. Others will explain the following material in greater depth. Please let me know if you might be interested in a video series like this. I'm trying to get an idea of what the attendance might be.)

Third, you'll probably need to adapt these instructions to the pivot and data tables that you set up. Your tables will look somewhat different from mine.

Of course, if you don't want to learn quite so much about the Excel formulas I describe below, you still can use PNP #2 with a stand-alone Excel database. The PNP #2 setup instructions explain how to do it.

Getting Started

If you're familiar with PivotTables, you probably know something about the GETPIVOTDATA spreadsheet function. It's the only function specifically provided to return data from a PivotTable to your spreadsheet formulas. I won't use that function here, for two reasons.

First, I cover it in Dashboard Reporting With Excel, and there's no need to repeat that material. Second, Excel's SUMPRODUCT function is more powerful in many ways, and it can work with all three sources of data shown in the figure. So that's the function I'll explain here.

All the examples below are from one workbook that I created in Excel 2007 using data from Access 2007's Northwind database. The same techniques will work with earlier versions of Excel.

The workbook I discuss below has five worksheets: Control, DataTable, PivotTable, DataTableDB, and PivotTableDB.

Introducing Dynamic Range Names and the OFFSET Function

To use SUMPRODUCT reliably, you need to set up several dynamic range names. These names are called "dynamic" because they change shape automatically. We need this feature because data tables and PivotTables usually change shape as we change our queries, and our range names need to adapt to the new shapes automatically.

To get started with dynamic range names, first consider standard range names. The definitions of standard Excel names use simple cell references. For example, we could define the name Foo like this:

=$A$1:$C$2

However, range names also can define formulas that return cell references. So we also could define Foo like this:

=OFFSET($A$1,0,0,2,3)

Both of these definitions return a reference to the same range, A1:C2.

The advantage to using OFFSET in Foo's name definition is that we can replace the hard-coded numbers in the OFFSET formula with cell references or range names. That way, as those values change, the range that Foo defines can automatically expand and contract in response. That's what dynamic range names are all about.

Most of the time, you'll use OFFSET to create a dynamic range name. Here are it's arguments:

=OFFSET(reference, rows, columns, height, width)

That is, OFFSET returns a range reference that starts at a given reference, is offset by a certain number of rows and columns, and that has the specified height in rows and width in columns.

It usually turns out that the key to creating a good dynamic range name is to generate accurate values for its height and width.

But before we do that, we need to talk about "scope".

The Control Sheet and the Scope of Range Names

All Excel range names have a "scope". Most range names are defined with a workbook scope. This means that the name has the same meaning in every worksheet in the workbook.

However, range names also can have a worksheet scope. That means that when a name is used in one worksheet it can have one meaning and when it's used in another worksheet in the same workbook, it can have a different meaning.

In this workbook we're going to use names with a worksheet scope. This will let us use similar formulas in different worksheets to refer to different data.

When you use range names with a worksheet scope, it's always a good idea first to define the same names with a global scope as I've done in the Control sheet, shown here.  

These Reserved Names are the ones we'll define below. We begin by defining each name with a workbook scope to refer to the cell at its right, and then we enter =NA() in those cells. By using this approach, we know immediately if we accidentally refer to the global version rather than to the local version that we need. We know, of course, because our formulas return #N/A.

Assuming that the names haven't been defined anywhere else in your workbook, it's easy to define them globally. Just select the range A3:B11, then...

• in Classic Excel, choose Insert, Name, Create...
• in New Excel, choose Formulas, Defined Names, Create From Selection...

Then, in the Create Names dialog, ensure that only Left Column is checked.

Generating Height and Width Values

Data tables and CSV files both look about like the top of the data table shown here, except that CSV files aren't quite so pretty and don't have the controls shown in row 2. So if you're using CSV files, use the following approach that I describe for data tables.

Here's the table I created in the DataTable worksheet. You would begin the process to create this table in Classic Excel by choosing Data, Import External Data, New Database Query. In New Excel, use Data, Get External Data.

Once you set up this table, enter the formula in the cell shown, which counts the number of rows in the table:

C1:  =COUNT(B:B)

The COUNT function returns the count of all numeric values in the column, hidden or not. Here, COUNT is returning the sum of all dates in column B. (Date serial numbers are values, remember.)

Using the COUNT function does not allow you to vary your results by filtering the data table. This is intentional, because we will be using the SUMPRODUCT function to return values from the table, and, by default, SUMPRODUCT ignores filtering.

(It is possible for SUMPRODUCT to return data only from non-filtered rows, but doing so is beyond the scope of this article.)

Once you've entered the formula in cell C1, select the range B1:C1, and use the Create Names dialog to define the NumRows range name. (If you've already defined the workbook-scoped version of NumRows, the Create Names command automatically defines a worksheet-scoped version.)

Now let's find the number of rows and columns for the PivotTable, which resides in the PivotTable worksheet.

Notice that this PivotTable has a very simple format, with no subtotals. The following method becomes needlessly complex if your PivotTable does include subtotals, or if it has more than one field in its rows or columns.

Also, for this figure only, I'm showing only the data for January, which allows you to see a complete table. For the remainder of this article, I've used all six months of dates that the sample data provides.

Because PivotTables can change height and width, we need to calculate both values.

To find the number of rows of data, we use the COUNTA function. This function counts both text and numbers. To get the actual number of data rows, we count the total number of rows of text in column A, then subtract 5. We subtract 4 to account for the labels in cells A1 through A4, and we subtract 1 to account for the "Grand Total" label in cell A12.

B1:  =COUNTA(A:A)-5

To find the number of columns of data, we use the COUNT function to count the number of numeric values in row 4. (Date serial numbers, again, are numeric values.)

B2:  =COUNT(4:4)

To complete this step, select the range A1:B2 and use the Create Names dialog to assign the two names.

Create the Dynamic Range Names

Now that we have the heights and widths we need, and we've set up their range names, we can define our dynamic range names. Let's start with the table in the DataTable worksheet.

We need to define dynamic names for all three columns. To begin, define the three standard names shown in the first table below. When you do so, make sure you define them scoped to the DataTable worksheet. To do so in New Excel, specify the scope using the Scope list box in the New Name dialog box.

To do so in Classic Excel, use the Define Name dialog and specify the scope by entering your range name using this format:

WorksheetName!RangeName

To illustrate, to define ProductName with a worksheet scope for the DataTable worksheet, define the name like this:

DataTable!ProductName

Define these three names using the DataTable scope:
 

ProductName	=Sheet3!$A$2
OrderDate	=Sheet3!$B$2
Amount	=Sheet3!$C$2

Now define the dynamic names shown below. Be sure to define them scoped to the DataTable worksheet.
 

aProductName	=OFFSET(DataTable!ProductName,1,0,DataTable!NumRows,1)
aOrderDate	=OFFSET(DataTable!OrderDate,1,0,DataTable!NumRows,1)
aAmount	=OFFSET(DataTable!Amount,1,0,DataTable!NumRows,1)

Notice my naming convention for these three names, where the dynamic name begins with "a". This groups dynamic names together near the top of the list of names, which helps you quickly to find dynamic names when you need them. (Here, "a" stands for "array".)

To see how these dynamic names work, take a look at aAmount, which references the table shown here.

Here's the general OFFSET formula again:

=OFFSET(reference, rows, columns, height, width)

Although aAmount uses the reference defined by DataTable!Amount (cell C2 above), it returns a reference that begins in cell C3, which is 1 row below cell C2 and zero columns in either direction. The height of its reference is defined by DataTable!NumRows. And here, the width of the reference is 1 column.
 

Now let's define the names in the PivotTable worksheet.

To begin, define the standard name shown below. Here, make sure to define it scoped to the PivotTable worksheet:
 

TopLeftCell

=Sheet1!$A$4

Now define these three names scoped to the PivotTable worksheet:
 

aProductName	=OFFSET(PivotTable!TopLeftCell,1,0,PivotTable!NumRows,1)
aOrderDate	=OFFSET(PivotTable!TopLeftCell,0,1,1,PivotTable!NumCols)
aAmount	=OFFSET(PivotTable!TopLeftCell,1,1,PivotTable!NumRows,PivotTable!NumCols)

To test any of these dynamic names, press the F5 function key, which launches the Go To dialog. Type in any of the dynamic names, choose OK, and Excel should select the appropriate range. (You'll notice that the Go To dialog doesn't include dynamic names in its list. Perhaps Microsoft will correct this bug in some future version of the product.)

In case you've forgotten after this long introduction, our purpose was to create a way to return data from your tables into your Excel database. We can do that now.

Set Up Your Excel Databases

The top section of the DataTableDB worksheet is shown below. With the exception of the title in cell B1, the Excel database in PivotTableDB is identical to the figure shown here. This is because I set up Data Table and the Pivot Table to use two different methods to return the same data from the same table in the Northwinds database. So the resulting databases display the same values.

These Excel database worksheets use the same format as the one that comes with PNP #2. In that database, however, the data is hard-coded. But here, it's returned by formula.

Enter this formula in the cell shown, and copy it to the remainder of the data area:

F4:  =SUMPRODUCT((DATE(YEAR(DataTable!aOrderDate),MONTH(DataTable!aOrderDate),1)=F$2)*(DataTable!aProductName=$B4)*(DataTable!aAmount))

Similarly, in the PivotTableDB database, enter this formula for the cell shown, and copy it to the remainder of that data area:

F4:  =SUMPRODUCT((DATE(YEAR(PivotTable!aOrderDate),MONTH(PivotTable!aOrderDate),1)=F$2)*(PivotTable!aProductName=$B4)*(PivotTable!aAmount))

These two formulas use the same logic:

1. Calculate the first of the month for each date in aOrderDate and compare it to the corresponding date in row 2 in the figure above. Return an array of TRUE where the dates match and FALSE where they don't.
    
2. Compare each product name in the aProductName range to the corresponding product name in column B. Return an array of TRUE where the products match and FALSE where they don't.
    
3. Get the array of values from the aAmount range.
    
4. Multiply the array of TRUE and FALSE values in (1) by the array of TRUE and FALSE values in (2). This returns an array of 1's and 0's, where the the 1's are found only when the corresponding values in arrays (1) and (2) both are TRUE.
    
5. Multiply this array by the values in (3). This returns an array mostly consisting of zeros, with the occasional non-zero values where the dates and the product names match the cell's requirements.
    
6. Finally, use SUMPRODUCT to return the sum of that final array.

Wrapping Up

Once you've checked that your Excel database has the data you expect, the process is done. Your data warehouse is feeding your data table or your PivotTable (Step 1 in the figure at the top of this page). Your Excel database is summarizing data from your data or pivot tables (Step 2). And your Excel dashboard is using the formulas provided by Plug-N-Play #2 to pull the live data from your Excel database (Step 3).

Enjoy!