Logic for (Multiple rows and one column to Multiple columns and one row)

[kskanth]

Hi Gurus,


I have a situation here, where for example an employee has different vacation dates in  vacation column.
Now I have to filter for today and have to retain latest 7 records for that particular employee. And have to display all the 7 different vacation list in one single row..

Example:

emp    vacation

alex     12-12-2012
alex     11-09-2011
alex     10-08-2010
alex     15-08-2007


In Report Studio we have to diplay it as

emp     vacation1          vacation2         vacation3          vacation4

alex     12-12-2012        11-09-2011      10-08-2010        15-08-2007




I tried using crosstab but in vain, it comes out a blank.

[Lynn]

A crosstab expects a numerical measure at the intersection, not a date. You could try a list with a repeater or repeater table object embedded within the list (master-detail relationship between the two). The list would render a row for each employee and the repeater would string out the dates of the vacations.

Since you have a fixed maximum (up to 7) you could probably also do some fun expressions to flatten the data on the query side. For high volumes this might perform faster than the repeater approach.

[CognosPaul]

What Lynn suggested would probably be easiest, but it's worth mentioning that there is a way around it. If you can convert the date into a number yyyymmdd you can use it in a crosstab. Then you can change the source type in the intersection from value to report expression and convert it back to date. If you're using Oracle you can use something like to_char([Date],'yyyymmdd').

It's a dirty rotten cheat, but it does work.

[Lynn]

Excellent suggestion Paul !

[kskanth]

Thank You Lynn, your suggestion worked and the report is working fine...


Thank You Paul ...


Thanx a ton bosses...