1

I'm looking for the value of Group1total in a Crystal Report. My background is more centered around SQL and I'm trying to determine the logic behind the following:

//{@Group1total}
whileprintingrecords;
numbervar group1cost

//{@Group1cost}
whileprintingrecords;
numbervar group1cost := group1cost + {@group2total}

//{@Group2total}
whileprintingrecords;
numbervar group2cost

//{@Group2cost}
whileprintingrecords;
numbervar group2cost := group2cost + {@group3total}

//{@Group3total}
whileprintingrecords;
numbervar group3cost

//{@Group3cost}
whileprintingrecords;
numbervar group3cost := group3cost + {@group4total}

//{@Group4total}
whileprintingrecords;
numbervar group4cost

//{@Group4cost}
whileprintingrecords;
numbervar group4cost := group4cost + {XVR_projectcostbudget.cost_budget}

I'm noticing that simply doing the SUM of XVR_projectcostbudget.cost_budget in SQL is not yielding the same result as the Crystal Report. Any ideas?

Thanks!

craig
  • 25,664
  • 27
  • 119
  • 205
Justin
  • 43
  • 1
  • 1
  • 6
  • 1
    Are those 8 separate formulas? What section of the report are they in?? It might be easier to just explain what you're trying to compute... something tells me that is much more complicated than it needs to be. – Ryan Jan 04 '13 at 20:32
  • I corrected your syntax to help the discussion (I hope). – craig Jan 04 '13 at 20:58

2 Answers2

4

WhilePrintingRecords does NOT get translated to SQL. It only affect a given formula's calculation--essentially assigning it to one of the report's 'passes'. See Crystal Reports 9+ - Evaluation times and the multi-pass reporting model.

In your situation, I would suggest:

  • Inserting a summary on a formula or database field in all four of your groups' footers; this will create the fields that you can use in a formula field (you can create them manually, but this is easier)
  • Create a formula field that tallies the group summaries as desired
craig
  • 25,664
  • 27
  • 119
  • 205
0

Like Justin I can feel his frustration with Crystal Reports and putting this here to help others too.

Was trying to produce totals at various group levels and at the end of the report with the usual sum(decode( sql structure. In crystal was trying to simulate this using running total field with evaluate formula. The evaluation column being a code (all values are numeric but table column declared as string type :). Evaluation formula only provides "sum" functionality if the field type is numeric. So created a formula field to produce this column as a numeric. Great, sum function now usable in the running total with IFF ( , 1, 0). But would not produce a result! (ie blank)

Switched to the Whileprintingrecords structure and working. How this works is succinctly put in the following article. http://crystalreportsblog.com/crystal-reports-running-totals/#comments

Obviously putting this calc into the main sql query would be straight forward but old report with gui table links setup.

Gary Thomann
  • 576
  • 6
  • 17
  • Back to the orginal idea but using basic syntax and works as formula evaluates to a numeric and sum does work on that. – Gary Thomann Jul 10 '13 at 02:17