| Table of Contents | |
|---|---|
|
Filtering data
|
| Excerpt | ||
|---|---|---|
| ||
You can insert a filter component to your analysis to simplify the filtering. This helps you to quickly change the view of the displayed data, for example to different periods of time. |
Filter data by Member
via the Toolbar
- Select
- a cell in the crosstab that belongs to the dimension
- you want to use for filtering.
- In the Analysis ribbon toolbar, select the "Filter"
- Select "Filter
- By Member..."
- The Filter by Member dialog box appears.
- Make the selection and click OK
via right click
From
the crosstab
Select
one cell for each member that you want to filter
- On the the Dimension Header or in the Design Panel Row or Column to Filter on this Value
- Out of the cross tab or in Design Panel datasource fields to Exclude this value from filter
for. To select multiple members, hold down the Ctrl or Shift key as you select members. Right click on the cells.
The context menu box appears
- Choose Filter Members to exclude data according to your selection
- Choose Filter Other Members in the context menu to filter out the selected members
- Choose Filter By Members to make a selection
From the design panel
Right click on the dimension in the design panel and select Filter By Member
Filter data by Member via right click
From the Crosstab :
- Right click to a Dimension
- Select « Filter By Member »
From the design panel :
Filter data via Background Filter
Background Filter allows filtering on the Dimension that is not in the Crosstab
- In the Design Panel select a Dimension, Right click and select « ADD to Filter »
- Make your selection and press OK
You select members for filtering that should not be displayed in the crosstab. These non-visible filters are background filters.
You can add the dimension using drag and drop or you can choose Move to Background Filter in the context menu.
Filtering Individual selections or range selections
- You can
- select "Filter By Member..." for individual selections. In this case you can select/unselect individually each value
- or you can select
- "Filter By Range
- ..." to filter a range of data.
Remove a Filter
Solution 1: Select the dimension header, right click and Select All Members
Solution 2: In the Analysis Tab, select the dimension, right click and Select All Members.
Filter by Measure
You can define rules to filter the data of your analysis to your current scope of interest. You can apply one or multiple rules to a measure.
- Select the Dimension.
- Click the « Filter » button and select « Filter by Measure ».
- Select the measure, set
- Set your filter,
- You you can Add multiple filter, then press
- Press OK .
| Note |
|---|
If you choose a Dimension instead of a measure before filtering by measure, the filter is applied directly on the level of the selected dimension |
The following options define on witch level the filter is applied :
- All Dimensions
- Independently
If you select this option, the filter is applied to all dimensions in the analysis, from the outermost to the most detailed one. For example, in an analysis with three dimensions in columns A, B and C, the filter is first applied to the dimension in column A, then to the dimension in column B and finally to the dimension in column C.
- Most Detailed Dimension in Rows
If you select this option, the filter is applied to the most detailed dimension of the rows. For example, in an analysis with three dimensions in columns A, B and C, the filter is applied to the dimension in column C. This option is optimized for threshold value filtering.
- Most Detailed Dimension in Columns
If you select this option, the filter is applied to the most detailed dimension of the columns. For example, in an analysis with three dimensions in rows 2, 3and 4, the filter is applied to the dimension in row 4. As with the option above, this option is also recommended for threshold value filtering.
Remove a filter by Measure
Solution 1: Select the dimension, right click and Select Filter by Measure, select « Reset »
Solution 2: Select the dimension, Filter and Filter by Measure, then select « Reset »
Filter by using the BEx Conditions
Conditions are special pre-defined (at the query creation) dynamic filters that can be activated or deactivated in the report.
If conditions exist for the report, you can click on the "Filter" menu, then go to "BEx conditions" and selecting one from the list.
Filter
out Zeros in Rows or Columns
In some cases, the BW report will still display a row or column even if all the values are equal to 0. This is usually the case when the "0" is the result of a sum of positive and negative numbers.
You can choose to remove those columns/rows via the "Suppress Zeros in Rows" and "Suppress Zeros in Columns" options in the Filter menu.
Sorting data
Sorting by measure
- Select the Measure you want to sort.
- Click the Sort button in the Navigation Panel
- Choose how you want to sort in the Navigation Panel.
Example :
Sorting by dimension
- Select the Dimension you want to sort.
- Click the Sort button in the Navigation Panel.
- Choose how you want to sort in the Navigation Panel.
Show/Hide Totals
- Select the Dimension you want to hide the Totals
- Select Totals and click Hide Totals.
| Info |
|---|
You can select several dimensions at the same time to hide/show the totals |
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
- Select the Hierarchy to filter
On the filter button, choose the « Filter By Member »
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
- Select two Measures
- In the « Calculations » Button select the Type of calculation.
- The result is added in the right of the report.
| Info |
|---|
To substract or divide, select the first cell, click [CTRL] pressed then select the second cell |
==> 
Insert a dynamic calculation (Single value)
- Select an existing measure
- In the Calculations Button Select « Add Dynamic Calculation » then choose your type of calculations option.
- 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.
- Select a crosstab cell from the measurement you want to convert.
- Select « Measures » in the Display group Tab. Select « Currency Translation »
- Select the target currency
- Select the type of currency conversion
Formatting
Conditional Formatting
In the same was as in Excel you can set a conditional formatting for the measures.
Setting
- Select « Conditional Formatting » in the Display Group Tab
- Select New
- Name the Conditional Formatting and based it upon a Measure
- Set your formatting and click Add
Multiple Choice Format
You can choose between several formats :
| Value Format |  |  | |
|---|---|---|---|
| Cell Background Format |  |  | |
| Symbol Format |  |  |  |
| Trend Format |  |
Multiple Choice Format
You can choose for which members the conditional formatting should take place. You can apply for a Member, Total, or Both.
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 :
- Select the cell of the measure you want to change.
- 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.
| Info |
|---|
Not all dimensions are available in both Text but all are available in Key This depends on the configuration. |
- Select your Dimension cell header that you want to display
Select « Members» in the ANALYSIS Ribon then choose your display.
Dynamic Charts
You can transform any crosstable into an excel chart.
| Note |
|---|
You can also add the chart directly from the excel menu BUT in that case, it will not be dynamic. If you add columns/rows, the chart will not be updated. For this to be dynamic, use the Analysis feature. |
- Click on cell in the crosstab you want to add a chart to
- Click the chart button to insert the type of chart you want.
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 |
|---|---|---|
 |  |  |
| Info |
|---|
Once added, you can use the normal excel functionality to modify the 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
- Click “Styles” button in the Setting Group Panel
- Select “Apply Style Set”.
Choose the desired Style
You can set one of the "Sets" as default for your Analysis. This set will automatically be applied to all your new workbooks:
| Info |
|---|
Analysis comes with 3 default sets :
|
Create a new Style Set
- Click the Home Excel ribbon
- Select the type of cell in your crosstab you want to customize
- Click “Cell Styles” button
- Right click in the highlighted cell and select “Modify”
- Select “Format”, and choose your style then click OK
- Select “Styles” in the Analysis Tab, select “Save Style Set”
| Info |
|---|
Note that the currently selected cell’s style is highlighted. You must repeat this process for all the cells you want to customize |
Export/Import a Style Set
To Export a Style Set (XML file) :
- Apply an existing Style Set
- Click the Styles Button and Select “Export Style Set”
- Enter a name Style and save your Style Set in a file
To Import a Style Set (XML file):
- Click the Styles Button and Select “Import Style Set”
- Select your Style set and click Open
Creating a Default Workbook
- Modify the workbook to set the default format you want to keep (Apply sets, add information cells, change formatting...)
- Save your workbook Locally (excel file)
- Click "Settings"
- Select "User Settings"
- Select the "Default Workbook Location" and browse to select your own saved workbook.
| Info |
|---|
Setting a default workbook can save you time if you always to have the same formatting in the future. |
Advanced Features
Concert to Formula
Text that is normally not displayed because it occurs several times in a row or column, is now repeated in each cell automatically to produce valid formulas
| Info | ||
|---|---|---|
| ||
1.Create persistant individual cell 2.Insert blank rows or columns to make the display easier to read 3.Copy parts of the table to other cells in the workbook 4.Use data from multiple data providers when creating calculations |
| Note | ||
|---|---|---|
| ||
1.Navigation with Drag & Drop is no longer possible 2.The context menu is not available 3.The number format in the cells is set to standard 4.The conversion can only be undone from the analysis ribbon 5.If the crosstab is save, it cannot be converted back to analysis mode
|
Add Additional Information via Formula
- Click the cell outside the crosstab and click the insert button
- Select Analysis for the “Category” and choose a function in the Pop up
- Select a data source to apply the Formula.Type DS_1 the default data source formula Alias
Alternatively , use the drag and drop solution :
- Click the Display tab and select “Information tab”
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.
Click “Filter” tab and select a dimension (Note that this filter selection has a green arrow)
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.
- Select a cell in the crosstab
- Click “Create Slide” button
- Log on to the BW server, enter your Password, press OK
- Adjust your crosstab to your slide
- 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 / ….
More information
More detailed user guides can be found here :
- SAP PDF documentation :
http://help.sap.com/businessobject/product_guides/AMS14/en/14SP8_aaoffice_user_en.pdf
In the Settings Panel, you can access the User Guides from SAP directly.
