AnsweredAssumed Answered

virtual list with totals

Question asked by greaterthandata on Sep 10, 2018
Latest reply on Sep 10, 2018 by beverly

I'm working on a cross tabular report using the virtual list technique and running into an issue.

 

The goal of the report is to create a financial report similar to a P&L, along the top will be months in a year, and the left side would be account types.

                                             January               February               March               April

Revenue from Product A

Revenue from Product B

Revenue from Product C

Total Revenue

 

Expenses

 

I am using a single SQL query to gather all the financials for January in one global field with the pkaccount then comma and the total for each pkaccount.  so for January this global field will look like this:

 

pkaccount, total for the month

18, 384

23, 18

26, 41

34, 8

 

Then I am using an unstored calculation to get the value of each pkaccount for January and place them on the report.  This works quite well, but I'm having trouble on the TOTAL rows.

 

In the example above, Ideally I'd like to grab the sum of pkaccount 23, 26 and 34 (18 + 41 + 8 = 67 ) and show this value on the Total Revenue row.  I can do this using a loop in a script, but ideally I wanted to do this in the unstored field calcuation without the use of a script.

 

The TOTAL REVENUE record does have a pkaccountsSummarized text field which has the fkaccounts we want to get the sum of in a return delimited list (26 ¶23 ¶8), but how can I get the total values?  I was thinking I could by using a relationship, but ideally wanted to stay away from that since it would require so many new TOs.

 

I also thought i could use FILTERVALUES to compare the two 'lists', but can't figure out how to get the total values.

 

Is there a way to get the summary just using a calculation?

Outcomes