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.
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.
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.
Excellent suggestion Paul !
Thank You Lynn, your suggestion worked and the report is working fine...
Thank You Paul ...
Thanx a ton bosses...