Changing Data Views from withn Excel

 

 With a dynamic Excel worksheet, you can access different Page Members and associated values, and even different Cubes and Dimensions.

You have already seen, in the previous topic, how quickly you can navigate a PowerOLAP Slice to access data for different Page members. Likewise, you can use a linked Excel worksheet to access different Page Members, as follows:

Selecting Alternate Page Members

1.   Double-click the cell with the Page Member you want to change.  
In the example worksheet, you would click cell C3 to change from USA to a different Region Member, or cell C4 to change Aunt Re’s Chicken to a different Product Member.

The following Select A Member dialog box is opened if you double-click on cell C4:

Select A Member dialog box, with Hierarchy expanded

The Select a Member dialog box includes both a Member tab and a Hierarchy tab. In the preceding figure, the Select A Member dialog box shows the hierarchy of the Product dimension expanded.

To change the Page Member shown in the Excel worksheet:

2.   Click the  button to expand the hierarchy tree. (You may need to expand the Hierarchy more than once to see its full structure in some Dimensions.)

3.   Select a Member (e.g., Southtown Pies).

4.   Click OK to update and return to the Excel worksheet.

The new Member is entered into the cell. Press F9, if necessary, to recalculate the worksheet. The following figure shows the worksheet with Southtown Pies data selected:

 

 Tip

Rather than work through the Select A Member dialog box, you can change the Member number in the argument (e.g., from 8 in the above formula) to another Member number in the list. You can also type a Member name into the appropriate worksheet cell to view that Member’s data (e.g., type a Member name in cell C4). Note, however, that in this last case a typed Member name will now be ‘fixed’ as a name, rather than as an argument.

Select Alternate Cubes and Dimensions

 

Select Cube

[Note: The procedure for these features is outlined in the following steps, but it has no applicability to the current example worksheet.]

Select Dimension

Just as you can use a linked Excel worksheet to access alternate Members, you can access alternate Dimensions and Cubes in a PowerOLAP database.

 

Access alternate Dimensions as follows:

1.   Click the cell containing the Dimension or Cube name you want to change.

2.   From the Excel ribbon, go to the Add-Ins tab then click PowerOLAP, Select then choose between Cube or Dimension,.
The Select Dimension (or Select Cube) dialog box is opened.

Assuming in the exercise that you selected Dimension, the following Select Dimension dialog box appears.

The database name is indicated at the top of the dialog box. The list of all Dimensions contained within the database is displayed in the Dimensions list box.

3.   Click a Dimension (or Cube) name.

4.   Click OK to update and return to the Excel worksheet.

The new Dimension or Cube is entered into the selected cell. The Dimension or Cube name can be typed into the appropriate cell to view alternate Dimension or Cube data.

Select Arrays (or Lists) of Members in Excel

 

Select Members

In addition to selecting individual Page members, you can place (or replace) an array of Members into an Excel worksheet as column or row heading labels. The array of Members placed into the worksheet can be a Dimension’s full list of Members, or a selected group of Members.

In the figure above (derived from the example Sales Account by Month slice, using OLAPReadWrite), the months January through December are listed as row headings down column A. However, you may want to view, instead, the month YTD (year-to-date) values. PowerOLAP allows you to insert an alternate list of Members into an active worksheet as follows:

1.   Click the first cell where the array of Members will be entered (for example, cell A9 in the figure above).

2.   From the Excel ribbon, go to the Add-Ins tab then click PowerOLAP, Select then Members.
The Select Members dialog box appears.

3.   Select a Dimension from the drop-down button. This will be the dimension whose members you want to use for the aray.

The figure above shows the Month dimension selected from the pull-down list at the top of the dialog box.

4.  Holding Ctrl key, click the Members you want to select for the array.

5.  Click the arrow button in the center of the dialog box.

In the Select Members dialog box above, the YTD Members have been selected in the Hierarchy list box on the left and moved to the Members list box on the right. There are two options on how to display the Members. First is the ‘Across’ option which will display the selected Members as Column headings. Second is the ‘Down’ option which will display the selected Members as Row headings. 
For this example, we want to display the selected Members in the example Excel worksheet in column A down as Row headings.

6.  Select Down option from the Fill drop-down list box at the bottom of the dialog box (circled).

7.  Click OK to return to the Excel worksheet.
You may delete the last row Total Year if you want to exclude that data from the report.

The newly selected Member list and the correct data cell values appear in the worksheet (in the example worksheet, the selected group of Members are entered down column A as row headings).