This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Thursday 18 August 2011

SSRS - SUM IF

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))) 
 

Posted by at
Labels:

25 comments:

  1. Charic27 February 2012 at 09:07

    Thanks, spotted this on the off chance when looking at using SUM IF.

    ReplyDelete
  2. eltoncezar20 June 2012 at 21:12

    Thanks man!!! You saved my day!

    ReplyDelete
  3. Deron21 November 2012 at 15:44

    Thanks 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.

    ReplyDelete
  4. siva24 January 2013 at 04:22

    Thanks Paul

    ReplyDelete
  5. Unknown31 January 2013 at 19:53

    Thanks! Spun my wheels for an hour today before finding this!

    ReplyDelete
  6. Unknown4 February 2013 at 06:00

    =SUM(VAL(IIF(Fields!Week.Value = "2010-08-01/2010-08-31",Fields!CoastDrayage.Value,0)))

    i want to sum only for one month out of 12 months plzz can any one help me trying from 2 days.........

    ReplyDelete
  7. paul4 February 2013 at 12:30

    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.

    If you need anymore help let me know.


    ReplyDelete
  8. RawGalColumbus13 February 2013 at 04:13

    You've saved me 7 hours! Thank you

    ReplyDelete
  9. Sheev9 July 2013 at 11:55

    Thanks

    ReplyDelete
  10. JohnnyAthers26 July 2013 at 12:40

    Perfect... been banging my head on the table for 8 hours now.

    ReplyDelete
  11. Unknown1 August 2013 at 16:18

    Thank you so much! Saved me many headaches.

    ReplyDelete
  12. Josh21 August 2013 at 20:17

    Thanks for this! I had this same issue and had no clue what was going on!

    ReplyDelete
  13. Brett Shearer20 November 2013 at 14:10

    Why is this not a bug in SSRS?

    ReplyDelete
  14. Unknown29 April 2014 at 15:26

    I worked on this for two hours before finding your post. Probably saved me several more hours. Kudos!!!

    ReplyDelete
  15. Unknown11 December 2014 at 23:53

    you the man!

    ReplyDelete
  16. Khalid Hussain29 December 2014 at 10:52

    You are the best! Got the right answer here, after investing hours on it.

    ReplyDelete
  17. Roche John10 August 2019 at 10:29

    Thanks man. This helped me. Also I noticed when you do a Sum (iif) the reports renders longer than plain field value.

    ReplyDelete
  18. Glenn9 January 2020 at 17:27

    You are the man !! Thank you, Thank you, Thank you !!

    ReplyDelete
  19. Unknown26 February 2020 at 23:35

    This is awesome, thank you!

    ReplyDelete
  20. Unknown14 August 2020 at 21:50

    I 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?

    ReplyDelete
  21. Amanda23 September 2020 at 16:06

    I 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!

    ReplyDelete
  22. Alyson30 September 2020 at 22:58

    Help! I am trying to get this to produce a value:
    =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?

    ReplyDelete
  23. Gourav4 February 2021 at 14:02

    excellent

    ReplyDelete
  24. Unknown4 March 2021 at 14:42

    Thank you!

    ReplyDelete
  25. manscalh15 March 2021 at 18:49

    thank you!!!

    ReplyDelete

Subscribe to: Post Comments (Atom)