Question Status

Verified
DavidR asked a question on 16 May 2013 5:46 PM

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

Reply
Lalasa responded on 17 May 2013 4:25 AM

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.

Reply
Suggested Answer
Kamalakannan Elangovan responded on 20 May 2013 3:39 AM

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.

Reply
DavidR responded on 20 May 2013 10:10 PM

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.

Reply
Verified Answer
DavidR responded on 20 May 2013 10:22 PM

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

Reply
DavidR responded on 21 Jul 2013 4:10 PM

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

Reply
Verified Answer
DavidR responded on 20 May 2013 10:22 PM

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

Reply
Suggested Answer
Kamalakannan Elangovan responded on 20 May 2013 3:39 AM

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.

Reply