Monday 13 February 2012

Move your QlikTech ProgramData folder

There are a number of reasons why you would want to move the C:\ProgramData\QlikTech folder to somewhere else.  For example:

-  Disk size issues 

The C: drive too small and the QlikView performance logs, reload logs, etc. are filling it up.

-  Failover

You have a "cold" standby server and it would be much better to have the ProgramData\QlikTech folder on the SAN drive so that when the cold server boots up, it will already have all the settings, schedules, etc. from the old server.

There are ways of moving the ProgramData folder - the supported one from Microsoft (with caveats!) is to do it during the installation of Windows -

This isn't really going to be an option for you if the server is already built by the nice friendly folks in the IS department.  They might also object to you trying to move the whole ProgramData folder anywhere else, just for QlikView.

I have run into this situation a couple of times now and there is a really neat solution - move the QlikTech folder to its new home and then create a symbolic link in the old ProgramData folder.  Everything will still work perfectly!

To do this:

1.  Stop all of the QlikView services.

2.  Move the QlikTech folder from C:\ProgramData to its new location, for example E:\ProgramData.

3.  Run a Command Prompt in Admin mode.  CD to the C:\ProgramData folder and run the following command:

     MKLINK /D QlikTech E:\ProgramData\QlikTech

4.  Restart all of the QlikView services.

All done.  Do the same on the "Cold" server (you will need to bring down the "hot" server first) and create the same symbolic link to the same SAN location and it will work as if it was the "hot" server and no need to ship settings files.

Hopefully you will find this useful.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Printing Reports to PDF using PDFCreator

PDF Creator is a nice tool for generating PDFs and it comes at a very attractive price.  Like other tools of this ilk, it creates a Printer driver which you can print your documents to.  Unlike other drivers (especially at this price point), it gives you additional control on what is printed using a COM interface.  This makes is quite handy to use from VBScript - either inside or outside QlikView.

I am going to give you some starter code here on how to do this from within a QlikView document using a Macro.  You can also do this from a .vbs file that is external to QlikView.  Just note that if you are using a .vbs then you will still need an appropriately licensed copy of QlikView Desktop to run this.

*** All script here is completely unsupported by myself or anyone at QlikTech.  Use at your own risk ***

If you are going to use a .vbs then you will need to create your own ActiveDocument variable.  This is how I do that:

    Dim QV, ActiveDocument

    set Qv = CreateObject("QlikTech.QlikView")

    QV.OpenDoc Document,"",""

    set ActiveDocument = Qv.ActiveDocument

In this case, the variable Document contains the full path to my .qvw.  The 2nd and 3rd parameter are a QVUser and Password - if you have those in Section Access.  Once you have an ActiveDocument object, the code is the same between the Macro and the .vbs.

Usually when I do this, I am going to loop across all the value in a field.  For example, I may have a field called "Seg" and I want to run a report for each value in this field.

I may choose to clear all values first:


Either way, I can call the GetPossibleValues to get a list of all the values in that field:

    Dim FieldName
    FieldName = "Seg"

    set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues

Now, I can loop through the values, select each one in the document and call a function that I have made called Print_PDF for each value:

    Dim i

    for i = 0 to mySelections.Count - 1

        Dim FieldValue

        FieldValue = mySelections.Item(i).text
        ActiveDocument.Fields(FieldName).Select FieldValue

        Print_PDF FieldValue, "My Report", "RP01"

Print_PDF is, essentially, using the reference code from the PDF Creator documentation:

Sub Print_PDF(FieldValue, ReportName, ReportID)

    ' Designed for early bind, set reference to PDFCreator
    Dim pdfjob
    Dim sPDFName
    Dim sPDFPath

    '/// Change the output file name here! ///
    sPDFName = ReportName & " - " & FieldValue
    sPDFPath = "C:\PDFReports"

    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

    With pdfjob

      If .cStart("/NoProcessingAtStartup") = False Then
         If .cStart("/NoProcessingAtStartup", True) = False Then

          Exit Sub

         End if
         .cVisible = True
      End If

      .cOption("UseAutosave") = 1
      .cOption("UseAutosaveDirectory") = 1
      .cOption("AutosaveDirectory") = sPDFPath
      .cOption("AutosaveFilename") = sPDFName
      .cOption("AutosaveFormat") = 0 ' 0 = PDF

    End With

    ' Print the QlikView Report
    ActiveDocument.PrintReport ReportID, "PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
     ActiveDocument.GetApplication.Sleep 20
        ' in VBScript use WScript.Sleep(20)
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
     ActiveDocument.GetApplication.Sleep 20
        ' in VBScript use WScript.Sleep(20)
    Set pdfjob = Nothing

End Sub

The only additional piece here from the PDF Creator documentation is the QlikView call to Print the report:

    ActiveDocument.PrintReport ReportID, "PDFCreator"

I pass the field value and a ReportName value into the function so that I can generate a different PDF file name for each field value.

Last thing is that you will need to enable System Access in the Macro settings.  In a way, this is where .vbs has an advantage.  Because the script is already external, there is no issue with enabling System Access.

Enjoy.  Just remember, this is all unsupported so use at your own risk.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner