JSON-like TM1 Text Output

JSON (Java-Script Object Notation) as defined by json.org is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate.

These properties make JSON perfect for facilitating when interchanging data between TM1, PHP and Java Script front-end interface. As discussed in the previous article, TM1 does not offer tools for encoding data directly to JSON and therefore some techniques have to be applied in order to obtain required outputs.

JSON basics

In short, JSON is an array of objects which are composed of pairs of names and values. Complicated? Imagine you want to encode single cell from bi-dimensional cube including Geo and Year dimensions such as 'Austria',''2012',1234.

JSON encoded: {"Geo":"Austria","Year":"2012","Value",1234}

In the previous example "Geo":"Austria" is the pair of name/value and the whole string within {} is JSON object. Also notice the double quotes! When more objects are joined together and separated by comma, the array of JSON object is created.

JSON with Text Output

Prior to any coding in Advanced tab of TI process, you should define the data source and identify all required variables as Other.

Secondly, in order to block TI from enveloping every variable into double quotes, you have to declare DatasourceASCIIQuoteCharacter local variable in the Prolog tab and assign it empty value!

DatasourceASCIIQuoteCharacter = '';

And the most important in Data tab:

TextOutput('C:\...\...\file.txt','{"Geo":"'|geo|'"','"Year":"'|year|'"','"Value":'|value|'}');

• Single quotes enclose whole expression and separate text from variables.
• Variables in green are enveloped with pink double quotes (except numerical value).
• Variable and double quote have to be separated with single quote and concatenated with pipe ( | ).
• Labels in blue are also enclosed with double quotes.

Despite it may seem extremely complicated at the beginning, you will later find out that when constructing the expression gradually, the quotes system actually make sense and the whole output is almost identical to real JSON format.