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.

Listing All Variables and Contents

[Author's note:  You can see information about a different method for accomplishing this same thing. Search on: QlikView Maven Utility That Can Extract and Show All of the Variables ]

After last weeks blog posting I got some questions about how you might list out all of the document variables and their contents. It can be a useful thing for reports that depend on variables for their functionality and useful for corporate developers who must document report functionality.

Below is a Microsoft Excel macro that could help you. When you run this macro it will ask you to select a QlikView report file and then it will copy all of the document variables and their contents into the current worksheet. Once the data is in a spreadsheet you can sort it, print it, search it for particular fields or functions, or write it out as a text or tab-delimited file. In a future blog posting I'll show how variables can be loaded into a report from a spreadsheet which, along with the macro below, will give you a method for sharing and maintaining variables.

You can get a copy of the spreadsheet with the macro installed and a button that runs the macro at this website:  [Author's note - old site is not working anymore].  The spreadsheet on the website also includes a button that will list all variables from a currently open QlikView document (sorry about the advertisements on the site - just click on the spreadsheet downloads).
Here's the Excel macro code. You can copy and paste the macro code from this window and put it into your own spreadsheet if you want to build your own.

Sub List_QV_Variables()
'This Excel macro will ask you to select a QlikView Report File and
'then it will put the variable names and contents into your current worksheet
'--Tim Benoit, Nov 2008
Dim oQV, oRpt, oVars, oTempVar, oThisVar, qv_fn

'Get the QlikView report pathname
qv_fn = Application.GetOpenFilename("QlikView Report File (.),*.*", , "Select a QlikView .qvw Report File", False)
If qv_fn = 0 Then
  Exit Sub
  End If

'Write out some heading text
ActiveSheet.Cells(1, 1).Formula = "QlikView Variables List"
ActiveSheet.Cells(2, 1).Formula = "Report pathname:"
ActiveSheet.Cells(2, 2).Formula = qv_fn
ActiveSheet.Cells(3, 1).Formula = "Variable Name"
ActiveSheet.Cells(3, 2).Formula = "Variable Content"

'Open the QlikView report file
Set oQV = CreateObject("QlikTech.QlikView")
Set oRpt = oQV.OpenDoc(qv_fn)
Set oVars = oRpt.GetVariableDescriptions
'Loop through the variables
For i = 0 To oVars.Count - 1
  Set oTempVar = oVars.Item(i)
  varname = Trim(oTempVar.Name)
  Set oThisVar = oRpt.Variables(varname)
  varcontent = oThisVar.GetRawContent
  'Write data into worksheet cells
  ActiveSheet.Cells(i + 4, 1).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 1).Formula = varname
  ActiveSheet.Cells(i + 4, 2).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 2).Formula = varcontent
  Next

'Close QlikView
oRpt.CloseDoc
oQV.Quit
End Sub

Copying Variables From One QlikView Document to Another

Today, one of the guys at work was trying to figure out how to transfer all of the document variables from one QlikView document to another. We discussed some of the things we've done in the past: loading variables and values from a spreadsheet or database either through the loadscript or through macro code. We even have an older application that stores variables in a table with loadscript code and then the document is used as a "binary" in another document's loadscript and the second document turns the table values back into variables. But, none of those options seemed to fit the requirement. One obstacle is that we could not figure out an easy way to export all of the variables and values from a document. You can export the expressions from the Expression Overview but the Variable Overview has no similar function.

With some help from Lars at Brait AB (a reader of this blog, Thanks, Lars!) who suggested a way to loop through the variables, I tested the VBScript below that can transfer variables from one document to another. I created a small text file on my laptop named "tbtest.vbs" and typed this code into it:

' /* Test Moving a variable from one QV document to another */

' /* QlikView Maven, Nov 2008 */
Option Explicit

Dim objQV, objSource, objDest, objSourceVar, objDestVar

Dim objVars, varcontent, objTempVar, varname, i

'initialize

Set objQV=CreateObject("QlikTech.QlikView")

Set objSource=objQV.OpenDoc("C:\QVfolder\Source_rpt.qvw")

Set objDest=objQV.OpenDoc("C:\QVfolder\Dest_rpt.qvw")
set objVars = objSource.GetVariableDescriptions

'Loop through the variables

for i = 0 to objVars.Count - 1
  set objTempVar = objVars.Item(i)

  varname=Trim(objTempVar.Name)

  Set objSourceVar=objSource.Variables(varname)

  varcontent=objSourceVar.GetRawContent

  'display the variable to check on progress if needed

  'msgbox(varname & " = " & varcontent)

  'update the value of variable in Destination document

  Set objDestVar=objDest.Variables(varname)

  If objDestVar is nothing then

    'must need to create variable

    objDest.CreateVariable varname

    Set objDestVar=objDest.Variables(varname)

    End If

  objDestVar.SetContent varcontent,true

next 'end of loop

'save Destination document if desired

objDest.Save

'we're done, close down

objSource.CloseDoc

objDest.CloseDoc
objQV.quit
WScript.Quit

In order to run it, I just double-clicked on the tbtest.vbs file on my desktop. You can see the pathname to the two QlikView document files in the code. This script successfully copied the variables and their values from the source to the destination document.

If you omit the part of the script that opens and updates the destination document you could turn it into a script that lists out all of the report variables and their contents -- something that is not available with the regular variable overview.

Putting a Count in the Chart Title

In my company we write a lot of QlikView reports to validate data. As part of our software projects we must check data to see that it matches assumptions and expectations. We also check the data against any other source available to validate data accuracy. Often, one of the simpler but important things on those reports is a count of things.

For example, a report might include a table box showing ITEM data. Instead of just showing the word ITEM in the table box title, we put a count of the ITEMs in the title. You can change the title on most sheet objects by right-clicking on the object and choosing Properties. The Title is in the General tab. The title might contain this:
=Num(Count(Distinct ITEM),'###,###') & ' ITEMs'

That gives us a count of the number of ITEMs that is formatted with the NUM function to use a comma as a thousands separator. The title then might show up on the chart as:
  1,220 Items

A List Box for the field named Location might contain this in the title:
=Num(Count(Distinct Location),'###,###') & ' Locations'

For another title you might want the number of sales orders in fiscal 2009. You can add an If test with a text expression like this:
=Num(Count(Distinct If(fiscal_yr='2008',order_id)),'###,###') & ' 2009 Orders'

These counts will change depending upon your current selection. The counts don't necessarily need to be in a title. The object title limits you to a single line of text. You can put the counts into a text box and that will allow you to show the information on multiple lines. If you type this into a text box:

=Num(Count(Distinct ITEM),'###,###') & ' ITEMs
' & Num(Count(Distinct Location),'###,###') & ' Locations
' & Num(Count(Distinct SDATE),'###,###') & ' Shipping Dates'

Note that the end-of-line or carriage return is part of the text. The text box will show up on the report saying something like this:
  1,220 Items
  15 Locations
  52 Shipping Dates

We have a report with a table box of SKU data where SKU ("stock-keeping unit") is a combination of item and location although the item and location fields are separate. The title for that table box contains this text expression that counts the number of occurrences of the concatenated item and location field values:
=Num(Count(Distinct ITEM&'@'&LOC),'###,###') & ' SKUs'

We often use charts with a cycle group in the dimension. That allows the report user to easily switch the chart dimensions. The current setting of the cycle group can be used in the report title if it makes sense. So, for a chart where the cycle group is named "Corp Group", you can make it a part of the chart title like this:
='Sales History by ' & GetCurrentField([Corp Group])

and then the chart title will switch between
  Sales History by Customer
  Sales History by Fiscal Month
  Sales History by Region
as the report user clicks to switch dimensions using the cycle group.

For a slightly more complex example of using the cycle group, consider a chart where the dimension is a cycle group named "Corp Group" and we want to include a count of dimension rows in the title. You could make the chart title like this:
='Sales History - ' & Num(Count(Distinct $(cg_label_1)),'###,###,###') & ' ' & GetCurrentField([Corp Group]) & 's'

You must also define a document variable named cg_label_1 that contains the name of the specific cycle group being used in the dimension. The variable would contain this text:
=GetCurrentField([Corp_Group])

now the chart title will automatically switch between text like this as the cycle group changes:
  Sales History - 4,800 Customers
  Sales History - 12 Fiscal Months
  Sales History - 6 Regions