So I have this ssrs 2014 report, with a table in the body that has a LineAmount column
Say my report has 300 lines, and each page fits about 50.
Is there any way I can insert the SUM RunningValue of LineAmount at the bottom of each page? Ideally this Running value will not be in the last page.
So I know the formula es
=RunningValue(LineAmount,SUM,MyDataSet)
But I can't figure out, how to make the Page Break a trigger to display this amount
In short, you will need to add a row group and add a simple SUM() there. The row gorup will also force the page break.
Assuming you currently only have a 'details' row group (if not then you will have to decide at what level the row group needs to be)
You need to do the following....
First determine how many rows you can fit on a page, for this exmaple we will use 50 although you may have to reduce that to take account of the addiotnal row.
Right-clck the 'details' row group and choose "Add Group => Parent Group".
In the Tablix group dialog box click the 'fx' button next to the Group By drop down. Set the expression to
=Ceiling((RowNumber(Nothing)) / 50)
Check the "Add group foter" option then click "OK"
Right-Click the new group you created and choose "Group properties". Click the "Page Breaks" tab and check the "Between each instance of a group" option.
Click the sorting tab and delete the sort. If you find data is not sorting correctly then sort by the same sort as your "details" group.
Click OK
You will now have a new row in your tablix. In this new row, in the LineAmount column set the expression to
=SUM(Fields!LineAmount.Value)
This will give you the total for that group and as each group is now a p[age full of data the group total shoudl give you what you want.
This is not the solution I was looking for, but if following the row counting approach, I think there is an easier way.
Just add a new row to the affected tablix Set the row visibility to something like:
=IF(RowNumber("Table_Lines_Group2") MOD 20 ,TRUE,FALSE)
Here 20 is the number of rows that can fit the page "Table_Lines_Group2" is the DataSet group you are counting rows on
In this new row, add the running total in one of the columns, Expr like for example:
=RunningValue(Fields!LineAmt_SalesInvoiceLine.Value,SUM, Nothing)
So basically, each 20 rows, you will get a running total
