Export cube view in txt format

[ViRa]

Hi all,

I've looked in the forum for help related to exporting a cube view into txt but still have some questions and request your assistance. I've tried couple of things -

1) By right-clicking a view and choosing 'Export as text data' option (an applying required filters for each of the dimensions in the export window), the resultant .cma file exports the data. However, the data is exported in column-wise for each of the member. Eg. If the first record in the cube is displayed in a row for a member like 'Mem Name', 'Mem Age', 'Mem Dob', 'Mem Address', then the .cma file exports the data as

'Mem Name'
'Mem Age'
'Mem Dob'
Mem Address'

Is there a way to export the data as it is in the cube but all the details of a member in a single row?

2) I tried ASCIIOUTPUT and TEXTOUTPUT functions in a TI process where I mentioned the appropriate elements for each of the dimensions, but despite the filter the resultant txt or csv file is generating ALL the results ignoring the filter part.

3) I tried creating a new TI and chose the TM1 Cube view as data source and wrote the above same ASCIIOUTPUT and TEXTOUTPUT functions, but the result is same as what the 'Export as text data' does (i.e. shows the results in column-wise format)

I really appreciate any help and guidance you can provide in achieving the desired results of displaying the cube view data as is in a txt file. Thanks for your help and time.

[PavoGa]

To be clarify, are you looking for this in your output file:

dim1val, dim2val, dim3val, mem name, mem age, mem dob, mem address

[ViRa]

Thanks Pavoga for your reply.
Yes, I need the cube view data in that format like you mentioned.
Would you please help me with this.

[PavoGa]

when building your view, on the dimension you want flattened out select one element that will ALWAYS be populated if using zero suppression or just select one if not depending on zero suppression.

In the data tab, this is quick and dirty:

Code: Select all

sName = CELLGETS(cubName, vDimval1, vDimval2, vDimval3, "mem name");
nAge = CELLGETN(cubName, vDimval1, vDimval2, vDimval3, "mem age");
...

ASCIIOUTPUT(strOutputFileName, vDimval1, vDimval2, vDimval3, sName, STR(nAge,3,0), sDOB, sAddress);

Otherwise, if wanting to make it dynamic, you could use the DIMSIZ or SubsetGetSize in the prolog,
loop through the measures dimension or subset in the DATA tab and read the measures from the cube using CELLGETN & CELLGETS (test the datatype)
build out a string variable based on the read values and after exiting the loop:

ASCIIOUTPUT(strOutputFileName, vDimval1, vDimval2, vDimval3, sBuiltUpStringVariable);

There are some variables to help you format the output in case you do not want " surrounding numeric values.

[tomok]

This post has some ideas http://www.tm1forum.com/viewtopic.php?f ... mns#p13802

[ViRa]

Thanks PavoGa and TomOk for your response.
I'll try the solutions and will update the post accordingly.