How to use QlikView to get MS Azure – Office365 BI Reports

February 3, 2016

QlikView Desktop is one of the leading BI In Memory Visualization Tools.

As I have shown in previous posts, this contains a VBScript host engine. And as well the Desktop Client is fully COM Compatible.

Giving these 2 nice bonus points for us scripters Smile

Let see how to use QlikView to provide BI Insights in your MS Azure and Office365 environment.

In the Office365 Admin portal, you can get nice statistics to analyze issues and performance if needed.

image

But QlikView can serve you better ! Because it is all centralized in your dashboard that is fully customizable Winking smile

image

Since it is all in memory, it is lightning fast to do your analysis. If you schedule the QVW you have it all ready each morning.

I built this proof of concept, and will be gradually ad more statistics.

See here for more info on Office365 Reporting web service

Happy Scripting !


QlikView – Access Data from SSRS

March 3, 2015

Since QlikView can’t access certain data sources like MS Analysis Services (SSAS) or other exotic data sources (SAP NetWeaver BI, Hyperion, TERADATA) natively.

We can fall back on the perfect middleware for this being MS SQL Reporting services

image

The approach is a simple as can be. Setup an SSRS server (can even be the MS SQL Express (Free) Edition & SSRS add-on)

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.

Next develop your SSRS reports (which can handle multi data sources in 1 report Smile)

image

Like for example a SharePoint List combined with an Oracle database, or anything else.

Simular to PowerPivot that can access an SSRS Data Source. We can do the same with QlikView.

Use a Web File connection as Data Source

image

Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report

image

If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile

image

That’s it, now you are ready to build your QlikView GUI

Once you know this technique you can as well use this to access an SSRS in the MS Azure cloud.Winking smile

Enjoy!


Automating QlikView – using the Object Model

December 3, 2014

When starting to automate the QlikView client you can use AutoIt or Powershell as a client application to run against local or remote server documents

image

This QlikView application is a guide to the QlikView 11 Automation Interface and the QlikViewOCX API.

The Automation Interface part is intended to serve as an interactive guide to the QlikView Automation API.
        – The Object Model sheet provides a simplified model of the API that shows the Class hierarchy.
     – The Members sheet contains information on all classes and members in the Automation interface.
     – The Examples sheet offers VBscript code examples for a specified class and member.
     – The Help sheet contains some explanations on some of the sheet objects in the main sheet and their contents.

The OCX API part is intended to serve as an interactive complement to the QlikViewOCX SDK documentation, which is available as PDF on the QlikViewOCX SDK CD.
     – The sheet contains information on all QlikViewOCX specific API members with code examples in VB, C# and VC++.

But what you need is of course knowledge of the QV Object model

image

You can get this COM API documentation here : http://community.qlik.com/docs/DOC-2640

In this nice QVW file you can find lot’s in interesting information, Help AND +1800 Examples.

image

image

image

This is how they pulled it together

image

Here is an Example using AutoIT

Enjoy !


QlikView – A BI Reporting Tool for MS SQL CE

October 6, 2014

There are numerous tools on the net which will help you to access a MS SQL CE  database.

Even PowerShell can do the job, but it is not the same as have a full blown BI Reporting Tool under you fingertips.

So let’s look at QlikView to be able to access the .sdf database.

I used the Northwind.sdf example database

When creating a new report you need to select the data source for a Compact Ed. Database.

But as you can see it it not available in the list Sad smile

image

No worries.

Just type in the connection string yourself like this.

OLEDB CONNECT32 TO [Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=”C:\Northwind.sdf”];

and complete with the SQL statement

Customers:
LOAD *;
SQL SELECT * FROM Customers;

Orders:
LOAD *;
SQL SELECT [Order ID],LOWER(Freight) as Freight, [Customer ID] FROM Orders;

image

Refresh and you are done.

PS: If it does not work make sure you have installed the MS SQL CE framework in your PC.

Enjoy!


QlikView – How to run MDX queries against OLAP Cubes

September 29, 2014

QlikView does not support natively accessing OLAP Cubes running MDX Queries.

But with a little creativity you can get it going.

1. First of all I setup an IcCube OLAP In Memory Cube to run the MDX against in the IDE.

image

IcCube supports the XML/A protocol natively Smile

Of course you can use your existing SSAS cube(s) as well as a data source, if you have setup an XML/A endpoint config.

I just used IcCube to get a quick testing environment.

2. Next I have used MS SQL as a middleware to grab the OLAP Cube data.

This MS SQL Express (Free) Edition is just there to connect to from the QV client.

3. On the QV client I made an ODBC connection to the MS SQL Express.

Next I run the OPENROWSET Query against the OLAP Cube using XML/A protocol.

image

As you can see the OPENROWSET command is executed locally on the MS SQL Server.

See : DATA SOURCE=http://localhost:8282/icCube/xmla?msrs

This makes it super easy to access the CUBE data.

Put in there your MDX Query and go.

4. When finished, reload and and look at the result.

image

This is realy fun stuff you don’t learn at school Winking smile

Enjoy !


Creating Macro’s in QlikView – using VBScript

September 29, 2014

Qlikview is heaven for scripters like us Smile

It has an API that can be accessed by any COM compatible client.

And on the other hand it supports the VBScript language for writing Macro’s.

This will get you started.

First add the Macro to QV => Tools -> EDIT Module

image

Go to => Settings -> Document Properties -> Triggers

clip_image002

Add your Macro Function

clip_image004

Open the document again and test it.

clip_image006

See help file to know where you can trigger macro’s. There are several objects to which you can attach Macros

This is a Button example

clip_image008

clip_image010

Now it’s up to your imagination what you are going to automate in QlikView.

We could also call PowerShell scripts if you like.

If you are interested stay tuned Winking smile


AutoIT Exporting QlikView data to Excel

July 31, 2014

As I showed before QV has a COM interface.

So we can use it in our AutoIT scripting event to extract data from the QV client (or any other object !) and save it to Excel.

It basically means you can manipulate the data in the QV Document and export to Excel or upload to SharePoint is you like.

Even the graphs are exportable.

Here we go for a simple example.

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$Qview = ObjCreate("QlikTech.QlikView")

if IsObj($Qview) Then
	MsgBox(0,"",$Qview.OSName & " " &$Qview.OSVersion)
Else
	MsgBox(0,"","NOK")

EndIf


$Qview.MsgBox("QlikView Messagebox Qv_Version " & $Qview.QvVersion )

$Qview.OpenDoc("C:\_\QlikView\SALES.qvw")

WinSetState("QlikView", "", @SW_MAXIMIZE)

$ActiveDoc = $Qview.ActiveDocument

; INSERT HERE and start every call with "QvDoc."
Local $Sheet, $obj, $w, $h

$Sheet = $ActiveDoc.ActivateSheet("DataSheet")

; Right CLick on the object to see the Object ID, don't use the Sheet ID !
$obj = $ActiveDoc.GetSheetObject("TB02")

$w = $obj.GetColumnCount
$h= $obj.GetRowCount

If $h >=  1 Then
	; Export the full object
	ConsoleWrite( $w  & " " & $h & @CRLF)
	$obj.ExportBiff(@ScriptDir & "\QV_Export_Test.xls") 
EndIf

; Get the distinct ItemGroup Field Values
$Values = $ActiveDoc.Fields("ItemGroup").GetPossibleValues 

for $i=0 to $Values.Count-1
  $curVal = $Values.Item($i).Text
  ;Select is like a click selection in the QV Client
  $ActiveDoc.Fields("ItemGroup").Select ($curVal) 
  ; Exports a file for each ItemGroup Field Distinct Selection, see above
  $obj.ExportBiff(@ScriptDir & "\QV_Export_Test" & $curVal &".xls") 
  ConsoleWrite($curVal & @CRLF)
next


ConsoleWrite(@ScriptDir & "\QV_Export_Test.xls" & @CRLF)

$Sheet.FitZoomToWindow

ConsoleWrite($ActiveDoc.GetVariable("MyVarTest") & @CRLF)
ConsoleWrite($ActiveDoc.GetPathName & @CRLF)

;$Qview.Quit


Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
	"err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
	"err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
	"err.number is: "         & @TAB & $HexNumber              & @CRLF & _
	"err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
	"err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
	"err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
	"err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
	"err.helpcontext is: "    & @TAB & $oMyError.helpcontext)
   SetError(1)
Endfunc

Nice to see such a seamless cooperation. A rather new BI technology supporting an old COM interface.

This make me happy Smile