FY report by month with counts & percentages 1

[CrystalBox]

Hello, I'm a newbee to Cognos(v8) and need some help. I've been asked to provide a report that tracks case info.

Example:
July/Aug/Sep/Oct
#cases completed within 15 business days
#cases completed within 90 business days
#cases completed over 90 business days
#cases completed within 180-270 business days

I need to show a count/percentage by month. I would like to show it in a crosstab format. Can anyone start me in the right direction? Thank you

 

[CrystalBox]

Any takers. Any help is appreciated.

 

[blom0344]

There are a couple of issues with your requirement:

1. how do you determine into which months category a case should fall? Startdate? Completion date? Any other date ?

2. The 4 groups are not mutually exclusive. Completion within 90 days may well mean completion in 13 days, which would mean that the result is scored within 2 groups.
This would give non-additive results after counting (say 500 distincts cases could score 150,120,180,210 giving an overall total way over the 500)

3. An approach using a 4-fold union will work best for a crosstab as it adds a generated dimension 'group' to the resultset while keeping the actual measure (fact) in one dataitem.

In SQL this would amount to (using completion date to compute the months):

SELECT '#cases completed within 15 business days' as typegroup,
extract(year,[completion_date])*100+ extract(month,[completion_date]) as monthdim,
count([case_id]) as #cases
where _days_between([completion_date],[startdate]) <= 15
group by 
extract(year,[completion_date])*100+ extract(month,[completion_date])
union all
SELECT '#cases completed within 90 business days' as typegroup,
extract(year,[completion_date])*100+ extract(month,[completion_date]) as monthdim,
count([case_id]) as #cases
where _days_between([completion_date],[startdate]) > 15
and
where _days_between([completion_date],[startdate]) <= 90
group by 
extract(year,[completion_date])*100+ extract(month,[completion_date])
union all
SELECT '#cases completed over 90 business days' as typegroup,
extract(year,[completion_date])*100+ extract(month,[completion_date]) as monthdim,
count([case_id]) as #cases
where _days_between([completion_date],[startdate]) > 90
group by 
extract(year,[completion_date])*100+ extract(month,[completion_date])
union all
SELECT '#cases completed within 180 - 270 business days' as typegroup,
extract(year,[completion_date])*100+ extract(month,[completion_date]) as monthdim,
count([case_id]) as #cases
where _days_between([completion_date],[startdate]) > 180
and
where _days_between([completion_date],[startdate]) <= 270
group by 
extract(year,[completion_date])*100+ extract(month,[completion_date])

Plot the monthdim against typegroup with #cases as the fact.

Cognos lets you build this 4-part union quite easily.



Ties Blom

 

[CrystalBox]

Thank you Blom0344. This is the jump start I was looking for. I gonna work with your suggestion and I'm sure I can put something together. Thank you again.