Displaying Months in Cross Tab - Newbie

[Kenti]

Hallo friends,

i am relative new in Cognos. I have Cognos 10.2.2.

I have entities with start date and finish date. I want to display them like a gantt chart
Is there a way I can define Months as columns?

For instance:

A(beginning is = 21.Mai 2017 Ending = 30.Okt 2017

                            2017                                                                   2018                                                                    2019
     Jan Feb Mar Apr Mai Jun Jul Aug Sept Okt Nov Dez |  Jan Feb Mar Apr Mai Jun Jul Aug Sept Okt Nov Dez | Jan Feb Mar Apr Mai Jun Jul Aug Sept Okt Nov Dez
A                              X    X    X    X     X    X

[MFGF]

Hallo friends,

i am relative new in Cognos. I have Cognos 10.2.2.

I have entities with start date and finish date. I want to display them like a gantt chart
Is there a way I can define Months as columns?

For instance:

A(beginning is = 21.Mai 2017 Ending = 30.Okt 2017

                            2017                                                                   2018                                                                    2019
     Jan Feb Mar Apr Mai Jun Jul Aug Sept Okt Nov Dez |  Jan Feb Mar Apr Mai Jun Jul Aug Sept Okt Nov Dez | Jan Feb Mar Apr Mai Jun Jul Aug Sept Okt Nov Dez
A                              X    X    X    X     X    X

Hi,

You can use the extract() function to extract parts of a date from a date item. In this case you'd use the month argument, eg

extract(month, [your date item])

This will give you a numeric month value - ie 1 to 12. You could then use a case expression to convert these values to month names, eg

case [your extracted month value]
when 1 then 'Jan'
when 2 then 'Feb'
...
when 12 then 'Dez'
else 'Other'
end

The issue you will face is that crosstabs can only show measure values in the cells, so where your requirement shows 'X' values in the cells, you're not going to be able to do this - only numbers.

Cheers!

MF.

[Kenti]

Thanks for your reply,

I am feeling a little bit helpless on this. Do you have an idea, how I could solve this ?

Regards

[MFGF]

Thanks for your reply,

I am feeling a little bit helpless on this. Do you have an idea, how I could solve this ?

Regards

Can you explain what you mean? Getting the month names? Doesn't the above help you?

MF.

[BigChris]

Ok, it would be a bit tedious to do, but you could create each column separately:

Code Select Expand

Jan 2017: If ([BeginDate] <= 2017-01-01 and [EndDate] >= 2017-01-01) then ('X') else (NULL)
Feb 2017: If ([BeginDate] <= 2017-02-01 and [EndDate] >= 2017-01-28) then ('X') else (NULL)
etc.