Group and Sum in Webi

[Former Member]

Hi,

I have three objects Business Unit, Department and Revenue

I would like to group by Business Unit and Departement; sum of Revenue.

How to do this in report level?

Please let me know?

Thanks and Regards,

Manjunath N Jogin

[Former Member]

Hi Former Member

I think what Arijit Das said i correct. Just qualify that object as a Measure and apply the aggregation for that revenue in the Universe. That will work fine.

[arijit_das]

Add three objects in the query and run the report. Then in report level, apply break on Business Unit and Department. I assume that the aggregation is set to sum for Revenue in the universe.

[Former Member]

Hi Pumpactionshotgun ,

I did same thing in webi but revenue field is diplaying all records.

It is not aggreagting (sum) based on Business Unit and Departement.

How to do 'aggregation seting for Revenue in the universe'

I am waiting your replay.

Thanks and Regards,

Manjunath N Jogin

[arijit_das]

what is the aggregation set for the measure object in the universe?

did you try using the sum function in webi for Revenue ?

[Former Member]

The universe I could not touch becuase that is used for other reports as well.

So I need to do these things in webi level.

Please give me some suggestions

[arijit_das]

try using sum function in webi for revenue.

=Sum([Revenue])

[Former Member]

can you please tell me how to create this?

[arijit_das]

Use the formula mentioned earlier in the Revenue column. Then insert break on Business Unit and then on Department.

[Former Member]

Thank you very much...it is working fine

just I wanted to know is it possible wihtout using formula?

in Crystal Reports, we can do without using formula.

Pelase let me know

[arijit_das]

you can set default aggregation as sum in the universe level.

[Former Member]

Ok...

1. Could you please tell me steps to be required to create aggregation in universe level?

2. If I create aggregation for revenue field in univese, does it affect other reports which are already connected to the universe?

for example , one report is just dispalying Business unit, deaprtment, revenue without group and sum.

[arijit_das]

1. steps to apply aggregation for measure:

a. import the universe in designer.

b. double click on the measure object.

c. in properties tab, select the aggregation function as sum.

d. apply and click ok.

e. save and export the universe.

2. yes. it will affect the reports created on top of the universe and which uses the revenue object. instead of showing all records, this will group the revenue for the dimension combinations.

[Former Member]

You gave me deatil information...

Thank you very much

[Former Member]

Hi,

I got correct answer

so I want to close this third but I am unable to close.

I could not find any options.

Regards,

Manjunath N Jogin

[Former Member]

I think the answer by Arijit on 'apply aggregation for measure' is slightly incorrect for a object to behave as a measure.

For your object [Revenue] to behave as a measure i.e do the summing; you need to put the prefix         SUM([Revenue]) in the SQL section of the 'Definition' tab in object property.

What Arijit explained is how the measure object will behave/aggregated when any of the dimension object is removed from the block.

Regards

NK