Hide/Suppress = different grand total results?

[kraber]

I am seeing something that is just plain odd - and I can't figure out what in the heck I've done wrong. I'm using CR7, FWIW...

I have a report with three levels of groupings (I'll add details below, not sure if they're necessary). At the end of each group I've used running totals to display a count of records, totals, and averages. As long as I show details, this works just fine.

If I suppress or hide the details, however, I get different results for SOME of the totals. If I unsuppress/unhide results, they change to the correct values. As best I can tell, one or more of the detail rows are occasionally either left out of the total or double-counted. I haven't discovered a pattern yet, but I'm still looking...

Now for the details, in case they might add some insight.

My report is structured as follows, with three grouping levels:

Group 1: Contractor Name

Group 2: Work order type (Maintenance, Residential, Commercial, etc.)

Group 3: Work order information: work order ID, trench footage, hours charged, etc.

Details: Individual job step information (can have multiple job steps per work order) - Start date, end date, duration (difference between start date and end date).

I display a count of jobs, totals and averages for each value (not including the dates, of course) for work order type, then roll-up information by contractor, and finally a grand total.

Any idea what might be causing this?? I'm about to pull my hair out over this!

Thanks!

-Karyl

 

[synapsevampire]

Wuzzza FWIW?

Perhaps you could share a little example data, formula used (and where) and the expected results?

-k

 

[kraber]

FWIW = For what it's worth ;-).

OK, let's see if I can cut & paste some data:
(sorry about the formatting - it looks good in the 'create message window'. Darn proportional spaced fonts...)


DETAILED REPORT DATA

Count Trench Wire Hours Duration
---------------------------------------------
CONTRACTOR1
N-Rev Cap T&D Pri Sys Improve

W190434 1,022 682 14,766.5
130 97.00
W190456 0 0 4,842.5
138 5.00
W205368 529 3,414 144.0
130 21.00
W208925 0 0 83.5
130 28.00
W218072 0 0 14.0
130 9.00

TOTALS for N-Rev Cap T&D Pri Sys Improve
5 1,551 4,096 19,850.5 160.0
AVERAGES
310 819 3,970.1 32.0


SUMMARY REPORT DATA:

Count Trench Wire Hours Duration
---------------------------------------------
CONTRACTOR 1
TOTALS for N-Rev Cap T&D Pri Sys Improve
5 1,022 682 14,780.5 106.0
AVERAGES 511 341 7,390.3 53.0


Notice that the numbers for the summary report do not match the numbers for the detail report, and the 'averages' numbers seem to be the totals divided by 2, not by 5 as they should be.

My formulas are as follows:

Count: DistinctCount of WorkOrder#

Total Trench: Running total; Field to summarize: Trench; Type of summary: sum; Evaluate on change of: WorkOrder#; Reset on change of: Worktype Description (in this case, N-Rev Cap etc.)

Total Wire: Running total; field to summarize: Wire; Rest of fields same as for Total Trench.

Total Hours: Running total; field to summarize: Hours; rest of fields same as for Total Trench

Duration: Running total; field to summarize: Duration; Type of summary: sum; Evaluate: for each record; Reset on change of: Worktype Description

Averages: Running totals; type of summary: average; rest of info same as for totals above.

The *only* think I've done differently for the summary report is to hide the detail rows. I get the same results if I suppress the detail rows. An interesting thing to note: When I exported the report results to an Excel spreadsheet so I could cut & paste, the exported data for the summary report actually MATCHES the results for the detail report. When I look at the report in Crystal, the numbers are as above. Very, very odd...

Thanks for any help!

-Karyl

 

[lbass]

In 8.0 there was a problem with running totals (especially when using multiple groups) which was fixed by installing a patch. I initially found discrepancies between running totals in CR and the same results when exported to Excel. The patch fixed this. Not sure whether any patches are still available for CR 7.0, but you might check the Business Objects website.

-LB

 

[kraber]

Thank you so much - Wish I had thought to look that up (or at least ask here!) much earlier. I wasted way too much time and effort thinking that somehow *I* was causing the problem with a mistake somewhere!

Here's the answer from the Knowledge Base:

---------------------------------------
Running total fields display incorrect values if the details section is suppressed. The more group levels that exist on a report, the greater the chance of incorrect values displaying.

This issue has been assigned Track ID 31517 and is still outstanding in Crystal Reports 7. As a workaround, suppress each field in the Details section, then set Section Format for the Details section to "Suppress if blank".
-----------------------------

-Karyl