BusinessObjects Board

what is the actual difference between using Sum() function

building semantic layer
#1

Hi Friends,
can any bod explain me the actual difference between using a sum function at Table level and Designer Level and Report Level…

Thanks in advance
mkmreddy

mkmreddy (BOB member since 2006-03-21)

#2

If you define your measures like Sum(SCHEMA.TABLE.COLUMN)
you are pushing grouping to DB, if not, the projection will be at report level using the function that you provide at properties.
Sum at report level can be used to calculate ratios and other measures that cannot be expressed as a simple aggregation method at universe level.

Astro :argentina: (BOB member since 2007-02-15)

#3

Hi Astro,
can you please explain a litttle bit more,
here i have one doubt suppose what happens to the result ,if you apply sum at DB level as well as Universe level.

mkmreddy (BOB member since 2006-03-21)

#4

It’s quite simple, really. The sum() in the select clause goes to the database and aggregates with a GROUP BY clause so that the report returns a smaller set of rows. Then when you “slice and dice” on the report the Sum for the projection function rolls the measure value up or down as you manipulate the report.

Suggestion: Make a universe, and play with the features. Change the projection function from SUM to NONE and slice and dice a report and observe what happens. Remove the sum() from the select and check the row counts that are returned from your query.

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

Let me add to this. So which would be a best practice grouping at the db level which would return lesser number of rows or retrieving all the rows and perform aggregartion at the report level? Any known pros & cons please!!
TIA.

BOB_DW (BOB member since 2004-08-26)

#6

Having database to do as much work as possible is always better than applying a load to PC.

So when it is possible do an aggregation in DB not in a report.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#7

See this post for a detailed explanation.

Andreas :de: (BOB member since 2002-06-20)

#8

Thanks Andreas your other post has Very Detailed explanation. I still would like to know or find any scenario wherein the business will be needing all rows retrieved from the db and do the projection at the report level. I am sure this happens quite often than mentioned. Can you think of a business justification? Appreciate your response.

BOB_DW (BOB member since 2004-08-26)

#9

I would not be too sure. :slight_smile: And besides, if you need the detailed rows you can get them if you add something “unique” to the query. Total sales by item can be broken down by SKU number by including the SKU number in the query. Total sales can be broken out by customer by including the customer ID in the query.

The intent is for measures to have an aggregate function. The implementation is up to you. 8)

Dave Rathbun :us: (BOB member since 2002-06-06)

#10

So far as business justification goes, I build numerous reports that have a summary table at the top (using report level summation) followed by detailed case listings. If I didn’t need the detailed listing then the advantage of using Universe Object aggregation would come to the fore as the data provider SQL would return far fewer rows.

This blended combination of universe and report level aggregation is one of the key features of Business Objects

Regards
Steve

Steve Morris :uk: (BOB member since 2004-12-14)