ASCIIOutput - Measures across top

[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!

[declanr]

Jimicron,

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

HTH

[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.

[jimicron]

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;

[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 )
 );

[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.

[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!

[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).

[lotsaram]

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.

[jrock]

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

[gtonkin]

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