SSRS- Issue with iif containing monthname function

0
votes

I have a data set that I am trying to force everything >12 months ago into a single group. I am trying to do this with a SQL case when that assigns all months and year >12 months ago a int value of 0 but when I try to put this into SSRS it doesnt work.

I have been trying to use this formula but it does not work unless I remove the 'MonthName' function. =iif(Fields!ASGN_SUB_DUE_MONTH.Value > 0,monthname(Fields!ASGN_SUB_DUE_MONTH.Value) & " " & Fields!ASGN_SUB_DUE_YEAR.Value
,"More than 12 months ago")

But this formula works =iif(Fields!ASGN_SUB_DUE_MONTH.Value > 0, monthname(iif(Fields!ASGN_SUB_DUE_MONTH.Value > 0 and Fields!ASGN_SUB_DUE_MONTH.Value < 13, Fields!ASGN_SUB_DUE_MONTH.Value,1)) & " " & Fields!ASGN_SUB_DUE_YEAR.Value
,"More than 12 months ago")

Does anyone know why and how I can resolve. The second function works but I'm not comfortable leaving it in the reporting incase the second part of the imbedded iif ever functions.

Thanks in advance

1
reporting-services
Were you having trouble getting the dataset to work? I can give you some pointers on that TSQL CASE statement if you post what you've tried. - Sonny Childs
That Sonny, the issue isnt with the way the CASE is written, just with the output. Having int values not 1-12 was causing the issues. - Franz Stoneking
You said you were trying to do this with a SQL CASE statement. Why don't you add this case statement back into your SQL query in your dataset? In many cases, it's easier to manipulate data in the dataset itself. Luckily, you can have as many datasets as you need to do your report. - Sonny Childs

1 Answers

0
votes

The issue is related to my case statement and how the iif processes in SSRS, from my subsequent research. The true statement needs the imbedded iif because its evaluating all entries vs all parts of the expression, so even when i have "Month" values of -1 or 0 that wouldn't hit the iif portion with MONTHNAME() its still evaluating it and causing an error.