KPI's Mapping from multiple lists
In my datahub I have transaction list that I deconstruct into financial elements, profit centre, cost centre, gl account, order number in a mapping table.
I have to report KPI's that are built up from multiple GL accounts and multiple Internal Orders, and I am wondering what approach to take to create a secondary KPI mapping from the original transactions?
I have tried using a List Property module but it falls down where there is duplication in one of the lists being mapped for a KPI. In this example the same Internal Orders are used for both KPI's but the GL04 is different.
Another idea I had was to create a module dimensioned with all 3 lists, (KPI, GL04 and Order Number) with a Boolean line item to capture the valid combinations, but I couldn't work out how to map that into an output using my original transactions.
Any suggestions would be greatly appreciated!