13 Replies Latest reply: Nov 5, 2014 10:58 AM by Gordon Savage RSS

    Pivot table sort

    Gordon Savage


      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:

      straight.png

       

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

      pivot.png

      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

        • Re: Pivot table sort
          Alessandro Saccone

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

            • Re: Re: Pivot table sort
              Gordon Savage

              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

                • Re: Re: Pivot table sort
                  Alessandro Saccone

                  An idea, just try:

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

                  • Re: Pivot table sort

                    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

                    • Re: Pivot table sort
                      Bill Markham

                      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.

                        • Re: Re: Pivot table sort
                          Gordon Savage

                          Hi Bill,

                           

                          If I do that it then pivots on HashVal:

                          24-10-2014 17-14-06.png

                           

                          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

                            • Re: Re: Re: Pivot table sort
                              Gordon Savage

                              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:

                              menu.png

                              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

                                • Re: Re: Re: Re: Pivot table sort
                                  Bill Markham

                                  Gordon

                                   

                                  Looks like an extra dimension called say Level0 would help.

                                   

                                  In the attached qvw I have frigged this as a Calculated Dimension with expression :

                                   

                                  =if ( match(Level1,'Cases','Claims','Recalls','Maintenance Plans') , 'Places' ,

                                  if ( match(Level1, 'Journals' ) , 'Journals' ,

                                  if ( match(Level1, 'Reports' ) , 'Reports'  ,

                                  'The Rest'

                                  ) ) )

                                   

                                  Calculated Dimensions are not something I would recommend, but you could sort this Level0 creation in your load script.

                                   

                                  Other things could also be tidied in the load script like after doing the above have a preceding load with something like :

                                   

                                       if ( Level0 = Level1 , Level2 , Level1 )     as Level1 ,

                                   

                                  To avoid Level0 & Level1 displaying as the same, by putting the Level2 value in Level1.  This would need similarly need sort for the other levels.

                                   

                                   

                                  Bill

                        • Re: Pivot table sort
                          Jakub Michalik

                          So, the mis-sorted rows all have null dimension values, right? Or are those actual values (empty strings, spaces)?

                          Because if they are null values, then maybe I'm crazy, but try switching the pivot table to show subtotals on top. Yeah, I know you don't have any.

                            • Re: Pivot table sort
                              Jakub Michalik

                              OK, the above is irrelevant, I haven't checked the example qvw first, sorry. I think it's an issue I have encountered before. Dimension sort order does not depend on pivot table context. Put a listbox somewhere, with for example Level2, and sort it by Min(HashVal). The order you get here will be preserved in pivot table no matter what, if you use the same sort expression for dimension in pivot table. So for example, Recalls will always be above Maintenance, even if for Level1=Setup it should actually be below.

                               

                              My workaround was rather hacky. In script, I have appended a number of spaces to each subsequent occurence of a same value in a single hierarchy level (first occurence as is, second + one space, third + two spaces, etc.) That way, they displayed exactly the same (trailing spaces are not shown - you don't get the ... because of them), but the values were actually distinct and could be sorted the way I wanted. In case you want to allow selection on those fields and do not want to see duplicates in listboxes, associated fields without the spaces can be kept in the data model.

                                • Re: Pivot table sort
                                  Gordon Savage

                                  Hi Bill and Jakub,

                                   

                                  Thanks very much for your responses - sorry for my late reply.

                                   

                                  I just couldnt get the pivot table to respond to a sort sequence - it always seemed to want to render in 'load order' which meant of course that any value previously loaded would appear out of sequence when it appeared on subsequent occasions.

                                   

                                  My 'solution' was to force a number of spaces on the end of 'Item' to make it 'unique' based on an ordinal position (relative to the dimension hierarchy) to try to sort on this structure. Where there was a 'collision' i.e. 'Claims  ' (3 spaces) had been previously loaded this wouldnt matter as it still represented the 3rd ordinal position in the list (relative to the hierarchy). This is a sample of the code:

                                  05-11-2014 15-50-44.png

                                  where e.g.:

                                  'lL2' represents the selections for a list box (the pivot table was made 'read only')

                                  'Level2' the 'Item' with a number of spaces appended to make it 'unique' in the list relative to the hierarchy (Level1,Level2,Level3 etc)

                                  anL2 the ordinal position in the list relative to the hierarchy (on which Level2 would in theory be sorted in the pivot table)

                                   

                                  I get the feeling it should have been easier but I tried everything and just glad to get past the issue!

                                   

                                  Regards,

                                   

                                  Gordon