TM1 Forum

Will anyone has a sample TI using asciioutput on a cube view?
The one in TI Help wasn't helpful to me.
I have a view made up and try to export the data of the view to a ascii file, all I get was a cma file with the value as header and no data to it.
I wonder what was wrong.

Chengooi wrote:Will anyone has a sample TI using asciioutput on a cube view?
The one in TI Help wasn't helpful to me.
I have a view made up and try to export the data of the view to a ascii file, all I get was a cma file with the value as header and no data to it.
I wonder what was wrong.

Is it possible that you weren't doing the ASCIIOutput in either the Metadata or the Data tab, and were instead doing it in either the Prolog or the Epilog?

Do you think it might be useful for us to see the code that you were using to do the output?

ASCIIOutput('PhilH.cma', 'CostCentre','Consol','Actual','MTD','period');

yes, only in prolog, never touch the other because don't know how to use it.
I have attached my pro file to your email.

Chengooi wrote:ASCIIOutput('PhilH.cma', 'CostCentre','Consol','Actual','MTD','period');

yes, only in prolog, never touch the other because don't know how to use it.
I have attached my pro file to your email.

First, the Prolog is executed only once, before even a single row of the data source is processed.

Consequently, you can't expect it to output the data from your view. By putting the AsciiOutput in the Prolog tab, it never sees the data. Not one single record of it.

The Metadata and Data tabs both loop through the records which are returned from the data source, one row at a time. It is only in one of those tabs (more commonly the Data one) that you will be able to output the data from your data source using AsciiOutput.

Second, you have put quote marks around each of the arguments in the AsciiOutput function. That means that instead of outputting the variable values from your data source (that is, the contents of each field on each row), you are outputting the field names as literal strings.

That is to say, even if you were to move the code into the Data tab, you'd end up with a file which would read:

"CostCentre","Consol","Actual","MTD","period"
"CostCentre","Consol","Actual","MTD","period"
"CostCentre","Consol","Actual","MTD","period"
"CostCentre","Consol","Actual","MTD","period"

for as many rows of data as you have.

You would need to remove the quotes around the field names if you wanted to output the data rather than the field names (but NOT the file name):

ASCIIOutput('PhilH.cma', CostCentre,Consol,Actual,MTD,period);

That would presuppose that all of the fields are given a variable type of String, not Numeric on the Variables tab. If you have any of those values set as Numeric, you will need to convert them to a string using a Rules function like Str() before you assign them as an argument to AsciiOutput.

success but almost!

I managed to output the cms file and with values !! but outting the line in data tab, but the Value (data) did not come across, I wonder why

NewAccounts String 00000 Element
TBRANCH String P6061 Element
Period String 39872 Element
Actbud String Actual Element
Data String MTD Element
Value Numeric 0.000000 Data

Thanks Alan for your excellent advice!!

Chengooi wrote:success but almost!

I managed to output the cms file and with values !! but outting the line in data tab, but the Value (data) did not come across, I wonder why

NewAccounts String 00000 Element
TBRANCH String P6061 Element
Period String 39872 Element
Actbud String Actual Element
Data String MTD Element
Value Numeric 0.000000 Data

Thanks Alan for your excellent advice!!

I always set the Contents to "Other" rather than "Element" or "Data"; those settings are only useful if you're using generated code, which I never do. (And if all you're doing is an AsciiOutput, you aren't either.)

If you aren't getting a compile error then presumably you have made some attempt to convert the Value field from a numeric to a string; my guiess would be that there's something wrong with the way you're doing that. Again we'd need to see the code that you're using.

Hi Alan,

I managed to output a file from the code asciioutput('path and filename',1,2,3,4,5,6);
the datasource is a view.
Now to go one step further the file created gave me data at zero level, I needed at consolidation level.
I had a played around the variable tab and set the content of one of the variables to consolidation, then in the advance tab, dimension definition pop up with errors that seeking defination of dimension name and child, but the pop up did not allow me to type anything on it. I wonder where is the control and where I have to fix to move on.
I wonder if you have any sample for me to copy, my cube view has 5 dimensions namely : accounts ( contain consolidations and zero level ), cost centres, period, actual and mtd and then data.

My success so far gave me the file output I wanted but I need it to be at consolidation level for the account dimension , how do I tell it?

My cube view was saved at consolidation level 1.

Hi Chengooi,

Thought I'd give Alan a break

First what type you say on the variable tab is what the value already is not what you want it to be, for example if you have a 0-level data source then you setting the variable type to consolidation does not magically change the definition of your view.

Have you unchecked the ignore consoldaitions tick box?

Your original process that worked with the original view should work with no changes on a different view of the same cube, it's your data source that is the problem.

HTH, ASCIIOutput is just about the easiest command to use in TI, the only thing to watch out for is that it won't export numbers so you have to convert them to strings first.

Cheers,

We had thought about that but how do you unchecked the ignore consoldations tick box in a cube view, that check bos is only available when you right click on a cube, on cube viewer , there is no such check box for it. or have I missed out something.

The short answer is Yes.

A "normal" cube view that a user uses behaves like one of the "right click export as ASCII" views when used by a TI. So you want to set up your source view using the export as ascci option rather than just saving a cube view.

Cheers

I did exactly what you said and it was a success , I am over the moon!!!
Now I have a new problem, I want the zero level to be shown underneath the consolidation, I think I should be able to sux this out myself eh!!

Thanking you kindly!!