nested case - is there an simpler way to do this?

QtyPcntCompleted should never exceed 100 but I have no control over
what is in PostedQty or ForecastQty.
So if the calculation exceeds 100 I set it to 100.
What I'm wondering is can the nested CASE be replaced by a function?
QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
(((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
thanks in advance for your feedbackI don't think you need nested CASE statements here
SELECT 'QtyPcntCompleted'=
CASE
WHEN ForecastQty = 0 THEN CAST(0 AS decimal(5,2))
WHEN (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN
CAST(100 AS decimal(5,2))
ELSE CAST((((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) AS
decimal(5,2))
END
All the possible returned values from the CASE construct have to be of the
same data type, so I would cast the 0 and 100 values into decimal(5,2).
Otherwise, a fractional percentage may get truncated or rounded because the
CASE statement selected a datatype of integer.
"Gerard" wrote:

> QtyPcntCompleted should never exceed 100 but I have no control over
> what is in PostedQty or ForecastQty.
> So if the calculation exceeds 100 I set it to 100.
> What I'm wondering is can the nested CASE be replaced by a function?
> --
> QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
> (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
> ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
> --
> thanks in advance for your feedback
>|||Thanks Mark,
much appreciated
Gerard

negative values when calculating percentages

I get negative values for percentage calculation in a MDX query. The MDX query has a crossjoin between two sets containing calculated members from the same dimension, one of the calculated members being a percentage value. I'm not sure why some of the percentage values are negative.

Another problem I'm facing is that the percentage value is not being displayed as per the FORMAT_STRING property, in my Reports in Reporting Services 2005 that use the data generated by the MDX query.

Any help or suggestion is appreciated.

Hi. Can we see the MDX query you're using and a small data sample which provides the negative percentage?

PGoldy

|||

Hope this will give you an idea:

WITH
MEMBER [ITEMA].[ITEMA].itemmember
as

' (ITEMA.ITEMA.&[itemmember], [Measures].[NUMBER]) '

MEMBER [ITEMA].[ITEMA].TOTAL
as

' SUM(ITEMA.ITEMA.Members, [Measures].[NUMBERS]) '

MEMBER [ITEMA].[ITEMA].ITEMPERC
as

' Iif(IsEmpty([ITEMA].[ITEMA].TOTAL),0,([ITEMA].[ITEMA].itemmember / [ITEMA].[ITEMA].TOTAL)) ', FORMAT_STRING = '#.#%'

select [Measures].[NUMBER] on columns,

non empty crossjoin({[ITEMA].[ITEMA].MEMBERS,[ITEMA].[ITEMA].OTHERS,[ITEMA].[ITEMA].itemmember,[ITEMA].[ITEMA].TOTAL,[ITEMA].[ITEMA].ITEMPERC},
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]})) on rows
FROM [MyCube]

Data Snapshot:

ITEMA_1 ITEMA_2 ........ ITEMA_itemmember ITEMA_TOTAL ITEMA_ITEMPERC

- ITEMB_1
ITEMC_1 10 20 10 100 -0.1
ITEMC_2 5 6 0 150 0.0
ITEMC_3 0 1 2 4 0.5

- ITEMB_2
ITEMC_1 ...................................................................
ITEMC_2 ...................................................................
ITEMC_3 ...................................................................
+ ITEMB_3
+ ITEMB_4
.
.
.

|||

Hi. Thanks for the detailed query and example.

I don't see why you get a negative percentage, but I see you're using the calculated members to get values which are normally available in the cube without the use of a calculated member when you construct the right query. I think you should reconstruct your query to use the WHERE clause and re-define, and eliminate, some calculated members to get the correct results Here are my recommendations:

(1) Use the WHERE cluase to slice your qeury by the desired measure: WHERE (Measures.Number)

(2) Reference ITEMA dimension on the columns.

(3) Eliminate the following calculated members because they are not needed and we can derive the dsired values from normal intersections in the cube: (a) MEMBER [ITEMA].[ITEMA].itemmember, (b) MEMBER [ITEMA].[ITEMA].TOTAL

(4) Change the definition of MEMBER [ITEMA].[ITEMA].ITEMPERC to reference the correct cube intersections.

Assumption: ITEMA hierarchy has an "all" member, aggregation type for Measures.Number is SUM.

Here's the new query with the recommended changes:

WITH
MEMBER [ITEMA].[ITEMA].ITEMPERC AS
' Iif(IsEmpty([ITEMA].[ITEMA].[All ITEMA]),0,([ITEMA].[ITEMA].CurrentMember / [ITEMA].[ITEMA].[All ITEMA])) ', FORMAT_STRING = '0.0%'

NON EMPTY {[ITEMA].[ITEMA].MEMBERS, [ITEMA].[ITEMA].[All ITEMA], [ITEMA].[ITEMA].ITEMPERC} ON COLUMNS,
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]}) on rows
FROM [MyCube]
WHERE ([Measures].[NUMBER])

Hoe this helps.

PGoldy

Negative Numbers from Calculation

I am trying to get this calculation to work, but it keeps coming back with a negative number.

I think its something to do with making the

Column 1 & 2 are numeric figures then i calcating the datediff with another numeric figure. but i keep getting a negative answer? Any ideas?

Sum((isnull([Column1],0)) * ((isnull([Column2],0)) - cast(DATEDIFF(d,(isnull([date1],0)),(isnull([date2],0)))as float)/365.00)) as New Column

Here You can try this ...

Isnull(Sum([Column1]),0) * isnull([Column2],0)
- Isnull(ABS(DATEDIFF(d,[Date1],[Date2])),0)/365.00)) as New Column

You need not to apply Isnull inside the SUM function, by default the null values will be eliminated on aggergation.. But you can apply the ISNULL on the result of the SUM function..

Try to execute the following statement to get the 3 part of your expression which may help you where you missed your expression..

select

A = Isnull(Sum([Column1]),0) ,

B = isnull([Column2],0)),

C= Isnull(ABS(DATEDIFF(d,[Date1],[Date2])),0)/365.00))

as per your earlier expression the result = A * B - C => (A*B) - C is it correct?

or you want to achive A * (B-C) ... not clear buddy... try to find the result of the 3 expression and debug it..

|||

Can't seem to get this to work. Keep getting syntax error. Incorrect syntax near ')'.

|||

Try the following expressions...

Sum(isnull([Column1],0) * (isnull([Column2],0)
- Isnull(DATEDIFF(d,date1,date2),0)/365.00))

A= isnull([Column1],0) ,
B=isnull([Column2],0),
C=Isnull(DATEDIFF(d,date1,date2),0)/365.00

A= isnull([Column1],0) ,
isnull([Column2],0) - Isnull(DATEDIFF(d,date1,date2),0)/365.00

|||Spot on mate. Thanks. Worked a treat