11 Replies Latest reply: Jan 31, 2011 11:35 AM by dj-kalle RSS

    Is this a hard constraint on pivoting axis using macro automation?

      It took me a while to figure out how to pivot rows into columns when automatically building a new Pivot Table chart. Normally, the only way to do this is by dragging the row into a column, which changes the dimension from being on the vertical axis, to the horizontal axis.

      The trick (it would appear) is to use the NumberOfLeftDimensions property. By default, this property is set to the number of dimensions in the Pivot Table. If you set this property to be an explicit value - e.g. 1, then all dimensions after the first dimension will be placed horizontally instead of vertically. Furthermore, if you increase the "NumberOfLeftDimensions" property to be greater than the number of dimensions, then this has the effect of moving expressions from columns to rows.

      Therefore it is possible to have near complete control over the Pivot Table axis layout.

      That notwithstanding, there is one scenario where I have not been able to figure out a way of controlling the layout through automation. Namely, I cannot find a way to tell the Pivot Table that I want to keep one or more expressions on the vertical axis, but keep one or more of my dimensions on the horizontal axis. It is possible to move expressions to my axis ,but only if all of my dimensions are already on the vertical axis. In other words, I cannot mix dimensions and expressions on my vertical axis, while having a dimension on the horizontal axis. But this is possible through mouse dragging.

      Any ideas?

        • Is this a hard constraint on pivoting axis using macro automation?
          Michael Anthony

          Hi Neil,

          I get the same problem when trying to achieve same outcome. My thinking is that there is a gap b/w the underlying table definition and what is available to the automation. With the single value NumberOfLeftDimensinos it is impossible to have some fields on the horizontal axis and expressions on the vertical.

          Have you reported this to QlikTech as a bug or enhancement?

          Regards,

          Michael

            • Is this a hard constraint on pivoting axis using macro automation?
              John Witherspoon

              It's been a long time since I attempted something like this, but I remember having similar trouble trying to set up my horizontal and vertical dimensions and expressions using the API. I remember concluding what Michael says, that the necessary controls simply weren't available in the API. Now, I don't guarantee that I'm remembering it correctly, and even if I am, I don't guarantee that I didn't just miss something. But yeah, I couldn't find it in the API either. I didn't report it.

            • Is this a hard constraint on pivoting axis using macro automation?

              I'm new at Qlikview and came across your post, I was wondering if you could tell me where I could find the "NumberOfLeftDimensions" property?

              Thank you.

                • Is this a hard constraint on pivoting axis using macro automation?

                  NumberOfLeftDimensions is a property of TableProperties. You're best to use the API Guide.qvw document to explore the OLE Automation API. I have pasted a code sample showing how it works.

                   



                  rem Create HLO system table on new sheet
                  set Sheet=ActiveDocument.CreateSheet
                  set Hlo=Sheet.CreatePivotTable
                  Hlo.AddDimension "$Field"
                  Hlo.AddDimension "$Table"
                  Hlo.AddExpression "Only([$Field])"
                  set Prop = Hlo.GetProperties
                  Prop.TableProperties.PivotAlwaysFullyExpanded=true
                  Prop.Dimensions.Item(0).SortCriteria.SortByAscii=0
                  Prop.Dimensions.Item(0).SortCriteria.SortByLoadOrder=1
                  Prop.Dimensions.Item(1).SortCriteria.SortByAscii=0
                  Prop.Dimensions.Item(1).SortCriteria.SortByLoadOrder=1
                  Prop.TableProperties.NumberOfLeftDimensions = 1
                  Hlo.SetProperties Prop


                   

                • Is this a hard constraint on pivoting axis using macro automation?
                  Jerry Bressler

                  Hello,

                  I have a similar but simpler problem, I have 1 or 2 dimensions I need to add via API that need to be at the top of a Pivot Table (header) with the expressions displayed down the side (vertically). It appears you have mastered this much but I seem to be having problems. Can you share the "NumberOfLeftDimensions" code where you had the expressions on the side and the dimensions in the header?

                  Thanks.

                    • Is this a hard constraint on pivoting axis using macro automation?

                      I've pasted a code excerpt below, that should help.

                      Basically the gist of it is that you need to remove all dimensions and add them back in again. I think that's the "gotcha". Also, if you use "Indent Mode", you need to turn it off while manipulating the dimensions, and then turn it back on again. Also, I recommend you detach the chart before performing these manipulations, and then reattaching the chart. Otherwise you'll likely experience lock-up.

                      I should also point out, that I don't use this approach any longer, and instead am using Calculated Dimensions to show/hide levels. In general, I've found that messing around with adding and removing dimensions through VB Script causes performance issues, in large part because it messes up chart caching. That said, your situation may be different.

                       


                      set chrtOrgQuestions = ActiveDocument.GetSheetObject(csScoredQuestionsChartId)
                      chrtOrgQuestions.detach
                      RebuildOrgChartDimensions chrtOrgQuestions, iShortestPath
                      chrtOrgQuestions.attach

                      sub RebuildOrgChartDimensions (chrtCur, iShortestPath)
                      dim propChart
                      dim dimsChart
                      dim asSecondaryDimensions
                      dim i
                      dim iOrgRollupFieldLen
                      dim iCurSecondaryDimension
                      dim dimCur, fldCur
                      dim iDimIdx
                      dim dimSG1_QShortDesc2
                      dim iSG1_QShortDesc2_dim_idx


                      iCurSecondaryDimension = 0
                      iOrgRollupFieldLen = len(csOrgRollupField)
                      redim asSecondaryDimensions(0)


                      set propChart = chrtCur.GetProperties
                      set dimsChart = propChart.Dimensions


                      '1. Cache the the current secondary dimensions, while at the same time removing all dimensions
                      for i = 0 to dimsChart.Count - 1
                      ' msgbox "Cur iteration: " & i
                      if strcomp(left(dimsChart(i).PseudoDef.name, iOrgRollupFieldLen), csOrgRollupField) <> 0 then
                      ' msgbox "Found a difference: " & dimsChart(i).PseudoDef.name
                      redim preserve asSecondaryDimensions(iCurSecondaryDimension)
                      asSecondaryDimensions(iCurSecondaryDimension) = dimsChart(i).PseudoDef.name
                      iCurSecondaryDimension = iCurSecondaryDimension + 1
                      end if


                      chrtCur.RemoveDimension(0)
                      next


                      '2. Add back all ORG dimensions
                      for i = 1 to iShortestPath
                      chrtCur.AddDimension csOrgRollupField & i
                      next


                      '3. Add back the secondary dimensions
                      if iCurSecondaryDimension > 0 then
                      for i = 0 to iCurSecondaryDimension - 1
                      'NB: This code must be refactored to show partial sums for all non-root and non-direct-child-of-root score groups
                      iDimIdx = chrtCur.AddDimension(asSecondaryDimensions(i))
                      if strcomp(asSecondaryDimensions(i), csSG1_QShortDesc2) = 0 then
                      iSG1_QShortDesc2_dim_idx = iDimIdx
                      end if
                      next
                      end if



                      '****WARNING WARNING DANGER DANGER
                      'It is necessary to refresh the chart properties object reference before pivoting
                      set propChart = chrtCur.GetProperties
                      with propChart
                      .TableProperties.NumberOfLeftDimensions = iShortestPath

                      end with
                      chrtCur.SetProperties propChart

                      end sub


                       

                    • Is this a hard constraint on pivoting axis using macro automation?

                      Hi Neil,

                      did you find a solution for mixing dimensions and expressions on vertical axis, while having a dimension on the horizontal axis? Thanks for you answer.

                        • Is this a hard constraint on pivoting axis using macro automation?

                          Nope - nothing beyond NumberOfLeftDimensions.

                          However, if you're looking to generate document layouts ahead of time it's possible to achieve whatever layout you desire through XML manipulation. Although this won't help you when you're trying to performing interactive chart manipulations.

                          That said, I've since changed my approach to QlikView, and generally avoid using VB macros whenever possible to achieve my goals. Instead, it's better to drive everything by way of expressions. In some cases you can achieve your goals by pre-creating chart layouts and then using Show/Hide Expressions. However, there are limits to this approach.

                            • Is this a hard constraint on pivoting axis using macro automation?

                              Hi Neil,

                              thanks for your fast answer. Iam trying to save a pivot table layout, my users are building via Mouse Drag and Drop. (e.g. expressions on vertical axis, dimensions on horizontal). I save all Dimensions and Expressions in QV Variables with the help of a QV Bookmark. Whenever my users are reloading this Bookmark, their saved Pivot Table is rebuilded via VB Macros. Now i am searching for a way, so save a Drag and Drop Layout in a Bookmark.

                              Martin