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!


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


QlikView – How to Consume a WebService

July 30, 2014

First get the proper webservice URL.

Example: http://wsf.cdyne.com/WeatherWS/Weather.asmx/GetCityForecastByZIP?ZIP=33139

Open QV go the the Script and press the button WebFile.

Paste your WebService URL

image

Select the fields you want

image

And add them to an Object

image

It’s not getting hot today Sad smile


QlikView – Access Data on SharePoint

July 2, 2014

QlikView is a nice BI Tool that is lightweighted and fast.

On top of that the Client version is free of charge and gives you access to the full features. There other limitations and that is you can’t share your work using the free goodie !

But here are some examples on how to access SharePoint Data.

Prerequisites :

1. WebClient Service

You need to make sure that you have the WebClient service installed and running on the server and the user has access to the SharePoint library.

To install the service on the server follow these steps:

Start the Windows Server Manager.

In the tree view, highlight the Features node.

In the details pane, click Add Features.

In the Add Features Wizard, check the Desktop Experience box, and then click Next.

Click Install.

When the Add Features Wizard has finished

Click Close.

Click Yes when promoted to restart the computer.

2. Authentication

Adding the SharePoint site to local intranet sites in the security tab in internet explorer on the machine that QlikView Server runs on.

More info…

As I understand, QlikView uses the IE authentication model – So if you can access your SharePoint site without a sign-on then you can also get list data from a script using owssvr.dll method.

Assuming you are using AD authentication to the SharePoint server.

If your PC is on the same domain as that used for SharePoint authentication, just add your SharePoint site to the “Local Intranet” zone in IE.

If your PC is not in the same domain, you will also need to authenticate and make sure you check the ‘Remember Password’. This will store your credentials in the windows Credentials Manager.

Examples :

1. Accessing SharePoint Document Libraries

Open a new QVW Project and open the Script editor.

Press the EDIT Script ICON and press the WEB FILE button.

image

This will bring up the File Wizard

QlikView accepts 2 different URL formats.

A. WebDav URL

Fill in your WebDav URL to the Doc. Libr.

\\YourSPSite\DavWWWRoot\sites\…\Doc.Libr.Name\FilenName.XLSX

Press next and see the data is available for further processing

image

B. HTTP URL

Use the URL to retrieve the file.

image

Use the Browser Copy shortcut of the file to get the URL.

2. Accessing SharePoint Lists

Same procedure as above to bring up the WEB FILE Wizard.

But using a different URL structure. SharePoint supports 2 interfaces :

A. URL Protocol (Remote RPC)

Syntax : http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUID&View=viewGUID

This one is the better one because you can filter or use a specific SP View

The following parameters can also be used in the protocol:
FileDialogFilterValue to set filters for a view and to return the list of all files of a specified type from a document library according to file name extension (for example, *.doc, *.ppt, or *.xls).
FilterField n to specify the name of a field in the database, where n is an integer that is limited only by the number of fields allowed in the database table or by the length allowed for the URL field.
FilterValue n to specify the string value on which to filter a field, where n is an integer that is limited only by the length allowed for the URL field.
SortField to specify the name of the field on which to sort.
SortDir to indicate an ascending (asc) or descending (desc) sort order.
Using to specify the relative URL of a virtual file used in exporting an item or list, which can be one of the following values: vcard.vcf, event.ics, query.iqy, or query.bqy.

Example :

image

B. Web Services API

Syntax : http://your-sharepoint-server/site/ICT/_vti_bin/listdata.svc/ListName

Keep in mind the oDdata or REST protocol has a few disadvantages (no VIEWS can be selected, and it returns only 1000 records max)

You can uses Filters and Sort syntax though.

 

image

image

On top this QlikView is COM visible, so you can pick this up in your scripting adventures. Surprised smile

Here is an Example on how to access the QV Client using the COM interface

See also here : SharePoint Interface