Page 1 of 1

ASCIIOutput - Measures across top

Posted: Tue Aug 06, 2013 8:45 pm
by jimicron
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!

Re: ASCIIOutput - Measures across top

Posted: Tue Aug 06, 2013 8:49 pm
by declanr
Jimicron,

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

HTH

Re: ASCIIOutput - Measures across top

Posted: Tue Aug 06, 2013 9:01 pm
by jimicron
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.

Re: ASCIIOutput - Measures across top

Posted: Wed Aug 07, 2013 5:48 pm
by jimicron
: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;

Re: ASCIIOutput - Measures across top

Posted: Wed Aug 07, 2013 6:15 pm
by declanr
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 )
 );

Re: ASCIIOutput - Measures across top

Posted: Wed Aug 07, 2013 6:36 pm
by jimicron
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.

Re: ASCIIOutput - Measures across top

Posted: Mon Oct 15, 2018 8:28 pm
by jrock
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!

Re: ASCIIOutput - Measures across top

Posted: Mon Oct 15, 2018 11:57 pm
by Wim Gielis
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).

Re: ASCIIOutput - Measures across top

Posted: Tue Oct 16, 2018 6:13 am
by lotsaram
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.

Re: ASCIIOutput - Measures across top

Posted: Wed Oct 17, 2018 2:25 am
by jrock
Sounds complex for my novice skills but I will give it a try. Thanks for the tip.

Re: ASCIIOutput - Measures across top

Posted: Wed Oct 17, 2018 6:35 am
by gtonkin
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