RESOLVED - TM1 and Report Studio Union query

[AJAYC]

Hi All,

I am new to Report Studio, historically a TM1 guy, so apologies in advance if my question maybe quite simply to resolve.

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 (as this appears easier to add/aggregate).

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

[pricter]

How do you find current year and current month?

Are there members in your cube are do you use a detail filter?

If it is the second case it is not a good practise to use detail filter on dimentional data.

[AJAYC]

Hi

The report contains a prompt page, upon which the following prompts are selected by the user:

Currentperiod
Currentyear
Previousperiod
Previousyear

These are carried through into the filters of the crosstab queries. There are no other filters in place.

Ajay

[pricter]

Somewhere I lost you.

The prompts are date prompts or the user selects currentperiod.

If the user selects current period how do you filter your crosstab query?

[AJAYC]

Hi Pricter

I have a prompt page in my report which contains four value prompts. The user picks from a drop down list the currentperiod, currentyear, previousperiod and previousyear.

These in are in the detail filter area of my crosstab queries as ?CurrentPeriod?, ?CurrentYear?, ?PreviousPeriod?, ?PreviousYear?

So in the crosstab which displays the current set of financials I use ?CurrentPeriod? and ?CurrentYear?, and in my crosstab showing the earlier period, I filter on ?PreviousPeriod? and ?PreviousYear?

These filters are applied to the TM1 cube dimensions called "Year" and "Month", so in my crosstab filter, i have something like this [CubeName].[Year].[Year]=?CurrentYear? etc etc for the other prompted parameters.

[pricter]

Instead of using a detail filter (which is not best practise with dimentional data) you can use the following

A data item with the following expression

Code Select Expand

[CubeName].[Year].[Year]->?CurrentYear?

Code Select Expand

[CubeName].[Month].[Month]->?CurrentMonth?

You can do the same for the previous year but if I was use I would use dimentional function to calculate the previous year and previous month rather than to have two extra prompt.

Whatever you choose you will have four items.
[Current Year]
[Current Month]
[Previous Year]
[Previous Month]

and you can perform calculations between theese data items.

So instead of having three queries you will have only one for the three crosstabs.

[AJAYC]

Hi Pricter

Thanks for your help on this.

I have managed to complete the report and all works fine now.

I was able to use only one query and added three data items, one for current period, one for previous and one which works out the difference between the two. The first two simply TUPLE the data, and as you said, the dimensional functions allowed me to retrieve data into the crosstabs for the current period/previous period and variance.

Happy days !!!!

Ajay