BI for Excel >
An Excel Tutorial:
Create an Accordion Report
In Excel, Using OLAP Data
Budget reports by department, and other
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
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
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:
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:
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:
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
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
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'
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
As shown above, I chose Formula Is in the list box and entered the
...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.