February 2, 2016 at 3:37 am
Hi All,
So I have the following expression and it's throwing up the "Overload Resolution Failed no accessible iif "error.
So what I need to to is add all the app so far from this year plus what the apps count was for last year based on a calculated field (this is done in the query).
I have this:
=iif((Fields!TIER_NAME.Value = "Tier 1") ,sum(Fields!Applcations_Made.Value),0) + iif((fields!TIER_NAME.Value, "APPS_LAST_YEAR" = "Tier 1"), Sum(Fields!Applcations_Made.Value, "APPS_LAST_YEAR"),0)
I am really trying to push outside my learning so far with this, what am I missing? My name is Karen and I will be going on a VB course near you soon hehe.
Thanks for your help it is appreciated, I have searched and tried to adjust (small tweaks to brackets if nothing else, but cannot for the life of me work this one out.
February 2, 2016 at 8:16 am
Your approach isn't going to work here, SSRS wont apply an IIF for each row in the range.
=iif((Fields!TIER_NAME.Value = "Tier 1") ,sum(Fields!Applcations_Made.Value),0) will not work.
What you need is to use a aggregate that will apply your comparison over each row in the range. In SSRS an IIF nested in a Sum will evaluate for each row within that aggregations scope.
=sum(iif(Fields!TIER_NAME.Value = "Tier 1",Fields!Applcations_Made.Value,0)).
In Excel terms it would be equivalent to: =SUMIF(A:A,"Tier 1",B:B)
For your second part,am I right that APPS_LAST_YEAR is a dataset? If so you can do the a similar trick, but you specify the scope for the Sum (as you were doing).
When you specify a scope, it implies a range (e.g. all rows in the dataset) and therefore has to be aggregated in some way.
So the second part is like passing a range to an IF, e.g.
=IF(A:A = "Tier 1",SUM(B:B),0) which wont work, it is here in SSRS you are getting the error.
The same SUMIF equivalent will apply here as well.
=sum(iif(Fields!TIER_NAME.Value = "Tier 1",Fields!Applcations_Made.Value,0),"APPS_LAST_YEAR")
Or if there is only one row for Tier 1 apps in the dataset try using the Lookup function to get the value.
February 3, 2016 at 8:08 am
Get me? Thank you so much for this. That was a wonderful explanation, I understood every word of that.
I was trying to relate it to Excel (am still learning that too, but am getting there). Now got what I need working. Really appreciate it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply