Jasmine
asked on
Running total as grand total outside of group
I am in the process of converting a large number of Crystal Reports to SSRS. I have hit a snag with a number of Running Total issues.
In a number of reports, A running total evaluating on change group is used as a grand total outside of the group. As you can imagine I can not get this to work as if I write a running value expression on group it is outside of the group scope and does not work, and if i set the scope to "dataset1" the value is not correct as it needs to evaluate on group.
Any ideas on a way to get this to work?
In a number of reports, A running total evaluating on change group is used as a grand total outside of the group. As you can imagine I can not get this to work as if I write a running value expression on group it is outside of the group scope and does not work, and if i set the scope to "dataset1" the value is not correct as it needs to evaluate on group.
Any ideas on a way to get this to work?
Bit of reading for you...
Either Cumulative Aggregates : https://www.mssqltips.com/sqlservertip/2587/cumulative-aggregates-in-sql-server-reporting-services/
Scroll down to the report output first to quickly check if it is the type of thing you are after
Or Aggregate across (column) groups via subreport : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9238b2e-a92b-4cbc-8129-3d5b5b6255d3/how-to-create-runningvalue-through-column-groups?forum=sqlreportingservices
Sorry about the links, but, it could end up being a bit of a novel....
Do you have a layout in mind ? Can you share a bit more info, even an Excel example ?
It may well be possible to manifest the aggregated totals on SQL Server and do it that way.... What version of SQL are you using ?
Either Cumulative Aggregates : https://www.mssqltips.com/sqlservertip/2587/cumulative-aggregates-in-sql-server-reporting-services/
Scroll down to the report output first to quickly check if it is the type of thing you are after
Or Aggregate across (column) groups via subreport : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9238b2e-a92b-4cbc-8129-3d5b5b6255d3/how-to-create-runningvalue-through-column-groups?forum=sqlreportingservices
Sorry about the links, but, it could end up being a bit of a novel....
Do you have a layout in mind ? Can you share a bit more info, even an Excel example ?
It may well be possible to manifest the aggregated totals on SQL Server and do it that way.... What version of SQL are you using ?
ASKER
Thanks so much for your help, sorry for taking so long to pick this back up.
Unfortunately we have over 25 reports that need to been converted from Crystal Reports to SSRS but they all have a running total as a grand total, typically evaluating on group, outside of the group. In some cases these running values are used in other calculations. It is a nightmare.
I have read through some of the links but i can't see how they will work, but i might just have a skill gap.
I have attached an example of one of the reports that has a running total, it is only one of the possible layouts and ways the previous reports used the running totals. but does highlight the issue i am hitting. If possible i would rather do this within the ssrs report but i am at a loss on how to get it to work.
We are on SQL 2014 but are about to migrate to 2016
Unfortunately we have over 25 reports that need to been converted from Crystal Reports to SSRS but they all have a running total as a grand total, typically evaluating on group, outside of the group. In some cases these running values are used in other calculations. It is a nightmare.
I have read through some of the links but i can't see how they will work, but i might just have a skill gap.
I have attached an example of one of the reports that has a running total, it is only one of the possible layouts and ways the previous reports used the running totals. but does highlight the issue i am hitting. If possible i would rather do this within the ssrs report but i am at a loss on how to get it to work.
We are on SQL 2014 but are about to migrate to 2016
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
What about pre-calculating this value and storing it in the database itself.
I've used the Quirky Update 'feature' myself, and it is solid once you get it to work. I referred to Jeff Moden's articles on this.
http://www.sqlservercentral.com/articles/T-SQL/68467/
Now there are features in SQL that can do the same thing without the need to exploit this undocumented feature.
HTH
David