on 11-12-2010 1:04 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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])
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.