Home >  BI for Excel >

An Excel Tutorial:

Create an Accordion Report
In Excel, Using OLAP Data

Budget reports by department, and other variable-length lists,
can take a lot of work to set up in Excel. But an Enterprise Excel
system, and some clever Excel formulas, make these "accordion
reports" a breeze.

by Charley Kyd
December, 2006
(Email Comments)

Budget variance reports typically aren't easy to prepare in Excel, for at least two reasons.

First, each department tends to budget to a unique set of accounts. So updating Excel with each new list of accounts can be a lot of work.

Second, because the departments spend money on different numbers of accounts, departmental variance reports are accordion reports. This is the term I use for reports with variable-length lists that expand and contract like an accordion. (The two figures below show you what I mean.) There's no obvious way to create such lists automatically, so if you create them in Excel they can take a lot of time to generate.

However, these problems go away with a BI system for Excel. Its multidimensional (OLAP) database provides the data; and simple spreadsheet formulas can manage the variable-length lists.

These two figures show what needs to be done.

The top figure, has two items and the bottom figure has four items. The third figure, below, has three items. All figures include subtotals.

I generated these reports easily. All I did was enter a number that specified one of the three groups of items shown in these three figures, and then I recalculated. Excel formulas performed the work. No macros were needed.

This article explains how it's done.

The Header Information

The following figure shows the entire spreadsheet that generates this report. For convenience, I assigned a range name to each cell in the range B1:B8, using the label to the left of each cell as the name.

This report uses data from PowerOLAP. In some ways, it acts much like a PivotTable report. Like a PivotTable, this report uses header information to control what the report displays. Here, rows 1 through 5 contain this information.

The formula that returns the Unit information is simply:

B4:  =OLAPMember(Database, "Unit",7)

(Here, "B4" is the cell address for the formula. The actual formula in cell B4 begins with the "=" sign above.)

OLAPMember is a PowerOLAP function. The function is returning the 7th item from the Unit dimension of the database specified in cell B1 of the figure. To choose another Unit, I could change the index number, if I know which number I want. Alternatively, I could simply double-click on this cell to display a dialog that shows all units. After I choose another unit, I press F9 to see that unit's data in the report.

Cell B6, named ParentGroup, shows the name of the parent for all the groups of items we're reporting. That is, Total Departmental Expenses includes Operating Materials and Supplies, shown here; it also includes Office Furniture and Equipment, shown in the first figure. The formula for this cell is simply:

B6:  =OLAPMember(Database,"Account",62)

Again, I could double-click on this cell to choose another member.

Cell B7 tells me how many groups of accounts are included in Total Departmental Expenses. That is, it tells me how many children that parent has. Its formula is:

B7:  =OLAPChildCount(Database,"Account",ParentGroup)

Cell B8 contains a value that I enter to choose which of the 11 children I want to see. I colored the cell yellow to make it easy for me to find the cell I need to change.

The Accordion Report

Now that you know what the setup information is about, let's see how the magic is performed.

Cell B10 contains the name of the expense group that I specified in cell B8. Its formula is:

B10:  =OLAPChild(Database, "Account", ParentGroup, GroupChoice)

Because of the value in GroupChoice (cell B8), I'm using the third expense group that belongs to the group of expenses specified in cell B6.

Cell A10 gives me the total number of children that belong to the group specified in cell B10:

A10:  =OLAPChildCount(Database, "Account", B10)

This formula is important because it tells me how many line items will be in my accordion report.

Cell A11 contains the value of 1. The formula in cell A12 generates the next value. As you can see from the figure above, this formula counts up from 1 to the number of items in the list, then it drops to zero and counts down from there. I copied it down the column as necessary:

A11:  1
A12:  =IF(A11=A$10,-A11,IF(A11<1,-1,1))+A11

Cells B11 through E11 use several Excel tricks. These tricks are necessary because the formulas needs to do one of three things. If cell A11 is positive, these formulas need to return the account information shown. If cell A11 equals zero, the formulas need to return the Grand Total information shown. And if A11 is negative, the formulas need to return an empty string, which essentially hides the formula.

Here's the formula that returns the General Ledger account number in column B.

B11:  =CHOOSE(SIGN(A11)+2,"","",
           OLAPChild(Database, "Account", B$10,$A11))

Rather than the IF function, I'm using Excel's CHOOSE function. The first argument in this function is the choice number, with values like 1, 2, 3,  etc. The remaining arguments are the items to be chosen. Here, the formula is choosing from three items. The first two items are null strings: "". That is, if cell A11 equals zero or cell A11 is less than zero, this formula returns "".

In the formula above, SIGN is an Excel function that returns +1 if cell A11 is positive; it returns 0 if cell A11 is zero; and it returns -1 if cell A11 is negative. By adding 2 to this value, we get values of 1 for negative numbers, 2 for zero, and three for positive numbers.

In the figure, repeated below, cell A11 happens to be positive. So the formula in cell B11 returns the third item, which is a formula that returns the General Ledger account number. To do so, it uses the OLAPChild function as described for cell B10.

In cell C11, if cell A11 is negative, the formula returns a null string; if cell A11 is zero, the formula returns "Grand Total"; and if cell A11 is positive, the formula returns the text shown.

The text in cell C11 is the Short Description for the GL account number. When I set up this PowerOLAP database I made sure to assign a short description property to each member in the Account dimension. In many companies, account descriptions can be quite long, which makes them difficult to handle in Excel reports. So having a short description available is very convenient. (At other companies, the account description is too short to understand easily. So those companies could use a Long Description property for other types of reports.)

Cell C11 also uses the CHOOSE function:

C11:  =CHOOSE(SIGN($A11)+2,"","Grand Total",
            OLAPMemberProperty(Database, "Account", $B11, "ShortDesc"))

PowerOLAP's OLAPMemberProperty function returns a value for a specified property. To illustrate, if this function had used "Desc" rather than "ShortDesc", the formula would have returned the full description, for this account which I set up as Repair and Maintenance.

The formula in cell D11 needs to return a null string if cell A11 is negative; it needs to return the Grand Total if cell A11 is zero; and it needs to return the account balance if cell A11 is positive. Here's its' formula:

D11:  =CHOOSE(SIGN($A11)+2,"",
           OLAPRead(Database,Cube,GLView,Unit,Department,$B$10,D$10),
           OLAPRead(Database,Cube,GLView,Unit,Department,$B11,D$10))

The OLAPRead functions return data from the PowerOLAP cube. The only difference between them is that if cell A11 is zero, the function returns the total for the group of expenses specified in cell B10. Otherwise, the formula returns the value for the current GL account number, which is in cell B11. In both cases, they return information about the current month, which is in cell D10.

I copied cell D11 to cell E11. Then I copied the range B11:E11 down the column as needed.

In actual practice I would have many more than six rows in a report. Typically, I'll set up formulas to accommodate about twice the number of rows that the longest version of the report requires. This allows the number of children to grow over time.

The Final Step, Adding the Underline

We only need to take one final step to finish this report. We need to add the line that separates the Grand Total from the line above it. To do that, we use conditional formatting.

When I set this up, I selected the range C14:E14, the range that needs the border row at its top. Then I chose Format, Conditional Formatting.

As shown above, I chose Formula Is in the list box and entered the formula...

=$A14=0

...in the edit box.

Notice that there's no "$" in front of the row number in the above formula. Therefore, when the range C14:E14 is copied to other rows, each row will base its conditional formatting on the value in column A in its own row. If I had used $A$14 in the conditional format, all rows would have linked their formats to the value in cell A14.

Next in the Conditional Formatting dialog, I clicked Format and specified the top border. After I clicked on OK, I could see the top border as shown in the spreadsheet image above.

Finally, I copied the range C14:E14 to the range C11:C16.