0

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?

LorenzoDonati4Ukraine-OnStrike
  • 6,875
  • 4
  • 37
  • 56
Jeff
  • 329
  • 3
  • 13

1 Answers1

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.

toddsonofodin
  • 513
  • 2
  • 4
  • If the transaction file has Facility: WH and product: ABC The cost file might have 10 buckets which each have a different component of the cost for the end item, so you would have a different row for each as in Row 1:WS;ABC;03;1;.50 Row 2:WS;ABC;03;2;1.00 Row 3:WS;ABC;03;3;.75 etc. through 10 – Jeff Jul 19 '13 at 17:31
  • So 1 transaction could have many costs. When you say your expected output is Bucket 1, Bucket 2 are you looking for the quantities in each bucket? It seems Bucket is an attribute, not a measure. – toddsonofodin Jul 22 '13 at 13:45
  • Yes 1 transaction (or item) has multiple buckets, and that is an attribute. For example, the item might be a car...bucket 1 might relate to tires and they might cost $428.54 , bucket 2 might relate to an engine which might cost $750.36. So the table would look like... – Jeff Jul 22 '13 at 14:05
  • WS : CAR : 03 : 1 : 428.54 WS : CAR : 03 : 2 : 750.36 – Jeff Jul 22 '13 at 14:07
  • on another note: isn't there an easy way to enter newline in these comments?!?!?!? – Jeff Jul 22 '13 at 14:07