Bi L3 Bex Query Designer Unit 3

BEx Query Designer

Unit 3: Calculated and Restricted Key Figures

7/17/2007

What is a Restricted Key Figure? Restricted key figures are (basic) key figures of the InfoProvider that are restricted (filtered) by one or more characteristic selections. Filter vs. Restricted Key Figure? Filter – restrictions are valid for the entire query Restricted Key Figure – only the key figure in question is restricted to its allocated characteristic value or characteristic value interval Examples: – Last Month’s Sales Amounts – Tons Shipped to Customer ABC Slide 2

Defining Restricted Key Figures When defining a restricted key figure, you want to select the restricting characteristics, individual characteristic values, value ranges, hierarchy nodes, or characteristic variables. You can include values in the selection or exclude values from the selection by selecting these options in the context menu of the relevant filter value. When selecting value ranges, the following operations are available: • Between • Greater than or equal to • Less than or equal to • Greater than • Less than Slide 3

Formulating Selection Conditions The characteristic values available in the Fixed Values window are restricted to 100 values by default. Click Display Other Values to modify this restriction. Choose the attribute, operator, and characteristic value to formulate a new selection condition. Example: – Display all products with the text toothpaste in the name

Heads Up!

If you set the Number of values displayed to All, and there are a large number of characteristic values, this may cause a long wait when selecting values. Slide 4

What is a Calculated Key Figure? You can use a formula to calculate key figures that are not in the InfoProvider by using basic key figures, restricted key figures, and existing calculated key figures in the formula definition. Percentage Functions – Percentage Variance (%) – Percentage Share (%S) – Percentage Share of Result (%CT) – Percentage Share of Overall Result (%GT) Data Functions – Count [COUNT(<Expression>)] – Division by Zero [NDIV(<Expression>)] – Result [SUMCT ] – Overall Result [SUMGT ] – Report Result [SUMRT ] Mathematical Functions – Maximum – Minimum – Absolute Value – Smallest integer value larger than operand – Division (integers)

Slide 5

Defining Calculated Key Figures You can use all basic key figures of the InfoProvider in question, as well as the newly defined restricted and calculated key figures of the InfoProvider, to define new calculated key figures. You can use the following boolean operators to create conditional calculations (IF-THEN-ELSE): • Relational Operators (<, <=, <>, ==, >, >=) • Logical AND (AND) • Logical OR (OR) • Logical Exclusive OR (XOR) • Logical NOT (NOT) • NOT <Expression> • LEAF

Note

The relational operators return a value of 1 if the expression is true, otherwise 0.

Slide 6

Relational Operators You can use boolean operators to implement the formula given below: •

IF Actual Cost > Plan Cost THEN Actual Cost ELSE Plan Cost

Use the boolean operator > to perform the comparison between the key figures. The result is 1 if ( Actual Cost > Plan Cost ), otherwise 0. The operator NOT returns 1 if <Expression> is 0, otherwise 0. Actual Cost > Plan Cost (1 * Actual Cost) + (NOT (1) * Plan Cost) = Actual Cost Actual Cost < Plan Cost (0 * Actual Cost) + (NOT (0) * Plan Cost) = Plan Cost

Slide 7

Boolean Operators - LEAF The boolean operator LEAF can be used for carrying out different calculations on results rows and elementary rows of a key figure. For example, this can be used for weighted average calculations for results rows: • Key figure Tons can be calculated as Tons in the elementary rows and as Tons/Load in the results rows The first part LEAF of the expression returns 1 for elementary rows and 0 for results rows. The second part NOT LEAF of the expression returns 0 for elementary rows and 1 for results rows.

Note

The LEAF operator returns a value of 0 for results rows and 1 for elementary rows. Slide 8

Properties of Key Figures You can assign a unique technical name to structure parts such as key figures (optional).

You can change the automatically determined key figure description here.

You can choose whether the key figure is to be highlighted in the report here.

You can hide key figures that you only need for calculating formulas, and do not want to display in the analysis.

Slide 9

Properties of Key Figures The scaling factor is not automatically displayed in the report. You have to set the scaling factor display separately in the query properties.

The reverse +/- signs is only a display function. It has no influence on calculation in formulas.

You can use this function to recalculate result rows and individual values that are displayed in the version according to particular criteria. You can use the Cumulated function to ensure that the cells of a column are presented from top to bottom, and the cells of a row are displayed from left to right.

Slide 10

Properties of Key Figures In the Properties of the Calculated Key Figure dialog box, you can also select an exception aggregation for a special characteristic for the calculated key figure.

You can set a currency conversion key and a target currency in the properties dialog box for a key figure. Slide 11

Formula Collision The Formula Collision function is only displayed in the properties dialog box if two structures are used in the query definition and both contain formulas.

In places where the two formulas collide, it is not clear how the system should calculate. You can therefore choose the formula from which you want the value to be calculated.

Slide 12

Structures Structures are combinations of characteristics and key figures of the InfoProvider. A structure can be used in a planned/actual comparison or in a contribution margin scheme. Structures can be used to calculate MTD, QTD, YTD, etc. figures.

Slide 13

Cell Editor Cell Editor allows direct definition of specific cells in a query. Cells are the intersection of two structures in a query definition. The Cell Editor can only be used in query definitions where there are two structures. Cells can be formulas, selections, references or auxiliary cells.

Note

One structure will hold only characteristics while the other structure holds only key figures.

Slide 14

Cell Editor You can use the cell editor to calculate the x- month rolling average for a key figure. –

Select the cell you want for calculation as a new reference.

–

Right-click and select New Formula (or New Selection).

–

Use the reference cell for doing the Average calculation. The original value will now be replaced with the cell editor calculation. Slide 15

Tabular Reporting Tabular Reporting will let you arrange the characteristics and key figures in any order when you create a query. –

For example, you can insert a characteristic column between 2 key figure columns.

Use the Table Display icon for changing to tabular mode. To re-arrange the characteristics and key figures, use BEx drag and drop features.

Heads Up!

Navigation functions like Swapping, drilldown, etc. are not supported Slide 16 with Tabular Reporting.