Modify the behaviour of an aggregate function

[Former Member]

Hi all,

I have to modify the behaviour of a measure (ex: MEASURE) object depending on the value assumed by a dimension (ex:DIMENSION) object.

When the dimension objects is a number the measure is sum([measure])

When the dimension is " " (in the DB there is a space when there is no value for that dimension object) the measure is 0 (the top would be putting "NA" instead of "0" but the measure is a numeric field so I'm not sure...)

I'm working on BO 6.5, SQL SERVER and I noticed that only the function "CASE WHEN THEN ELSE" is supported.

Can anybody tell me if I have to work on Web intelligence or Designer and write for me precisely the function I should use?

Regards

[Former Member · Accepted solution]

Hi Stratos,

Writing your code BO 6.5 restitutes me the error:

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of the varchar value '000220082140489077' overflowed an int column. Maximum integer value exceeded.State: 22003

I guess because of the absence of the aggregate function sum. Do you have any other suggestion?

Regards

[former_member142702 · Accepted solution]

Hi

what is the data type of you key figure in the database and in the BO universe?

All error messages indicate problems on the DB side.

I assume that you have to cast your key figure in the CASE statement to another data type than integer.

Or you can try the following

sum(CASE

WHEN table1.dimension_objects = ' '

THEN table2.measure_object

ELSE 0.0

END)

This should return a float value

Regards,

Stratos

[Former Member]

Precise and very helpful explanation, problem solved, star assigned.

Thank you.

Regards,

Riccardo

[Former Member]

Hi,

for ex. in the universe i tried to process this query (built directly in the select statement of a measure object):

CASE

WHEN table1.dimension_objects >0

THEN sum(table2.measure_object)

ELSE sum(table2.measure_object)*0

END

the problem is that BO can not process an aggregate function (sum) into another function (case) --> DBD EXCEPTION

So I don't know how to go on.

It's important to solve the problem in some way, in universe designer not in web intelligence.

Regards

[former_member142702]

Try this one:

CASE

WHEN table1.dimension_objects = ' '

THEN table2.measure_object

ELSE 0

END

Set the aggregation operator Sum on your key figure.

Regards,

Stratos

[Former Member]

Hi,

You can achieve this in the universe using case.

Please try,if you need any help please explain your query in detail.

Regards,

Ravi