February 1, 2011 at 4:56 pm
Hi
I would like to ask for advice about some iif statment i am using in Reporting Services 2008.
when i am writing this statment i get an error
=IIF
(
Sum(Fields!PACB.Value)=0,0,Sum(Fields!RCB.Value) / Sum(Fields!PACB.Value))
)
so i have written this one:
=IIF
(
Sum(Fields!PACB.Value)=0,0,
Sum(Fields!RCB.Value) / IIF(Sum(Fields!PACB.Value)=0,1, Sum(Fields!PACB.Value))
)
now i am having this statment:
=IIF(SUM(Fields!AB.Value) = 0,0,SUM(Fields!AB.Value*Fields!GB.Value)/IIF(SUM(Fields!AB.Value) = 0,1, SUM(Fields!AB.Value)))
=IIF(SUM(Fields!AB.Value)=0,0,SUM(Fields!PCBBN.Value)/(100*SUM(Fields!AB.Value)))
which gives me #Error.
Any advice how to make it better will be appriciated
Thank You
Ori
February 8, 2011 at 8:08 am
there is always more info in your Output of Visual Studio on any error
February 9, 2011 at 6:14 am
Hi
The error is on runtime, when i run the report i can see "#error".
when i save the expression there is no error within the visual studio
thank you
ori
February 9, 2011 at 6:20 am
You have to check in your Visual Studio, usually left bottom corner. See screenshot.
Open Error list or Output.
February 9, 2011 at 6:31 am
Add this code to you report in the code section (report properties)
PUBLIC FUNCTION NDZ(Numerator, Denominator, DZResult) AS Double
IF Denominator = 0 THEN
RETURN DZResult
ELSE
RETURN Numerator / Denominator
END IF
END FUNCTION
Then here's how you call it :
=VAL(code.NDZ(SUM(Fields!Marge.Value), SUM(Fields!Amount.Value), 0) * 100)
The val avoids a weird error in subtotals and grand totals in matrices.
February 8, 2012 at 3:04 pm
Use it in a text box of a table, apply Grouping on the entire row. This should work
=IIF(Sum(Fields!PACB.Value)=0, 0,Sum(Fields!RCB.Value) / Sum(Fields!PACB.Value))
or
=IIF(Fields!PACB.Value=0, 0,Sum(Fields!RCB.Value) / Sum(Fields!PACB.Value))
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply