Author |
Topic |
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 02:22:09
|
Hi Guys,I have an SSRS report. i have a fields calculation using iif, intead of doing this in field calculation i will do it in the query.how could i translate this in case statement.=IIf(Fields!TOTAL.Value = 0, FormatPercent(0), IIF(Fields!CoresReclaim.Value=0, FormatPercent(0), FormatPercent(Fields!CoresReclaim.Value/Fields!TOTAL.Value,0)))something like this.. SUM(Case When ASUPRODLINEID='Line1' and d.total=0 Then 0 ???? Thank you in Advance . |
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 02:38:48
|
Just come up with this solution. not yet tested.SUM(Case When ASUPRODLINEID='Line1' and d.total=0 or d.CORESQTY=0 Then 0 else d.CORESQTY/d.total End) as CoresUsage1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 03:04:21
|
should beSUM(Case When ASUPRODLINEID='Line1' and (d.total=0 or d.CORESQTY=0) Then 0 else d.CORESQTY/NULLIF(d.total,0) End) as CoresUsage1------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 03:12:10
|
Thank you for your prompt reply Visakh.what if i did not put a clise and open patenthesis for this (d.total=0 or d.CORESQTY=0)what is the issue? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 03:25:56
|
If you dont put parenthesis it will ignore the other conditions (eg. ASUPRODLINEID='Line1' ) when d.CORESQTY=0 is satisfied because of the orI hope what you were looking for was either of total= 0 or d.CORESQTY=0 being true and ASUPRODLINEID='Line1' being always true------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 03:56:25
|
Thank you very much. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 04:01:42
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 04:06:49
|
Hi Visakh, additional query.there are two condition.=IIf(Fields!requiredQty.Value = 0, FormatPercent(0), IIf(Fields!requiredQty.Value <= Fields!TOTAL.Value, FormatPercent(0), FormatPercent((Fields!requiredQty.Value-Fields!TOTAL.Value)/IIf(Fields!requiredQty.Value=0,1,Fields!requiredQty.Value)))) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 04:13:08
|
so what was the question?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 04:17:32
|
could not fgiure out on how to translate this in case statement with multiple condition. same as my previous sample..SUM(Case When ASUPRODLINEID='Line1' and requiredqty=0 then 0, ?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 04:22:24
|
SUM(Case When requiredQty > 0 AND requiredQty > TOTAL THEN (requiredQty - TOTAL)/requiredQty ELSE 0 END) as ...------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 04:27:37
|
Thank You. |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-04 : 21:54:57
|
Tried to convert this into Case but i got an error. =IIf(Fields!requiredQty.Value = 0, FormatPercent(0),IIf(Fields!requiredQty.Value <= Fields!TOTAL.Value, FormatPercent(0), FormatPercent((Fields!requiredQty.Value-Fields!TOTAL.Value)/IIf(Fields!requiredQty.Value=0,1,Fields!requiredQty.Value))))--Translate to SUM(Case)SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0 else (d.RequiredQty-d.total)/SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1 else d.RequiredQty End)) as CoresUsage1 Incorrect syntax near ")" Thank you in Advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 22:54:58
|
SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0 else (d.RequiredQty-d.total))/NULLIF(SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1else d.RequiredQty End),0) as CoresUsage1I dont think this is equivalent to IIF though as applying sum will mean aggregating the 1 conditions which might give you a different result when you've multiple records within each group------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-05 : 01:09:36
|
Hi Visakh.Thank you for the reply.I got this error.Msg 102, Level 15, State 1, Line 135Incorrect syntax near ')'. |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-02-05 : 01:13:51
|
just place an End of the open and close parenthesis.. SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0 else (d.RequiredQty-d.total) end)/NULLIF(SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1 else d.RequiredQty End),0) as CoresUsage1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 02:29:30
|
quote: Originally posted by Villanuev just place an End of the open and close parenthesis.. SUM(Case When ASUPRODLINEID='Line1' and d.RequiredQty=0 and d.RequiredQty <= d.total Then 0 else (d.RequiredQty-d.total) end)/NULLIF(SUM(Case When ASUPRODLINEID='Line1' and d.REQUIREDQTY=0 Then 1 else d.RequiredQty End),0) as CoresUsage1
coolhope you saw what i told reg. the equivalence of above against IIF. make sure your group doesnt have mutiple records other wise above will give different results than the IIF------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|