Articles > Effective Dashboards with Excel 2007
Summary
This article explains how you can create Excel dashboards for Analysis Server 2005 data with Excel 2003 cube formulas and MicroCharts sparkline formulas.
Download the sample sheet Excel 2007 Dashboard.xlsx and MicroCharts.
Background
Dashboard reports are often disjoint, (unlike Pivot Tables). The dashboard is organized in small blocks of tables and charts, spread over the sheet.
PivotTables show the data as one adjacent rectangular block of data, with limited formatting capabilities. A cube formula, on the other hand, can be placed everywhere in the Excel grid using the rich Excel formatting.
Here a list with Excel cube formulas:
| Formula | Description |
| CUBEMEMBER(connection,member) | Member defined by member_name |
| CUBEKPIMEMBE(connection,kpi_name,kpi_type) | Member defined by kpi_name |
| CUBEVALUE(connection,member1,member2, …) | Get cube value |
| CUBESET(connection,set_expression) | Set defined by set_expression |
| CUBERANKEDMEMBER(connection,set_expression,rank) | Returns the Nth item from a set |
The green boxed cells contain CUBEKPIMEMBER formulas to get the KPI members of the cube:
=CUBEKPIMEMBER(“Adventure Works Cube”,"Financial Variance",1)
The first parameter defines the cube connection, the second parameter defines the KPI name and the third parameter defines the KPI type.
The blue boxed cells contain CUBEVALUE formulas that get the cell values.
=CUBEVALUE(“Adventure Works Cube”,"[April 2002]","[Return on Assets Value]")
The first parameter defines the cube connection. All subsequent parameters define the dimension members. [April 2002] returns the time member and [Return on Assets Value] returns the KPI member.
The red boxed cells contain CUBERANKEDMEMBER formulas that get the ranked members of a defined set. The set is defined with the CUBESET formula and uses the MDX ranking function TOPCOUNT to retrieve the top 10 products.
=CUBESET(“Adventure Works Cube”,"TOPCOUNT([Product].[Product].[Product].MEMBERS,10,([Measures].[Internet Sales Amount],[April 2002]))")
The CUBERANKEDMEMBER formulas refer to the CUBESET formula ($U$1) that is located in a hidden cell in the dashboard header:
=CUBERANKEDMEMBER(“Adventure Works Cube”,$U$1,1))
MicroCharts Formulas
The sparklines, the bullet graphs and the bars in the dashboard are done with MicroCharts formulas. You simply use the MicroCharts formulas like a normal Excel formula:
The MicroLine formula refers to a hidden range of CUBEVALUE formulas that retrieve the values of the last 24 months from the cube. The 24 month members are created with the following CUBEMEMBER formula:
=CUBEMEMBER("MicroChartsSample","[Date].["&$AO$2&"].LAG("&A4&")")
The cell AO2 contains the time member selected in the header of the dashboard. A4 refers to the hidden row that contains a decreasing index from 23 to 0. The formula evaluates in Excel to the MDX expression "[Date].[March 2004].LAG(1)" which refers February 2004 in the cube.
Related Articles:
Related Links: