Sorting

[Kamille]

Hi,
I have a question about sorting. I have created a report displaying a cross-tab. The ROW Headers are months of the year. In report wizard I stated to have my rows sorted. But now when I view my report the months are sorted but Alphabetically i.e April, August, July, June .... instead of January, February, March, ....etc.
Does anyone know a way around this?
Thanks,
Kamille.

 

[randysmid]

Hi,
It appears as though your Month field is setup as text, rather than date. One simple option is to create a new table called "Months". It will contain the field "Month_number", which is really numbers 1 to 12, with the description being the name of the appropriate month. For instance, 1 has "January", 2 has "February", etc. Then, do your month sorting on the Month_number field.

HTH,
Randy Smith, MCP
California Teachers Association

 

[Kamille]

That could be the problem because in the crosstab query used to generate the report I typed the following under "Field" : Date: Format([PrevDate],"mmmm yyyy&quot, where PrevDate in it's table is declared as type: Date\Time but I guess in the formatting this type gets lost and is converted to type Text. The reason why I need this formatting is in my report I sum numbers so i only display summarized Monthly information as opposed to detailed daily information. Is there any other way I can fix this sorting issue without having to generate another table?
Thanks,
Kamille!

 

[randysmid]

Hi,
I think you should to do the real sort on PrevDate. But, I am confused what the formatted date is used for.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[Kamille]

The info generated on the report looks something like this:

DATE PROD1 PROD2 PROD3 ...
april 20 1 5
august 15 25 12
july 5 8 18
june 9 7 21
...

My tables have individual dates for when each PROD was requested. So I wanted to summarize this info. on a monthly basis.
There for my crosstab query:
Under "Field"
Date: Format([PrevDate],"mmmm yyyy&quot

"Total"
Group By

"Cross Tab"
Row Heading

And then my VALUE for the Crosstab (intersection of each row/column):
GroupSize
Sum
Value

My query is parameter driven, the user enters a date range, for which montly range they want summarized.
i.e data range apr-aug 2003
PROD1 PROD2
apr 4 2
aug 2 5

 

[randysmid]

Kamille,
Your crosstab query can have more than one Row Heading. Inside the query, you would set "Sort" based on the PrevDate, which is NOT displayed on the report. Instead, your newly created field would appear.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[Kamille]

Thanks Randy for your tips!
It works great now!
Kamille.