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 »

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: 2836
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 »

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 »

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: 5
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 »

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 »

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: 1261
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

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

Post by gtonkin »

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.
BR, George.

Learn something new: MDX Views
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 »

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: 3234
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.1.5
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

That should work:
ShaneK wrote: Wed Sep 25, 2019 7:57 pmvVAR = 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

IBM Champion 2024-2025
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
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 »

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: 3234
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.1.5
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

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

IBM Champion 2024-2025
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
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 »

Thanks so much Wim... All Sorted.. Was just going round and round in circles with this..
Wim Gielis
MVP
Posts: 3234
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.1.5
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

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

Wim Gielis

IBM Champion 2024-2025
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply