Crosstab Report - Need to Group Columns by 2 Different Date Fields

[khdavis]

Hello All!

I need to create a crosstab report with output as follows:

JAN 2013 FEB 2013 ETC...
Tickets Opened in Month 500 400
Tickets Closed in Month 650 300

Simple enough, except my problem arises as Tickets Opened in Month is based on the Open Date of the ticket and Tickets Closed in month is based on the Close Date of the ticket. I need to find a way to create the month groupings (Jan, Feb, Mar, etc) so I can display both opened and closed tickets below.

I've tried doing a union but it's not working. That's not saying much as I haven't used unions before and I may be doing in incorrectly.

I'm using Cognos 10.
DB is Oracle (relational)

Thanks for any help!

 

[blom0344]

A union is an excellent approach to the problem. You need a dataitem with a fixed value per query to use for the row:
'Tickets openened in month' (query1)
'Tickets closed in month (query2)

The resulting measure from the union needs to have an aggregate set, otherwise the crosstab may no show data. What problem do you have with the union?

Ties Blom