Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks Oleg! Appreciate your feedback and prompt reply.
This could be listed as one of QlikView's future enhancements TBD.