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].&[2002]" )

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]

■
[DimTime].[FiscalYear].&[2002]

■
[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.