Forum Discussion
Dynamically Generate a Table from Information Distributed Across Data Structure
Thank you for your response. That helped me with another problem. Unfortunately, I do not think that works for my posted problem. Let me explain.
For each unique Ctr_Id in tblCtrMtrDetail, I need as many rows as their are indices associated with the price schedule associated with the Ctr_Id. For example, for Ctr_Id =1 and Mtr_Id = 1, I have volume 100. Ctr_Id =1 has an associated Price Schedule Id =1. Price Schedule Id = 1, has 3 price indices associated with it Index_Id ={1,2,3} with Index_Proportions of {25%,25%,50%} for index 1,2,and 3 respectively. For the given CtrMtr combination (1,1), I would need 3 rows in my auto-generated table.
for that scenario the three output table rows would need to be:
Ctr Id Mtr Id Sched_Id Index_Id Allocated Volume
1 1 1 1 25
1 1 1 2 25
1 1 1 3 50
TonySwad,
Could you please share sample data of your tables?
Regards,
Lydia
The following are samples of the data in the tables relevant to my posted problem.
The following table relates to a contract entity and is used to relate a schedule to each contract.
Ctr_Id Co_Id Cust_Id Sched_Id
1 1 1 1 2 2 2 2 3 3 3 3 4 1 4 4 5 2 5 1 6 3 1 2 7 1 2 3 8 2 3 4 9 3 4 1 10 1 5 2 11 2 1 3 12 3 2 4 13 1 3 1 14 2 4 2 15 3 5 3 16 1 1 4 17 2 2 1 18 3 3 2 19 1 4 3 20 2 5 4 21 3 1 1 The following table is the detail for the rate schedules. Each rate schedule is the weighted average of one or more indices. The table contains a row for each index used in a given rate schedule and the weighting applied to the index in that given rate schedule.
Sched_Id Index_Id Index_Proportion
1 1 0.25 1 2 0.25 1 3 0.25 1 4 0.25 2 1 0.5 2 2 0.5 3 1 0.333333333 3 2 0.333333333 3 3 0.333333333 4 1 1 This table contains metered volumes assigned to a contract in a given month.
Ctr_Id Mtr_Id Volume Prod Date
1 9 5,810 12/1/2017 1 19 3,081 12/1/2017 1 20 2,388 12/1/2017 2 2 2,985 12/1/2017 2 34 488 12/1/2017 2 39 8,470 12/1/2017 3 11 4,946 12/1/2017 4 25 (7,056) 12/1/2017 4 40 5,687 12/1/2017 5 4 8,325 12/1/2017 6 16 8,883 12/1/2017 6 18 (9,178) 12/1/2017 6 24 (245) 12/1/2017 6 31 (6,818) 12/1/2017 6 37 (2,811) 12/1/2017 7 17 (2,447) 12/1/2017 7 30 2,288 12/1/2017 8 27 4,516 12/1/2017 9 14 7,995 12/1/2017 9 21 4,011 12/1/2017 9 29 6,184 12/1/2017 10 26 7,255 12/1/2017 11 7 (7,500) 12/1/2017 11 33 (4,271) 12/1/2017 12 10 4,029 12/1/2017 13 5 (1,131) 12/1/2017 13 28 (877) 12/1/2017 14 1 (258) 12/1/2017 14 36 (844) 12/1/2017 14 38 (6,577) 12/1/2017 15 12 (4,918) 12/1/2017 16 3 3,982 12/1/2017 17 15 5,153 12/1/2017 17 35 (9,369) 12/1/2017 18 6 (1,346) 12/1/2017 19 8 (917) 12/1/2017 19 13 9,838 12/1/2017 20 22 (4,111) 12/1/2017 20 32 (9,260) 12/1/2017 21 23 (1,758) 12/1/2017 The problem is to allocate the metered volumes per contract-meter-month record to the individual indices to which the metered volume is exposed. I was hopeful of creating a table with the following headings:
Ctr_Id Mtr_Id Prod_Mo Sched_Id Index_Id Volume
such that when I added the volume for a given Ctr_Id Mtr_Id Prod_Mo across Index_Id's, the total would equal the volume for the Ctr_Id Mtr_Id Prod_Mo record in the 3rd table above.
By generating such a table, I can write one set of measures on that table that allows me to report at the level of detail I require.