cancel
Showing results for 
Search instead for 
Did you mean: 

Why WebI cannot add key figures automatically(based on BW query)??

Former Member
0 Kudos

Hello Guys

I got a problem. When creating an WebI based on a BW query, when I drag and drop dimensions into the report, the key figures cannot add automatically, just list all the line items.

for example, I have 10 sales orders, and the customers are the same. if I add customer field and order value into a webi, it will list ten rows, instead of one row with aggregated order value. But you know, if it is in BW, there will be only one row.

However, if the WebI is based on BW Cube, WebI can add key figures automatically.

Can the WebI based on query behave the same?

Thanks a lot!

Best Regards

Xiao

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Xiao,

Thinking of it in SQL terms (easier to grasp than MDX for most!):

If your measure is simply defined as the column name, then it will not create a group by.

If you have defined your object as:

SALES_TABLE.ORDER_VALUE

Then the select statement generated would be

SELECT 
 SALES_TABLE.ORDER_VALUE
FROM
 SALES_TABLE

This would return your ten rows.

However, if you define your measure as:

sum(SALES_TABLE.ORDER_VALUE)

Then the select statement generated would be

SELECT 
 Sum(SALES_TABLE.ORDER_VALUE)
FROM
 SALES_TABLE

This would return your single row.

The general rule is that measures in universes should use aggregate functions.

Similarly, if you were to include an ORDER_DATE object, then your select statements would be:

SELECT 
 SALES_TABLE.ORDER_DATE,
 SALES_TABLE.ORDER_VALUE
FROM
 SALES_TABLE

and

SELECT 
 SALES_TABLE.ORDER_DATE,
 Sum(SALES_TABLE.ORDER_VALUE)
FROM
 SALES_TABLE
GROUP BY
 SALES_TABLE.ORDER_DATE

BusinessObjects automatically generates the group by in the SQL statement because you've defined the measures with the sum() aggregate.

Hope that clears it up for you.

Regards,

Mark

Former Member
0 Kudos

WEBI will not aggregate by default, you have to force aggregation levels in the formula definition. However, it does sound like you have included an object in the drill down of the report block which is causing the aggregation to apply at a non-aggregated level.

Try:

=sum([Order Value])

Former Member
0 Kudos

HI,

Even i am also facing the same problem....

Khaled,

This formula should be written at a universe level or webi....?. level.. if it is in webi level whether we want to create a variable for that.

Former Member
0 Kudos

Hi ...anyone please reply on this thread

0 Kudos

Hi,

This is possible at both places,

Universe : you should set projection level aggregation

Webi: by creating new variable.

Regards,

Sachin