TM1 Forum

Hello All,

Back again with another question between TM1 (9.5.1) and Report Studio (Cognos 10).

I am writing a Report Studio report, which pulls in data from two TM1 cubes. I want to allow the end user to select a period when running the report by way of a Report Studio prompt.

The dimensions are shown below for each cube, and as you will see both share the "Time" dimension:-

Cube A
--------
Customer
Time
Debt
Account Manager


Cube B
--------
Line ID
Time
Commentary


Cube A needs to be shown as a crosstab report, and Cube B as a list report. The prompt will filter on the "time" dimension.

I can place two independent value prompts on my prompt page, but this is messy and relies on the user making sure both prompts are the same time element. I've also looked briefly into using java scripting, but this is beyond me at this stage I think. I've even tried combining the queries create for both reports into a third one and tried to create a prompt from this, but this didn't work either. When I try to point the filter in the list report to the parameter for the prompt created for the crosstab, this doesn't validate and throws errors at me !

Has anyone managed to create a single prompt, based on a shared dimension across multiple cubes in a Report Studio report and how did you do it ?

Any help greatly appreciated, as I am going mad !!!

Thanks
Ajay

Ajay

Cognos BI is not great at querying more than one cube because MDX is not great at querying more than one cube. If you look at MS Analysis Server design it usually only ever uses one cube, however, the 'cube' can have multiple facts with different dimensionality. In reality several cubes in one. Unfortunately this model doesn't work well with TM1 which has a genuine multi-cube architecture.

I can only hope that at some point IBM will realise that they need to make BI speak the native TM1 API, rather than using a common interface to Essbase etc, which gives a lowest common demoninator approach.

In the meantime, I would suggest that you take the prompt into a separate query - we use one called Default Parameters. You can then use this in your report views so long as you mark it as a property at the page level. We use it to display the list of what was chosen on the report page.

You may be able to get around your issue by using the prompt macro in the two queries. I haven't had to do it with two cubes, but we use the prompt macro in both the Default Parameters Query and the three different Queries on the one Cube. That seems to work OK, so it should be possible to use it on a second cube.

I would avoid JavaScript if you can.

Regards

Paul Simon

Thanks for getting back to me Paul.

In relation to your comments "I would suggest that you take the prompt into a separate query - we use one called Default Parameters. You can then use this in your report views so long as you mark it as a property at the page level. We use it to display the list of what was chosen on the report page".....I'm still a bit confused.

Let me step through what I have done as it seems I may have missed a step somewhere.

1. The Crosstab Insertable Object has been dragged onto the Report Page. I have then dragged the relevant row dimension, column dimension and measure from Cube A, into the crosstab. The generated query has been named "Query1_Crosstab". There are no detail filters set in this query. This crosstab will hold data from Cube A.

2. The List Insertable Object is then dragged onto the Report Page, under the existing crosstab from 1. above. The dimension and measures are dragged from Cube B into the list report. The generated query has been named "Query2_List". Again, there are no detail filters set in this query. This list will hold data from Cube B.

3. A Prompt Page is added, and the Value Prompt Insertable Object is dragged onto the page. When the Value Prompt wizard opens a new parameter is created and named "DefaultParameter". The parameterized filter is created and the package item is pointed to the "Time" dimension in Cube A. Importantly, none of the queries are selected for the filter to be applied. A new query is created and named "Query3_Prompt", which defaults the "Time" dimension in Cube A to use.

At this stage, I would open the "Query1_Crosstab" query and add a filter to the "Time" dimension and set it to "DefaultParameter". I would repeat the same for the "Query2_list", however on validation, I now get errors, telling me that "[CubeA].[Time].[Time]" cannot be set to the "DefaultParameter", and it must be because I'm trying to set the Cube B filter using the Cube A prompt.

Is my approach correct so far ?

Thanks
Ajay