Sort order help for date string in crosstab

[#1]

Hello All,

I need to calculate the number of calls for service for the last 13 months. Display the number in the report footer by month and year in a grid similar to a crosstab and in ascending date sort order.

Example:

May 2008 1,400

Jun 2008 1,287

Jul 2008 1,290

all months through last full month

May 2009 1,385

I can calculate the number of calls per month and display the month and year by converting the month and year to text. However, I cannot seem to find a way to sort the month and year without some kind of on-going manual intervention. I need to make the report maintenance free for monthly scheduled e-mail distribution in business objects enterprise. Specified order does not work since the column headers will change each month. It needs a sort order formula, but I am not sure how can this be done inside a cross tab?

Any help or suggestions are appreciated!

Thanks,

Wendy Douglass

[former_member260594 · Accepted solution]

Hello Wendy,

If you didn't convert the date to text and just added the date field to the crosstab as the column field grouped by each month it would sort ascending by default. You could then format the date fields to appear as MMM/YYYY

[Former Member · Accepted solution]

Perfect! Thank you very much! As usual, I was making it too hard.

Wendy

[Former Member]

Hi,

I have a similar cross tab in my report. but only problem is grouping is done based on a parameter GroupBy, which has Day, Week, Month values.

if user selects day or week, cross tab should display column in dd/mm/yy format, if its month, MMM-yy format.

I have created a date formula , GroupBy = Day then Opendate

GroupBy = Week then opendate -dayofweek(opendate)+1

GroupBy = date(year(opendate),month(opendate),1)

and used this date formula as column in cross tab. I tried to format using custom date style...but its not working in cross tab.

if I use totext to format the date formula, data will not be in sorted order...ex, its showing jul-99 before jun-99

can anyone help?

Thanks in Advance,

Jyothi

Edited by: Jyothi Yepuri on Jul 8, 2009 5:27 AM

Edited by: Jyothi Yepuri on Jul 8, 2009 6:10 AM