Hi,

If the return value is negative (e.g. L1 = -300, L2 = -500, L3= 300) on some
dimension members, how do I convert them into absolute value before they sum
up? So the aggregation value would be 1,100 (300+ 500+300) instead of 500
(-300 + 500+300)? Thanks in advance for help.

-Charles

If you are using AS 2000 put "ABS(<Measure Column>)" in the Souce Column
property for the measure.

If you are using AS 2005 you can create a calculated column in the DSV
with this expression and base your measure off this calculated column.

This approach basically gets SQL Server to send an absolute value to AS.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <B53ABA50-AC3C-49FB-80C4-26BBC2198366 (AT) microsoft (DOT) com>,
Chuck (AT) discussions (DOT) microsoft.com says...

Thanks for your reply. My problem here is a bit more complex. I need to
aggregate absolute value on each dimension member on a dimemsnion level. Here
is the sample data.

L1 = 200
L1 = 400
L1 = -300
L2 = 100
L2 = -300
......

The standard SUM aggregation issue here is 100 (200+400-300+100-300). But I
don't want to sum them up directly. Instead, I need to sum absolute value of
L1 and L2 individually. The right calculation is below and the value should
be 500.

L1 = 200 + 400 - 300 = 300
L2 = 100 + (-300) = -200 * -1 = 200

==> L1 + L2 = 500 (300 + 200)

Can you suggest me how to do that in AS function or in MDX calculated
members? Thanks.

-Charles

"Darren Gosbell" wrote:

OK, that's a slightly different issue to what I thought it was

Have a look at custom rollups. AS2k has a customRollupFormula property
on the level, AS2k5 has a CustomRollupColumn associated with each
attribute.

In psuedo code the rollup would look something like the following.

, <Dimension>.Level2),abs(<measure>)
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <A21F9BFC-A328-4FC5-A2D1-2C9BD0FF3400 (AT) microsoft (DOT) com>,
Chuck (AT) discussions (DOT) microsoft.com says...

It works on the level of ONE dimension based the calcauted member in the
report. Thanks. But it ran into another issue if I bring other dimension
(e.g. Div dimension) in. There are many dimensions (e.g. Div dim and
Production dim) associated with this measure/calculated member in the fact
table. For example,

Div Product value
111 L1 200
111 L1 - 400
112 L2 100
112 L1 -200

Based on the formula it sums up abs value only on each production member, we
get

Div Product value
111 L1 400 ((200+ (-400) + (-200) ) * -1
112 L1 400 ((200+ (-400) + (-200) ) * -1
112 L2 100

Total Div value
Div value
111 400
112 500

But the final value I need is to sum up absolution value of each production
members (i.e. L1, L2 , ...) associated with the members in Div dimension. So
the Div value should be

Correct Total Div value
Div Value
111 200 ((200 + (-400)) * -1
112 100 ((100 + (-200)) * -1

How do I make it more dynamically with other dimensions? Thanks for help.

-Charles


"Darren Gosbell" wrote:

It could be that we are dealing with a granularity issue here.

Maybe you are better off using SQL Server to create a second fact table
with the absolute values at the level that you need them. Then use a
virtual cube (AS2000) or multiple measure groups (AS2005) and create a
calculated measure to choose which of the base measures to display.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <A6444C82-5F7E-4215-977E-783DE7D522E5 (AT) microsoft (DOT) com>,
Chuck (AT) discussions (DOT) microsoft.com says...

Thanks Darren. It looks like that's the only way to go since I can't build in
many logics in that particular absolute value with other dimension
relationships.

-Charles

"Darren Gosbell" wrote: