15 Replies Latest reply: May 14, 2015 5:57 PM by SaaiiKirran pabbuu

# Group and Sum in Webi

Currently Being Moderated

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?

Thanks and Regards,

Manjunath N Jogin

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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.

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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'

Thanks and Regards,

Manjunath N Jogin

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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

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

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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.

• ###### Re: Group and Sum in Webi
Currently Being Moderated

try using sum function in webi for revenue.

`=Sum([Revenue])`

• ###### Re: Group and Sum in Webi
Currently Being Moderated

can you please tell me how to create this?

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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.

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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.

• ###### Re: Group and Sum in Webi
Currently Being Moderated

You gave me deatil information...

Thank you very much

• ###### Re: Group and Sum in Webi
Currently Being Moderated

Hi,

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

I could not find any options.

Regards,

Manjunath N Jogin

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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

• ###### Re: Group and Sum in Webi
Currently Being Moderated

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.