IIF Statements in Reporting Services

Afternoon All,
I'm working on a report and I'm trying to include an IIF Statement
since there's a possiblity that I could get a division by zero error.
Here is my calculation:
=IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
Also tried:
=IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
This seems to work if the calculation is not as complex but doesn't
like this one at all.
I would appreciate any suggestions.
Thanks,
JodyFor complicated expressions I would do such calculations in custom code.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
> Afternoon All,
> I'm working on a report and I'm trying to include an IIF Statement
> since there's a possiblity that I could get a division by zero error.
> Here is my calculation:
> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> Also tried:
> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> This seems to work if the calculation is not as complex but doesn't
> like this one at all.
> I would appreciate any suggestions.
> Thanks,
> Jody
>|||Reporting services equates both sides of an if before it execute it there
for
=IIF( 1=0,0,10/0)
will give an error therefore the only solution I have found is to use custom
code as suggested by the previous poster.
Thanks
Dale
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:O7Rw3sWHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> For complicated expressions I would do such calculations in custom code.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
> news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
>> Afternoon All,
>> I'm working on a report and I'm trying to include an IIF Statement
>> since there's a possiblity that I could get a division by zero error.
>> Here is my calculation:
>> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> Also tried:
>> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> This seems to work if the calculation is not as complex but doesn't
>> like this one at all.
>> I would appreciate any suggestions.
>> Thanks,
>> Jody
>|||Thanks for the help... I created a custom function that fixed my
issues... Here is my code in case it can help someone else down the
road.
Public Function ConvertSecToTime(ByVal NumSec As Double, ByVal Calls As
Double) As String
Dim theTime As String
If NumSec = 0 Or Calls = 0 Then
theTime = "0:00:00"
Else
theTime = (NumSec \ Calls) \ 3600 & Format(((NumSec \ Calls) \ 60) Mod
60, "\:00") & Format((NumSec \ Calls) Mod 60, "\:00")
End If

IIF Function in Dataset

I am trying to build my dataset WHERE clause with the IIF function.
I have 4 parameters that include a "ALL" option and that is what I am trying
to cater for.
Have done is successfully when there is only one parameter involved,
IIF (Parameters!Rep.Value = "0", " WHERE A.Region = '" &
Parameters!Region.Value & "'" , " WHERE A.Region = '" &
Parameters!Region.Value & "' AND A.SLPRSNID = '" & Parameters!Rep.Value &
"'")
but now I have multiple conditions ... have tried the following ...
" WHERE (CustomerServiceRatesReport.Region = @.Region) " &
IIF (Parameters!Rep.Value = "0", "" , " AND
CustomerServiceRatesReport.SLPRSNID = '" & Parameters!Rep.Value & "'") &
IIF (Parameters!Depot.Value = "0", "" , " AND
CustomerServiceRatesReport.OFFID = '" & Parameters!Depot.Value & "'") &
IIF (Parameters!ContractType.Value = "0", "" , " AND
CustomerServiceRatesReport.AV_Contract_Type = '" &
Parameters!ContractType.Value & "'") &
IIF (Parameters!CustomerNumber.Value = "0", "" , " AND
CustomerServiceRatesReport.CUSTNMBR = '" & Parameters!CustomerNumber.Value &
"'")
The above does not give me any errors, but neither does it give me any
results on the report - no matter what my parameter selections are.
Any help / pointers or suggestions on how I can make this work or
alternatives would be much appreciated.
Thank you,
SmeSme,
One thing that I do in these situations is place a textbox on my report
that contains the expression in your dataset, that way you can see what
SQL the dataset is executing.
Should give you a clue about what the query syntax ends up being after
all the expressions are evaluated.
Andy Potter|||Hi Andy,
Thank you for your reply. I will most certainly try that.
Is there any error in the syntax though?
What would the basic syntax be for a multiple IIF function in a query?
Kind Regards,
Sme|||Just in case someone needs the solution ... quite simple actually ...
WHERE (CustomerServiceRatesReport.Region = @.Region) AND
ISNULL(RTRIM(CustomerServiceRatesReport.SLPRSNID ),'') =COALESCE(@.Rep,RTRIM(CustomerServiceRatesReport.SLPRSNID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.OFFID),'') =COALESCE(@.Depot,RTRIM(CustomerServiceRatesReport.OFFID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'') =COALESCE(@.ContractType,
RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.CUSTNMBR),'') =COALESCE(@.CustomerNumber, RTRIM(CustomerServiceRatesReport.CUSTNMBR),'')