cancel
Showing results for 
Search instead for 
Did you mean: 

WEBI Report - Exception aggregation..

Former Member
0 Kudos

Hi,

I'm new to WEBI and need help in figuring out how to achieve below reporting need.. We've below data set.

DocNo DocItem Material ConditionType Amount

1000 001 12345 XXXX 100

1000 001 12346 XXXX 100

1001 001 12345 XXXX 50

Report o/p should show

ConditionType Amount

XXXX 150

Same amount is always repeated for the different materials for the combination of DocNo, DocItem and ConditionType. Requirement is to calculate amount at condition type by considering amount only once ($100) for document 1000 and ($50) for document 1001.

Is it possible in WEBI?

Any help is greatly appreciated.

Thanks,

Milind

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

First you need to check whether your Measures aggregate is in "Database Delegate" or "Sum".

For this you can go to Designer -> Double Click on Measure -> Go to Properties tab.

If you found as "Database Delegate", then change it to "Sum".

Now when you remove the extra columns from WEBI report layout, it will by default get aggregated.

Hope this help.

Thanks,

Muiz Ahmed

Former Member
0 Kudos

Hi Milind Rane

You can acheive this like using a formula below

=sum(max(amount) in (DOcNO,DocItem,Material))

Hope This helps u

Sunil

Former Member
0 Kudos

Thanks Sunil / Muiz,

I tried using the formula provided by you. I believe you mean CondType instead of Material in your formula. It gives me MAX value when I'm at detail level within the data set. It seems not working if I remove the column Material from the report. It aggregates and gives me SUM as $250 instead of $150.

Requirement is to Consider MAX(amount) value in DocNo, DocItem, ConditionType and sum it up at ConditionType level.

Any idea..Do I also need to change the measure property as suggested by Muiz in addition to your formula?

Thanks,

Milind

Former Member
0 Kudos

Hi

Sorry for mistake in formula use. Type instead of material and check it.and also do what ahmedabad said .

Regards

Sunil

Former Member
0 Kudos

I tried the formula and also checked the measure property in universe. It is set as SUM as default. Still report o/p is aggregating all amounts and not the aggregation of max amount by DocNo, DocItem and ConditionType as needed when shown by conditionType

Any input.

Thanks,

Milind

Henry_Banks
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

try this - select your table outline - > Table properties

select "avoid duplicate row aggregation"

Does that do anything?

Regards,

H

Former Member
0 Kudos

Thanks.

It now giving me 3 records as below for my data set.

ConditionType Amount

XXXX $150

XXXX $150

XXXX $150

I need only one record to display in o/p.

Thanks,

Milind

Former Member
0 Kudos

Hi

Use the "first function" to display only one record.

Regards

Peri