Using Running Totals in Crystal Reports 2013- Advanced Reporting Tools
Using Running Totals
You can make formula fields which display the results of a running total, versus simply showing the results tabulated at the end of a record grouping. You create these fields in the “Field Explorer” pane, just as you would any other type of formula field. These fields function in much the same way as a summary field functions, however, you have a greater amount of control over how the calculation is performed and when the calculated value is reset.
To create a running total field, click on the “Running Total Fields” entry in the “Field Explorer” pane. Then click the “New” button in the toolbar at the top of the “Field Explorer” pane to launch the “Create Running Total Field” dialog box. You use this dialog box to name the field and set its calculation properties.
In the “Running Total Name:” text box you can type a name for the running total field. In the “Summary” section, you select which field’s values to calculate. Click on the field to summarize within the “Available Tables and Fields:” list at the left side of the dialog box, and then click the “>” arrow button to move the selected field into the “Field to summarize” text box at the right. Then use the “Type of summary” drop-down to select what type of summary calculation to perform over the chosen field.
In the “Evaluate” section you can set the parameters under which the calculation will be performed for the selected field’s values. You can select the “For each record” option to calculate the chosen function for every record in the report. You can also choose the “On change of field” option to calculate a value based on the change to the value shown in another field. If you do choose that option, then click on the field to use for the changing values in the “Available Tables and Fields” list at the left of the dialog box and click the “>” arrow button to move it into the “On change of field” text box at the right side.
You could also choose the “On change of group” option button in the “Evaluate” section to evaluate the selected field on the change of a grouped value. If you choose this option, then select which group’s values you want to use from the drop-down to the right of the “On change of group” label.
You can also select the “Use a formula” option button and then click the “X+2” button to invoke the “Formula Editor.” In the Formula Editor, you can create a formula that, when evaluated and found to be true, will then perform the selected calculation of the chosen field. This is very handy for creating conditional running sum fields. For example, if you want to sum the “Amount Sold” field where the “Order Country” field was “USA,” you could set that up as the condition under which the field would be added. The result would be the sum of all sales from the USA.
In the “Reset” section, you can choose under which conditions the running total will be reset back to zero. If you select “Never,” the field’s values will never be set back to zero in the report. You can also select the “On change of field” option button in the “Reset” section to have the running total field get reset back to zero on the change of value to a specified field. If you select this option, then select by which field you want to reset the value from the list displayed in the “Available Tables and Fields:” list at the left side of the dialog box, and click the “>” arrow button to move the selected field into the text box displayed. You can also select the “On change of group” option, and then select by which group’s changing values you wish to reset the running total back to zero from the drop-down which appears. You can also click the “Use a formula” option button, and then click the “X+2” button to invoke the “Formula Editor.” In the “Formula Editor,” you can create a condition that, when met, will reset the value of the running total field back to zero.
Once you have set the desired attributes of the running total field, just click “OK” in the “Create Running Total Field” dialog box. The field that you created will then be displayed in the “Field Explorer” pane. You can then insert it into your report just as you would any other type of field.