You have probably used images in your QlikView apps, you may have linked to images hosted on another site. This post looks at a use case and describes how you can use images created on the fly, by web APIs, in your apps.
Images In QlikView
There are many ways that images can be used in QlikView: wallpaper on sheets, backgrounds on text objects, expression based in text objects and in-line in straight tables. The later two require you to construct a link to the file in an expression. A simple example is where you have a table with details of where images are stored, this can then be used to populate images in tables and text objects. In a few projects I have used this technique to good affect showing product images and customer logos in QlikView. Images can also be used as the background of text boxes; for buttons or performance indicators.
Think beyond putting your company logo in the top right corner and images can really enhance your QlikView applications.
Image Based Web APIs
For the un-initiated, a web API (application programming interface) is a mechanism for calling functionally from a server and getting back a result. Often, but not always, the parameters to this API call are sent via the URL. The result may be a set of data, a flag indicating an action has been carried out on the server or an image file. It’s image files generated by APIs that we are most interested in here.
You may well already have an API generated image in some of your apps. If you have ever put a dynamic Google map (or Open Streetmap) into an app then it is the API on that server that you have called.
The Use Case
Recently I was asked by a client to incorporate bar codes for product shipments into their application. These would then be scanned by their bar code readers to log in their production systems when exceptions were identified in QlikView. The way this had been dealt with in previous reporting solutions was to use a bar code font – this font was set on one column of a tabular output. There were two immediate problems I could see with replicating this in QlikView, first you can not set a different font for a single column in a straight table, also you would be reliant on all clients having the correct font installed. A different approach was called for.
I assumed that generating a bar code for a given string would be a relatively common thing to want to do – and therefore someone would have created a web API for this.
Implementing Bar Codes
After trying out a few of the APIs in my browser I settled on one that seemed to have all the features I needed. The site I chose was this one:
The call to the API was through the URL and no authentication was needed – these things are essential to using APIs out of the box (for any other API take a look at the excellent Qlik Web Connectors). The basic parameters for this API are:
Full instructions can be found here: https://www.barcodes4.me/apidocumentation
Once this had been sorted the only thing left was to plumb this into the QlikView app itself. This was simply a case of building the correct URL using string concatenation and turning the expression type to image.
The expression required was this:
='https://www.barcodes4.me/barcode/c39/' & Only([Pallet No]) &
'.png?IsTextDrawn=1&width=360&height=70'The extra parameters simply define the size of the resultant image and the fact we wanted to show the pallet number as well as the bar code.
The settings on the Expressions tab of the Chart Properties look like this, most importantly the Representation set to Image:
The result was a table of shipments with a barcode at the end of each row. A quick test with the customer’s bar code reader showed these bar codes could be scanned straight off the screen.
Incidentally, I found I was able to test also using a free bar code reading app on my smartphone.
Other Web APIs
Now you know how to use web APIs you can go and find out some others. One of the most fully featured visualisation APIs I have find is the Google Image Charts API. This API allows you to pass your data set to the API in the URL and it then renders you a chart on the server and passed it back to you as an image. This image can then be used in a text box or a straight table.
For instance the URL:
Returns the following image file:
The documentation for the Google Image Charts API can be found here, and details on the parameters for Venn Diagrams is here.
This is a quick and simple way of incorporating charts that QlikView does not support natively. Be warned though they won’t be clickable like other Qlikview charts and things like popup text is not supported. If you require that kind of functionality from a non-native chat you need to look to extensions.
In order to build the URL to send to Google to receive back a Venn diagram you will need to construct it using various QlikView expressions, in this case summing the values for each dimension, then summing all the intersections and then concatenating them together to give the number string. You would also need to work out which dimensions were in play in your current selections in order to build the colour and legend lists.
To see this working see the post by Michael Solomovich on Qlik Community here: https://community.qlik.com/thread/116059
Be aware though, Google are deprecating the Chart Image API in favour of their new Chart API. These are HTML 5 based charts, so would need to be implemented via Extensions in QlikView.
Other APIs?
If there are any other web APIs that you use in QlikView that I have not mentioned please let me know in the comments. If you have examples you can share then even better!
Hopefully you will now be able to incorporate functionality in your QlikView apps that would have previously have been impossible.
great post
Nice post Steve. Thanks.
Steve, you got me excited for a minute about google charts API until I realized that it is depreciated by google and will be shut down in 2015 :(
Hi Boris – hopefully someone will (or already has) produced a chart API that returns images that will pick up when Google turns this feature off (just as Feedly picked up when Google Reader expired). Also, the Google notice just says they have stopped developing the API and they will cease supporting it – they may leave it working. We will have to wait and see how things pan out.
For now though, the Google Image Chart API serves as a good demonstration of how APIs work.
Steve, the barcode idea is really cool, have never seen that done before. Although more and more clients are using QV on mobile platforms nowadays–you can’t scan a barcode if you’re viewing the app on your phone/iPad to begin with.
On a slightly unrelated note, Google is just deprecating the API that creates PNGs. It’s still leaving the main Chart Tools API intact as far as I know. You won’t be able to use charts as background images, but you can create extensions that are more interactive. It will definitely be a more involved process, but one with a potentially higher payout. I’ve been toying with the idea of creating some turnkey extensions that would let users do so with relatively little or no backend coding. (sigh) just need to find the time.
Hi Vlad – it would give people the excuse to ask the boss for two devices – to scan their tablet with their phone!
I was thinking with the newer Google Chart API it would be possible to create nice charts using the HTML (and JScript) store that I blogged about a while back: https://www.quickintelligence.co.uk/write-csv-qlikview-store/ . Getting a generic extension for the API would be great – but as you say a fair bit of coding.
appreciation
Hi Steve.
You can try to use the REST services of MS Share point to view Excel charts http://msdn.microsoft.com/en-us/library/office/ff394634(v=office.15).aspx
It could be useful in case you need some data to be compared/viewed as reports/expert assessments and there is no need to strongly integrate them into QlikView data model.
Hi Alexander – I’ve not looked at Office 365 much yet – having always used Google Docs as cloud office platform of choice. Will have to take a look at what I am missing though at some point. Thanks for the tip.