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
Quote from: Kenti on 18 Apr 2017 08:12:47 AM
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.
Thanks for your reply,
I am feeling a little bit helpless on this. Do you have an idea, how I could solve this ?
Regards
Quote from: Kenti on 18 Apr 2017 08:39:15 AM
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.
Ok, it would be a bit tedious to do, but you could create each column separately:
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.