Pivot table - dynamic sorting using variables - identifying # dimensions in chart

[#1]

Hi All,

Am currently trying to build a user defined sorting option within a pivot table using variables.

ie I would like the user to be able to define which expression (volume, value, delta to previous year etc...) they would like the data to be sorted (ascending / descending) by, by using variables.

For a static report (ie one where the number of dimensions included are known), I have managed to get this to work, through re-engineering a solution posted on these forums.

Due to the fact that I want to combine this functionality within a dynamic report, where the number of dimensions will vary depending on user selection, I need to edit my macro so that it recognises that there are X dimensions, and therefore sorts all X dimensions by the user selection...

For some reason, not all my code is being returned when I attempt to copy and paste into Internet Explorer, but is similar to the below:

sub SortMultiple

set chart = ActiveDocument.GetSheetObject("DynamicReport2")

set r = ActiveDocument.Variables("SortName")

set s = ActiveDocument.Variables("SortExpression")

set prop = chart.GetProperties

prop.Dimensions(0).SortCriteria.SortByNumeric = 0

prop.Dimensions(1).SortCriteria.SortByNumeric = 0

if s.GetContent.string = "Value" then

prop.Dimensions(0).SortCriteria.Expression.v "=sum(Euros)"

prop.Dimensions(1).SortCriteria.Expression.v "=sum(Euros)"

else

prop.Dimensions(0).SortCriteria.Expression.v "=sum(QtyKG)"

prop.Dimensions(1).SortCriteria.Expression.v "=sum(QtyKG)"

end if

if r.GetContent.string = "Desc" then

prop.Dimensions(0).SortCriteria.SortByExpression = 1

prop.Dimensions(1).SortCriteria.SortByExpression = 1

chart.SetProperties prop

r.SetContent "Asc", true

else

prop.Dimensions(0).SortCriteria.SortByExpression = -1

prop.Dimensions(1).SortCriteria.SortByExpression = -1

chart.SetProperties prop

r.SetContent "Desc", true

end if

end sub

Would like to adapt the above formula so that it returns "prop.Dimensions(i)" and then loops around for the number of Dimensions ( i), but unsure how to write this...?

Anyone with any thoughts, greatly appreciated!

Kind regards,

Rich

[#2]

could someone kindly help me with my VBA syntax....?

Kind regards,

Rich

[IAMDV]

Hi Rich,

Here you go... I hope this what you needed.

The below procedures calculates the number of dimensions and assigns the count to variable MyDim. You can take this two step further, I mean you can do the same for experssion and dynamically count and add the experssion for sorting. Then you can change the code to generic code so that it works with any chart intead of using the chart name as literal.

'----------------------------------------- Parent Procedure ----------------------------------------------

Public Sub SetObjectToVariables ()

    Set MyGraph = ActiveDocument.GetSheetObject("DynamicReport2")                    'Assigning the Chart to the variable MyGraph

    Set MyDims = MyGraph.GetProperties.Dimensions                            'Assigning the Chart Dimension Properties to the Variable MyDims

    Set MyExps = MyGraph.GetProperties.Expressions                            'Assigning the Chart Expression Properties to the Variable MyExps

    Set MyGraphProp = MyGraph.GetProperties                                'Assigning the Chart Preoperties to the Variable MyGraphProp

End Sub

'--------------------------------------------------------------------------------------------------------------------

'----------------------------------------- Calling Procedure ----------------------------------------------

Sub SortName

    set chart = ActiveDocument.GetSheetObject("DynamicReport")

    set r = ActiveDocument.Variables("SortName")

    set s = ActiveDocument.Variables("SortExpression")

    set prop = chart.GetProperties

    prop.Dimensions(0).SortCriteria.SortByNumeric = 0

    if s.GetContent.string = "Value" then

    prop.Dimensions(0).SortCriteria.Expression.v = "=sum(Euros)"

    else

    prop.Dimensions(0).SortCriteria.Expression.v = "=sum(QtyKG)"

    end if

    if r.GetContent.string = "Desc" then

        For i=0 to MyDims.Count       

            prop.Dimensions(i).SortCriteria.SortByExpression = 1        'ascending

            chart.SetProperties prop

            r.SetContent "Asc", true

        i = i + 1

        Next

    else

        For i=0 to MyDims.Count               

            prop.Dimensions(0).SortCriteria.SortByExpression = -1        'descending

            chart.SetProperties prop

            r.SetContent "Desc", true

        i = i + 1

        Next           

    end if

end sub

'-----------------------------------------------------------------------------------------------------------------------

I totally agree with Steve that is not recommended to use the VBA/VB Scripting approach but I have solved many real life problems using this VB Scripting. So I have love and hate relationship with VBA since my Excel days. I am not sure if VB Scripting in QlikView supports full OOP concepts but I am sure it support object model concept. I always create public procedure & functions outside my calling procedure and call the procedures or functions wherever required by passing the parameters. I am not sure if we can still call it inheritence in OOP concepts. I dont come from computers background so please bear with my ignorance.

I hope this helps!

Let me know if you need anything more in VBA. I'd love to do contribute.

Good luck.

Cheers - DV

[IAMDV]

Hi Rich,

Have you got this working? I am curious to know.

Thanks,

DV

[IAMDV · Accepted solution]

Hi Rich,

I am posting the working example here. So that other users can benefit the solution. Please can you mark this thread as answered? I hope its okay.

Thanks,

DV

[#6]

Hi DV,

Apologies re the delay in posting the app, had a bit of tweaking still to be done and then I got side tracked onto other tasks...

The attach uses a dynamic report and then applies your macro to enable a user to display the data in the report according to their needs (ie select an expression to sort by, and whether ascending or descending)....

Kind regards,

Rich

[IAMDV]

Hi Rich,

Nice one. Thank for taking time to share. I like this done of sorting.

Cheers - DV

[AB108]

Hi @IAMDV ,

Thanks for coming up with this. I am looking for a similar solution but I have more than a dozen Expressions that I would like to use for sorting. Some of these expressions are calculated off of other expressions.  Any idea how can the Macro be modified so that it picks up the expression names and formulae.