Sorting pivot table by unvisible fiels

[Anonymous]

Hello,

In my DB I have this table:

CategoryID, SortOrder

A     5

B     2

C     3

D     4

E     1

I'd like to show ONLY the categories (joined with other data), in a pivot table SORTED by SortOrder;

Category Month, Hours

E     Jan     5

E     Oct     8

B     Mar     10

B     Nov     6

C     Sep     9

C     Dec     15

D     Aug     5

D     Oct     4

A     Jun     6

A     Aug     3

It seems that sorting by a field not shown in the dimension it's not possible in a pivot table; is there a trick to workaround this limit?

Thank you.

N.

[antoniotiman]

[avinashelite]

Pull both the tables from DB , rename your CategoryID to Category ...then in the Pivot table got to sort Tab and select expression and add SortOrder

[Anonymous]

Hi Antonio,

Can you have a look at my attachment and find what's wrong?

Grazie 1000 😉

Nicola.

[avinashelite]

have you sent the complete image ?? i could see only sort 

expression

[Anonymous]

I sent only the sort image to show that, despite of the descending sort-by-expression in the first column (status) choice, the pivot tables shows the data in another sort (and i'm not able to understand what is).

N.

[avinashelite]

Give the expression based sort order for all the dimension columns and un-check other sort options  ...I feel that is the issue

[antoniotiman]

Try

Max(Ordinamento)

[Anonymous]

QV File...

Thank you.

N.

[antoniotiman]

Like this