-1

I tried using IIF condition to sum the amount based upon few conditions in SSRS.

But it shows #error when executing the report as It is the multivalued parameter. Below is the sample query I used:

=IIF(Parameters!p_Colortypes.Value="-1" OR Parameters!p_Colortypes.Value = "2",SUM(Fields!amt.Value),"").

Can anyone suggest me the logics to sum the amount based on value of the multivalued parameter ?

Thanks, Selvi.P

Selvi.P
  • 31
  • 7
  • 3
    You are trying to sum and empty string where the criteria is not met. Try replacing the `""` with `0` Also check the parameter type is Text as you are comparing to text, if it's an integer remove the quotes from the `"-1"` and `"2"` – Alan Schofield Oct 30 '18 at 15:40
  • Is there a message in the Error List at the bottom? I don't see anything that would cause an error. Is amount a numeric value? – Hannover Fist Oct 30 '18 at 16:28
  • Other users marked your question for low quality and need for improvement. I re-worded/formatted your input to make it easier to read/understand. Please review my changes to ensure they reflect your intentions. But I think your question is still not answerable. **You** should [edit] your question now, to add missing details (see [mcve] ). Feel free to drop me a comment in case you have further questions or feedback for me. – GhostCat Oct 30 '18 at 18:58
  • Hi Alan, I tried giving without quotes. That doesn't worked. I'll check replacing "" with 0 and tell. Thanks – Selvi.P Oct 31 '18 at 05:45
  • Hannover Fist, No Error message. #error returned in the result set. Thanks. – Selvi.P Oct 31 '18 at 05:46
  • Ghost cat, Your changes looks good. Thanks – Selvi.P Oct 31 '18 at 05:47

1 Answers1

0

I cannot comment sadly as I fon't have enough rep.

Alan's Comment pretty much sums up what I was going to suggest:

Try replacing the "" with 0 Also check the parameter type is Text as you are comparing to text, if it's an integer remove the quotes from the "-1" and "2"

IIF Statements need to use the same datatype for each potential result from the logic, as each row in a result set needs to be the same datatype (much like a case statement in T-SQL).

I would add however that if it doesnt work you could try moving the SUM to outside of the IIF:

=SUM(IIF(Parameters!p_Colortypes.Value=-1 OR Parameters!p_Colortypes.Value = 2,Fields!amt.Value,0))

I have taken liberties with the code and assumed that you are using numerical values.

More info on this post that may help:

Carrying out a SUMIF like operation using SQL Server Report Builder

Iyla
  • 41
  • 5
  • Thanks Manga John. I found issue in the query. The logic we using are damn perfect. Issue is the param is Multivalued parameter. So if I choose more than one value, it is not summing up . For example, I need total amount for color red. I choose Red and orange color in the color parameter selection. Now it should display the total amt for red color alone whereas it is displaying #error Msg. Choosing red color alone solves the issue. Can you suggest me an idea to solve this. Thanks – Selvi.P Nov 01 '18 at 15:48
  • After digging into it, it seems that for multivalue parameters when calling in an IIF you have to specify in the array which value you are looking at using (X) at the end of the .Value (where X is the item in the parameter array s0 0 for the first, 1 for the second etc): =SUM(IIF(Parameters!multi1.Value(0) = -1 OR Parameters!multi2.Value(0) = 2,1,0)) There may be a better way of doing this that others could help, but a really inefficient way would be to use nested ifs or some other logic to loop through each item in the parameter array, but I can't help but feel there may be a better way – Iyla Nov 01 '18 at 16:02
  • Some functions in here may help if you scroll down to the multi value ones: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms157328(v=sql.100) – Iyla Nov 01 '18 at 16:18
  • Thanks Manga John for the efforts you taken to solve the issue. The methods you suggested all are good.Using Fields instead param solving the issue. Like, IIF(Fields!colors.value="Red",Sum(amt),0). – Selvi.P Nov 09 '18 at 08:37