TM1 Forum

I am a new TM1 developer and my background is an accountant, not a programmer.
I have been struggling with this problem and unable to find an answer in any of my searches or documentation.

Here is my problem:

The business rule to calculate Earning EOC in the Payroll Budget cube does not return a value.

Also, If I could get that working, I would like to create the GL Account measure in the Payroll Budget Cube to be a combination of Dept and Earning EOC:
[For Example Dept="17.71.1155000" and Earning EOC="3532111" then the GL Account using VBA string concatenation would be built by this VBA code :

{=Left(Dept,2) & "1" & Right(Dept,11) & "." & Earning EOC} = "171.71.1155000.3532111."

The pipe character is used for concatenation in TM1 rules from what I've read, but can you use string extraction/variables to create the GL account

measure as in my example here?

Below are the Cubes in the example (as well I've uploaded a screenshot of the TM1 screens here:

https://docs.google.com/leaf?id=0B4mA78 ... ist&num=50

Cube: Payroll Budget
Dimensions: Dept, Job Code, Earning
Measures: Hours, Base Rate, Dollars, Job Code EOC, Earning EOC, GL Account

Cube: Job Code Info
Dimensions: Job Code
Measures: Base Rate, Job Code EOC

Cube: Earnings Info
Dimensions: Earning, Earning EOC
Measures: Job Code EOC

Here are the business rules in the Payroll Budget cube:

['Base Rate' ] = DB('Job Code Info', !Job Code, 'Base Rate');
['Dollars' ] = ['Hours' ] * ['Base Rate' ] ;
['Job Code EOC' ] =DB('Job Code Info', !Job Code, 'Default EOC');
['Earning EOC' ] = DB('Earning Info', !Earning, DB('Payroll Budget', !Dept, !Job Code, !Earning, 'Job Code EOC'), 'Earning EOC');

If I use this below for the rule it works and returns "3132311" from the earnings cube for earning 0060 for example:
['Earning EOC' ] = DB('Earning Info', !Earning, '3111011', 'Earning EOC');

So using DB('Payroll Budget', !Dept, !Job Code, !Earning, 'Job Code EOC') to supply the Job Code EOC dimension for the Earning Info cube did not work.

I also tried replacing the above DB with this DB ('Job Code Info', !Job Code, 'Job Code EOC) which I thought should also work but it does not.

Am I doing something wrong or can you not use a lookup cube with more that one dimension? And the second part of my problem, can you use string
manipulation to combine dimension/measures to create a new measure GL Account in this example?

I thank you in advance for any help,
Please let me know if any other info is required.

Regards,

...Tom

This kind of lookup to return an element name from a cube or attribute reference does work and is very common. Just make sure that the value that is retrieved is a string not a numeric value.

I use this all the time. They key here is that if you are going to use a lookup cube to supply the name of a dimension element, just like you are attempting to do in your example, then 1) the value you are retrieving must be a string and 2) it has to represent an actual element name from the dimension you are plugging it into. I'm guessing one of these conditions is FALSE in your case.

Thanks lotsaram/tomok for your help.

Yes the Job Eoc measure was a simple element type, so I changed it to string. I also made the name of the element the same in all 3 places (Job Code Info Measure, Payroll Budget Measure, Earnings Info lookup cube). At first it did not work until I put a S: in front of the DB reference as below which works:

['Earn Eoc' ] = S:DB('Earnings Info', !Earnings,DB('Job Code Info', !Job Code, 'Job EOC') , 'Earn Eoc');

So that solves the first part of my problem. I got part ways with the second part of my problem on building the GL Account measure by a rule from the known components now as follows:

['GL Account' ]=S:!Dept|'.'|DB('Payroll Budget', !Dept, !Job Code, !Earnings, 'Earn Eoc');

Here for example !Dept = 22.71.1234567 and 'Earn Eoc' = 3556510 and ['GL Account'] returns:

22.71.1234567.3556510 when it needs to be:
221.71.1234567.3556510

So is there any string extraction formula available in a rule that works like the LEFT, MID, RIGHT functions in VBA that can break apart the !Dept into the first 2 characters and the last 11 characters?

OK I found it - The SUBST function

SUBST
This is a TM1 rules function, valid in both TM1 rules and TurboIntegrator processes.

SUBST returns a substring of a given string.

Syntax
SUBST(string, beginning, length)Arguments

Argument
Description

string
The string from which you want to extract the substring.

beginning
The character at which the substring begins.

length
The length of the substring.


Example
SUBST('Retirement', 3, 4) returns 'tire'.

Here is the rule I used that works:

['GL Account' ]=S:SUBST(!Dept,1,2)|'1'|SUBST(!Dept,3,11)|'.'|DB('Payroll Budget', !Dept, !Job Code, !Earnings, 'Earn Eoc');

that returns 221.71.1234567.3556510 as desired.

Thanks again...

...Tom