Pivot table sort

[#1]

Hi all,

In my load script a column 'HashVal' is calculated to define the ordinal position of the rows.

In a straight table I can define a primary sort on 'HashVal' (ascending) and it renders correctly like this:

But I can't seem to get it to appear in the correct sequence in a pivot table (fast type change):

for example 2058 should appear before 2059, 2062 before 2063 etc.

I've tried unchecking all the sort values in properties and then using expression min(HashVal) for combinations of the dimensions but to no avail.

I've only included HashVal for visibility - I don't actually want to see it in the pivot table (nor want a straight table option).

Can anyone help please?

Regards,

Gordon

v11sr8

[alexandros17]

In sorting expression use simply HashVal (not min of it) and ... let me know

[#3]

Hi Alessandro,

Thanks for the reply.

Just 'HashVal' in the expression doesn't work either.

I've attached an extract from the straight table if you want to see the problem for yourself (my way of saying I'd appreciate the help!)

regards,

Gordon

[alexandros17]

An idea, just try:

use hashval as expression (it must be the first expr.) then order by Y value

[#5]

Gordon Alessandro answer is the right way to do it but you have to put hasval in expression sort for all the dimensions not just for one

[Anonymous]

In a pivot table sorting a sub dimension [i.e.HashVal], in preference to the upper dimensions does not make logical sense.

Use a straight table and it should work.

Or in your pivot table promote the HashVal dimension to the top.

[#7]

Hi All,

Thanks for the replies.

I tried adding the expression to all the dimensions and then checking 'Y value' but it seems to mess it up even more:

Bill - you can't prmote columns in sort for pivot tables.

Regards,

Gordon

[Anonymous]

Promote the dimension on the dimension tab.

[#9]

Hi Bill,

If I do that it then pivots on HashVal:

This needs to be a pivot table and I dont want to display HashVal anyway. HashVal simply defines the logical sequence of the data.

This should be easy!

Regards,

Gordon

[#10]

Hi all,

I still can't get this to work unfortunately and would really appreciate any help.

Perhaps an explanation is required. I'm trying to pull together some security reports that identify permissions by user group for items on menus. I want to mimic the sequence to make it easy to cross reference. Some menus/items won't appear in the security report for all user groups e.g. no access for a group of items means the lower level items won't appear; I resolved this (I believe) by creating an autonumber based on the concatenation of all the levels.

This is an example of a menu for a module:

The items for 'Places' and 'Common Forms' are not grouped as such, whereas those items e.g. Journals/Reports etc do.

Attached is a qvw which contains the transformed security report for a single user group and hopefully demonstrates the sorting issue.

If anyone has the time, I'd sure appreciate it.

Reagrds,

Gordon