Had nothing but problems today with Microsoft Business Intelligence Development Studio, so after spending nearly 7 working hours on the problem I thought I'd post the solution to save someone else the hassle!
Problem
When using groupings in a table, an expression consisting of a SUM and an immediate if (IIF) can be used to sum/count/aggregate values where another value in the dataset meets a certain condition. In my example below where the academic year is = "2010/2011" I want to include the grade value (a numeric).
=SUM(IIF(fields!Academic_year.value = "2010/2011",Fields!Grade.value,0))
However despite the report working in in BIDS, after deploying the report and viewing it on the SSRS server I get #error on some/all of the groups.
Solution
For some reason the SSRS server when rendering the report is struggling to interpret the grade field as a numeric and also the 0. The use of the VAL conversion function removes this problem.
=SUM(VAL(IIF(fields!Academic_year.value = "2010/2011",Fields!Grade.value,0)))
Thanks, spotted this on the off chance when looking at using SUM IF.
ReplyDeleteThanks man!!! You saved my day!
ReplyDeleteThanks for posting this. I see there are only 2 other thank yous here but I'm betting there are many more that have benefited from this. I was getting the same #error# message. I really don't understand what VAL is actually doing but it works and I'm grateful.
ReplyDeleteThanks Paul
ReplyDeleteThanks! Spun my wheels for an hour today before finding this!
ReplyDelete=SUM(VAL(IIF(Fields!Week.Value = "2010-08-01/2010-08-31",Fields!CoastDrayage.Value,0)))
ReplyDeletei want to sum only for one month out of 12 months plzz can any one help me trying from 2 days.........
You may need to format the week.value to the month so that you are comparing "february" to "february", either that or do it in a matrix in which case just hide the other month values dynamically.
ReplyDeleteIf you need anymore help let me know.
You've saved me 7 hours! Thank you
ReplyDeleteThanks
ReplyDeletePerfect... been banging my head on the table for 8 hours now.
ReplyDeleteThank you so much! Saved me many headaches.
ReplyDeleteThanks for this! I had this same issue and had no clue what was going on!
ReplyDeleteWhy is this not a bug in SSRS?
ReplyDeleteI worked on this for two hours before finding your post. Probably saved me several more hours. Kudos!!!
ReplyDeleteyou the man!
ReplyDeleteYou are the best! Got the right answer here, after investing hours on it.
ReplyDeleteThanks man. This helped me. Also I noticed when you do a Sum (iif) the reports renders longer than plain field value.
ReplyDeleteYou are the man !! Thank you, Thank you, Thank you !!
ReplyDeleteThis is awesome, thank you!
ReplyDeleteI need some help. I think I'm so close, but not quite. I have A,B,C quantities, but I want to sum only A and B. How do I do that?
ReplyDeleteI want to just echo everyone's sentiments here, this is so helpful! I have been spinning my wheels way to long trying to figure this out. Thank you!
ReplyDeleteHelp! I am trying to get this to produce a value:
ReplyDelete=Sum(VAL(iif(Fields!akoya_paymentdate.Value>=Parameters!DateFrom.Value and Fields!akoya_paymentdate.Value<=Parameters!DateTo.Value,fields!akoya_amount.Value,0)))
Even if I hard code the dates I always get $0. Anyone see what i am doing incorrectly?
excellent
ReplyDeleteThank you!
ReplyDeletethank you!!!
ReplyDelete