Cross tab not sorting correctly 1

[cjbrown815]

Hello,
The report is gruoped by "Entered_by" the crosstab is in the GH1, the crosstab is ROWS=ENTERED_ON ,Columns= PA_NAME (these are test names) Summarized Fields=Count of Entered_by.

Its a usage report, we want to see how many times data is entered and by whom on a daily basis. The grouping is working great, but I can't get the rows within the crosstab to sort by date properly. Its all over the place.


Thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

I'm assuming entered_on provides a date, but that it might be a string datatype. Can you verify this? Also please show a sample of how the field displays.

-LB

 

[cjbrown815]

Entered_on is a date/time field, the raw data is
"7/28/2010 9:34:43AM"

I formated it to just "07/28/2010"

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

If it is a datetime field, it would have sorted correctly. After adding this as your row field, did you then select group options and choose an "on change of" interval?

Another thing that would change the order is if you set up a topN/group sort on the crosstab.

-LB

 

[cjbrown815]

I went to Cross-Tab group options and do not see a "on change of" option, the common tab is Entered_on in descending order for each day the first date in the period, there's nothing checked in the Options tab . I also did not create a TopN/group sort.

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

The database shows the field is

ENTERED_ON(datetime, null)

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[lbass]

In the crosstab expert, highlight the entered_on field->click on group options-> "The row will be printed on"--that's all I meant--it is essentially an "on change of" decision. So your field should be printing in descending order of date. If it isn't, then in preview mode, while the crosstab is selected, go to report->group sort and see if you have a group sort set.

-LB

 

[cjbrown815]

I had the sort "ALL" on, I changed that to "No Sort" and then the sort in the XTAB expert worked. Thanks.

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931