TM1 and Report Studio Union query

[Ajay]

Hi All,

I am having a few issues with a report that I am producing in Report Studio using a TM1 cube as it’s source.

The report contains two crosstabs; crosstab1 (with Query1), referring to the current month, and crosstab2 (with Query2), referring to the previous month.

Both crosstabs are identically in layout and format.

The two queries are identical, with the exception, that the filters on both, reference prompts on my prompt page. So Query1 uses currentyear and currentperiod prompts, and Query2 uses previousyear and previousperiod prompts.

What I now need to do is create a third crosstab which should simply deducted crosstab2 from crosstab1.

Ideally, I should simply copy the format of the crosstabs thus creating crosstab3 and point it to a new query, which should give me the difference.

The first step I have taken is to make an identical copy of Query2 called Query3 which effectively makes the measure a negative number (easier to add).

Since Query1 and Query3 are identical in data items and order, I have created a union query (Query4) to bring the two together, and have pointed the new crosstab, crosstab3, to the new query, Query4.

Unfortunately I cannot retrieve any data and am getting a “no data available” error message. Since filters exist in Query1 and Query 3, I have not included them in Query4 as theoretically they should not be needed.

Has anyone successfully used the union query in report studio when using TM1 cubes? I am loathed to enter into my TM1 dimensions, customised rollups which work out differences since the dimensions of year and month are separate, but am very keen to understand any other simple approaches which can be used.

Thanks
Ajay

[Ajay]

Hi Guys

An update on this for anyone else working with TM1 and Report Studio.

I managed to resolve the issue and complete my report.

Steps involved are to use only one query within which you add three data items which correspond to the areas you are looking to report on. In my case this was CurrentMonth, PreviousMonth and Variance.

For the dataitems related to the currentmonth and previousmonth, you need to define an expression using a TUPLE dimensional command. This in effect is our DBR formula in the TM1 world. You can pass prompts into the formula for flexible usage. You need to pass the measure too.

Next my third dataitem, for the variance, simply deducts the dataitem for previousmonth from the dataitem for current month.

Add the columns and rows for your crosstab, which adds the additional query items and then apply the query to the crosstabs you want using the dataitems as the measure. In my case for the current month data I used dataitem 1 (current month) as the measure, for crosstab2 which was my previous month, I used dataitem2, and for the variance crosstab, use the final dataitem as the measure.....hey presto.....working report !

Hope this helps
Ajay