Measures should not be aggregated

[Former Member]

Hi,

We are creating a webi report on the top of BEx query in BI 4.0.
The measure values are aggreagated in the report level when we run a query. But we need a row for each transaction.

The report needs this kind of data as there is a formula defined at report level, which gives a different value when applied on each transaction from the value applied on the aggregated data.

A new dimension object is created at report level for the measure, but as it is aggregated at report, this doesnt help.

Could you please let us know how to get each transaction with out aggregation.

Thanks in advance.

Priya

[Former Member]

Hi Priya,

As its on BEx query, have you removed the L00 objects? You need to take only L01 objects at the report level which will give you values for each row, L00 will give you aggregated values.

Let me know if this helps.

thanks.

Sujit

[Former Member]

Hi sujit,

Webi report is on the top of BEx query in BI 4.0. Here there are no L00 and L01 objects seperately.

Thanks,

Priya

[Former Member]

Hi priya

What i understood is you are getting Grand total values for each and every transaction. so you need to get aggregated values for each and every transactions

yours table is like this

material qty

AAA     10

bbb      10

ccc       10

You would like to see the table in report

material qty

aaa     3

bbb     4

ccc     3

right ??

My suggestion is to create a universe at top of bex query and at measure goto properties and change the aggregation to sum . By default it is database delagated.

Hope this helps u

Let me know if there any issues .

[Former Member]

Hi Sunil,

Thanks for your reply.

The issue is,

In crystal report data is fetched as

Material Qty1 Qty2 @Formula(Qty1*Qty2)

aaa     5     2     10

aaa     2     0     0

bbb     1     2     2

bbb     2     2     4

Aggregated data is

aaa     10+0 = 10

bbb     2+4=6

But in webi the aggregated data is as

Material Qty1 Qty2 @Formula(Qty1*Qty2)

aaa     7     2     14

bbb     3     4     12

Now both the results are incorrect and there is data mismatch in the reports.

I need the data to be aggregated as Crystal report in webi also.

I hope you understand the issue.

Please let me know for any further clarifications.

Thanks,

Priya

[Former Member]

Hi Sunil,

Thanks for your reply.

The issue is,

In crystal report data is fetched as

Material Qty1 Qty2 @Formula(Qty1*Qty2)

aaa     5     2     10

aaa     2     0     0

bbb     1     2     2

bbb     2     2     4

Aggregated data is

aaa     10+0 = 10

bbb     2+4=6

But in webi the aggregated data is as

Material Qty1 Qty2 @Formula(Qty1*Qty2)

aaa     7     2     14

bbb     3     4     12

Now both the results are incorrect and there is data mismatch in the reports.

I need the data to be aggregated as Crystal report in webi also.

I hope you understand the issue.

Please let me know for any further clarifications.

Thanks,

Priya

[Former Member]

Hi Priya

These were problems of calculation context . Suppose you are using material grp and material then we have to use foreach and in operators for calculating

let me give u a small hint , you can wrk around that .

sum(([Qty1]foreach(material grp,material)  * [Qty2]foreach(material grp,material)) foreach(material grp,material)) in (Material grp)

I know it sounds confusing , try to split each and every formula and wrk around and i am sure u  will get the result

Hope this helps u

Thanks

Sunil

[former_member188911]

Have you tried to format the table and select "Avoid duplicate row aggregation" ?

[Former Member]

I have tried this, but this is not giving all the transactions. Even this is displaying aggregated data only.

The same report when created using crystal report tool, displaying all the transactions and formula is applied for each row.

But same formula when used in webi, the formula is applied on aggregated row, and there is mismatch of data between both the reports.

Could you please let me know any solution for this.

[Former Member]

Does any one face the above issue. Does anyone have resolution for this issue.