Hello,

We have a drilldown report that is exhibiting discrepancies between the summary and detail portions. The summary portion of the report is supposed to roll up all the records contained in the detail report based on the BY fields, and include the aggregated values for all the DETAIL columns. The last DETAIL column on the summary report (which is not aggregated from the detail report) is a count of the underlying records in the detail report.

For example: For a given part number, the summary report aggregates the DETAIL columns and displays a total count of 9 records that exist in the underlying detail report. However, the detail report for this given part number actually contains 10 records. Upon examination, we found that the last of these 10 records was being dropped from the summary report. We verified this by calculating the difference between the totals of several DETAIL columns on the detail and summary reports, and found that the difference was always equal to the value supplied by the tenth record on the detail report. (i.e. The difference between Actual Machine Hours on the detail and summary reports was 4.50 hours...the exact amount supplied by the tenth record on the detail report.) We then queried the underlying SQL tables that feed the detail report and duplicated the conditions of the detail report's BY fields. When we received 10 records from the raw data, this verified that something in the FEX is causing the tenth record to be dropped from the summary report.

Does anyone have an idea as to what might be the cause of the last record in the detail report being dropped when they are 'rolled up' into the summary report?

The two FEX files can be viewed here:

Summary Report

Detail Report

Please let me know if you have any questions or need further clarification,


sleepy

This message has been edited. Last edited by: sleepyEDB,

Without seeing the data it is hard to tell but here are some suggestions/questions that you might answer for yourself.

If you change the summary report to print instead of sum, do you get the same number of records that you get on the detail report.

Why are the WHERE clauses different? If they are the same, do you get the same number of records?

I find that the solution to these kinds of problems are rooted in the data and how it is processed.

Your date screening conditions are not equivalent:

Summary:
WHERE ( DW_ACTSTDRPT.ACTUAL_START GE '&ACTUAL_START.Start Date.' )
WHERE ( DW_ACTSTDRPT.ACTUAL_STOP LE '&ACTUAL_STOP.End Date.' )

Detail:
WHERE ACTUAL_START IS-FROM '&ACTUAL_START' TO '&ACTUAL_STOP'

quote:

Originally posted by GinnyJakes:
Without seeing the data it is hard to tell but here are some suggestions/questions that you might answer for yourself.

Thanks!

quote:

Originally posted by GinnyJakes:
If you change the summary report to print instead of sum, do you get the same number of records that you get on the detail report.

No. Using PRINT displays the same 9 records that are on the summary report when SUM is used, and drops the same tenth record from the detail report.

quote:

Originally posted by GinnyJakes:
Why are the WHERE clauses different? If they are the same, do you get the same number of records?

Honestly, I don't know why they are different as I did not write these reports. Are you referring to the differences in the date screening methods that j.gross posted about? If not, could you please specify?

quote:

Originally posted by GinnyJakes:
I find that the solution to these kinds of problems are rooted in the data and how it is processed.

That sounds logical.

Thanks for the input,


sleepy

quote:

Originally posted by j.gross:
Your date screening conditions are not equivalent:

Summary:
WHERE ( DW_ACTSTDRPT.ACTUAL_START GE '&ACTUAL_START.Start Date.' )
WHERE ( DW_ACTSTDRPT.ACTUAL_STOP LE '&ACTUAL_STOP.End Date.' )

Detail:
WHERE ACTUAL_START IS-FROM '&ACTUAL_START' TO '&ACTUAL_STOP'

Interesting.

As I mentioned in my reply to Ginny, I did not write these fexes, and I apologize for the discrepancy. I can see that GE and LE will include records that begin and/or end on the actual start/stop dates specified by the user, but what is the behavior of IS-FROM? Is it inclusive or exclusive with respect to the endpoints of the range? Meaning, if the user specifies a date range of 3/15/08 to 4/27/08, will jobs that began and/or ended on those exact dates be included when IS-FROM is used?

Also, which of these two options is preferable...should I replace the Summary version with what's in the Detail report, or vice versa?

Thanks for noticing the difference,


sleepy

IS-FROM should be equivalent to GE ... LE

-- But my point was they are testing different database variables against the user-supplied end-date.

I agree. That is the point I was trying to make. The screening conditions have to be the same if you want to get the same data.

Thank you both very much. You were both correct...it was the difference in date screening conditions between the two reports that was dropping the last record. We changed the fexes so they were screening the same database variable (DW_ACTSTDRPT.ACTUAL_START) and I now get 10 records on both the detail and summary reports.

Summary:
WHERE ( DW_ACTSTDRPT.ACTUAL_START GE '&ACTUAL_START.Start Date.' )
WHERE ( DW_ACTSTDRPT.ACTUAL_START LE '&ACTUAL_STOP.End Date.' )

Detail:
WHERE ACTUAL_START IS-FROM '&ACTUAL_START' TO '&ACTUAL_STOP'

Thank you both very much!!


sleepy