ASCIIOutput - Measures across top

Post Reply
jimicron
Posts: 110
Joined: Tue Oct 30, 2012 5:21 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP2 MSO
Location: Boise, ID

ASCIIOutput - Measures across top

Post by jimicron » Tue Aug 06, 2013 8:45 pm

Hi all,

I was wondering if you knew how to accomplish this. I am exporting into a .csv a multidimensional cube. I want my measures to be across the top - in this case, they are Expense, Cost Per Module, and Cost Per Component. I've accomplished that - no problem. :) However, it's doing it in a line per measure versus just combining (see my example). This typically wouldn't be a big issue, but this cube is quite large and that will 'eat up' a lot of space needlessly.

Am I almost there? :?

Basically, all my code is on my Data tab and is as follows:

Code: Select all

#****Begin: Generated Statements***
vExpense=
IF (Measure @= 'Expense' , Value , '' );
vCostPerModule=
IF (Measure @= 'Cost per Module' , Value , '' );
vCostPerComponent=
IF (Measure @= 'Cost per Component' , Value, '');
#****End: Generated Statements****

IF (Header = 1);

# Following are column header names

ASCIIOutput('\\analyst-app\libraries\TM1\BU\SIG\SIG Module Cost 2.csv', 

'Month' ,
'Site' , 
'Expense Type' , 
'MPN' , 
'Expense' ,
'Cost per Module' ,
'Cost per Component' );

ENDIF;

# Following are variables from the variables tab of this process

ASCIIOutput('\\analyst-app\libraries\TM1\BU\SIG\SIG Module Cost 2.csv', 

vMonths, 
vSite , 
vExpenseType , 
vMPN , 
vExpense ,
vCostPerModule ,
vCostPerComponent  );

Header = Header -1;
Thanks a lot for any suggestions to get it like I am showing below in the middle screenshot!
Attachments
ASCIIOutput Example.jpg
ASCIIOutput Example.jpg (123.39 KiB) Viewed 1844 times

declanr
MVP
Posts: 1588
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: ASCIIOutput - Measures across top

Post by declanr » Tue Aug 06, 2013 8:49 pm

Jimicron,

The exact same principles will apply as they did in this topic:
http://www.tm1forum.com/viewtopic.php?f ... 543#p42543

HTH

jimicron
Posts: 110
Joined: Tue Oct 30, 2012 5:21 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP2 MSO
Location: Boise, ID

Re: ASCIIOutput - Measures across top

Post by jimicron » Tue Aug 06, 2013 9:01 pm

So change the variables to the following?

Code: Select all

#****Begin: Generated Statements***
vExpense=
CellGetN ('SIG Module Cost', vSite, vMPN, vExpenseType, vMonths, 'Expense');
vCostPerModule=
CellGetN ('SIG Module Cost', vSite, vMPN, vExpenseType, vMonths, 'Cost per Module');
vCostPerComponent=
CellGetN ('SIG Module Cost', vSite, vMPN, vExpenseType, vMonths, 'Cost per Component');
#****End: Generated Statements****
So use the above instead of using this:

Code: Select all

#****Begin: Generated Statements***
vExpense=
IF (Measure @= 'Expense' , Value , '' );
vCostPerModule=
IF (Measure @= 'Cost per Module' , Value , '' );
vCostPerComponent=
IF (Measure @= 'Cost per Component' , Value, '');
#****End: Generated Statements****
I'm getting an error when I try to save it and since I was just using examples in the other, I decided to do this post which is using real life screenshots. Appreciate the help.

jimicron
Posts: 110
Joined: Tue Oct 30, 2012 5:21 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP2 MSO
Location: Boise, ID

Re: ASCIIOutput - Measures across top

Post by jimicron » Wed Aug 07, 2013 5:48 pm

:oops: I'm at a bit of a loss. I've been looking on the forum, on the internet, etc. and haven't been able to figure out why I am getting an error. I asked a coworker who recently went through some actual training and she said they didnt' cover CellGetN and CellGetS in class :(

I am using the CellGetN as recommended, and when I "Evaluate" in the Formula, I do not get an error - it is accepted. However, when I try to save the Process, I get an error. All of the elements in my Measures dimension (which is last in the cube) are #, not AB (String).

I've tried changing my Variable types and that hasn't been successful either :(

Any ideas? Appreciate the help!

Here is the code from my Advanced (Data) tab:

Code: Select all

#****Begin: Generated Statements***
vExpense=
CellGetN ('SIG Module Cost', vSite, vMPN, vExpenseType, vMonths, 'Expense');
vCostPerModule=
CellGetN ('SIG Module Cost', vSite, vMPN, vExpenseType, vMonths, 'Cost per Module');
vCostPerComponent=
CellGetN ('SIG Module Cost', vSite, vMPN, vExpenseType, vMonths, 'Cost per Component');
#****End: Generated Statements****


IF (Header = 1);

# Following are column header names

ASCIIOutput('\\analyst-app\libraries\TM1\BU\SIG\SIG Module Cost 2.csv', 

'Month' ,
'Site' , 
'Expense Type' , 
'MPN' , 
'Expense' ,
'Cost per Module' ,
'Cost per Component'
 );

ENDIF;

# Following are variables from the variables tab of this process

ASCIIOutput('\\analyst-app\libraries\TM1\BU\SIG\SIG Module Cost 2.csv', 

vMonths, 
vSite , 
vExpenseType , 
vMPN , 
vExpense ,
vCostPerModule ,
vCostPerComponent 
 );

Header = Header -1;
Attachments
Variables Tab and Error Upon Save.jpg
Variables Tab and Error Upon Save.jpg (64.21 KiB) Viewed 1770 times

declanr
MVP
Posts: 1588
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: ASCIIOutput - Measures across top

Post by declanr » Wed Aug 07, 2013 6:15 pm

Asciioutputs need all items to be string.

Try converting the numeric values to strings using NumberToString() and that should do the trick.



Edit - In case the above didn't make sense:

Code: Select all

ASCIIOutput('\\analyst-app\libraries\TM1\BU\SIG\SIG Module Cost 2.csv', 

vMonths, 
vSite , 
vExpenseType , 
vMPN , 
NumberToString( vExpense ) ,
NumberToString( vCostPerModule ) ,
NumberToString ( vCostPerComponent )
 );

jimicron
Posts: 110
Joined: Tue Oct 30, 2012 5:21 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP2 MSO
Location: Boise, ID

Re: ASCIIOutput - Measures across top

Post by jimicron » Wed Aug 07, 2013 6:36 pm

THANK YOU!

OMG - Finally got it!! It looks just like what I have above (middle screenshot) where i stated "But I'd like it to look like this:"

It's perfect! Thanks a lot for your continued patience. I wish I could attend some proper training.

jrock
Posts: 9
Joined: Thu Jun 28, 2018 10:56 pm
OLAP Product: Planning Analytics
Version: 10.2
Excel Version: Excel 2010

Re: ASCIIOutput - Measures across top

Post by jrock » Mon Oct 15, 2018 8:28 pm

This is great stuff and I got it to work. However, currently I have 275+ variables I need to create. These variables may change and/or expand to 300+ in the future. Is there an automated way to create a variable within the process? I have set everything up in Excel as to the naming of the variable, and the formula, as well as the ASCIIOUTPUT code, and would prefer to load these (at least for the variables), rather than manually create and maintain in the future. Any help would be greatly appreciated!

Wim Gielis
MVP
Posts: 1824
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: ASCIIOutput - Measures across top

Post by Wim Gielis » Mon Oct 15, 2018 11:57 pm

I guess you mean copy-paste from Excel ? Yes, that would be an absolutely fantastic idea. You can also have a look at the Expand function, possibly linked to looping over a dimension of measure names (or aliases, whichever works best).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

lotsaram
MVP
Posts: 3136
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: ASCIIOutput - Measures across top

Post by lotsaram » Tue Oct 16, 2018 6:13 am

jrock wrote:
Mon Oct 15, 2018 8:28 pm
This is great stuff and I got it to work. However, currently I have 275+ variables I need to create. These variables may change and/or expand to 300+ in the future. Is there an automated way to create a variable within the process? I have set everything up in Excel as to the naming of the variable, and the formula, as well as the ASCIIOUTPUT code, and would prefer to load these (at least for the variables), rather than manually create and maintain in the future. Any help would be greatly appreciated!
As Wim has already alluded to, if you have a very large number of variables then by far the best way to manage it is to use the default variable naming of v1, ... vN and have a section on the Prolog mapping the measure names to the variables. Then on the Data tab you can use a while loop to iterate over all the variables and send to the correct measure.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

jrock
Posts: 9
Joined: Thu Jun 28, 2018 10:56 pm
OLAP Product: Planning Analytics
Version: 10.2
Excel Version: Excel 2010

Re: ASCIIOutput - Measures across top

Post by jrock » Wed Oct 17, 2018 2:25 am

Sounds complex for my novice skills but I will give it a try. Thanks for the tip.

User avatar
gtonkin
MVP
Posts: 634
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: ASCIIOutput - Measures across top

Post by gtonkin » Wed Oct 17, 2018 6:35 am

lotsaram wrote:
Tue Oct 16, 2018 6:13 am
jrock wrote:
Mon Oct 15, 2018 8:28 pm
This is great stuff and I got it to work. However, currently I have 275+ variables I need to create. These variables may change and/or expand to 300+ in the future. Is there an automated way to create a variable within the process? I have set everything up in Excel as to the naming of the variable, and the formula, as well as the ASCIIOUTPUT code, and would prefer to load these (at least for the variables), rather than manually create and maintain in the future. Any help would be greatly appreciated!
As Wim has already alluded to, if you have a very large number of variables then by far the best way to manage it is to use the default variable naming of v1, ... vN and have a section on the Prolog mapping the measure names to the variables. Then on the Data tab you can use a while loop to iterate over all the variables and send to the correct measure.
A bit more complicated than Wim/Lotsaram's suggestion but I add a text attribute to my measures and then set the value to v1, v2, v3 as required.
I then use a subset to loop through all the measures that are mapped, retrieve the attribute e.g. v1, v2 etc., expand that to get the value from the incoming record then write that to the cube. You may need two attributes or a concatenation to determine Numeric vs String e.g. v1|S or v1|N

Post Reply