SSRS Sum issues in Group footer

Posted on 2009-04-20
Last Modified: 2012-06-21
I'm working on SSRS 2005

Im having an issue with Sum of a report group in the report footer to get my grand totals. This report is grouped by Vendor. Some vendors we have credits with but an over all balance. Other vendors we have an over all credit with. I want to show both but dont want to sum the ones with an over all credit. This is so our totals will come out right but we can see who we need to collect these credits from. In the example below I dont want to sum any amount from Vendor D. Vendor A has a sum of 0 dollar so its OK. In the below example I would like to see a grand total of $105.00 not $95.00 which is what I get if I just sum my net amount due.


Vendor A = $0.00 (This is my Net amount due for Vendor A)

Vendor B = $25.00

Vendor C = $80.00

Vendor D = ($10.00)

I have created the below expression in the group total so that Vendor D will show 0 dollar in hops I could just sum the textbox with group totals.
=(iif(sum (Fields!net_amnt_due.Value)<=0,0,sum (Fields!net_amnt_due.Value)))
Below is the expression I have in my report footer textbox.
It says that the value expression for the Report Footer uses a numeric aggregate function on the data that is not numeric.

Any ideas will be helful as I've been trying different things over the past weekend with no luck.
Question by:abarefoot
    LVL 16

    Expert Comment

    Try using the same formula from your group footer.
    LVL 1

    Author Comment

    I'm having issues with group footer so I don't see how that will help.
    LVL 1

    Author Comment

    Maybe if someone knows how to make an iif statement be numeric that would fix my issue.
    LVL 1

    Accepted Solution

    OK I figured out that adding Val around my statement changed it to numeric.  I'm still having issues as I cant sum a ReportItems! in the body of the report.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
    A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. (htt…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now