9 Replies Latest reply on Mar 19, 2012 3:12 AM by Chris Turnbull

Sort Problem on Name Field

Chris Turnbull Mar 17, 2012 3:17 AM

Hi All,

I have a starnge problem that I am struggling with.

I am linked to a MSSQL 2008 Data Source.

I have a Cross Tab Table of which the First field is a PersonID which is an integer and is hidden. When not hidden I can sort this as expected. My next field is a name of the format - Turnbull, Chris which is concatenated in the SQL View with the field name of Name. The problem that I am having is that when I click on Sort for this field either descending or ascending.

I would appreciate any pointers as to why this happens.

Thanks

Chris

1. Re: Sort Problem on Name Field

Russell Christopher Mar 17, 2012 5:22 AM (in response to Chris Turnbull)
Hey Chris -

Sorry to be dense, but can you give us a little bit more? You say:
The problem that I am having is that when I click on Sort for this field either descending or ascending.

...but you don't tell us what the problem is...I'm guessing that you simply aren't getting a sort, which would be expected: Your "Name" field is simply sorting within the "group" of Person ID - and since there is only 1 "Name" per "PersonID", nothing happens. See attached, sheet1.

Maybe a "Set" which combines both PersonID and Name might work for you? (Sheet2)

Book2.twbx.zip 13.1 KB
Like Show 0 Likes(0)

Actions
2. Re: Sort Problem on Name Field

Chris Turnbull Mar 17, 2012 11:01 AM (in response to Russell Christopher)

LOL

No its me been dense or in a rush. I didn't finis the sentence of as I was on my way out.

It doesnt sort at all nothing happens wen I click on the name sort but it does sort when i click on the person ID and every other field.

The SET Method does work but I want to hide the Person ID as I don't need to show it and also how is a set kept updated. Does it alway auto update the members?

Chris
Like Show 0 Likes(0)

Actions
3. Re: Sort Problem on Name Field

Alex Kerin Mar 17, 2012 12:42 PM (in response to Chris Turnbull)

I'm thinking a sample workbook with some data and what you want to happen will help here.
Like Show 0 Likes(0)

Actions
4. Re: Sort Problem on Name Field

Chris Turnbull Mar 18, 2012 1:00 AM (in response to Alex Kerin)
Please find attached workbook where I cannot sort the surname field.

Thanks

Chris

Sort.twbx.zip 12.2 KB
Like Show 0 Likes(0)

Actions
5. Re: Sort Problem on Name Field

Alex Kerin Mar 18, 2012 5:47 AM (in response to Chris Turnbull)

I think this solves your problem - you need to sort ID on the field Name with aggregation minimum or maximum depending if you want A to Z or Z to A:
Like Show 0 Likes(0)

Actions
6. Re: Sort Problem on Name Field

Russell Christopher Mar 18, 2012 11:17 AM (in response to Alex Kerin)

What he said!
Like Show 0 Likes(0)

Actions
7. Re: Sort Problem on Name Field

Chris Turnbull Mar 18, 2012 12:44 PM (in response to Chris Turnbull)

Thanks boys - that does in fact work and gives me a sort order by name that is fixed.

Is it possible to simply sort ascending or descending by clicking on the sort button above the field.

Thanks

Chris
Like Show 0 Likes(0)

Actions
8. Re: Sort Problem on Name Field

Alex Kerin Mar 18, 2012 4:17 PM (in response to Chris Turnbull)

Yes, if you move ID after Name on the shelf. If you have names that are the same that could mess things up.
Like Show 0 Likes(0)

Actions
9. Re: Sort Problem on Name Field

Chris Turnbull Mar 19, 2012 3:12 AM (in response to Alex Kerin)

Alex,

Thanks for the reply. I thought that was the case and I will stick with your original solution.

Thanks for your time

Chris
Like Show 0 Likes(0)

Actions