Comparing TM1 and DMR in a crosstab

[Marvin Falentin]

Hey everyone,

this time I got the requirement to give a table-like display of a former Excel spredsheet in our Active Report in Cognos 11.
I'm trying to compare the sales from our Datamart (DMR) to our TM1 cube for the current month of the year. in Excel it's in a crosstab displaying the 'YYYY.MM.' and a cell containing (TM1 value - DMR value). It's colorcoded in green when the output of the cell is 0 otherwise red.

Right now I'm trying to build this in our Active Report but it's my first time using a TM1 source and I really don't know how to do this. I tried creating a calculated measure in the query and simply doing the same e.g. TM1 - DMR but the Report won't build.

I think it might be because of the date Format of the two sources (TM1 is 'YYYY' and 'MM' in two different columns, DMR is simply 'YYYY.MM.') but I'm not 100% sure because when I try to Display it in a crosstab without any timestamp it still won't build.

As always I appreciate any help on this topic and try to give as much information as I can.

Kind regards,

Marvin

[New_Guy]

Hi,
Did you try building a regular report with the requirements that you mentioned and are you sucessful? You can create the date columns as you need by concatenating the 2 different columns to make the data format. Try and let us know.
Good luck
New guy

[Marvin Falentin]

Hey NewGuy,

unfortunately I have to admit so far I have not been succesful. There seem to be two problems 1) the Dates have a different Format ('YYYY.MM' and 'YYYY' 'MM') and 2) you can not do a calculation using DMR and TM1 at the same time.
I talked to MFGF about it and he suggested using a list in a list with a masterdetail-relationship but I did not try it out yet.

If you have any ideas on how to overcome that problem I will gladly try them out :)

Kind regards,

Marvin