0

I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:

Category   Month   Amount
A          Jan       20
A          Feb       25
A          Mar       10
R          Jan       15
R          Feb       50
R          Mar       55

On the report I need:

    Jan   Feb   Mar
A   20    25    10
R   15    50    55

I have tried placing this expression in each group row column, for example, in the Jan column it would be: 

=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)

But I get the result 0, like false result.

Could you please let me know how to solve this problem.

Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
user10581977
  • 29
  • 5

2 Answers2

0

If you need it as expression, the correct expression would be:

=Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))

But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value and into your column group Fields!Month.Value. And where the row and column group meets you can also drag and drop the Fields!Amount.Value because the default aggregate function in the matrix is Sum(). Which is exactly what you need Sum(Fields!Amount.Value)).

Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category. – user10581977 Nov 11 '18 at 18:56
  • @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something? – user10581977 Nov 11 '18 at 19:32
  • Your expression is correct`=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9)`. The only possibilty is that your `Fields!Month.Value` doesnt work. Is this field a `Date/Time`? Try `CDate(Fields!Month.Value)` and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct. – Strawberryshrub Nov 12 '18 at 05:21
  • Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you. – user10581977 Nov 12 '18 at 14:42
  • @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] https://stackoverflow.com/questions/1204179/does-the-iif-function-compute-both-paths-in-ssrs-or-is-it-short-circuited – user10581977 Nov 12 '18 at 16:59
0

SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with

=IIF(Fields!Category.Value = "A" ,1, 0)

Another with

=IIF(Fields!Month.Value = "Jan", 1, 0)

Another with 

=IIF(1=1,Fields!Amount.Value, 0)

When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g. 

=sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))
RET
  • 861
  • 6
  • 15