Microsoft Office Tutorials and References
In Depth Information
Now take a look at the formulas inside the cells. They are of two different types:
■ CUBEMEMBER This type of formula is used for headers of both columns and rows. It
returns an object, called a member , which is basically the value of a column in a table.
For example, the formula in the 2002 header contains
= CUBEMEMBER( "PowerPivot Data", "[DimTime].[FiscalYear].&" )
and it can be read as return the value of the column FiscalYear in table DimTime where
the value is 2002 . The formula might be confusing because we are asking for something
whose value is 2002 and provide 2002 as the final parameter. This has to do with the
internals of PowerPivot, which reasons in terms of members and values of an OLAP cube.
■ CUBEVALUE This type of formula is used for the cells in the table. Each cell asks for a
value that gives the set of members that form the coordinates of the requested value. For
example, in the case of sales in North America for the 2002 year, these are the coordinates:
■ [Measures].[Sum of SalesAmount]
■ [DimSalesTerritory].[SalesTerritoryGroup].&[North America]
The result is the value of the measure SalesAmount in 2002 for North America.
Readers used to OLAP databases recognize in these formulas the standard set of coordinates
used to navigate OLAP cubes. In fact, at the very end, PowerPivot stores its data in cubes that
are automatically processed by PowerPivot itself, which we show later in this chapter.
The really interesting part of all this is that now the worksheet is composed of formulas (the
PivotTable disappeared), and the formulas can be moved wherever you want them. Moreover,
even if the source data changes, Excel does not change the position of any cell.
You can now create a new worksheet and proceed to write your dashboard, referencing the
values in this new set of cells. But be careful: if a cube does not return any data, it is returned
by CUBEMEMBER as an empty string. So whenever you need to reference its value, you need
to surround it with an IF, as in the cell Total Sales in Europe for 2002 of Figure 8-14, which
contains this formula:
= IF( Internet!C6 = "", 0, Internet!C6 ) + IF( Resellers!C6 = "", 0, Resellers!C6 )
This is the only particular care you need to take when using values coming from an OLAP
cube—that is, you need to remember that empty values are empty strings, not numbers.
The remainder of the dashboard can be easily created by using standard Excel formulas
and some formatting, all things that are already well known and documented.
Note Results from OLAP cubes are always strings, so the ISBLANK function does not help here.