MTD, YTD columns in Query Studio

[terry_stjean]

I have a need to allow the Query Studio users to add columns for MTD and YTD in their Queries.
I have a Calendar table with Month and Year columns. I have create MTD and YTD columns which contains values like MTD, Last Month, LY Month etc, in the MTD column and YTD and Prior YTD in the YTD column. These get recalculated each day.
However, because they are in separate columns, I cannot add them side by side in a Query Studio report.
Any suggestions even if it requires a database change?

[rockytopmark]

However, because they are in separate columns, I cannot add them side by side in a Query Studio report.

Please explain the above statement!?!?  Separate Columns (aka, Query Items) are added side-by-side all the time.  I assume I am completely missing something from your description here...

[MFGF]

Hi,

My guess would be that you're referring to stacking thsese two separate items in the columns area of a crosstab?  If so, you would effectively need a UNION between these two items to display them both as crosstab column headings at the same level.  Query Studio doesn't support this directly, so you would need to do it in Framework Manager (possibly using a Query Set) or use Report Studio which does support stacking (peer sets).

If I'm barking up the wrong tree, please ignore! :-)

Regards,

MF.

[larsonr]

Please refer to the proven practices section in Cognos for Framework.  There are some documents on building relative time within a relational model.  We have used this before and it works beautifully with query studio.  There is a relative time table that has in one column all the different slices by name and joins to our time dimension.  So this makes all the different slices of relative time appear as rows of the column instead of bringing each one individually as a column.  I would suggest looking into this method.  It may solve your problem.