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
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.
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
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.
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
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.
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
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.