Wednesday, June 15, 2011

Calculation Context : Ulmitate Solution for Complex Query in Business Object

Calculation contexts give you more control over how a formula or measure is evaluated. To understand calculation contexts, you need to be familiar with basic report concepts.

A report contains two kinds of objects:

  • dimensions - which are types of data about your business that can have measures associated with them (for example: products, years, states)

  • measures - that you can calculate in relation to dimensions (for example: sales revenue, number of sales).

For example, a report could show sales revenue (a measure) by year (a dimension). In this case the sales revenue results are calculated by year.

The important thing to remember about measures is that they are semantically dynamic. This means that the results returned by a measure depend on the dimension(s) with which the measure is associated. In other words, the results returned by a measure depend on the context in which the measure is placed.

WebIntelligence calculates measures according to default contexts depending on where the measures appear in a report. However, you can change these default contexts. This is what is meant by defining the calculation context. You define the calculation context by including context_operators and context_keywords (Report, Section, Break, Block and Body) in your formulas.

A calculation has an input context and an output context. You can specify neither, either or both explicitly. The input context is the set of dimensions used to make the calculation. The output context is a set of dimensions that functions like a break in the calculation - it causes the calculation to output values as if they are calculated in the footer of a table break (or an SQL COMPUTE BY clause).

Example

The final column in the table below is calculated as follows:

calculation - minimum

input context - year, quarter

output context - year

This combination of input and output contexts tells Web Intelligence to sum the revenue for each year/quarter combination (the dimensions used to make the calculation), then output the minimum such value in each year ( the value that appears in the break footer if the table contains a break on Year and a minimum calculation in the break footer).

Year

Quarter

Product

Revenue

Minimum

2005

Q1

Product 1

10000

41000

2005

Q1

Product 2

15000

41000

2005

Q1

Product 3

16000

41000

2005

Q2

Product 1

17000

41000

2005

Q2

Product 2

17000

41000

2005

Q2

Product 3

16000

41000

2006

Q1

Product 1

14000

33000

2006

Q1

Product 2

15000

33000

2006

Q1

Product 3

16000

33000

2006

Q2

Product 1

12000

33000

2006

Q2

Product 2

11000

33000

2006

Q2

Product 3

10000

33000

This is how the calculation looks when made explicit in a table:

Year

Quarter

Revenue

2005

Q1

41000

2005

Q2

50000


Min:

41000

2006

Q1

45000

2006

Q2

33000


Min:

33000

In Web Intelligence formula syntax, this can be expressed as either

Min ([Revenue] In ([Year];[Quarter])) In ([Year])

or

Min ([Revenue] ForAll ([Product])) In ([Year])

The input context appears inside the function parentheses and the output context appears after the function parentheses. The set of dimensions in each context is itself enclosed in parentheses and the dimensions separated by semi-colons.

You use the context operators In, ForEach and ForAll to set up input and output contexts. You can also use context keywords to specify the data referenced in the contexts.

For more information, please download the manual from help.sap.com and read chapter 3 - Understanding calculation contexts.