Cognos Framework Issue with joins

Question

I have two tables:

The Cost file:

Facility,
Product,
Cost Set,
Cost Bucket,
Cost

The Transaction file:

Facility, 
Product,
Quantity,
Date

My issue is that I need to run through the cost file for a specific cost set (03) and present the information in a list report as :

Facility:Product:Quantity:Bucket 1:Bucket 2:Bucket 3:etc. etc.

I have tried many things, but cannot get the information to display without the quantity aggregation getting hosed by the number of cost buckets for the facility/item combination. I am pretty sure it has to do with determinants, but I set them up and found it to be of no help.

Can anyone point me in a direction that might be helpful, or is there something more I can add to this description that might be useful?

Answer 1

What's the relationship of cost bucket to product? When you say "Bucket 1", "Bucket 2", etc are these the data values within the Bucket data item? If you could give us some sample data which ultimately generates one line of output, that'd be great.

General advice. If this model is solely for RS use (not QS adhoc), you may find it easier to simply create an object within FM which is custom SQL joining the required tables together.