I’m working on a SSRS report and have a text box in the footer for grand total. The expression is =Sum(ReportItems!Textbox5.Value).
It works as expected but once the report has multiple pages, I only get the sum per page.
Example,
Page one Grand Total: 500
Page two Grand Total: 100
I would like to have the sum of Textbox5 on all pages displayed in the footer. So in the example above, I would expect to see Grand Total: 600 on both pages.
Because of the way the report is written (1 main report, 2 sub reports) I’m not able to do a sum expression in the table footer or tablix. It due to the way its joined, I get duplicate rows which multiplies the grand total per line. Doesn’t actually display that way as the sub reports are all joined by parameters.
Has anyone came across this issue before? All of the resources I’ve found online either didn’t directly apply to this scenario or resorted back to using table footers.
Off the top of my head, if I were going to attempt to meet the requirement that you are proposing, my first design would be something like the following:
Use a field at the bottom of the report Body, and above the Page Footer, determine the Grand Total Sum and display it there.
Use a reference similar to what you have done already in the page footer, to bring in the value of the Grand Sum Total. For example, ReportItems!GrandTotalSumAmt.Value.
If it actually works, then mark the Grand Total Sum field that is in the report Body, as Hidden.
If that does not work, then I would try some variation of using an expression and Code reference to set a Variable to the value of the Grand Total field that I would then retrieve using expression and Code in the Page Footer.
You may consider the POForm report as some guide for what you are looking to do.
@Michael_Ramsey I actually tried what your first suggestion which is to add a field at the bottom of the report body and had the same results as the table footer where the sum is getting multiplied by each line item.
The other option to use Code reference to do Sum(ReportItems!Textbox5.Value) then retrieve it in the page footer I believe is going to be my best option. I’ve never done that previously so I’m trying to wrap my head around the syntax. Do you have any suggestions/examples for bringing in the expression that way? Thanks!
I think that may be because of where the field is located. For example, when you said that the value changes from page to page, then I assumed that your field was actually contained in the Tablix? or somewhere that it was actually being calculated per page. Which I assumed was how the 500 showed on page 1, and then 100 showed on page 2. My suggestion is to place the Grand Total so that it really gives you the correct Grand Total, and only once - at the very end of the report and on the last page. It needs to do this correctly even in the event of trying to use Variables to pass the value around. The only reason to use the Variable was in case the order of execution needed to be circumvented. So, if your ReportItems! did not show correctly, then trying to use a Variable may have given different results.
Key is to get the Grand Total Sum giving you the correct amount at the end of the report, first. At least in my mind. You may have to re-explain what your goal is, if I have misunderstood.
The highlighted field is Textbox5. The expression for Textbox5 is =Sum(Fields!Calculated_Price.Value) + Fields!Calculated_Price2.Value (Subtotal). The fields “Price” & “Price2” are both on different sub reports. Due to the way there joined, I get duplicate results on Price2. thats what I mean by the sum gets multiplied by each line item on sub query1.
The Grand Total at the footer is the sum of sub total (Textbox5). As I mentioned before, this works fine when the report is contained to a single page.
Is it possible to bring in =Sum(ReportItems!Textbox5.Value) as code and reference it in the footer?
It is possible, but not necessary at this point, IMHO. To do it, you are going to need to do some due diligence in order to apply it properly to your requirement. In the past I have used similar logic to control the printing of AR Invoice page header RemitTo in a batch of invoices that can contain various “companies” issuing the invoice - such as Company doing business as Company A, B, and C.
What I was suggesting, as a way to get your Grand Total to be the sum of all of the Sub Totals in the report, is to move him out of the Page Footer and into the Body of the report, but outside the Tablix, or at least as the last object in the Group. You can’t just put him in the Page Footer because then he is re-evaluated for every Page. If you want the real Grand Total you will need to place him where the evaluation makes sense for your requirement.
Of course, if you use the Code approach to increment a Variable value everytime Sub Total is evaluated, and then you reference the Variable value, then yes you should get different results.
Just understand that order of execution may affect the final results. I’m giving you a general direction to go in, as I don’t have any similar requirement where I’ve actually proved this out to be any kind of final solution.
