Dynamic Assignment of Variable Names

[Steve Vincent]

Not sure if this can be done in TI or not, but it'd be damn helpful if it could.

I have a need to export data using asciioutput in a tabular format, with variables down the left and months across the top. Normal export like this would put one value per line, so the same variables that had data against 200 months would create 200 seperate lines of data.

I could manually create 200 variables (M1, M2, M3 etc) and use CellGetN to grab the number, but i'd prefer to write a loop that goes through the subset of months i'm interested in, iterates the variable name and assigns the value that way. TI doesn't seem to want to do this tho, something along these lines will not save.

Code: Select all

#test code for dynamic variable names
nSub = 1;
While ( nSub <= 5 );
    'M' | NumberToString (nSub) = nSub;
End;

It doesn't like anything other than "hardcoded" variable names. Expand can help but only if they are in the variables tab - i can't iterate the number there so its of little use. Anyone know of a different way to approach this, or am i stuck with lots of lines of code?

TIA

[Martin Ryan]

Yeah, this is annoying. I've wanted to do it quite a few times myself - effectively have an array in TI. The only workaround I've found is creating a temporary dimension that then stores the variable data as an attribute. It's effective, but I don't really like it, as I'd like the variables to be internal to the TI process.

Martin

[paulsimon]

Steve

A few things occur

1) If you want a tabular report from TM1, is there any reason why you can't use Excel and Print Report/VBA to produce it?

2) As I understand it, your data source is a view, which has a row with an element from each dimension, including the months dimension, with its 200 elements, and then a value. You want a report which has the elements from all dimensions except months on the rows, and months across the top as columns.

If you are running on 9.4 and have access to long strings, then potentially you could build up your row as a string, and asciioutput at break of whatever dimensions are before month. If you don't need to output all 200 months but just a selection of them, then even in 9.x this approach could work.

You could drive the process from a view which just has the Total Months consol element, or whatever element will give all combinations of the other dimensions. As you read each record it, you could then get the appropriate months data via CellGetN.

Regards


Paul Simon