See a Title Tab When Report Opens

Normally, the active sheet or tab a person sees when they open a QlikView document is the sheet that was active when the document was last saved. So, the easiest way to influence which sheet a person sees when they open the document is just to be careful which sheet is active when the document is saved and closed.

You can also control which sheet or tab a person sees when they open a QlikView report with a small macro. We often do this when a report contains a title tab. The title tab contains information about the report. It would show the report title, of course, and could also show the date the data was loaded, databases used, contact info for the author or support person, security label, etc. It is also a great place for a company logo or other identifying graphics.

Step one is to build the tab you want users to see when they open the report.

Second, create a small macro (Settings->Document Properties...->Macros). For example, if the name of the title tab is Title_Tab then the macro would look like this:

Sub Activate_Title_Tab
'---------------------
ActiveDocument.Sheets("Title_Tab").Activate
End sub

Then, associate the macro with the OnOpen document event trigger and click OK.

Now you have a macro that is executed whenever the document is opened and the macro activates the tab that you want the user to see first. The Activate_Title_Tab macro is also available to be associated with a button or other events or executed by other macros when it is appropriate to switch to a particular sheet or tab.

Loading Variables With A Macro

In order to complete the discussions on the automated loading of QlikView document variables I have to provide an example of loading the variables and values from a file using a QlikView macro. This would be useful for an application that can't load variables as part of the loadscript. If your loadscript needed to have the variables already loaded then you would need to load the variables before beginning the loadscript. Or you might load a group of variables based on something that the report user does to the report, like loading in a set of historical monetary conversion factors in order to do analysis of historical finance data. Here's an example of how it could be done.

This is written as a function but it could also be done as a subroutine. Because this function is part of an application that runs automatically and unattended on a server it has more error-detection than you might ordinarily use. A subroutine that is invoked as a QlikView macro would have to call this function.

Function SET_RPT_VARS()
'----------------------
'Set document variables based on the data in RPT_VARS.csv file.
'File is comma-delimited. First field on each record is variable name.
'Second field is variable value. First record is column labels.
Dim varsfile, varsrec, tempvar, tempvalue, ret, recctr
SET_RPT_VARS="SET_RPT_VARS Error"
recctr=0

On error resume next
Set fso=CreateObject("Scripting.FileSystemObject")
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to create FileSystemObject"
Err.Clear
Exit Function
End If
On error goto 0

If not fso.FileExists("RPT_VARS.csv") then
SET_RPT_VARS="ERROR: RPT_VARS.csv file not found"
Exit Function
End If

On error resume next
Set varsfile=fso.OpenTextFile("RPT_VARS.csv",1,False,0)
If Err.Number <> 0
then SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to OpenTextFile"
Err.Clear
Exit Function
End If
On error goto 0

Do While not varsfile.AtEndofStream 'loop through the file
recctr=recctr+1
On error resume next
varsrec=varsfile.ReadLine
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to ReadLine"
Err.Clear
Exit Function
End If
On error goto 0

If recctr > 1 then
'ignore first record column labels
On error resume next
tempvar=Trim(Left(varsrec,Instr(1,varsrec,",")-1))
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to extract tempvar"
Err.Clear
Exit Function
End If

tempvalue=Trim(Right(varsrec,Len(varsrec)-Instr(1,varsrec,",")))
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to extract tempvalue"
Err.Clear
Exit Function
End If

ret=ActiveDocument.CreateVariable(tempvar)
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to CreateVariable"
Err.Clear
Exit Function
End If

ret=ActiveDocument.GetVariable(tempvar).SetContent(tempvalue,false)
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to SetContent"
Err.Clear
Exit Function
End If
On error goto 0

End If

Loop 'end of loop through file

varsfile.Close
SET_RPT_VARS=FormatNumber(recctr-1,0) + " document variables created from RPT_VARS.CSV"
set fso=nothing
set varsfile=nothing
End Function

QlikView Tutorial

People often come across this blog when they are searching the web for information about how to use QlikView. If you have a working copy of QlikView on your computer then you already have links to excellent information. Start up the QlikView application and look at the start page -- click Resources and you should see something like the picture on the left. Those links will take you to current information about QlikView. There's a lot of basic info but it isn't just for beginners - I never fail to find something new and useful in the QlikTech and QlikCommunity websites.

If you don't have a working copy of QlikView or can't find the start page then follow this link: Click here for QlikView Information

or follow this link to the QlikCommunity: Click here for QlikCommunity

Variables in the .bat script

This series of blog postings about variables has probably been of interest mostly to technical folks concerned with automating QlikView documents. This one is also like that only more so. Here’s some automation wisdom that you won’t find anywhere else:

We have a set of reports where the document variables contain all the file pathnames and database access names. Loading of the reports is handled by Windows .bat scripts. We saw that the .bat scripts also needed to be able to use flexible file pathnames and database access. The variables and their values are stored in a comma-delimited file (a .csv file). The .bat scripts load their variables from the same file that the QlikView documents use to load their variables.

This is a .bat script command that will load DOS variables from a comma-delimited file:

FOR /F "skip=1 tokens=1-2 delims=," %%a IN ('type ..\Report_Control\RPT_VARS.csv') DO set %%a=%%b

That one line reads a comma-delimited text file named RPT_VARS.csv that is stored in a folder named ..\Report_Control
Skip=1 tells it to skip over the first line of the file (because it contains column titles).
The text file is simple. It looks something like this:

Variable Name,Variable Value
RPT_DBNAME,INVT_PROD
RPT_INPUT,E:\CURRENT\DATA\
RPT_OUTPUT,\\AMSERV28\PROJECTS\
RPT_LOG,C:\LOGS\BATCHLOG.TXT

Within the .bat script the variables are used between percent signs. So a line in the script might look like this:

echo %date%,%time% QV Batch started >> %RPT_LOG%

---

One of the reasons we stored all of the pathnames and access parameters in an external file that could be turned into variables is that it simplified the change control or promote to production process. We could develop a QlikView report and the associated scripts on a development server and then easily move the report and scripts as files to the QA server or production server without any changes. The programs would run correctly after being promoted to QA or production because they picked up the appropriate pathnames and database access variables info from the comma-delimited file stored on the new server.

We name a lot of our files with a yyyymmdd datestamp in the filename, for example “20081126_NorthRegion.dat”. It’s easy to create today’s yyyymmdd date in the .bat script as a variable with this script line:
FOR /F "tokens=1-4 delims=/- " %%A in ('date/T') do set TTDATE=%%D%%B%%C

Then it can be used in the .bat script as part of a filename like this:
COPY CURRENT.DAT %TTDATE%_NorthRegion.dat

Loading Variables Via Loadscript

You have two basic alternatives if you want to load document variables from a database or file. You can load them as part of the loadscript as you load data for reporting, or, you can load them through a macro script either within the QlikView document or external to the document.

In order to load variables from a database or file you must first have the variable names and the variable contents defined in the database table or in the file. The file might be a spreadsheet or text file but it must have one column or field defined for the variable names and a second column or field defined for the variable contents.

Within the QlikView loadscript you first load the variable names and contents into a QlikView table. From a database you might code it something like this:

RPT_VARS:
SQL SELECT
VAR_NAME,
VAR_VALUE
FROM REPORT_VARIABLES;

Then insert some code like this to convert the table into document variables:

Let RowCount = NumMax(NoOfRows('RPT_VARS'),0)-1;
For i=0 to '$(RowCount)'
  Let TempVarName = peek('VAR_NAME',$(i),'RPT_VARS');
  Let TempVarValue = peek('VAR_VALUE',$(i),'RPT_VARS');
  Let $(TempVarName) = '$(TempVarValue)';
  next

//If the table is no longer needed it can be dropped
Drop table RPT_VARS;

Note the NumMax function. That is necessary if the RPT_VARS table were ever empty; a null RowCount variable will cause a never-ending loop.

At work we have some reports where all of the expressions and titles and field labels are handled with variables. This allows us to use the same report at several different companies but the appearance and functionality of the report is tailored to local terminology and business practice. Another way to use this concept might allow the same report at a single company to look and work differently when it loads data from different databases that contain different sets of customized variables. So, a standard sales report could look different for the Commercial Division than it does at the Baked Goods Division.