topic How to set always show for Pivot Table (Macro) in QlikView

How to set always show for Pivot Table (Macro)

Anonymous — Wed, 20 Jul 2011 09:19:01 GMT

Hi experts,

I am working on a report cloning button to clone the existing report and paste the clone(collaboration object) into another sheet. Currently, I have pivot tables initially set with conditional show, I have created a macro function to clone an object with the conditional show but I need to remove the conditional show in the new object and change it to always show so it will be displayed in the destination sheet because the new object has exactly 100% same properties as the original object has. I have tried a few ways to do it but did not have any luck XD, this is the code that Im using currently to change the conditional show to always show. "prop.Layout.Frame.Show.Always=true"

Below is the script to give you guys better understanding about my problem, hope it helps.

Thanks.

Sub cloneReport

Dim currentID 'as String

currentID= ActiveDocument.Variables("varRptID").GetContent().String

if((currentID="-"))then

msgbox("Please select a report")

exit sub

else

' direct to customizable report tab

ActiveDocument.ActivateSheetByID "SH11"

'set x = clone(currentID)

clone(currentID)

end if

End sub

Function clone (objectID)

tempfile = "qvtemp-" & ActiveDocument.Evaluate("purgeChar(osUser(),'\')") & ".xml"

' Write out the model object and read it back. This does *not* require macro system access.

ActiveDocument.GetSheetObject(objectId).WriteXmlPropertiesFile tempfile

ActiveDocument.Sheets("Saved Report").CreateObjectFromXmlPropertiesFile tempfile

' Trick is getting a handle to the newly created object.

' From testing, it appears the new object is always first in the collection.

' Will this always be true?

sObjects = ActiveDocument.ActiveSheet.GetSheetObjects 'Objects this sheet

set cloneObj = sObjects(lBound(sObjects)) ' Get first obj in collection

' Now we want to turn Show on for the new object so it's visible.

set prop = cloneObj.GetProperties

prop.Layout.Frame.Show.Always=true

cloneObj.SetProperties prop

End Function

How to set always show for Pivot Table (Macro)

kaushiknsolanki — Wed, 20 Jul 2011 09:35:46 GMT

HI,

try below one.

set chart=ActiveDocument.GetSheetObject("CH01")

set cp=chart.GetProperties

setdims=cp.Dimensions

setexpr=cp.Expressions.Item(0).Item(0).Data.ExpressionData

dims(0).Show.Type=1 '0=false, 1=true, 2=cond

expr.Show.Type=2

expr.Show.Expression="sum(a)<10000"

chart.SetProperties cp

Regards,

Kaushik Solanki

Re: How to set always show for Pivot Table (Macro)

Anonymous — Thu, 21 Jul 2011 06:53:50 GMT

Hi Kaushik.solanki,

Thanks for helping but unfortunately it didn't solve my problem, maybe you could not understand my question because of my poor english, thanks anyway my problem is solved.:D

Found the problem, the problem was because of the statement below did not really get the new created object

sObjects = ActiveDocument.ActiveSheet.GetSheetObjects 'Objects this sheet set cloneObj = sObjects(lBound(sObjects))

so I added in a For loop to get the object id of the last pivot table object type which is the new created object and set the object to always show. The script is attached below.

Sub cloneReport

Dim currentID 'as String

currentID= ActiveDocument.Variables("varRptID").GetContent().String

' Make a clone of report. The clone will automatically get a new id.

if((currentID="-"))then

msgbox("Please select a report")

exit sub

else

'set x = clone(currentID)

clone(currentID)

end if

End sub

Function clone (objectID)

' Need some work on the filename. Make sure two users don't collide and that the

' file is created in a temp area.

' This will create a filename like "qvtemp-UPIwts03.xml"

tempfile = "qvtemp-" & ActiveDocument.Evaluate("purgeChar(osUser(),'\')") & ".xml"

' Write out the model object and read it back. This does *not* require macro system access.

ActiveDocument.GetSheetObject(objectId).WriteXmlPropertiesFile tempfile

ActiveDocument.Sheets("Saved Report").CreateObjectFromXmlPropertiesFile tempfile

' direct to customizable report tab

ActiveDocument.ActivateSheetByID "SH11"

sObjects = ActiveDocument.ActiveSheet.GetSheetObjects 'Objects this sheet

'get the object id of the last pivot table object

For i = lBound(sObjects) To uBound(sObjects)

set obj = sObjects(i)

if obj.GetObjectType = 10 then 'objectType=10=pivot tables

id = obj.getObjectId

end if

set finalObj = ActiveDocument.getSheetObject(id)

set prop = finalObj.GetProperties

prop.GraphLayout.Frame.Show.Always=True

finalObj.SetProperties prop

finalObj.Restore 'the unminimized the object

End Function