Crosstab

[BIUser]

 Hi,

I want to calculate the Resolved time in mins for a ticket and availability for a day in crosstab.

e.g.
             |            1-Jun-2012         |             2-Jun-2012         |                 3-Jun-2012    |
----------|--------------  ----------------|--------------------------------|------------- --------------- -|--
            |ResolvedTime|Availability|ResolvedTime|Availability |ResolvedTime|Availability|
----------|-----------------|-------------|-----------------|--------------|-----------------|------------- |--
12345  |   660             |54.16%    |      1440       |      0%      |   780             | 66.66%    |
78980  |                      |100%       |                      |      100%  |                |     100%   |


Here, Start Date is  1-Jun- 2012 1:00 PM and Resolved Date is 3 - Jun -12  1:00 PM for ticket# 12345
Resolved time (in mins) = start Date - Resolved Date
Availability = ((24*60) - Resolved time (in mins))/(24*60)

For all other days, the availability should be 100%

My concern is that how to segregate the Resolved time daywise. Currently, I am getting below data:


             |            1-Jun-2012         |             2-Jun-2012          |
--------- |--------------  ----------------|-------------------------------|--
            |ResolvedTime|Availability|ResolvedTime|Availability |
--------- -|-----------------|-------------|-----------------|--------------|---
12345   |   2880            |-1%         |                     |                  |
78980   |                      |                 |                      |                 |


Environment:
Cognos 8.2
SQL Server 2005

[rockytopmark]

I believe you will need to break up the single row into 3 rows (representing the 3 dates) in the Tabular View of the Query. 

I haven't done anything like this before, so I can't offer ideas on HOW to do it, but there are quite a few good date/time functions in Cognos, and probably more in your specific RDBMS's function set, so you will need to get creative and figure out how to derive 3 tabular rows from the single data row.

[blom0344]

Search on the use of non-equi or theta joins.  You can model solutions by using a calender table and an 'between'  join. I think non-equi join cannot be build from within a report, but I use these from time to time to generate rows within a framework model..