How to get YTD, QTD, MTD in a crosstab

[mmckeen1]

I need to create a crosstab report that will be scheduled to run on a weekly basis.  My client wants to see the results rolled up in columns for month to date, quarter to date, and year to date.  I've tried to write a data item using the following expression for 'month to date:'

if ([Time Period] >=  _first_of_month(current_date)) then ([Count]) else (null)

However, I cannot get it to work.  I'm getting the following errors when I go to validate the query:

GEN-ERR-0016 The 'fn:current_date' function is not supported in the context in which it is used.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0011 The pattern 'manyDimensions' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'manyDimensions' is not supported in 'TabularOperationProvider'.

My package is relational, and I'm using Cognos 8.4.  Any help would be appreciated, thanks.

[blom0344]

My favorite alternative it to create 3 seperate queries and union them into a fourth, like:

Code Select Expand


SELECT '1.MTD',...........................
FROM ......
WHERE << add specific filter>>
union all
SELECT '2.QTD',...........................
FROM ......
WHERE << add specific filter>>
union all
SELECT '3.YTD',...........................
FROM ......
WHERE << add specific filter>>



For this to work you need to make sure that total aggregate is set for the measures in the union.
By taking the dataitem that stores '1.MTD' / '2.QTD' / '3.YTD' as dimension in the crosstab you can automatically create the 3 diverse columns

[Sreeni P]

My favorite alternative it to create 3 seperate queries and union them into a fourth, like:

Code Select Expand


SELECT '1.MTD',...........................
FROM ......
WHERE << add specific filter>>
union all
SELECT '2.QTD',...........................
FROM ......
WHERE << add specific filter>>
union all
SELECT '3.YTD',...........................
FROM ......
WHERE << add specific filter>>



For this to work you need to make sure that total aggregate is set for the measures in the union.
By taking the dataitem that stores '1.MTD' / '2.QTD' / '3.YTD' as dimension in the crosstab you can automatically create the 3 diverse columns

Hi blom,

u did such a good job, but thing is plz....explain in detail taking any example, then people will know and understand of their level best...(guess it takes bit more time but it is help full )

[blom0344]

Okay, let me try a different perspective..

The trick is to keep the crosstab design as simple as possible.

• The union approach makes it possible to keep all of the factdata in a single dataitem
• The essence of a crosstab is dimensional in nature. We plot 2 or more dimensions against each other
• With the union approach you can imagine the resulting datasets as 3 subsets on top of each other
• We create an artificial dimension by labelling each set with a fixed value ('1.MTD' / '2.QTD' / '3.YTD') , say we call it 'McGuffin'
• We use this artificial object in the crosstab

In the simplest form (structure) this will yield:

                                 |McGuffin
-----------------------|----------------
[Someotherdimension]   | [count]


With data from the videostore:

                                 |1.MTD     |2.QTD    |3.YTD
-----------------------|---------------------------
Apocalypse Now           |30           |70         |147
Strangers on a train      |2             |7           |11
Avatar                        |4500        |8960      |32490

*sorry for mentioning a videostore, my gen. still not 100% adjusted to downloading   ;D