How to get the the value out of a Amounts Dimension

Post Reply
ShaneK
Posts: 6
Joined: Thu Dec 20, 2018 8:34 am
OLAP Product: Cognos Analytics
Version: 2.0.4
Excel Version: 2013

How to get the the value out of a Amounts Dimension

Post by ShaneK » Wed Sep 25, 2019 12:50 pm

I'm trying to create a TI Process to extract data from my Budget Cube into SQL Server and i can pass everything except the Value from the BudgetAmount Dimension. If i # out vVAR and just load 1 instead it works but i think the vVAR is trying to convert the leaf Value instead of the Number it's holding..

Can anyone tell me how to get the number and not the leaf from the Value Leaf of my BudgetAmounts Dimension.. BudgetAmounts has a value and a FX Value leaf.


vVAR = 'CellGETN(Value)';

vTemp = '';
vChar = '';
k=1;
WHILE( k <= LONG(Customer) );
vChar = SUBST(Customer, k, 1);
IF(vChar @= '''');
vChar = ' ';
ENDIF;
vTemp = vTemp | vChar;
k = k + 1;

END;
Customer = vTemp;


ODBCOutput('LIVE01-REPO',
'INSERT INTO repository.mapping.TM_TEMP
(
[vNominal]
,[vEntity]
,[vMK2_Cust]
,[Vmast_Cust]
,[vAcc_Mgr]
,[vPeriod]
,[vBU]
,[vDepartment]
,[vREGION]
,[vSALES_PERSON]
,[vVersion]
,[vValueType]
, [vValue]
)
VALUES (''' | Nominals| ''','''
| Entity | ''','''
| Customer | ''','''
| Master_Customer | ''','''
| Account_Manager | ''','''
| BudgetCalendar | ''','''
| BusinessUnit | ''','''
| Department | ''','''
| Regions | ''','''
| Sales_Person | ''','''
| Version | ''','
| numbertostring (vVAR ) | ')');

tomok
MVP
Posts: 2631
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How to get the the value out of a Amounts Dimension

Post by tomok » Wed Sep 25, 2019 12:58 pm

ShaneK wrote:
Wed Sep 25, 2019 12:50 pm
vVAR = 'CellGETN(Value)';
What is this formula? The CellGETN formula needs one parameter for the cube name, and one parameter for each dimension in the cube specifying an element from that dimension. This thing you have does nothing.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

ShaneK
Posts: 6
Joined: Thu Dec 20, 2018 8:34 am
OLAP Product: Cognos Analytics
Version: 2.0.4
Excel Version: 2013

Re: How to get the the value out of a Amounts Dimension

Post by ShaneK » Wed Sep 25, 2019 6:39 pm

Hi, Thanks for replying, i found that on a IBM Fix link, and couldn't get it to do anything.

What I’m trying to do is get the data out of the BudgetAmounts Dimension, Value Leaf..

In the Variables page, it shows the BudgetAmounts as a Element Dimension and the Value Data Element..

I've tried numbertostring (BudgetAmounts ) and numbertostring (Value ) but both return a non numeric error, I’ve spent days googling this but can't find the answer.

I know the TI Proc works as i can send the load to SQL Server when i use numbertostring (1 )

I just can't figure out how to get the number associated to BudgetAmounts.Value into the numbertostring conversion.. I don't think it's a ATTR option, but could be wrong..

I don't even know what to google on this.. Data element of leaf in TM1 and a million other variations..

Any ideas?

Thanks

jwilliamson_uci
Posts: 3
Joined: Wed Dec 08, 2010 11:32 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: Excel 2013

Re: How to get the the value out of a Amounts Dimension

Post by jwilliamson_uci » Wed Sep 25, 2019 7:00 pm

Hi Shane,

Your vVar should not have quotes around it. You are setting vVar equal to "CellGetN(Value)", which is a string. At the bottom, when you are trying to switch that from a number to a string, it is getting confused because it is already a string.

Jeff

ShaneK
Posts: 6
Joined: Thu Dec 20, 2018 8:34 am
OLAP Product: Cognos Analytics
Version: 2.0.4
Excel Version: 2013

Re: How to get the the value out of a Amounts Dimension

Post by ShaneK » Wed Sep 25, 2019 7:13 pm

Thanks Jeff, one step closer.

Now i'm getting this error message though

"1110112","DimLHINT","Unknown","Unknown","Unknown","P1","HIH-OCC","SALES","AMERICAS","Jane Poulton","Budget 2019-20","Value","128.2051282",Data Source line (1) Error: Data procedure line (0): Cannot convert field number 12, value "Value" to a real number.

I need to extract the 128.2051282 which is linked to the Value Leaf in BudgetAmounts..

vVAR = 'CellGETN(Value)'; just tried to send leaf called Value into a Decimal Field in a table on SQL Server

Also Tried

vVAR = CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value');

Same Error

Any thoughts?

User avatar
gtonkin
MVP
Posts: 736
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: How to get the the value out of a Amounts Dimension

Post by gtonkin » Wed Sep 25, 2019 7:43 pm

Field 12 looks like your measures and should be string.
Field 13 is the value and should be accessible using NValue or SValue depending on your measure type.

ShaneK
Posts: 6
Joined: Thu Dec 20, 2018 8:34 am
OLAP Product: Cognos Analytics
Version: 2.0.4
Excel Version: 2013

Re: How to get the the value out of a Amounts Dimension

Post by ShaneK » Wed Sep 25, 2019 7:57 pm

Hi gtonkin, any chance you could elaborate on that please, i'm not being lazy i just don't know how to google this stuff properly at the moment?

Can i use that with

vVAR = CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value');

or

vVAR = 'CellGETN(Value)';

Field 13 is numeric variable type

Thanks

Wim Gielis
MVP
Posts: 2258
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: How to get the the value out of a Amounts Dimension

Post by Wim Gielis » Wed Sep 25, 2019 11:51 pm

That should work:
ShaneK wrote:
Wed Sep 25, 2019 7:57 pm
vVAR = CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value');
To retrieve a numeric value from a cube.
Alternatively use NValue which is the value that you have in the Metadata / Data tab.
Or still use your last variable to store the result.
Then use NumberToString to convert it and insert in the relational database.
All your variables except the last one should be string, the last one can be ignored unless you use it in the code.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

ShaneK
Posts: 6
Joined: Thu Dec 20, 2018 8:34 am
OLAP Product: Cognos Analytics
Version: 2.0.4
Excel Version: 2013

Re: How to get the the value out of a Amounts Dimension

Post by ShaneK » Thu Sep 26, 2019 6:03 am

Hi Wim,

Thanks for that, but are you able to provide a sample using NValue please?.

My Cube is called Budget_MK2

CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,BudgetAmounts)

The last Dimension in the Cube Holding data is caleld BudgetAmounts

Inside BudgetAmounts Dimension i have 2 leaf called Value & FXValue

I'm trying to pick out the numeric value from the leaf called Value.

Do you know how to use NValue or CellGetN to do that?

Thanks

Wim Gielis
MVP
Posts: 2258
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: How to get the the value out of a Amounts Dimension

Post by Wim Gielis » Thu Sep 26, 2019 7:25 am

As to the numeric value (cell value), you have 3 options:

Code: Select all

NumberToString( CellGetN('Budget_MK2',Nominals,Entity,Customer,Master_Customer,Account_Manager,BudgetCalendar,BusinessUnit,Department,Regions,Sales_Person,Version,'Value'))
or

Code: Select all

NumberToString( NValue )
or

Code: Select all

NumberToString( whatever_you_call_your_13th_variable_which_should_be_type_numeric )
All 3 methods have advantages. The only prerequisite is that you know what you are doing.

NValue refers to the value of cell that the TI process is processing in the view containing the data (=the data source). If you don't use a view as the data source of the process, you cannot use NValue.

In case there are issues with separators or delimiters, use the AsciiOutput function to write the values to a text file, and change the code based on that output and your evaluation of it.

Hope this is clear.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

ShaneK
Posts: 6
Joined: Thu Dec 20, 2018 8:34 am
OLAP Product: Cognos Analytics
Version: 2.0.4
Excel Version: 2013

Re: How to get the the value out of a Amounts Dimension

Post by ShaneK » Thu Sep 26, 2019 7:58 am

Thanks so much Wim... All Sorted.. Was just going round and round in circles with this..

Wim Gielis
MVP
Posts: 2258
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: How to get the the value out of a Amounts Dimension

Post by Wim Gielis » Thu Sep 26, 2019 9:20 am

You're welcome. Glad it's solved.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

Post Reply