Crystal reports to SSRS - Running total as grand total outside of group

Jasmine
Jasmine used Ask the Experts™
on
Hi, Just dropping this question again with some more detail to see if i can get a work around.

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. we have over 25 reports that need to been converted 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.

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.

I have read through many links but i can't see how they will work, but i might just have a skill gap, none of them cover where the running total evaluating on group are used as a total only.

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

Any ideas on a way to get this to work?

Example-of-Running-total-in-Report.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
Related to the very specific "running total" issue mentioned above encountered for the
...over 25 reports that need to been converted but they all have a running total as a grand total

It is not 100% clear to me if you actually managed to convert at least one of these Crystal Reports to SSRS as an example and if you did that or you can do that, please have a look at SSRS topic:
To add a total for an individual value in the tablix body area

In the tablix data region body area, right-click the cell where you want to add the total. The cell must contain a numeric field. Point to Add Total, and then click Row or Column.

A new row or column outside the current group is added to the data region, with a default total for the field in the cell you clicked.

If the tablix data region is a table, a row is automatically added.

from https://docs.microsoft.com/en-us/sql/reporting-services/report-design/add-a-total-to-a-group-or-tablix-data-region-report-builder-and-ssrs?view=sql-server-2014 
to see if it helps.

Author

Commented:
Thank you for your input, Sadly it does not address the issue at all.

The issue is not adding a total (i have converted 220 other reports all with totals without an issue).

The issue is these reports in crystal reports use a very specific running value aggregate to calculate the total based. This does not work in SSRS. In SSRS it will not let me evaluate on group outside of the group and any total outside of the group totals the whole data for report. when we only want the sum of the group totals.

if that makes sense. I have had no luck finding a workaround for this and sadly these reports are vital.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial