Avoiding a large intermediary module

GeorgeDuckett

I have a list of transaction codes that a user wants to summarize in a report that replicates what they've produced in excel. The transaction codes don't form a natural hierarchy for this. The excel report pulls out various individual codes, groups some together, summarises others etc.

 

To achieve this I've got a line item subset with each of the report's line items in it. The user can then define what codes go into which line items. The way I do this is to have a DATA list where each item has a line item from the report, and a code to be included:

I then have an "included?" line item in a module with the formula: DATA Management Reporting Account Codes.Included?[ANY: DATA Management Reporting Account Codes.GL Account, ANY: DATA Management Reporting Account Codes.LineItem, and an "Amount" calculated with IF Included? THEN 'REP01 - Amounts by GL Code'.ClosingBalance[SUM: PROP G GLCodes.'G2 Department', SUM: PROP G GLCodes.'GA3 Account Code'] ELSE 0 both dimensioned by GL code and the lineitem subset.

The report module then uses the Amount line item: 'SYS01 - Management Reports GL Code Mapping'.Amount[SELECT: 'GA1 Financial Statements'.Total, SELECT: 'Management Reports (SS)'.International]

The problem I have is that this intermediary module is very large. The number of GL accounts * number of report module line items is big. (I'm leaving out some other dimensions common to the report and subsequently the intermediary module which further increase the size).

 

Is there any way I can remove, or reduce the size of the intermediary module?

andrewtye

Exactly a composite list of the properties needed or at least the GL Account / line item ie where there's most sparsity. It would probably need to be a numbered list because of the length of the list name item required. This would then flatten the hierarchy out.

BTW... how large are we talking for the intermediary module?

TimPeterson

Are you able to use list subsets for department or account codes, if the entire lists aren't relevant to the transaction codes? Or can you potentially drop the account codes dimension from the intermediary module if you have the mapping module to define what account codes are used in the calculations?

andrewtye

Are the combinations relatively fixed? So once set that's basically it?

Why not consider creating a new list of the combinations involved and then doing the summing using that. Then you wouldn't need to have both dimensions at full scale.

And even if it was changeable then to run the process wouldn't take too long (and a lot more quickly than an equivalent SUMIFS in Excel).

GeorgeDuckett

Thanks for the reply. I could look into creating a subset, if not all codes are reported on / turn up as part of a line item, although I think it's a mistake if they don't.
Re. dropping the account codes dimension I'm not sure what that would make the formulas look like in the reporting module?

GeorgeDuckett

Thanks for the reply. They are relatively fixed yes, although like you say even if not having an update process isn't a big deal. I don't quite follow what you mean about creating a list with the combinations involved. Isn't that what I have with the DATA Management Reporting Account Codes list? Or do you mean create a composite list with every dimension in the intermediary module?

GeorgeDuckett

Got it. Makes sense, thanks. Currently just under 3/4 of the total model size, which is around 3gb. Currently not big enough to justify the added complexity of a composite list, but good to have options for the future (and I'd asked the question hoping for a formula technique I wasn't aware of rather than going down the composite list route which is obviously more involved).