3 Replies Latest reply on Jan 16, 2015 8:49 AM by Stu412

Create new records based on the results of calculations in a report

Title

Create new records based on the results of calculations in a report

Post

Hi there

This is for me as much a concept question as a technique question.

I have created a report based on sub summaries and summary formulas to produce a result set.  I now need to use this result set in further calculations.

I have two key pieces of data that at this stage I'm interested in:

Number of staff (represented on my sub summary by data pulled from a customer record)

Share of profit (profit per customer / number of staff) where profit is also a summary field

What I need to do is populate a new table with a quantity of records per staff per customer...

So, let's say customer ABC (ID 1010)  has 3 staff and they each share profit of £60,000.  This would result in a profit share of £20,000 each.  Let's also say that customer DEF (ID 1011) has 2 staff and they each share profit of £20,000.  On the new table, what I need therefore is a set of three records like this:

ID | CustomerID | NoStaff | ProfitShare

1   | 1010           | 3          | 20000

2   | 1010           | 3          | 20000

3   | 1010           | 3          | 20000

4   | 1011           | 2          | 10000

5   | 1011           | 2          | 10000

I'll need to create 3 records for customer ABC and then populate these with the right info, then run the process again and create 2 records for customer DEF and populate these with the right info, and so on and so on for the numbers of customers and staff per customer I have.

I think what I need is a loop, a variable of total staff (3+2) to sit in the loop and create x new records.  Then somehow I need to populate the first record, then the second and third, and then when I populate the fourth, ensure that it's using the correct customer ID....

Nothing too complex.......!

My thoughts on how to do this could be completely wrong, so if there are other methods to do this, please let me know :)

Thanks

• 1. Re: Create new records based on the results of calculations in a report

What does each record in the new table represent? From what you describe, all three records for customer ABC would be identical. What would be different in each of the three records?

Why do you need such a table? (It's possible that you might get the desired results without adding a table of denormalized data.)

• 2. Re: Create new records based on the results of calculations in a report

Hi Phil

Yes, strange but true.

Each record represents a person and how much they themselves have 'earned'.

I need to do some statistical analysis on the results set I'm looking to create.  A couple of things I need are mean, mode and median profit share through a range of unique individuals, along with top quartiles, so that is why I need what appear to be identical records, just to represent each individual and their wealth!  This will give me a total count of all people.

At the end, the total table needs to be printed for management to give a full view of the same earnings per unique person.

Thanks

• 3. Re: Create new records based on the results of calculations in a report

I've managed to set up a lovely little looping script which looks up the numbers of staff per record and creates the appropriate numbers of new records in the data table.  This means that columns 1, 2 and 3 from the original post are now complete.  A little progress :)

The final issue now is that the numerical data I wish to place against these new records (that which has already been calculated on the sub summary report) needs to have years filtered out.

I have a global field GF_YEAR which is used on the summary report to allow the user to use checkboxes to select the year of their choice.

I can get the total of all year's profits to the data table, but need them splitting by GF_YEAR.

I was thinking of a GetSummary(Profit;GF_YEAR) with the global field as the break but as yet this isn't working.

A little madness inducing because the sub-summary report is perfect, but it's just a matter of transposing the summary results per year to the new records.... :)