Include Gross Total at the end of CSV file using TI Process

[pradeep.k.jagadeesan]

Hi,

I want to include a Gross Total row at the end of the CSV file after uploading all the records. I have written logic to get the gross total in Data Procedure.

I have a little problem in extracting data in a TI process. As you know, in a TI we can write the ASCIIOUTPUT function to write data from the cube into a csv file. But what happens is that when we open the same file in different tabs in the Advanced tabs, the files is opened as a fresh file. So when I write the data in the Data tab and at the same time, total the profit amounts, I need to write the final total of the profit amount in the same file as the last record. But I don’t know when the records will end so I am not able to decide when the Final total record has to be written. So I can write it in the Epilog tab. But then it cannot be done in the same file because if we open the same file then it will be opened as a fresh file and the original data will be lost.

Please suggest me how to do it. Thanks in advance.

[Alan Kirk]

Hi,

I want to include a Gross Total row at the end of the CSV file after uploading all the records. I have written logic to get the gross total in Data Procedure.

I have a little problem in extracting data in a TI process. As you know, in a TI we can write the ASCIIOUTPUT function to write data from the cube into a csv file. But what happens is that when we open the same file in different tabs in the Advanced tabs, the files is opened as a fresh file. So when I write the data in the Data tab and at the same time, total the profit amounts, I need to write the final total of the profit amount in the same file as the last record. But I don’t know when the records will end so I am not able to decide when the Final total record has to be written. So I can write it in the Epilog tab. But then it cannot be done in the same file because if we open the same file then it will be opened as a fresh file and the original data will be lost.

Please suggest me how to do it. Thanks in advance.

One option to consider:
- Write the output data as you're doing in the (presumably) Data tab;
- Write the total to another text file in the Epilog;
- Call a batch file which uses the DOS Copy command to merge the two files together.

[Martin Ryan]

Another option is to have a running total column in your csv file and update that as you go with every line.

You can keep a running total in the Data tab as it remembers what the variable was from the last loop through. In the prolog set runningTotal=0 then in the data tab set runningTotal=runningTotal+currentValue. Export that from the Data tab as your last column in the csv file.

Martin

[Steve Rowe]

The only way I can see to do exactly this (in pure TM1) is to process the datasource twice. This is because you need to know how many records are in the source so you can output your total.

Prolog
ixMDLine=0;
ixDLine=0;

MetaData
ixLine=ixLine+1,

Data

#Near the top
ixDLine=ixDline+1;


#Near the end

If (ixDline=ixMDline);
#Your extra ascii output
endif;

Of course this will be slower to execute due to processing the source twice, so you will have to judge if it's worth the effort. Alan's approach wouold be faster but you'll need to google some DOS commands, it's probably not hard though.

Cheers