Group Formula subtotals incorrect after a Page Break

[rich914]

I have client that I have created some 70 odd reports in Crystal 8.0 Developer edition. This was part of a conversion from several DOS based Clipper systems to a VB 6.0 with an Access 2000 backend database.

I created a system DSN so that crystal could open and read the stored queries that I have in Access.

There three reports that I have not been able to get to work correctly. My group subtotals for the first printed group footer after a page break is incorrect. Other group footers on that page are correct. Does not matter if I run the report from the crystal ide or the VB exe.

The way I have done these reports is all formulas use visual basic syntax. Each report has a formula in the report header section that initializes all global variables (mostly number or currency) and sets them to zero.

I will use one of the three as an example:

3 groups
Group I - call it corporate id - header suppressed
Group II - call it employee id - header suppressed
Group III - call it workday - for each day - header suppressed

Detail section is suppressed - is used to accumulate hours worked by type (28 codes grouped into 5 types). This formula uses 'WhilePrintingRecords', group formulas use 'EvaluateAfter(X)'.

Group III footer - suppressed - rolls detail totals into another set of totals, zero detail totals, and then accumulates additional totals that are repeated fields for that workday.

Group II footer prints these totals AND uses some of them in other formulas for additional printed information. Totals are rolled into Group I totals and zero group II totals.

This is repeated for Group I footer with the totals being rolled into report totals.

I know that this is probably not the best way to utilize crystal reports, but neither my client nor I could afford to send me to a training class. Do not know about Crystal Decisions, but Seagate's manual (which is the help file) was not very good at explaining anything.

Not sure if this problem is explained enough or not. Will answer any questions the best I can.

Thanks
Richard Barbre
Developer

 

[lbass]

Do you happen to have "Repeat group headers on each page" checked for the group that is returning the wrong summary? If you have any resets in the group header for that group, they will fire in the repeating group header, unless you add a clause to your reset formulas, as in:

whileprintingrecords;
numbervar amt;

If not InRepeatedGroupHeader then amt := 0;

-LB

 

[rich914]

No do not have "Repeat group headers on each page" checked on any of the groups. I mean I've tried it both ways, does not make a difference.

 

[dgillz]

please post all formulae related to this problem, an d where in the report they are placed.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

I agree with dgillz that we need to see the formulas and where they are placed.

Also, do you happen to have any formulas placed in the page header or page footer that could be causing an increment in those sections?

-LB

 

[IdoMillet]

Go into each one of your groups (Right-click GH1, GH2, GH3
and select Change Group...) and make sure in the dialog that come up that you do NOT have "in original order" selected in the dropdown list.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

If that doesn't fix the issue, debug the situation by placing formulas that simply display the variable values in the detail section and in the group footers. This would allow you to trace and locate exactly where the problem occurs.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[glalsop]

I noticed that you said 'initializes all global variables'. Are you using subreports to do any of your summations?

I ran into a problem very similar to this using subreports and shared variables that broke across pages. Here is a link to the Business Objects whitepaper:

http://support.businessobjects.com/library/kbase/articles/c2009314.asp

-Gary

 

[rich914]

First, I want to thank everybody for responding; second, I will try to answer some of the questions before posting the formulas, which will make this a long post.

No formulas in the page header or footer.

The data for this report comes from an Access stored query. The query is pulling data from multiple tables and it is sorted in the correct order by access. So the groups are set for ‘in original order’.

Not using subreports.

Have tried to use either a current formula or a separate formula to print a running total of one of the group totals. Does not show anything, mainly due to changing when the pages breaks occur.

Ok, now I am going to like change horses in midstream and switch to one of the other reports that has one less group so this post will not be a mile long. The data behind the report is basically the same, just do not need the corporate id grouped. Groups 2 & 3 now become groups 1 & 2

----- first formula: In Report Header InitializeVars

' Report Totals
Global nGlbPcNsup As Number
Global nRptTot1 As Number 
Global nRptTot2 As Number 
Global nRptTot3 As Number 
Global nRptTot4 As Number 
Global nRptTot5 As Number 
Global nRptTot6 As Number 
Global nRptTot7 As Number 
Global nTotEmps As Number 
Global nTotStnd As Number

' Analyst Totals
Global nEmpHrsWrk as Number 
Global nEmpHrsAlzd as Number 
Global nEmpHrsHsr as Number 
Global nEmpAmtAlzd As Number 
Global nEmpNetUnd As Number 
Global nEmpNetOvr As Number
Global sRate1 as string
Global sRate2 as string
Global sRate3 as string 

' Daily Totals
Global nDayHrsWrk as Number 
Global nDayHrsAlzd as Number 
Global nDayHrsHsr as Number 

nGlbPcNsup = 0
nRptTot1 = 0
nRptTot2 = 0
nRptTot3 = 0
nRptTot4 = 0
nRptTot5 = 0
nRptTot6 = 0
nRptTot7 = 0
nTotEmps = 0
nTotStnd = 0

nEmpHrsWrk = 0
nEmpHrsAlzd = 0
nEmpHrsHsr = 0
nEmpAmtAlzd = 0
nEmpNetUnd = 0
nEmpNetOvr = 0
sRate1 = "0"
sRate2 = "0"
sRate3 = "0"

nDayHrsWrk = 0
nDayHrsAlzd = 0
nDayHrsHsr = 0

formula = ""

----second – placed in detail section: DailyHours

WhilePrintingRecords
' Daily Totals
Global nDayHrsWrk as Number 
Global nDayHrsAlzd as Number 
Global nDayHrsHsr as Number 

if Not ({qryRptLcrPerfmByAll.CodeType} = "W") and Not ({qryRptLcrPerfmByAll.CodeType} = "P") then
  nDayHrsWrk = nDayHrsWrk + {qryRptLcrPerfmByAll.Hours}
end if

if {qryRptLcrPerfmByAll.HourCodeID} = 1 or {qryRptLcrPerfmByAll.HourCodeID} = 44 then
  nDayHrsAlzd = nDayHrsAlzd + {qryRptLcrPerfmByAll.Hours}
elseif {qryRptLcrPerfmByAll.HourCodeID} = 2 or {qryRptLcrPerfmByAll.HourCodeID} = 40 or _
        ({qryRptLcrPerfmByAll.HourCodeID} >= 20 and {qryRptLcrPerfmByAll.HourCodeID} <= 29) then
  nDayHrsHsr = nDayHrsHsr + {qryRptLcrPerfmByAll.Hours}
end if

formula = ""

-----third – placed in group II footer: AddAnalTotals

EvaluateAfter (GroupName ({qryRptLcrPerfmByAll.WorkDate}, "daily"))
' Analyst Totals
Global nEmpHrsWrk as Number 
Global nEmpHrsAlzd as Number 
Global nEmpHrsHsr as Number 
Global nEmpAmtAlzd As Number 
Global nEmpNetUnd As Number 
Global nEmpNetOvr As Number 

' Daily Totals
Global nDayHrsWrk as Number 
Global nDayHrsAlzd as Number 
Global nDayHrsHsr as Number 

nEmpAmtAlzd = nEmpAmtAlzd + {qryRptLcrPerfmByAll.AmtAnlzd}
nEmpNetUnd = nEmpNetUnd + {qryRptLcrPerfmByAll.NetUnder}
nEmpNetOvr = nEmpNetOvr + {qryRptLcrPerfmByAll.NetOver}

nEmpHrsWrk = nEmpHrsWrk + nDayHrsWrk
nEmpHrsAlzd = nEmpHrsAlzd + nDayHrsAlzd
nEmpHrsHsr = nEmpHrsHsr + nDayHrsHsr

nDayHrsWrk = 0
nDayHrsAlzd = 0
nDayHrsHsr = 0

formula = ""

-------The next 11 all are placed in the Group I footer.

----- HospAnalyst

EvaluateAfter (GroupName ({qryRptLcrPerfmByAll.AnalystNumber}))
' Report Totals
Global nGlbPcNsup As Number
Global nTotEmps As Number 
dim cTmp as string
dim nTmp1 as number
dim nTmp2 as number
dim nTmp3 as number

nTotEmps = nTotEmps + 1
nGlbPcNsup = {qryRptLcrPerfmByAll.WkDayHours} / ({qryRptLcrPerfmByAll.WkDayHours} + 0.25)

if Len (Trim ({qryRptLcrPerfmByAll.InternalRptName})) > 0 then
    nTmp1 = Len ({qryRptLcrPerfmByAll.InternalRptName})
    cTmp = Trim ({qryRptLcrPerfmByAll.InternalRptName})
else
    nTmp1 = Len (Trim ({qryRptLcrPerfmByAll.Name}))
    cTmp = Trim ({qryRptLcrPerfmByAll.Name})
end if
nTmp2 = Len (Trim ({qryRptLcrPerfmByAll.lastName}))+ 5
if nTmp1 > 30 then
  nTmp3 = 30 - nTmp2
else
  nTmp3 = (30-nTmp1) + nTmp1
  nTmp3 = nTmp3 - nTmp2
end if
formula = left(cTmp,nTmp3) & " ->" & Trim({qryRptLcrPerfmByAll.LastName}) & " " & left(trim({qryRptLcrPerfmByAll.FirstName}),1)

----- prtEmpWrkPcent

EvaluateAfter ({@HospAnalyst})
Global nGlbPcNsup As Number
Global nEmpHrsWrk as Number 
Global nEmpHrsAlzd as Number 
Global nEmpHrsHsr as Number 
Global nRptTot1 As Number 
dim nTmp as number

nTmp = 0
if nEmpHrsWrk > 0 then
  nTmp = (((nEmpHrsAlzd + nEmpHrsHsr) / nEmpHrsWrk) * 100) / nGlbPcNsup
  nRptTot1 = nRptTot1 + nTmp
end if
formula = nTmp

----- prtEmpAlzdHour

EvaluateAfter ({@prtEmpWrkPcent})
Global nEmpHrsAlzd as Number 
Global nEmpAmtAlzd As Number 
Global nRptTot2 As Number 

if nEmpHrsAlzd > 0 then
  nRptTot2 = nRptTot2 + (nEmpAmtAlzd / nEmpHrsAlzd)
  formula = nEmpAmtAlzd / nEmpHrsAlzd
'  formula = nEmpAmtAlzd 
else
  formula = 0
end if

----- prtEmpUndErr

EvaluateAfter ({@prtEmpAlzdHour})
Global nEmpAmtAlzd as Number 
Global nEmpNetUnd As Number 
Global nRptTot3 As Number 

if nEmpAmtAlzd > 0 then
  nRptTot3 = nRptTot3 + ((nEmpNetUnd / nEmpAmtAlzd) * 100)
  formula = (nEmpNetUnd / nEmpAmtAlzd) * 100
else
  formula = 0
end if

----- prtEmpUndHour

EvaluateAfter ({@prtEmpUndErr})
Global nEmpHrsAlzd as Number 
Global nEmpNetUnd As Number 
Global nRptTot4 As Number 

if nEmpHrsAlzd > 0 then
  nRptTot4 = nRptTot4 + (nEmpNetUnd / nEmpHrsAlzd)
  formula = nEmpNetUnd / nEmpHrsAlzd
else
  formula = 0
end if

----- prtEmpOvrErr

EvaluateAfter ({@prtRptUndHour})
Global nEmpAmtAlzd As Number 
Global nEmpNetOvr As Number 
Global nRptTot5 As Number 

if nEmpAmtAlzd > 0 then
  nRptTot5 = nRptTot5 + ((nEmpNetOvr / nEmpAmtAlzd) * 100)
  formula = (nEmpNetOvr / nEmpAmtAlzd) * 100
else
  formula = 0
end if

----- prtEmpOvrHour

EvaluateAfter ({@prtEmpOvrErr})
Global nEmpHrsAlzd as Number 
Global nEmpNetOvr As Number 
Global nRptTot6 As Number 

if nEmpHrsAlzd > 0 then
  nRptTot6 = nRptTot6 + (nEmpNetOvr / nEmpHrsAlzd)
  formula = nEmpNetOvr / nEmpHrsAlzd
else
  formula = 0
end if

----- prtEmpTotErr

EvaluateAfter ({@prtEmpOvrHour})
Global nEmpAmtAlzd As Number 
Global nEmpNetUnd As Number 
Global nEmpNetOvr As Number 
Global nRptTot7 As Number 

if nEmpAmtAlzd > 0 then
  nRptTot7 = nRptTot7 + (((nEmpNetUnd + nEmpNetOvr) / nEmpAmtAlzd) * 100)
  formula = ((nEmpNetUnd + nEmpNetOvr) / nEmpAmtAlzd) * 100
else
  formula = 0
end if

----- prtRate1

EvaluateAfter ({@prtEmpTotErr})
Global nGlbPcNsup As Number
Global nEmpHrsWrk as Number 
Global nEmpHrsAlzd as Number 
Global nEmpHrsHsr as Number 
Global sRate1 as string

sRate1 = "0"
if (nEmpHrsWrk > 0) then
  if (((nEmpHrsAlzd + nEmpHrsHsr) / nEmpHrsWrk) * 100) / nGlbPcNsup >= {?prmPcHour} then
    sRate1 = "1"
  end if
end if

formula = sRate1

----- prtRate2

  EvaluateAfter ({@prtRate1})
Global nEmpHrsAlzd as Number 
Global nEmpAmtAlzd As Number 
Global nTotStnd As Number
Global sRate2 as string

nTotStnd = nTotStnd + {qryRptLcrPerfmByAll.Standard}
if nEmpHrsAlzd > 0 then
  if ((nEmpAmtAlzd / nEmpHrsAlzd) > {qryRptLcrPerfmByAll.Standard}) then
    sRate2 = "1"
  else
    sRate2 = "0"
  end if
  formula = sRate2
else
  formula = "0"
end if

----- prtRate3

EvaluateAfter ({@prtRate2})
' Analyst Totals
Global nEmpHrsWrk as Number 
Global nEmpHrsAlzd as Number 
Global nEmpHrsHsr as Number 
Global nEmpAmtAlzd As Number 
Global nEmpNetUnd As Number 
Global nEmpNetOvr As Number 
Global sRate3 as string

sRate3 = "0"
if nEmpHrsAlzd > 0 then
  if ((nEmpNetUnd / nEmpHrsAlzd) + (nEmpNetOvr / nEmpHrsalzd) > {?prmPerHour}) then
    sRate3 = "1"
  end if
end if

' Reset for next employee (group)
nEmpHrsWrk = 0
nEmpHrsAlzd = 0
nEmpHrsHsr = 0
nEmpAmtAlzd = 0
nEmpNetUnd = 0
nEmpNetOvr = 0

formula = sRate3

These formula are from my last debugging attempt, which has made a difference. Note that the first formula in the group I footer uses EvaluateAfter the group change, the rest evaluate after the one before. Now only the first calculated percentage (second formula) is incorrect and only by a small amount.

Do a refresh using the same data and it is perfect!