Joining three tables in a Subreport

[kuberacupidagra]

Hello All,

Using CR 10.

I am creating a Subreport called "Overhead Hours". The Main report is called "Overhead Dollars". There are three tables in each report. There are no variables passed from the Main to the Sub report. The Overhead Dollar report gives correct values for each Month and YTD for each department.

The Subreport has three tables namely:

Departments:
Fields: T_CWOC, T_DSCA, Category, Dept

Overheadlaborhours:
Fields: Acctg_date, work_dept, Activity, Acct, Hours

Overheadhours:
Fields: T_Leac, T_Desc and Overhead_type

The tables are joined as:
Departments.Dept = Overheadlaborhours.work_dept
Overheadlaborhours.Acct = Overheadhours.T_Leac

For December data I use the following:

@Hr_12=
Local DateVar d_start ;
Local DateVar d_end ;
Local Numbervar RptMonth := 12 ;
// start of fiscal month
d_start := DateSerial ({@ReportYear1}, RptMonth , 1-1 ) ;
d_start := d_start - (DayOfWeek(d_start, crFriday) - 1) ;
// end of fiscal month
d_end := DateSerial ({@ReportYear1}, RptMonth , 31 ) ;
// select data for fiscal month
if ( {OVERHEADLABORHOURS.ACCTG_DATE} in ( d_start to d_end ) )
and ({OVERHEADLABORHOURS.ACTIVITY} = 'OVERHEAD' )
then {OVERHEADLABORHOURS.HOURS}
else 0 ;

I then Sum by Group 1 as:
@Hr_12_Dept_Sum=
sum({@Hr_12},{DEPARTMENTS.DEPT})

There are about 30 departments in the subreport and about 20 of them give correct results when summed on the Department.Dept. However, others are 2 or 15 times higher than what the values should be.

I feel there is a Cartesian Product which is increasing the values.

I would appreciate some feedback and how to resolve it.

Thanks and Regards,

AA

 

[synapsevampire]

Well, at least row inflation.

This means that you need to understand your data better, perhaps your dba can shed some light upon this.

Your formula has nothing to do with understanding your data, you need to look (perhaps ost) what is in the data, and what it is you need.

Including the database type should help as well. Keep in mind that subreports are slow, if possible, move that processing back to the database using Views or Stored Procedures.

-k

 

[kuberacupidagra]

Hello synapsevampire,

The data in overheadlaborhours table is as follows:

Acctg_date Work_Dept Activity Acct Hours
12/08/05 601 OVERHEAD 9001-000 40.0
12/08/05 601 OVERHEAD 9102-000 8.0
...

12/15/05 603 OVERHEAD 9101-000 8.0
12/15/05 603 OVERHEAD 9101-100 8.0


Overheadhours table is as follows:

T_LEAC T_DESC Overhead_type
9101-000 Indirect Labor Overheadlabor Hours
9102-000 Vacation Pay Overhead Fringe Hours

Departments table is as follows:

T_CWOC T_DSCA Category Dept
LOA Leave of Absence DS 603
MAA01 Payroll OH 604


Using SAS datasets.

Would look forward to your help.

Regards,
AA

 

[kuberacupidagra]

The problem is solved. It was because of cartesian product or data inflation. In the department table for each department(Dept field) there are several work center(T_CWOC) records which was inflating the numbers. The only field that I need from the Department table was T_DSCA which I got from another table. I removed the Department table from the report and the numbers add up fine.

Thanks,

AA