BusinessObjects Board

sum() in select statement is mandatory for measure object?

building semantic layer
#1

Hi ,
I have a doubt regarding measure object. Suppose I need to create an object for summing balance_amount .
I have created a measure object with the select statement as:
table_name.balance_amount
And in the tab have select SUM function when aggregated.

I would like to know whether it is mandatory to add the sum in the select clause or not? For example do I need to define the following definition in select statement of measure object as:
sum(table_name.balance_amount) and in tab need to select SUM function
when aggregated.

I am aware about what the SUM function does for the object. I only like to
know whether it is mandatory to have sum() defined in the select statement or not?

Your views will be appreciated.
Regards,
Anshuman

anshubit@gmail.com :india: (BOB member since 2007-07-03)

diff between Select SUM (Table. Field) & Select Table. F
#2

Measure objects should always be defined with an aggregate function in the SELECT part of the definition.

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

#3

Marek Chladny
also applied aggregate function in where clause for condition purpose.
for ex :
emp_count<=count(empid) and emp_designation=‘managerlevel’
latha

latha08 (BOB member since 2008-04-09)

#4

I don’t think that you need to define the aggregation function when you have already selected it from the drop down list in the “Properties” tab .

rimpa :india: (BOB member since 2008-04-14)

#5

Wrong!

Using aggregate function in the select box of the object definition will cause that the generated SQL will have the form

select dimension1, SUM(measure) from table group by dimension1

whilst the aggregate function set in the properties of the measure object will cause how the measure object will be aggregated in the report.

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

#6

I covered a number of different measure configuration scenarios in a presentation titled “ZEN and the ART of Universe Design” some years ago, and the information in it is still valid. Basically every measure should have an aggregate function and an appropriate / matching projection setting. If not, you are doing too much work on the client.

The presentation is available on the “Library” page from the Integra Solutions site; see signature for link.

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

#7

Appologies for my comment. Thanks for the information folks.

rimpa :india: (BOB member since 2008-04-14)

#8

Thanks: Marek , Latha , Rimpa and Dave for such a nice discussion. Dave
the presentation was really an useful one. Thanks again

anshubit@gmail.com :india: (BOB member since 2007-07-03)

#9

Basically, using an aggregation function in measure objects (in addition to the Projection setting) forces the grouping to the database level, thus reducing the number of rows coming back to the client and cutting down on the work that needs to be done on the user’s PC.

That’s the explanation in a nutshell. :wink:

Judy

JMulders :us: (BOB member since 2002-06-20)