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.

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.