Hi there once again,

it is raining here, in Europe

I've managed to create a Report (a kind of a List Report) for Opportunities. I managed to output some Data Fields from Opportunities in Data Row.

I also managed to get a Total of all listed Opportunities in Summary Row by putting 'parent.parent.reportData.objects.@sum.total' token (?) into cell.

Question is as follows: How to get total for Profit?

I.e. each Opportunity has a Total (sum of an LineItems), hence each Opportunity should have its Profit? A kind of 'parent.parent.reportData.objects.@sum.profit'

But it doesn't work. I need a Profit Total next to Opportunities Total.

Any ideas? MC? Community?

Andy

Actually we don't have that key. We'll have to add it in the future.

You could do in an F-Script token though.
You would iterate over all the opportunities, then for each opportunity, you iterate over the lineItems.

Also - you should use cachedTotalAmount as the key - it is faster. We specify this key in the data description doc.

AJ,

Any examples of iteration over lineItems? Where do I have to put this code? Into Summary Row, as @sum.total? My table has Header, Body, and Summary Rows: parent.parent.reportData.objects.@sum.total is "sitting" at the summary row.

Help?!

Andy

This one had me digging....
This is how you do it.

In the summary row, add a script token. (get from Dynamic text -> Special -> Script) and do the following script:

  
opportunities := element valueForKeyPath:'enclosingReporTable.reportData.objects'.

"Get all cost of goods"
all_cost_of_goods := opportunities valueForKeyPath:'lineItems.costOfGoods'.

"Because this is an array of arrays, we need to flatten it"
all_cost_of_goods := all_cost_of_goods flattenArrayOfArrays.

"As bug repellant, remove any nils and nulls"
all_cost_of_goods := all_cost_of_goods difference:{nil}.
all_cost_of_goods := all_cost_of_goods difference:{(NSNull null)}.

"Sum all costs together using F-Script's reduction and compact block concepts"
sum_of_all_costs := all_cost_of_goods \ #+.


"Now get the totals and get rid of any nils at the same time. In this case 
we don't have to worry about NSNull since we are not using valueForKeyPath"
totals := (opportunities cachedTotalAmount) difference:{nil}.

sum_of_totals := totals \ #+.

"Now get the profit"
profit := sum_of_totals - sum_of_all_costs.

"Return the profit"
profit.

F-Script provides some powerful array operators - it's crazy how powerful it is in the area. You can do all kinds of fancy operations on the array of totals or costs or together.
Look at Sections 16, 17, 18 in the F-Script Guide.

That is awesome, AJ!

But... This kind of solution is not for me either...
I am an ordinary user with some knowledge in databases, SQL, etc.

Isn't it be easier to have @sum.profit, just like @sum.total ?

parent.parent.reportData.objects.@sum.total

and

parent.parent.reportData.objects.@sum.profit

? ?

Yes - we will have to add that to make it easier (as mentioned above). That solution is pretty scary - but at least we didn't make it impossible