SSRS invoice totals

This question is answered

Hi,

  I'm redesigning the SalesInvoice SSRS report and trying to display the Pre-tax amount, Tax amount and net amount.  Inside a tablix these fields sum correctly but if I create a text box and add the expression =Sum(Fields!TaxBaseAmount.Value, "SalesInvoiceDS") the total is different to what is displayed as the tablix (=Fields!TaxBaseAmount.Value).  In fact the total outside the tablix is exactly double the total displayed inside the tablix - it seems to be summing it twice.

  Any idea how I can get the correct invoce totals to display outside the tablix?

Thanks,

David

Verified Answer
  • After looking into this a bit more a found the following (which may be obvious for advanced users):

    The code seems to iterate across the sales lines quite a few more times than the actual number of lines.  If I add a tablix row under my sales lines I think I can see this playing out (not that I understand it completely).  The 3 columns below the lines are [taxbaseamount] [taxamount] and [invoiceamount].

    By default when I select these columns in a textbox outside the talbix it automatically tries to SUM the values. Therefore the first 2 columns sum to double the actial amount.  To solve this I changed the operator from SUM to LAST.  This now picks up the last line figures which has the correct values.   I need to test this more to confirm this works in all situations but even if I'm wrong its interesting nonetheless.  I can now delete that extra row of the tablix and just have the correct figures totaled in text boxes below.

    If this doesn't work I'll look further into the other suggestions posted in this thread.

    Thanks

All Replies
  • Please check if the table has any filters at table level or group level. In that case some of the data is not shown in the table based on the filter condition.

     Hence less amount is shown in table, compared to whole dataset value Sum(Fields!TaxBaseAmount.Value, "SalesInvoiceDS") without any filers.

  • Hi David

    Try to add the group for sum using the Add totals option in the "RowGroups". Select After for the master sum and before for sum of each group in the tablix. It will automatically add all the summable fields to the row and you can drop the ones that are not required later.

    - Kamal

     MyBlog | Twitter | LinkedIn

  • Thanks Lalasa,

    I couldn't see any filters on the group or table.  I've found another way of getting my result which I'll post shortly.

  • After looking into this a bit more a found the following (which may be obvious for advanced users):

    The code seems to iterate across the sales lines quite a few more times than the actual number of lines.  If I add a tablix row under my sales lines I think I can see this playing out (not that I understand it completely).  The 3 columns below the lines are [taxbaseamount] [taxamount] and [invoiceamount].

    By default when I select these columns in a textbox outside the talbix it automatically tries to SUM the values. Therefore the first 2 columns sum to double the actial amount.  To solve this I changed the operator from SUM to LAST.  This now picks up the last line figures which has the correct values.   I need to test this more to confirm this works in all situations but even if I'm wrong its interesting nonetheless.  I can now delete that extra row of the tablix and just have the correct figures totaled in text boxes below.

    If this doesn't work I'll look further into the other suggestions posted in this thread.

    Thanks

  • Now I've been doing this for much longer I've found the much better solution is to just grab the totals from custInvoiceJour.

    e.g in insertIntoSalesInvoiceHeaderFooterTmp() add the following code and drag and drop those fields from custInvoiceJour into salesInvoiceHeaderFooter.

       salesInvoiceHeaderFooterTmp.SalesBalance = custInvoiceJour.SalesBalance;

       salesInvoiceHeaderFooterTmp.SumTax      = custInvoiceJour.SumTax;

       salesInvoiceHeaderFooterTmp.InvoiceAmount = custInvoiceJour.InvoiceAmount;

    David