date in crosstab?

[everlearner]

hi gurus,

i have to display date in crosstab meaures. i took the date into measures, but am unable to get it. help me how to get that.
this is my o/p format.

              status1           status2         status3
type1     12/01/2012   13/01/2012  14/01/2012

type2     12/02/2012    13/02/2012  14/02/2012

thanks in adv

[MFGF]

You can't do this, sorry. The measure cells in crosstabs can only display numeric measure values.

There are some awfully complex hacks to try to work around this, but they all seem to be very limited in their scope. You need to go back to the drawing board and redesign your approach to this report to take into account the way crosstabs work.

Cheers!

MF.

[charon]

correct me if im wrong mr. muppetmaster, but i think its possible (in case the etl/ fm modell contains that already) to use date ids.
for example, instead of having 5. jul/ 2012 use 20120705
this, you could transform in an data item with an case when syntax.
e.g.
case
.
.
when right(dateid,3) 07 then 'july'
.
.

In a nutshell, use id instead of date and create data item which translate it into a string using a case when construct.

Just an idead though ..cheerz :P

[MFGF]

It might be possible, but there would be issues to overcome such as aggregation of cell values. Crosstabs are designed to present measures in the cells rather than attributes.

MF.

[charon]

true, aggregations and calculations are almost impossible. but on the otherhand, what do you want to aggregate using date at all?

again, was just an idea...dont know the report request so its hard to say if we can nail the request by using this approach.

everlearner, plks give us a feedback :)
cheerz :P

[tjohnson3050]

I would agree a cross tab is not the object to use.  This report can be built using a list report.  Column one is type and the only data item in the query that is not aggregated.  Column two is max(case when status = status1 then [date field] else null end) , then similar functions for the other two columns.

[RobsWalker68]

Hi,

Below is a proven practice technique for displaying a date in a crosstab.  This method uses an ISO date format and layout calculation.

http://www.ibm.com/developerworks/data/library/cognos/page482.html

Cheers

Rob

[everlearner]

hi rob i followed the link what u have been provided, thanks. by that technique now i able to display dates in cross tab.
thanks everybody for ur replies.