Bridging Table Not Working For Me

Hi All,

        I have been working with the Microsoft project server data schema to report on resource demand (held in the ResourceDemandTimephasedDataset - RDTD) table versus capacity (held in the ResourceTimephasedDataset - RTD) table.

I added a bridging table (Date) in addition to the resource table to accomodate the many to many relationship between RDTD and RTD. See below for table setup and relationships.

The RDTD can have multiple records for the same day for any resource. The RTD table will have a capacity for the same day of 7.5 hours (37.5 hours per week). So, for example person A on the 26th Feb could have 2 records in the RDTD table because they are working on 2 projects on the same day. The RTD will have 1 record or the 29th March for Resource A showing capacity of 7.5 hours.

My problem is that when I have a resource that has two records for the same date in RDTD and I try to generate a report to show demand (which displays correctly) versus capacity (per week) the report display 75 hours (2 * 37.5 hours) capacity ie. one 37.5 hour weekly capacity for each demand record in RDTD.

Hope thsis clear. Any help greatly appreciated.

Tables:

Relationships

Report