Dynamic Update of both Month and YTD Values from a TM1 Cube

[jv_oz]

Hello,

I have a TM1 cube with both Year and Month dimension - pretty standard TM1.  My month dimension has structure for All Months, with Jan to Dec as children.  In the month dimension there is also a parent called "Dec YTD", which then has children "Dec" and "Nov YTD".  "Nov YTD" then has "Nov" and "Oct YTD" etc all the way back to "Jan YTD" only having "Jan" as a child.

I have published the cube to BI and am trying to now build a report off it.  All good so far, however I have run up against a snag.  I need to be able to prompt for the relevant month and then have both Monthly and YTD values appear in the report.  I have a prompt (pMonth) defined where the user can select Jan to Dec.  I then expected that I would simply be able concatenate pMonth with " YTD" to generate the element to return for the YTD values.

Just cant' work out how to do it!  Any help would be greatly appreciated!

Thanks

John

[TheFrenchGuy]

Hello,

I don't get it, pls can you put enclosed files with what you have and what you want ?

ps : you can only enclose 1 file

[jv_oz]

Hi and thanks for replying. 

I don't see that I would be able to send the files.  I have a TM1 cube, so need all the files for that, then the Cognos deployment package with an example report.

Maybe I can try and explain it somewhat better.  There is a TM1 cube that has multiple dimensions including a Year dimension and a Month dimension.  My Month dimension has YTD calculations in it that add the prior month's YTD value onto the current month's monthly value.  For example, "Dec YTD" is a rollup of "Nov YTD" and "Dec", and so on.

I can bring that into BI no problem. I then have a report where I have a prompt for Month.  The user can select "Jan", "Feb", "Dec" etc and this is stored in pMonth.  Then I want columns that show the monthly value (based off pMonth) and I want YTD.  It is this last part that I can't make work.  I expected that I could just merge pMonth with the text " YTD" to derive the element to put into the second column.

Make sense?

Thanks

[TheFrenchGuy]

Good morning,

1) Put the data item [Month] in columns
2) Put a constructed data item wich make the total of month (running-total, aggregate, ...)
3) Make a slicer on the only month you want (lastchild([Current Quarter])

ps : when I spoke of enclosed file, I meant screen sprint to see your dimensions, how they are construct, what concrete output you want ...