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),'')