1214912 - How to create a manual running total for a Cross-Tab row

Archived Reason: Duplicate of the SAP Knowledge Base Article: 1202772

In Crystal Reports (CR) 9, is it possible to create a manual running total that increments based on the values in a Cross-Tab row?

In CR 9, there is a formatting property, 'Display String', that can be used to create a manual running total formula in a cross-tab.

Creating a Manual Running Total in a Cross-Tab

----------------------------------------------

====================

NOTE:

This example is based on the sample Access database (Xtreme.mdb) that is installed with CR. It uses the Customer and Orders tables.

====================

1. Go to the 'Insert' menu and click 'Cross-Tab'. The 'Cross-Tab Expert' dialog box appears.

2. In the 'Available Fields' box highlight 'Customer.Country' and move it into the 'Rows' box.

3. In the 'Available Fields' box highlight 'Orders.Order Date' and move it into the 'Columns' box.  

4. Click the 'Group Options' button below the 'Columns' box and the 'Cross-Tab Group Options' dialog box will appear. From the 'The column will be printed:' drop-down box, click 'for each year'. Click the 'OK' button to close the 'Cross-Tab Group Options' dialog box.

5. In the 'Available Fields' box highlight 'Customer.Last Year's Sales' and move it into the 'Summarized Fields' box twice.  

· The first instance of this field displays the sum.

· The second instance of this field is formatted to display the manual running total.

6. Click the 'Customize Style' tab and under 'Summarized Field', select 'Horizontal'.

7. Click the 'OK' button to close the 'Cross-Tab Expert' dialog box. Place the cross-tab in the Report Header section.

8. On the cross-tab, right click the second summary field and click 'Format Field'.

9. On the 'Common' tab, click the 'X+2' button next to 'Display Text'.

10. Enter the following formula in the 'Format Formula Editor' dialog box:

global numbervar RowNumber;  

global numbervar RunningTotal; 

global numbervar array SummaryValues;

global datetimevar ColumnValue;

if GridRowColumnValue ("Orders.Order Date") <> ColumnValue then RowNumber:=0; 

ColumnValue := GridRowColumnValue ("Orders.Order Date"); 

RowNumber := RowNumber + 1;

RunningTotal := 0;

Redim Preserve SummaryValues[DistinctCount({Customer.Country})];

RunningTotal := SummaryValues[RowNumber] + ToNumber(CurrentFieldValue);

SummaryValues[RowNumber] := RunningTotal;

ToText(SummaryValues[RowNumber],0)

====================

NOTE:

"Orders.Order Date" is the alias assigned to the Order Date field.

The alias for a field can be found in the 'Functions' box of the 'Format Formula Editor' dialog box under 'Formatting Functions' > 'Row or Column Names'. 

====================

11. Click the 'Save and close' button to close the 'Format Formula Editor' dialog box. Click the 'OK' button to close the 'Format Editor' dialog box. Refresh the report.

A row manual running total will now be displayed in the cross-tab.

More Information

--------------------

For information on Manual Running Totals in columns, refer to knowledge base article C2011824.

CROSS-TAB CROSSTAB CROSS TAB MANUAL RUNNING TOTAL ADD INCREMENT ALONG ROWS Crystal Reports Running Totals Cross-Tabs Rows , c2011808 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports 9.0