Greetings.

I have a Cross-tab that will display sales results in a month-by-month table. The more months that are queried, the more months are displayed in the table. All that works great.

However, when I run the report, the months are displayed in alphabetical order, either Ascending or Descending, or the ever-mysterious Specified Order.

Maybe it's a bug - I don't know, I'm new to CR - when I select [in specified order] from the drop-down menu in Cross-tab Group Options, I get a pop-up called Enter Values that requests the parameters for the report, which is Start Date, End Date, and Customer.

I fill in dummy content: Start Date = 2010-01-01 00:00:00 , End Date = 2010-01-01 00:00:00, and I pick the vendor .Not Set, which is vendorid = 1 in the database (ODBC MSSQL). I click OK.

A second, identical window pops up. I fill in the same dummy information, and click OK.

After that, I get to the Specified Order window in the Cross-Tab Group Options. It is blank, so I click New.

That pops open two new windows - on top: Define Named Group, behind it: Enter Values again!.

In the Define Named Group, I enter January, and click OK. The Enter Values window remains, while the Define Named Group window goes away. I enter, again the same dummy info or 2010-01-01 00:00:00, 2010-01-01 00:00:00, and .Not Set.

I click OK, and a fourth Enter Values window appears. I again, for the fourth time, enter the same dummy information, and click okay.

So, okay, I have a Sort group named January. So, now, I want to add February. I click New again from the Cross-Tab Group Options, which pops out another Enter Values window, and another Defined Named Group, which I enter the group name of February. I click OK, and I get this error:

This group query:

"{BO_SP_CustomerOrderByMonthYear.MonthName} = {BO_SP_CustomerOrderByMonthYear.MonthName}"

is already being used by the group "January". Please specify a unique query.

So, now I'm stuck. I obviously don't understand what I'm doing here. I'm referencing a report using a similar stored procedure, for a different client set, which has all the months of the year named as individual named groups. When I click to Edit those existing named groups, their properties are blank! I don't get it!

So, how can I sort my months by chronological order in a Cross-tab? Otherwise, I get April, August, February, January, May, March... and so on.

Thanks in advance. :)

Edited by Smittles - 05 Oct 2010 at 6:42am

Don't use month names in your cross tab, use the actual sales date.
Place the date field in the row or column of your crosstab and select "group options". Choose the order (ascending or descending) and the grouping (by first date in each month).

Your months will display in the correct order. You can then click on the month header, right-click and choose "format field" to choose the date formatting you want.

If your data does not have sales dates and you only have month names, create a formula, something like this:

select {sales.month}
case "January" : #1 Jan 2010#
case "February" : #1 Feb 2010#
...

This will convert your month name back into a date. Then use the formula in your crosstab, as above.


Edited by rvink - 06 Oct 2010 at 1:36pm