Ugly Case Statement -- Help!

[mstrgrl]

This case statement works (29 total passes of SQL)when I run the SQL generated by MSTR; however, on the MSTR report the value shows as zero(0). The actual values showing when I query the temp tables in Oracle are long like 0.370419202, 0.260209601 and I'm thinking maybe there are too many decimal places. I tried to TRUNC within the Case statement without any changes. Can someone suggest a fix??

THANKS!!!

ApplySimple(&quot;CASE WHEN #0>1.95 THEN 1.85 WHEN #0<1.95 AND #0>1.4 THEN #0-.15 WHEN #0<1.4 AND #0>.75 THEN #0 WHEN #0>.75 THEN #0+.20 WHEN #0<.75 THEN #0+.15 ELSE 0 END&quot;, [D - Sales Index])

 

[mstrgrl]

Some additional details:

I added the ROUND function in Oracle SQL, and it gives me exactly what I want:

SELECT organization_hierarchy_id
,product_id
,wjxbfs1
,CASE WHEN ROUND(wjxbfs1,2)>1.95 THEN 1.85
WHEN ROUND(wjxbfs1,2)<1.95 AND ROUND
(wjxbfs1,2)>1.4 THEN ROUND(wjxbfs1,2)-0.15
WHEN ROUND(wjxbfs1,2)<1.4 AND ROUND
(wjxbfs1,2)>0.75 THEN ROUND(wjxbfs1,2)
WHEN ROUND(wjxbfs1,2)<0.75 THEN ROUND
(wjxbfs1,2)+0.20 ELSE 0 END
FROM ddw_rpt.zzt3c0208qfmd009

When I cut and past this into my CASE statement in MSTR, I still get 0 showing on the report (formatted to fixed,2:

ApplySimple(&quot;CASE WHEN ROUND(#0,2)>1.95 THEN 1.85 WHEN ROUND(#0,2)<1.95 AND ROUND(#0,2)>1.4 THEN ROUND(#0,2)-.15 WHEN ROUND(#0,2)<1.4 AND ROUND(#0,2)>.75 THEN ROUND(#0,2) WHEN ROUND(#0,2)>.75 THEN ROUND(#0,2)+.20 WHEN ROUND(#0,2)<.75 THEN ROUND(#0,2)+.15 ELSE 0 END&quot;, [D - Sales Index])

Am I missing something really simple here??

Thanks!

 

[entaroadun]

Could you attach your consolidation pass and your CASE statement pass?

 

[HrvojeSunjic]

mstrgrl,
just a stab in the dark, but i know because i've been there: is your metric display format ok?

 

[mstrgrl]

I got the problem fixed. I had my lowest level attribute in report objects but not on the report and I was pulling metrics at that level so this showed zero. As soon as I added this lowest level attribute to the report grid the calculation showed. ((the ROUND did work)). The case stmt syntax was correct.

Thanks for your help all !!