Thursday, February 19, 2009

How to integrate QlikView with Excel or any MS Office products



Most of my customers ask "Is it possible to integrate QlikView reports to Microsoft office Product Suite?". My answer is Yes we can integrate QlikView with MS office products like Excel, Power Point, MS Word , Outlook Express etc.

The easiest way to integrate is using OCX Components. With earlier versions of QlikView it was required to register OCX components at each user machines but with new 8.5.xx version it is not required to register OCX components. I will take excel as an example.

There may be two kind of users, first the person who is having QlikView licenses in his machine and second who don't have QlikView licenses (Thus he cannot use OCX capabilities). First kind of user can easily drag and drop QlikView objects in Excel sheet and he can use dynamic capabilities of reports in 100 % same way as he can do in QlikView itself. But if he needs to send these reports in excel to third party i.e. users of second kind who does not own QlikView licenses then what he need to do? Traditionally one can export data to excel from QlikView but it converts dynamic reports in static one and it is not acceptable everywhere.

What is the solution for above problem??

Solution is writing macro. One can write a macro in QlikView that will integrate QVW file to excel and whatever analysis based on clicks can be mapped directly to excel. Actually that macro can write a parallel program in excel and since data is available in QlikView application a click can push data with filters in excel and that excel can be shared to any one and it would remain dynamic even if user is not owning QlikView licenses.

Same way one can integrate QlikView with MS outlook or with any MS office product.

In next blog I will cover how effectively PDFs can be used to share QlikView reports to n number of users automatically.


I invite my readers comments.

15 comments:

  1. One of my reader of blog has commented on above artical and it is bellow ( I did copy and paste that bellow) Look my comments bellow Tims Comment.

    Hi Sudhir,
    I don't understand what you mean by, "Once can write a macro in QlikView that will integrate QVW file to excel and whatever analysis based on clicks can be mapped directly to excel. Actually that macro can write a parallel program in excel and since data is available in QlikView application a click can push data with filters in excel and that excel can be shared to any one and it would remain dynamic even if user is not owning QlikView licenses".

    I can write a QV macro that will copy values from a QV object like a chart or table object into the cells of an Excel spreadsheet. Is that the sort of thing you are describing? But then, I do not understand the part about Excel "would remain dynamic". Once the Excel spreadsheet file is moved to a computer without QV or the OCX then it can no longer be updated through a QV macro.

    Thanks for discussing a topic that people are very interested about.
    -Tim

    Dear Tim,
    Thanks for your comments. Take it like this. let say I am using QV licence and I wanted to send a report to my client who dont have QV licence with him, and I need to send report in the same way as I am getting in QV. Now question is that is it possible in Qlikview.
    My answer woluld be yes it is possible I can send a report to my Client who dont have QV licence, in the same format as I am getting it in QV. For that I need to write some macro in QlikView that will call Excel and parlaly it will write the same macro in Excel also so the report I am getting in QV is readily available in Excel and I can share it with my client who dont have QV installed in his machine. If you see this featuter is available in BO ( probable they use Exelsius for that But I am not vey much sure how they do it but I saw it working at one of my client place) I tried same with QV and one of my cunsultant dis it successfully. And it can be done by using Macros.

    Hope Now I am clear about what I wanted to deliver. Kindly put you comments on blog it self so my other readers can also see it.
    Regards
    Sudhir

    ReplyDelete
  2. Dear Sudhir, my name is Esteban and I'm really interested in your post. I'm a kind of "QV superuser" in my company but I don't have technical skills to write macros or special developments. How complicated is this for a non-IT person? Could you share an example of this Macro?

    ReplyDelete
  3. Dear Estbane,
    Writinng macro is little technical work but it is not too hard to learn. you can use API guide manual of QlikView to learn macros ( Whne ou install QlikView it automaticaly coppied in your computer). I also ask one of my Qlikview cunsultant to write some expmle for you in same blog. Give me couple of days you will get it .

    ReplyDelete
  4. Hi, Sudhir Kumar Singh
    This very interesting challenge to avoid QV licenzes. I'm very interesting this solution. Could you, please provide more detailed example how to implement.

    Also, I'm interesting on PDF sharing to users. So, will wait you further articles.

    Thanks,
    Ramunas

    ReplyDelete
  5. Dera Ramunas,
    It is not to avoid QlikView licences. But it is to help thos user who is in field and do not require analytics. They can get PDF as well as Excel. Uses oflicences has its own benifit that you can not avoid. In my next blog piost I iwll try to cover some introduction on who to print automatic pdfs.

    ReplyDelete
  6. Hi Sudhir,
    Do you have any idea/thoughts over setting up Qlikview HTTP server along with DMS Authorization.

    ReplyDelete
  7. Hii Gaurav Just wait for few days I am working on a draft to publish on this.. mean while you can get help from QVS refference manual that automatocally coppied to your machine when you install QlikView.

    ReplyDelete
  8. Hi Sudhir,

    For those who would like an Excel Add-in which will take out the work of writing macros, as well as providing navigation buttons (back, forward, clear etc.) for QlikView objects dragged into your sheets - I am working on a QlikView Excel Connector Add-In.

    If you are interested you can download the latest build and keep up with developments from my Qlikster Blog:

    http://www.qlikster.com/post/QlikView-Excel-Connector-Beta2.aspx

    I'd be interested in your thoughts!

    ReplyDelete
  9. If you are looking for an easy way to move data from QlikView to Excel (no macros or programming skills necessary), then check out NPrinting. It allows you to open qlikview documents and move the fields you want into excel templates by dragging and dropping. Generates reports dymanically so that you can reuse the templates when the qlikview document is updated.
    website: www.nprinting.com

    ReplyDelete
  10. Many thanks for the tutorial, this tutorial was very useful for me to master how to integrate qlikview with excel or any ms office products. Thanks a lot

    ReplyDelete
  11. All information on songs and artist is from personal knowledge and Last.
    In fact, Daft Punk makes an appearance in not only Kanye
    West's music video for Stronger but also his Grammy performance. The B-52's - Love Shack This party favorite is a pop classic from the
    90s which combines the B-52's trademark lyrical kookiness and some jangly guitars with a solid beat and some funky decoration.

    Take a look at my page: daft punk random access memories flac free download

    ReplyDelete
  12. Clownaround will be released exclusively for purchase via Masterworks - Broadway.

    Martin Solveig & Dragonette-"Hello" (Dada Life remix). Over-all, machinima is expected to carry
    on growing within its acceptance.

    My blog post: Free Download Top 20 Music

    ReplyDelete
  13. , Is Muscle Maximizer a sham or does it sincerely work.
    These days there are virtually 1000's of guys and females who are seeking to create muscle mass and strip off body body fat in purchase to transform the way their bodies'
    glimpse and really feel. No make a difference what sort of
    fat coaching regimen you.

    Take a look at my page Somanabolic Muscle Maximizer Results

    ReplyDelete
  14. Client-side data access.Can access and analyze Google Analytics data via client browser, simplifying security.Metadata can be imported and linked to attributes.Definitely, what a fantastic blog and revealing posts, I surely will bookmark your site.All the Best!. thanks.qlikview training in hyderabad

    ReplyDelete

Search more on BI on Web

Know the Blogger