Rules Based on Element Selection

[damientaylorcreata]

Hi Guys,

I have been asked to make the following alteration to one of our TM1 Cubes:

We have a Reporting cube which contains all company transactions named GMA.

The GMA cube can be used for both P&L and Balance Sheet. However one of the consolidations "Net Profit" is used in the consolidation for the equity consolidation (for balance sheet reporting). Everything is fine with this, however we also have a element named "USD Reporting" which basically is populated by a rule which converts the figures to USD currency no matter what currency the transaction is recorded as.

However this means that the conversation is made at the rate for each transaction. What we need is to take the net profit figure and convert the entire consolidation by the currency of the year and month selected from the dimension drop down.

Therefore my question is instead of converting a value based on the date of the individual record by appending with a exclation mark, e.g. !Year and !Month. Is there a way to pass the selected element of the drop down menu.

Here is the current rule that converts all values to USD:

['USD Reporting'] = N:
['AUD']\DB('GMA_FX',!Year,!Period,'AUD','USD Average')+
['BRL']\DB('GMA_FX',!Year,!Period,'BRL','USD Average')+
['CNY']\DB('GMA_FX',!Year,!Period,'CNY','USD Average')+
['EUR']\DB('GMA_FX',!Year,!Period,'EUR','USD Average')+
['GBP']\DB('GMA_FX',!Year,!Period,'GBP','USD Average')+
['JPY']\DB('GMA_FX',!Year,!Period,'JPY','USD Average')+
['NZD']\DB('GMA_FX',!Year,!Period,'NZD','USD Average')+
['SGD']\DB('GMA_FX',!Year,!Period,'SGD','USD Average')+
['MXN']\DB('GMA_FX',!Year,!Period,'MXN','USD Average')+
['ARS']\DB('GMA_FX',!Year,!Period,'ARS','USD Average')+
['USD']\DB('GMA_FX',!Year,!Period,'USD','USD Average');

My question is: If was to create an addition rule for ['USD Reporting', 'Net Profit'] = C: ........... Would I be able to pass to the DB function values selected from the Year and Month Drop down menus. So that the Net Profit consolidation could be converted for the select year and month as a whole instead of each transaction making up Net Profit being converted at the individual months and years.

Any help would be very much appreciated.

Thanks,
Damien

[belair22]

I'll try to specifically answer your queries in 2 parts :

1) Short answer is no - you can't define TM1 Rules to interact with any of the 'dropdowns' in your Cube Viewer.

2) Yes - you can 'override' the Net Profit consolidation using a '= C:' rule.

Your 'C-Level' 'Net Profit' rule should be something like:

['USD Reporting', 'Net Profit'] = C: ['AUD', 'Net Profit'] \ DB('GMA_FX',!Year,!Period,'AUD','USD Average') + ....

[damientaylorcreata]

Yes, thanks for your reply.. This should do the trick.. In this case I assume that !year and !month will relate to current year and month selected as it will be looking at the consolidation and not the individual transactions. I have tested and it seems to be doing what is needed.

Thanks so much for your help.