How to sum of specific categories using a straight/pivot table (Financial Reports_

[#1]

Hi all,

I had some trouble to formulate an appropriate title for my problem, but I will try to describe the problem as well as possible.

What I try to realize is the following. I have a number of General Ledger entries that I want map to certain categories. This is quite easy to realize when using the ApplyMap() function.

GL_Account	GL_Account_Description
7301501	Total revenues X
7301502	Total revenues X
7301503	Total revenues X
7400302	Total revenues Y
7400303	Total revenues Y
7400304	Total revenues Y
7400305	Total revenues Y
7400304	Revenues Z
7400305	Revenues Z
7400306	Revenues Z
7701900	Revenues other
7701901	Revenues other
7701902	Revenues other
7760054	Services X
7760055	Services X
7760056	Services X
7701900	Services Y
7701907	Services Y
7750000	Services Y
7760054	Services Y
7760071	Services Z
7760072	Services Z
7760073	Services Z
9000002	Services other
9000003	Services other
9000005	Services other

However, if I want to sum specific categories of those GL Accounts (e.g. "Total net revenues" , "Total Services" , "Total Gross Margin") and try to visualize this by using a straight table, it seems to be quite hard.

Description	Calculation
Total revenues X	1
Total revenues Y	2
Revenues Z	3
Revenues other	4
Total net revenues	5 = 1 + 2 + 3 + 4
Services X	6
Services Y	7
Services Z	8
Services other	9
Total Services	10 = 6 + 7 + 8 + 9
Gross margin X	11 = 1 + 6
Gross margin Y	12 = 2 + 7
Gross margin Z	13 = 3 + 8
Gross margin other	14 = 4 + 9
Total gross margin	15 = 5 + 10

I know there a some alternatives to realize this ( e.g. using set analysis in expression, seperate objects, custom objects, etc). However, it is essential that the client has the opportunity to export the data to excel into one single overview.

Another option I got now is to pivot the fields into columns using a cross-table function and calculate the additional fields needed. I am quite unhappy with this option, because this requires lots of coding with potential errors as far more categories are need than shown in the example. Also, in terms of maintanability this option is quite sustainable as more categories could be added in the future.

I am wondering which alternative methods are there. Also links to related topics are very welcome.

Thank you!

Regards,

Stefan.

[#2]

Hi Roel,

Thank you for commenting with a great solution.

It looks very promising solution. And as far as I can see now it is also very easy to maintain in case additional business logic need to be added. I will work this solution out and let you know about the results.
Thanks!

Stefan

[#3]

Nice and clean approach!

1 question though, just out of curiosity:

Your expression in the loadscript is this:

'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &',null()))' as Expression

My question: what does the if/else and dimensionality() function add in this part?

I don't see any difference when I leave this out; perhaps I'm missing something (since I assume you didn't put it in without a reason)?

I thought the dimensionality() returns the number of dimensions (and in case of a pivot table it will increase with 1 for every +-sign you open...

How I now ready this if/else, is that always the if-part will be executed, and the else part will never apply. Maybe I'm missing someting? Perhaps someone can explain the purpose of this.

Kind regards,

Roberto

#edit: after some thinking I think the reason for adding it, is that always a minimum of 1 dimension is needed, meaning that this expression won't work in textboxes, gauges. Buttons etc.

if there are additional reasons, I'd like to hear!

[#4]

For those who also want to use this, I think there should be a small modification in the loadscript:

TEMPTABLE:
LOAD
//Origi 'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &',null()))' as Expression
'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &'),null())' as Expression
Resident ReportingTemplate;

>>change: inserted the red-colored ) and removed this ) after null()

As we could al see, the old expression did already work... So you could question what the issue is...

I discovered it when I tested the if (dimensionality() ) part....

I would expect to have the else-part (from the if/else) be active when I put this expression (or variable) into a textbox or some other object without dimensionality. I discovered that the else-part never got activated.

Notice, it is a cosmetic thing probably.

Should there be any comments on this, or if the idea is that this addition is incorrect, please let me know what and why

The final expression I would use is the one below, so you get an error when you try to use it in objects without dimensionality.

'if(dimensionality(),pick(match(ID,|' & concat(distinct ID,'|,|',ID) & '|),' & concat(distinct Formula,', ',ID) &'),''Error: this expression cannot be used in objects without dimensions'')' as Expression