How to eliminate Macro code to replace with triggers at Document/Sheet level.

Fri, 01 Feb 2013 16:59:00 GMT

Hi All,

I'm using QlikView version 10.00.9274.8 SR4 x64

Need help converting the below macro code into the triggers on the document/sheet. Any help would be greatly appreciated and thanks a lot in advance for your time and input.

The variable 'DocumentLevel' has the definition:

=if(count(DISTINCT ALL [Territory Number])=1,'Territory',

   if(count(DISTINCT ALL Region)=1,'Region',

      if(count(DISTINCT ALL Area)=1,'Area',

         if(count(DISTINCT ALL [Sales Team])=1,'Sales Team',

            'All Data'))))

and Variable 'SalesGroupCurrentField' has the definition =GetCurrentField(SalesGroup)

Have created a cyclic group called 'SalesGroup'

==============================================================

Macro code to be eliminated and replaced with triggers at document/sheet level

==============================================================

Sub SetSalesGroup

     mDocumentLevel =
     ActiveDocument.Variables("DocumentLevel").GetContent.String

     set group = ActiveDocument.GetGroup("SalesGroup")

     set gp=group.GetProperties
          gdims = gp.FieldDefs.Count

          'Clear current fields from group

for i = gdims-1 to 0 step -1

   group.RemoveField i

   'set gp=group.GetProperties

next

if mDocumentLevel = "All Data" then

   group.AddField "Sales Team"

   group.AddField "Total
Market"

   group.AddField "Area"

   group.AddField "Region"

group.AddField "Territory"

elseif mDocumentLevel = "Sales Team" then

   group.AddField "Area"

   group.AddField "Total
Market"

   group.AddField "Region"

   group.AddField "Territory"

elseif mDocumentLevel = "Area" then

   group.AddField "Region"

   group.AddField "Total
Market"

   group.AddField "Territory"

elseif mDocumentLevel = "Region" then

   group.AddField "Territory"

   group.AddField "Total
Market"

else group.AddField "Territory"

end if

End Sub

'if SalesGroup cyclic group value is Total Market set PlanTrendMgrs chart to show Trellis as 1x1

Sub PlanTrendMgrsProperties

     mDocumentLevel =
     ActiveDocument.Variables("DocumentLevel").GetContent.String

     mSalesGroup =
     ActiveDocument.Variables("SalesGroupCurrentField").GetContent.String

     if mDocumentLevel = "Territory" OR mSalesGroup = "Total Market" then

        PTColumns=1

        PTRows=1

     else PTColumns=2

        PTRows=3

     end if

     set chart = ActiveDocument.GetSheetObject("PlanTrendMgrs")

     set p = chart.GetProperties

     p.ChartProperties.TrellisProperties.Columns = PTColumns

     p.ChartProperties.TrellisProperties.Rows = PTRows

     'v10 IR bug; some prop on chart must change to recalculate chart so set Error Bar Color to some random color

        randnum = round(rnd()*255)

     p.ChartProperties.ErrorBarColor.PrimaryCol.Col = RGB(randnum,randnum,randnum)

     chart.SetProperties p

End Sub

Re: How to eliminate Macro code to replace with triggers at Document/Sheet level.

Oleg_Troyansky — Fri, 01 Feb 2013 17:13:00 GMT

Unfortunately, you can't eliminate all of this code and replace it with Actions. Actions have a limited scope - they can make and modify selections, set variables, activate sheets or sheet objects. They can't, however, manipulate the contents of a Group or modify properties of any objects such as charts...

The only thing you can do is add an Action at the appropriate trigger level and call the macro subroutine to perform this logic, triggered by sertain Document and Sheet events...

cheers,

Oleg Troyansky

Re: How to eliminate Macro code to replace with triggers at Document/Sheet level.

Fri, 01 Feb 2013 17:39:53 GMT

Thanks Oleg! Appreciate your feedback and prompt reply.

This could be listed as one of QlikView's future enhancements TBD.

topic Re: How to eliminate Macro code to replace with triggers at Document/Sheet level. in QlikView

How to eliminate Macro code to replace with triggers at Document/Sheet level.

Re: How to eliminate Macro code to replace with triggers at Document/Sheet level.

Re: How to eliminate Macro code to replace with triggers at Document/Sheet level.