Categories
QlikView /en Tips n Tricks /en

extended dataloader

/**
 * LoadSourceTableByName
 * @param TableName String the name of the table that will be exported
 * @param Historical Boolean if a historical version will be saved on a monthtly basis, pls. check comment!
 * @version 1.01
 * @author Philipp Frenzel <philipp@frenzel.net>
 */

SUB LoadSourceTableByName(NameSpace, TableAlias, TableName, Historical)

LET varTableName = TableName;
LET varTableAlias = TableAlias;
LET varNameSpace = NameSpace;

QUALIFY "*";
 
$(varTableAlias):
LOAD *;
SQL SELECT * FROM spaceman.$(varTableName);
 
UNQUALIFY "*";
 
CALL Qvc.Log('No of rows loaded in table: $(varTableAlias)' & NoOfRows('$(varTableAlias)'),['INFO']);
 
// Here we store the table as is into the filesystem 
STORE $(varTableAlias) INTO "$(PATH_DATASTAGING)$(varNameSpace)_$(varTableAlias).qvd" (qvd);

IF Historical = 1 THEN

// ATTENTION, if you save historical data, pls. ensure that a folder with the tablename name exists within the datastaging root folder!
STORE $(varTableAlias) INTO "$(PATH_DATASTAGING)HISTORY/$(varTableName)/$(varTableName)$(VERSIONDATE).qvd" (qvd);

END IF

DROP TABLE $(varTableAlias);

END SUB

 

Categories
Common QlikView /en Script /en Tips n Tricks /en

QlikView Generating Year over Year Targets

From time to time you have to create budget figures upon a percentage change compared to the last year. This can be a little bit difficult, depending on the complexity of the KPI. The following example is build upon a pre-cleaned datamart and shows how you create the monthly figures for you budget.

/**
* Find the opening balance for the sqm this year
*/
OpeningSQM:
NOCONCATENATE LOAD Distinct  
     %SiteID, 
     date(monthend(ReportingDate) + 1) AS ReportingDate //need to cast it in the next month to have the start in january
     ,'BUD'  AS MM_TYPE
     ,'2015' AS BUD_VERSION
     ,'SQM_CUM_OPENING'  AS BUD_KPI
     ,sum(KPI.UnitMoveIn_SQM_CUM) - sum(KPI.UnitMoveOut_SQM_CUM) AS Budget.Value
FROM
[..\04 Datamart\EVENTS_ALL_MONTHLY_KPI.qvd](qvd) 
WHERE 
     month(ReportingDate) <= 12 AND year(ReportingDate) = 2014
GROUP BY
     %SiteID, 
     ReportingDate;

/**
* Let's add the SQM changes by month, to make the cumulated calculation
*/
Concatenate(OpeningSQM)
LOAD
      *
Resident FinalBudget
WHERE
	BUD_KPI = 'SQM';
	

//Lets build the cumulated sqm
FOR MonthIter = 1 TO 12

	Concatenate(FinalBudget)
	LOAD
		%SiteID
		,MM_TYPE
		,BUD_VERSION
		,'SQM_CUM' 					AS BUD_KPI
		,floor(max(ReportingDate)) 			AS ReportingDate
		,sum(Budget.Value)				AS Budget.Value
	RESIDENT OpeningSQM
	WHERE 
		month(ReportingDate) <= $(MonthIter)
	GROUP BY
		%SiteID
		,MM_TYPE
		,BUD_VERSION
	;	
	
NEXT;

DROP TABLE OpeningSQM;

As you can see the real magic here is done in the FOR-TO-Loop, here it shows a cumulated budget figure, but you can easily change to a mothly figure!

/**
* Find the opening balance for the occ units this year
*/
LET InsuranceFactor = 1.05;

CONCATENATE (FinalBudget)
LOAD 
	%SiteID,
	floor(monthname(ReportingDate) + 365) AS ReportingDate
	,'BUD'  AS MM_TYPE
	,'2015' AS BUD_VERSION
	,Lead.CleanLeadSource AS BUD_KPI
	,Lead.CleanLeadSource
	,count(DISTINCT %ActionID) * $(InsuranceFactor) AS Budget.Value
FROM
[..\04 Datamart\INVOICES.qvd]
(qvd)
WHERE
	year(ReportingDate) = 2014 AND 
GROUP BY
	%SiteID
	,monthname(ReportingDate);

 

Categories
Common QlikView /en Script /en

Load multiple GoogleAnalytics-Properties at once w/ dynamic metrics in QlikView

Our objective is to load GoogleAnalytics data, seperated in months with a dynamic metric, fixed dimensions and for multiple Properties. To achieve this we’re using the QlikView addon: QVSource. The script contains the following (important) variables:

  • gAppId = googleAnalytics PropertyId
  • vMetric = the dynamic metric, we want to load (note that this script will only load 1 metric per request)

First of all we need to define our googleAppIds within a static table.

GoogleId:
LOAD * INLINE [
    AppId, Land
    12345678, AT
    23456789, CH
    34567890, DE    
];

Now we have to loop through our table:

Let vStartTime = now();
Let vNr = NoOfRows('GoogleId')-1;

Trace $(vNr);

FOR i = 0 TO  $(vNr)
        Trace Loading $(i);

        LET vGAppId = peek('AppId',i,'GoogleId');
        LET vCountryId = peek('Land',i,'GoogleId');
        
        Trace start Sub loadGoogle $(vGAppId) and $(vCountryId);
        CALL loadGoogleId(vGAppId, vCountryId);
NEXT;

 

As our loadGoogleId Sub is still missing we have to add this here:

SUB loadGoogleId(gAppId, CountryCode)

        Trace loading the values day by day;
        Set x=1;        
        
        FOR zz = 2010 TO 2015
                FOR ii=1 TO 12 //monat
                                // Date period                        
                                LET jj = num($(ii),'00');
                                LET ee = num($(dd),'00');
                                LET ff = num($(dd),'00');
                                
                                LET vMonthend = left(monthend('01.' & $(jj) & '.' & $(zz)),2);
                                SET gDateStart='$(zz)-$(jj)-01';
                                SET gDateEnd='$(zz)-$(jj)-$(vMonthend)';
        
                                CALL LoadGoogle('users');
        
                                TRACE ende der schleife;
        
                                STORE GoogleAnalyticsConnectorV3_DataFromQueryURI INTO '$(PATH_DATASTAGING)\GOOGLEANALYTICS\$(CountryCode)_FILENAME_HERE_$(zz)_$(jj)_test.qvd' (qvd);
        
                                DROP TABLES GoogleAnalyticsConnectorV3_DataFromQueryURI;
                                x=x+1;
                        //NEXT; //next day
                NEXT; //next monat
        NEXT; //next jahr
END SUB;

Here is just a loop to address every single month within our prefered timespan. (one step for every month, as we want one table/file for every month). We call LoadGoogle in every step and store it afterwards.

LoadGoogle is the Sub, where the true magic happens. You have to build your LOADing-Statement with the QVSource-GoogleAnalyitcsConnector / DataFromQueryURI. Now you have to modify your string like the one below (just add the variables where they are required):

SUB LoadGoogle(vMetric)

GoogleAnalyticsConnectorV3_DataFromQueryURI:
LOAD
        dim_source,
        dim_adContent,
        dim_deviceCategory,
        dim_region,
        dim_hostname,
        dim_pagePath,
        dim_date,
        makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date_qv,
        metric_users
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=https%3a%2f%2fwww.googleapis.com%2fanalytics%2fv3%2fdata%2fga%3fids%3dga%253A$(gAppId)%26start-date%3d$(gDateStart)%26end-date%3d$(gDateEnd)%26metrics%3dga%253A$(vMetric)%26dimensions%3dga%253Asource%252Cga%253AadContent%252Cga%253AdeviceCategory%252Cga%253Aregion%252Cga%253Ahostname%252Cga%253ApagePath%252Cga%253Adate]
(qvx);

END SUB

 

Categories
Developer Teaching QlikView /en Script /en

QlikView Script Sub for Dataloading

/**
 * LoadSourceTableByName
 * @param TableName String the name of the table that will be exported
 * @param Historical Boolean if a historical version will be saved on a monthtly basis, pls. check comment!
 * @version 1.0
 * @author Philipp Frenzel <philipp@frenzel.net>
 */

SUB LoadSourceTableByName(TableName, Historical)

LET varTableName = TableName;

QUALIFY "*";
 
$(varTableName):
LOAD *;
SQL SELECT * FROM spaceman.$(varTableName);
 
UNQUALIFY "*";
 
 
// Here we store the table as is into the filesystem 
STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

IF Historical = 1 THEN

// ATTENTION, if you save historical data, pls. ensure that a folder with the tablename name exists within the datastaging root folder!
STORE $(varTableName) INTO "$(PATH_DATASTAGING)HISTORY/$(varTableName)/$(varTableName)$(VERSIONDATE).qvd" (qvd);

END IF

DROP TABLE $(varTableName);

END SUB

Sorry, typo in the path variable, now fixed! Enjoy the script!

Categories
QlikView /en QlikView GUI /en Tips n Tricks /en

QlikView – Color Blend by Dimension Value

Overview

Sometimes you wanna define a “color schema” by the values of a dimension. The easiest way is to generate a mapping table where each dimensionvalue gets an color assigned.

Downloads

First load the color table as CSV from here and save them into the same folder where you wanna create and run the QlikView Worksheet:

Sample

//Load ColorTable as IS
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
 Red, 
 Green, 
 Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

//first init variables
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

ColorTable:
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************************** //
// Dynamic Variables by Frenzel GmbH - COPY 2013 www.frenzel.net  //
// AUTHOR Philipp Frenzel <philipp@frenzel.net                    //
// ************************************************************** //

//Get the number of available rows in the rights table
LET varIntegerNoVariables = NoOfRows('ColorTable');

TRACE Number of Dynamic Color Vars: $(varIntegerNoVariables);
 
//Lets build the dynamic variables
FOR DatenbankIter = 0 TO $(varIntegerNoVariables) - 1
 
 	//here we build the logic for the color coding
	LET curVariableName = peek('ColorName',DatenbankIter,'ColorTable');
	LET col$(curVariableName)= 'rgb(' & peek('Red',DatenbankIter,'ColorTable') &',' & peek('Green',DatenbankIter,'ColorTable') &','& peek('Blue',DatenbankIter,'ColorTable') &')';
	
NEXT;

DROP TABLE ColorTable;

// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //


//then we assign the values to a dimension mapping
//create the dimension mapping
Map_Color:
MAPPING
LOAD * INLINE [
 YourFieldname, Color
 a, colCadetBlue
 b, colCadetBlue1
 c, colCadetBlue2
 d, colCadetBlue3
 e, colCadetBlue4
];

//load you data into the application

Testdimension:
LOAD * INLINE [
 YourFieldname
 a
 b
 c
 d
 e
];

//assign the colors
Dimension:
NoConcatenate LOAD
 *
 ,applymap('Map_Color',YourFieldname,'colBlue') AS ColorName
RESIDENT Testdimension;

DROP TABLE Testdimension;

If you wanna update the colors “online” just replace the first load with:

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[http://www.beeye.org/wp-content/plugins/download-monitor/download.php?id=3]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq, no eof);

After this we create a bar chart with the YourFieldname as Dimension and 1 as Formula:

Screenshot 2015-04-02 12.55.30

To color it as we wanted to, we will edit the background formula for the formula as here:

Screenshot 2015-04-02 14.26.18Which leads us to the following result:

Screenshot 2015-04-02 14.27.15

Categories
Common Macros and Automatisation QlikView /en Tips n Tricks /en

Export a Chart or Tablebox via VBScript

As sometimes it makes sense to export data with a loop using VBScript. This little toolset should do the job for you:

Sub ExecByFieldValues

	SET Doc = ActiveDocument
	SET Field = Doc.Fields(getVariable("pstrFieldName"))
	SET Values = Field.GetPossibleValues
	
	FOR i = 0 TO Values.Count-1		
		ExportObject Values.item(i).Text
	NEXT

End Sub

Be aware that you need some helpers:

rem ** will read and return the content of a variable defined in script or document **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel

function getVariable(varName)
 Set v = ActiveDocument.Variables(varName)
 IF v Is Nothing THEN
     getVariable = ""
 ELSE
     getVariable = v.GetContent.String
 END IF
end function

rem ** will check if the folder exists, if not, will build it **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
function MyCheckFolder (pdfPath)
	Dim objFSO
	Dim objFolder
	Dim Overwrite

	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Overwrite = objFSO.FolderExists(pdfPath)

	Select Case Overwrite
	Case False: Set objFolder = objFSO.CreateFolder(pdfPath)
	Case Else
	End Select

	Set objFSO = Nothing: Set objFolder = Nothing
end function

and finally you can export the object like this:

Function ExportObject (pstrFieldFilter)
	
	SET Doc = ActiveDocument
	SET Field = Doc.Fields(getVariable("pstrFieldName"))

	Dim vstrExportPath
        Dim vstrExportFileName
        Dim vstrCompleteFile
    	
        'Lets create a temporary bookmark, to store the previous selection
	Doc.CreateUserBookmark "MakroStorage"
	
	'Select the Current fieldvalue
	Field.Select pstrFieldFilter
	
	SET obj = Doc.GetSheetObject(getVariable("pstrExpObject"))
	vstrExportPath = getVariable("pstrExpPath") & "\"
	'will check if the folder exists already or should be created if not so
	'MyCheckFlolder vstrExportPath
	
	vstrExportFileName = pstrFieldFilter & ".xls"
	vstrCompleteFile = vstrExportPath & vstrExportFileName
	'will Delete an old existing version
	deleteReport vstrCompleteFile
	obj.ExportEx  vstrCompleteFile , 5
	
	'Wait until object is created then release the objects
        Doc.GetApplication.Sleep 5000
	
	'reset the start selections
	Doc.RecallUserBookmark "MakroStorage"
	set obj = Nothing
	
	Doc.RemoveUserBookmark "MakroStorage"       

End Function

Take care that you’ve added the variables for the path in you enviroment variables or in your load script:

//Variables for the ExportObjectMakro
Let pstrFieldName = "Standortname";
Let pstrExpObject = "CH526";
Let pstrExpPath   = "C:\ttemp";

 

Categories
QlikView /en QlikView Workarounds /en Tips n Tricks /en

QlikView and Google Analytics – primary key issues

The main challenge I’m facing now, is the definition of a primary key, that allows me to build up my own data mirror to google analytics without messing up the metrics. Walking around on the internet, I found a lot of interesting articles about this and the best solution to come up with, seems to be the option to hand over custom variables to google analytics. This custom variables allow you to identify a single session and then you can grep the related data to it!

For the historical data, I have to come up with another solution, as the tracking ID will only be implemented for the upcoming periods. And as google doesn’t want people to be able to track the visitors down to the detail record (limit of 10 to dimensions and metrics) I’ll have to work with a “bridge” anyway.

Update

After some time of investigation, with the new google universal analytics you can use the following steps to get a unique primary key!

  1. You need to login to your google analytics account.
  2. Screenshot 2014-11-11 11.47.22 Then enter the “Admin” Menu, so you can add the new custom dimension.
    Screenshot 2014-11-11 11.47.31
  3. After you generated the custom variable, google offers you the code to include into your webpage:
    Screenshot 2014-11-11 11.48.08

So by doing this, the new custom dimension is available and can be “queried” although by the connector.

Categories
QlikView /en Tips n Tricks /en

QlikView and Google Analytics – Getting Started

Hello,

as I got the challenge to integrate Google Analytics into our company warehouse with QlikView I will write a series of hopefully more or less usefull articles on how to fetch the data needed.

For accessing the Google Analytics API (v3) you have two possibilities:

  1. You are a technic geek and you know how to use REST-Services and OPENAUTH
  2. You use the ready to use and well tested tool QVSource – www.qvsource.com

As we had some time pressure for the analysis and I didn’t wanna write the “fetcher” by myself. We decided to order and use the QVSource solution.

Where to start?

Facing the “KPI-Definition List” from our marketing department and looking inside the google analytics dev documentation I realized that this will be a time consuming and knowledge building task;)

After digging into more and more details (help from dev friends) I ended up on this fancy little tool:

Google Analytics Query Explorer 2
Google Analytics Query Explorer 2

How to configure this tool is as simple as login into the tool with your oauth google credentlials (your normal google account). Then you can select the google analytics profile and the site you wanna query on.

To get started, I build a simple request structure where I ask for the dimension:

  • ga:date (YYYYMMDD)

Can be selected as followed:

Screenshot 2014-08-11 13.30.18

And the following metrics:

  • ga:users (former visitors)
  • ga:sessions (former visits)
  • ga:pageviews

If you have the feeling of getting lost within dimensions and metrics (measures) within the google analytics datamodel – I did so too – took me about 2 days to understand what’s going on (birdseye)…

The really cool part now is, that by clicking on the following symbol:

Screenshot 2014-08-11 13.36.29You’ll get the query uri for the latest Google Analytics API, that you can (if you although decide to use the QVSource solution) copy and paste within the follwing dialog in QVSource:

Screenshot 2014-08-11 13.47.12

After you clicked this you can use the generated code to fetch the first records from google!

I made a quick an dirty line/bar chart to display the so fetched metrics:

qlik view first quick chart
qlik view first quick chart

Check out my next posts, to see and learn more! Philipp;)

Categories
Macros and Automatisation QlikView /en Tips n Tricks /en

Source Control for QlikView with git

What you need?

So, facing the general question of “reasons” for lacking data quality, one of the most important thinks is transparency within the creation of the KPI’s. To reach this aim, I’ll switched to a full version control management with git to show my QlikView “customers” from where possible “issues” may resulting.

To reach this, I first registered myself at bitbucket – because the offer unlimited private repos for teams till 5 people. What means a perfect match for me! I although have an paid account on github, but for this “project” bitbucket fits my needs more.

Bitbucket offers a git client “sourcetree” that I although installed (free) and it works like charm!

Building the source to control

As we wanna track source changes, we first need to create _prj folders for all applications that we wanna manage by git. If you don’t know this folder, pls. just goolge it and you’ll find wonderfull explenations by other qlik’s that will help you to understand the contents. As I’m lazy and my QlikView Projects have more than 100’s of qvw’s. I wrote/adapted this little script to generate the _prj folders automatially:

Sub CreateProjectFolders

	' Call the RecurseFolders routine
	' Takes one argument - in this case, the Path of the folder to be searched
	RecurseFolders "D:\qlikview\"
	 
	' echo the job is completed
	MsgBox "Completed!"

End Sub

Sub RecurseFolders(sPath)

Dim fso

' create a global copy of the filesystem object
Set fso = CreateObject("Scripting.FileSystemObject")
 
With fso.GetFolder(sPath)
    if .SubFolders.Count > 0 Then
        For Each Folder In .SubFolders
            'WScript.Echo folder.path
            Set Files = Folder.Files
                For Each File in Files
                If fso.GetExtensionName(File.Name) = "qvw" Then
                    IF fso.FolderExists(Replace(File ,".qvw","-prj")) THEN
                    	' NOTHING NEEDS TO BE DONE
                    ELSE
                    	MsgBox (File.Name)
                    	fso.CreateFolder(Replace(File ,".qvw","-prj"))
                    	OpenClose(File)
                    END IF
                End If
            Next
            ' Recurse to check for further subfolders
        	RecurseFolders folder.Path
        Next
        End if
End With

' clean up
Set fso = Nothing
 
End Sub
 
Function OpenClose(qvwFullPath)

  Dim Qv
  Set Qv = CreateObject("QlikTech.QlikView")
	
  Set docObj = Qv.OpenDocEx (qvwFullPath,0,false)  ' Open the document
  docObj.Save
  docObj.CloseDoc
  OpenClose = qvwFullPath
  
  Set Qv = Nothing

End Function

After this, we’ll have a wonderfull set of xml’s and txt/dat within the _prj folder. And suprise, they can be tracked within source control!

Convert XLS to TXT in QlikView

As some of you might store mappings inside XLS-Files, I coded a small load script, that will convert your xls-files to txt, so changes within your mappings or intervall maps can be tracked to! Here is the code:

sub ScanFolder(Root)
          for each FileExtension in 'xls'
                    for each FoundFile in filelist( Root & '\*.' & FileExtension & '')
                    	if Right('$(FoundFile)',3) = 'xls' THEN
                              File:
                              LOAD 
                              	*
                              FROM [$(FoundFile)] (biff, embedded labels);
                              
                              STORE File INTO [$(FoundFile).csv] (txt);
                              
                              DROP TABLE File;
                        end IF
                    next FoundFile
          next FileExtension
          for each SubDirectory in dirlist( Root & '\*' )
                    call ScanFolder(SubDirectory)
          next SubDirectory
end sub
 
Call ScanFolder('D:\qlikview\ADMIN\00 Config') ;

Add a local “mirror”

After working in praxis, I introduced a local mirror to my qlikview work directory, that only contains the txt, xml, csv, xls files. This is much better, as all qvw and qvd files are not really needed within the source control!

To achieve this, I just wrote a small batch file to copy the relevant folders. The /XD-Parameter allows me to “skip” predefined folders:

robocopy "D:\qlikview" "D:\qvsources" *.xml *.txt *.xls* /e /copy:DT /XD "D:\qlikview\pfrenzel"

 

Categories
Macros and Automatisation QlikView /en Tips n Tricks /en

QlikView User Access and Object Access

Introduction

Security is one of the main topics and issues, deploying data to different users or group of users. Additional you have to seperate between data filters and object/feature visibilities. To achieve a individual “restriction” we split access rules into:

  • ObjectRights (What a user can “see”)
  • AccessRights (Which data a user can “browse”)

Scripts

Here you’ll find all scripts that are needed to implement the functionality.

Macros

Here you’ll find all VBA-Macros that are needed to use the functionality.

Loop Sheets

SUB LoopSheets

 REM ** loop through all sheets **
 REM @Author <Philipp@Frenzel.Net>
 REM beeye.org
 FOR i = 0 TO ActiveDocument.NoOfSheets - 1
     SET ss= ActiveDocument.GetSheet(i)
     varCompare = ss.GetProperties.Name 
     IF LEFT(varCompare,7) <> "Sandbox" THEN
      ReadObjectsAndSetRights varCompare
     END IF
 NEXT

 END SUB

Set Object Visibility

SUB ReadObjectsAndSetRights(SheetName)
 
 REM ** minimize all sheet objects on sheet Main **
 REM Rights by beeye.org
 REM Author Philipp Frenzel
 
 varVisibilityPrefix = "=if(only({<RightRule={'$(=OSUser())"
 varVisibilitySuffix = "'}>} RightAccess)=1,1,0)"
 
 SET s=ActiveDocument.Sheets(SheetName)
 FOR i=0 TO s.NoOfSheetObjects-1
     'the formula for our rights management
     varVisibility = varVisibilityPrefix & s.GetProperties.SheetId & s.SheetObjects(i).getObjectId & varVisibilitySuffix
     SET obj = ActiveDocument.GetSheetObject(s.SheetObjects(i).getObjectId)
     ' type 4 means Tablebox so there you can find the properties over the following context
     IF obj.getObjectType < 5 THEN
 SET objProp = obj.GetProperties
 SET objExpr = objProp.Layout.Frame.Show
 ' toggle invisible property of chart expression
 ' based on value of DEPT field selection
 objExpr.Expression.v = varVisibility
 'objExpr.Expression.v = 1 'uncomment this, if all should be visible
 objExpr.Always = false
 obj.SetProperties objProp
 END IF
 ' type 10 are Pivottables here we set the visibility under the layout tab
 IF (obj.getObjectType > 9 AND obj.getObjectType < 16) OR (obj.getObjectType > 19 AND obj.getObjectType < 24) THEN
 SET objProp = obj.GetProperties
 SET objExpr = objProp.GraphLayout.Frame.Show
 ' toggle invisible property of chart expression
 ' based on value of DEPT field selection
 objExpr.Expression.v = varVisibility
 objExpr.Always = false
 obj.SetProperties objProp
 END IF
 NEXT
 
 END SUB