Working with Hierarchies

Break Hierarchies

If the report uses a hierarchy, you can "Break" it to basically remove it. This via the "More Sort Options" in the Sort Menu.

Filter Data via Hierarchies

1. Select the Hierarchy to filter

2. On the filter button, choose the « Filter By Member »  

3. Select then press OK.

Using the Compact Hierarchical Display

The option Compact Display allow to generate a hierarchical view of the dimensions of an axis (row & column)

• The purpose is to give an easier way to read crosstab in case of multiple dimensions
• This allow to navigate to the detail 

Using Calculations

Add a calculation between 2 measures

1. Select two Measures
2. In the « Calculations » Button select the Type of calculation.
3. The result is added in the right of the report.

 ==> 

Insert a dynamic calculation (Single value)

1. Select an existing measure
2. In the Calculations Button Select « Add Dynamic Calculation » then choose your type of calculations option.
3. The result is added in the right of the report.

Currency Conversion

Even if no currency conversion is set by default in the query (or via prompt), you can request BW to convert the values to a different currency.

1. Select a crosstab cell from the measurement you want to convert.
2. Select « Measures » in the Display  group Tab. Select « Currency Translation » 
3. Select the target currency
4. Select the type of currency conversion

Conditional Formatting

In the same was as in Excel you can set a conditional formatting for the measures.

Setting

1. Select «  Conditional Formatting » in the Display Group Tab
2. Select New
3. Name the Conditional Formatting and based it upon a Measure
4. Set your formatting and click Add

Multiple Choice Format

The formatting can be applied to 3 places :

• Data Cells: Conditional formatting will execute to the Cells.
• Row Headers: Conditional formatting will execute to the Row Header.
• Columns Headers: Conditional formatting will execute to the Column Header.

Measures Formatting

You can change several options on how the measures are displayed :

1. Select the cell of the measure you want to change.
2. Select "Measures" in the menu. 

• Number Format
  • Scaling Factor: 1 if you want to display the number as is. 1000 if you want to display thousands.
  • Decimal Places: Change the default number of decimals shown (The full decimal will still be in the cell, just the display changes). 
• General Format
  • Display of Negatives Values : several ways of displaying the negatives values. Ex : - X, (X)
  • Display Zero as : several ways to display the 0 values. Ex : empty, 0 with currency...

Members Display

For most dimensions, you can change the way it is displayed and choose between "Key" or "Text" or both.

1. Select your Dimension cell header that you want to display   

2. Select « Members» in the ANALYSIS Ribon then choose your display.

Dynamic Charts

You can transform any crosstable into an excel chart.

1. Click on cell in the crosstab you want to add a chart to
2. Click the chart button to insert the type of chart you want.

3. A Chart is added in your sheet, you can drag and drop and re size.

3 main types of charts :

"Standard" chart	Waterfall Chart	Pivoted Waterfall Chart

Working with Style Sets

Style sets are pre-saved/defined sets of formats (colors etc...). You can create your own or use one of the 3 default ones.

Apply a Style Set

Create a new Style Set

Export/Import a Style Set

To Export a Style Set (XML file) :

1. Apply an existing Style Set  
2. Click the Styles Button and  Select “Export Style Set”
3. Enter a name Style and save your Style Set in a file

To Import a Style Set (XML file):

1. Click the Styles Button and Select “Import Style Set”
2. Select your Style set and click Open

Creating a Default Workbook

1. Modify the workbook to set the default format you want to keep (Apply sets, add information cells, change formatting...)
2. Save your workbook Locally (excel file)
3. Click "Settings"
4. Select "User Settings"
5. Select the "Default Workbook Location" and browse to select your own saved workbook.

Add Additional Information via Formula

1. Click the cell outside the crosstab and click the insert button  
2. Select Analysis for the “Category” and choose a function in the Pop up
3. Select a data source to apply the Formula.Type DS_1 the default data source formula Alias

Alternatively , use the drag and drop solution :

1. Click the Display tab and select “Information tab”

2. Drag and drop any information from the Design Panel Information tab into an empty cell then save your workbook.

Filter component

You can add a special cell in the workbook which allows you to dynamically add filters on a dimension.

1. Click a cell outside a crosstab 

2. Click “Filter” tab and select a dimension (Note that this filter selection has a green arrow)

3. Click the Filter Icon to change your filter 

Create a Power Point Slide with dynamic results

This features allows you to embed a BW query result inside a power point slide. It is then possible to refresh the power point data by connecting to Analysis for Microsoft Power Point.

1. Select a cell in the crosstab 
2. Click “Create Slide” button  
3. Log on to the BW server, enter your Password, press OK  
4. Adjust your crosstab to your slide
5. The crosstab had been added to your PPT slide

Once added in Power Point you can then use the normal tools to format the table.

Additional features for power point :

• Select « Chart » to insert a Chart
• Select « Info-Field » to add information in the slide
• Select «  Fit Table » to allowed table split into multiple slide
• Select «  Move to » to move to another slide
• Another Analysis features: Insert A Data source / Prompt / ….