SAP Knowledge Base Article - Public

1232321 - Grouping on WhilePrintingRecords formula

Symptom

Formula summarizes the value from the database field and from the subreport. Customer needs to group records based on formula results. 

Environment

  • Crystal Reports 2016

  • Crystal Reports 2020

Cause

Shared variables values are available in WhilePrintingRecords only. Crystal Reports cannot group based on WhilePrintingRecords formulas, because the grouping done in WhileReading evaluation time, but there is a workaround to resolve the problem.

Resolution

It is possible to have a copy of the same report added as a subreport in Report Footer, then pass results of WhilePrintingRecords formula as concatenated string parameter to subreport.

If the data is available as a parameter then it is possible to parse values in WhileReadingRecords evaluation time of the subreport and group records accordingly.

Next step is suppress all sections of the main report except the one with the subreport. Attached is the sample report to demonstrate how to group and sort based on WhilePrintingRecords formulas.

Below is the step by step instructions:

  1. Create a “StringToPass” formula in the main report to concatenate values of WhilePrintingRecords formula:
 whileprintingrecords;evaluateafter({@LastYearsSalesRunningTotal   });stringVar CountrySales:=CountrySales + GroupName ({Customer.Country})+ "/ "+Totext({@LastYearsSalesRunningTotal   })+ "/ "; 
  1. Add formula to main Report Details section
  1. Add a copy of the same report as a subreport in Report Footer
  1. Link the subreport with @StringToPass formula
  1. Create a new “CountryArray” formula in subreport:
 whilereadingrecords;stringvar array country;numbervar n;numbervar sales;country:= split({?Pm-@StringToPass},"/ "); for n:=1 to Ubound(country) do if country [n] = {Customer.Country} then sales := tonumber (country [n+1]);sales 
  1. Add formula to the Details section of the subreport
  2. Now the formula is available for grouping, group subreport using this formula
  3. Suppress main report sections
 

Keywords

grouping, sorting, WhilePrintingRecords formula, subreport, parameter   , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports XI

Attachments

WhilePrintingRecordsFormulaSortingVI.rpt