The Outline feature is part of the PowerOLAP Add-in features to Excel. This feature enables users working on a Slice to Excel report to use the hierarchical definitions defined within a PowerOLAP database and deploy those hierarchies out to Excel. In other words, users can then easily format their Slice to Excel report and apply the hierarchical structure directly in Excel based on how these hierarchies have been defined from within PowerOLAP.
You can create an Outline either vertically which means an Outline will be applied to the Members along the Rows; or Horizontally which means an Outline will be applied to the Members along the Column.
.png)
Create an Outline
To show how the Outline feature works, begin by creating a basic Slice to Excel report. In the following example, open Avon Trading Company.olp and using the Financial Data cube, create a Slice to Excel report.
To create an Outline along the Rows:
1. Launch PowerOLAP and open the Avon Trading Company database.
2. Open a slice view of the Financial
Data cube, then arrange the slice as follows:
Page: Version, Year,
Region
Column: Month (select a subset
which includes only Quarters and
Total Year)
Row: Account
Note: Remember to press F9
or click the Recalculate button
after arranging the slice to refresh the slice view.
.png)
3. Create a Slice to Excel report out
of the Financial Data cube by
clicking on the Create Excel Worksheet
button.
The Slice view appears as follows. Next, create an Outline for the Dimension
members along the rows (Vertical Outline) and column (Horizontal Outline).
.png)
4. Click on Revenue, which happens to be the first member of the Account dimension along the rows.
.png)
Outline Command
5. From the Excel ribbon, click the
PowerOLAP tab then select the
Outline command icon.
The Create Hierarchy dialog box
appears. This is where you configure some settings for the Outline that
will be applied to your report in Excel.
.png)
6. In the Create Hierarchy dialog that
appears, confirm that it shows the:
Financial Data cube,
Account dimension,
0 Root Level
All Root Members
5 Generations
Vertical direction; and
Top-Down order.
7. Click OK.
Then press F9 to refresh the worksheet.
Notice that the members of the Account dimension are now arranged into
a hierarchy along the column.
With the Excel outlining set up using the hierarchy information from PowerOLAP®,
you can now contract and expand sections of the account dimension by clicking
on the Plus and Minus buttons in the tree diagram to the left of the row
numbers (see boxed item at the lower left side)
Clicking on the small numbers (see boxed item at the upper left side) at
the top of this section expands or contracts an entire level at a time.
Next, create an Outline for the members of the Month dimension along the
column.
.png)
8. Click on QTR1, which happens to be the first member of the Month dimension along the columns.
9. Again, go to Excel ribbon then click PowerOLAP tab and then Outline command icon.
.png)
10. In the Create Hierarchy dialog that appears, confirm
that it shows the:
Financial Data cube,
Month dimension,
0 Root Level
Total Year Root Members
2 Generations
Horizontal direction; and
Top-Down order.
11. Click OK.
Then press F9 to refresh the worksheet.
Notice that the members of the Month dimension are now arranged into a
hierarchy.
Likewise, you can easily collapse and expand the hierarchy by clicking
on the Plus and Minus buttons located at the top part (see boxed item).
Similarly, clicking on the small numbers (see left most boxed item) will
expand or collapse an entire level of the hierarchy at a time.
Notice also that Total Year is
now displayed first, this is a result of configuring the Outline to display
in a Top-Down order, or in other words, the parent-most member will be
displayed first.
If we had configured Months members
to be displayed in Bottom-Up order, the order will be reversed, where
child-most or leaf level members will be displayed first. As in the example,
it will just retain the original order where QTR1
is displayed first.
.png)
Clearing an Outline
You can also remove the Outline you have applied on an Excel report. However, when you do clear the Outline, you are just removing the Outline functionality/feature. The arrangement or order of the members along the Rows and Columns will still retain the same arrangement prior to clearing the Outline.
To Clear the Outline:
1. Open the Excel report and click on any cell.
2. From the Excel ribbon, go to PowerOLAP
tab and click Clear Outline command icon.
Notice that the Plus and Minus sign in the tree diagram at the top or left
area of the worksheet, as well as the numbered buttons, disappears. The
hierarchy structure, however, is retained.
.png)
Clear Outline Command
.png)
Create Hierarchy Options
When you use the Outline feature to apply PowerOLAP defined hierarchy structure to a Slice to Excel report, you can actually configure the format on how the hierarchy will be applied to your Excel report. When you click the Outline command icon, it will bring up the Create Hierarchy dialog which is where you can configure some settings for the outline to be created.
Create Hierarchy Dialog
Provided below is a brief description of the options available in this Dialog box:
POWEROLAP
Section:
• CUBE drop down
- a drop down menu that lists all the available Cubes
from the active or current database
• DIMENSION drop down
- a drop down menu that lists all the Dimensions that comprises the currently selected Cube
OPTIONS
Section:
• ROOT LEVEL
-indicates the level or depth in the hierarchy of the
members that will be used as the root members in the Outline that will
be created into an Excel report.
• ROOT MEMBERS drop down
-This allows you to pick specific root members for
the Outline that will be created in an Excel report.
• GENERATIONS drop down
-This indicates the number of sub-levels of the members from a hierarchy that will be displayed in an Excel report once an Outline is created.
DIRECTION
Section:
• HORIZONTAL
- This will create an Outline along the columns
• VERTICAL
- This will create an Outline along the rows
ORDER
Section:
• TOP-DOWN
- When this option is enabled when creating an Outline,
the Outline that will be produced will have the members arranged starting
from the root member or the parent-most member then down to the detail
members.
• BOTTOM-UP
- When this option is enabled when creating an Outline, the Outline that will be produced will have the members arranged starting from the detail members or the lowest level members then going up to the root member or the parent-most member.