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.

QlikView vs. BO

Dear Friends,

A Most Awaited Comparison of QlikView with BO is ready for you. While comparing QlikView with BO and Cognos I was really wondering if you have to buy either BO or Cognos which one you will opt for you. Why I am wondering for because it is really hard to find difference between BO and Cognos technically both the architectures are almost same. Then you left yourself on the sales person's experience (I mean now it depend on the sales persons of both products how better they can explain their product and convince you).

I suggest before you decide for a BI ,you must have a look on QlikView and ask your QlikView vendor for a proof of concept (POC), it will help you to explore capabilities of QlikView and a POC will not take more than 3 hrs to make decent no. of reports. Hope this Comparison with BO will help you while deciding a BI tool for you.

I welcome your suggestions.

Happy Reading.

#	Product	Business Objects	Qlik View
A	Product
1	Product	Business Objects	QlikView: World's Fastest Growing BI tool. With 10.3 customers being added every working day.
2	Vendor	SAP	QlikTech
			QlikView is an IN-memory based BI tool which can be deployed in a week's time. It comes at HALF the TCO and delivers TWICE the value compared to any OLAP based BI Solution In QUARTER of the TIME
C	Applications
1	Business intelligence	Yes	QlikView is a single integrated tool for ETL, DW, Multi Dimensional Analysis, Dash boarding, Reporting, as per the Butler group user takes couple of hours training instead of weeks taken by traditional OLAP based tools
2	Multi dimensional view
3	Strategic planning	Yes
4	Budgeting	Yes
5	Forecasting	Yes
6	Consolidation	Yes
7	Score carding	Yes
D	Hardware and Licenses
1	Warehousing is (Mandate/ Optional)	Mandate	NOT REQUIRED, One of the major advantage of using QlikView is, it does not require database which saves money and very well skilled human resource. With no data warehouse required QlikView can be implemented in a couple of weeks saving a lot of TIME, MONEY AND RESOURCES.
2	Server Licenses(Named/CPU)	Named	Named
3	User Licenses (Name/Concurrent/Unlimited)	Named	Named
4	Web Users	Unlimited	User lic
5	If preparatory Database specify (Technical and Methodology)	NO	NO: QlikView uses its integrated Robust ETL to Extract data from multiple data sources and forms a data repository using its patented Technology (AQL: Associative Query Logic) This technology overcomes the efforts and time required to build data warehouse, normalization, schema writing, creation of cubes. And Empowers the customer to be independent of the vendor to create and use analytics
E	Technology
1	Supported database		Any OLEDB, ODBC complains which covers althea database in the world today + FLAT FILE, WEBFILE. TEXT FILE DELIMITED FILE. ALSO WORKS WITH SYBASE
	Microsoft SQL Server	Y	Y
	Oracle	Y	Y
	Pervasive SQL	Y	Y
	IBM DB2	Y	Y
	Informix	Y	Y
	Access	Y	Y
	FoxPro	Y	Y
	Progress	Y	Y
	MySQL	Y	Y
2	Supported networks
	Microsoft	Y	YES
	Linux	Y	Y TO CONNECT TO THE DATABASE
	Unix	Y	Y TO CONNECT TO THE DATABASE
	AIX	Y	Y TO CONNECT TO THE DATABASE
	Novell Netware		Y TO CONNECT TO THE DATABASE
	IBM AS/400 (OS/400)	Y	Y TO CONNECT TO THE DATABASE
3	Development tools used to build application		NO THIRD PARTY DEVELOPMENT TOOL REQUIRED. THE CUSTOMER BECOMES VENDOR INDEPENDENT FOR ANY NEW APPLICATION/ CHANGES REQUIRED
4	Report writer used - which one(s)?	Crystal	NO THIRD PARTY TOOL REQUIRED
5	Data Extract Transform Load (ETL) tools - which one(s)?	All Major ETL Tools (bobj Data Integrator)	NOT REQUIRED: QLIK VIEW HAS ITS OWN ROBUST ETL INTEGRATED AS A QLIKVIEW SUITE. THE ETL IS WIZARD DRIVEN CAN BE LEARNT IN A FEW DAYS AND OPERATED BY CUSTOMER HENCE EMPOWER THEMSLEF TO CREAT OWN APPLICATION AS AND WHEN DEMAND ARISES
6	Cubes supported - which one(s)?	All Major OLAP	IN ADDITION TO ITS OWN DATA REPOSITORY QLIKVIEW SUPPORTS ALL MAJOR OLAP CUBES
7	Existing integration to accounting and ERP systems - which systems?	SAP, PeopleSoft, JDE, SSA Global/BAAN, Portal integration kits	ALL ODBC COMPLAINT DATABASE(all the current database in the market) IRRISPECTIVE OF THEIR FRONT END, SAP USING A SAP CERTIFIED CONNECTOR
8	Existing integration to customer relationship management systems - which systems?	Siebel	BY MEANS OF THE GLOBAL STANDARD ODBC, OLEDB CONNECTIONS (all major crm system)
9	Existing integration to payroll systems - which systems?	All Payroll systems on an ODBC data source.	BY MEANS OF THE WORLD STANDARD ODBC, OLEDB CONNECTIONS( all major payroll system)
10	Existing integration to HRMS systems - which systems?	All Payroll systems on an ODBC data source.	BY MEANS OF THE WORLD STANDARD ODBC, OLEDB CONNECTIONS (all major hr system)
F	Features
1	Analytic functionality	Yes	YES
2	Bubble up exceptions	Yes	This feature is better handled where the exceptions are managed at the lower level and not popped to the upper layer and confuses the user. However all precautions are taken to handle the exceptions efficiently.
3	Drill down	Yes	YES
4	Drill through to source data	Yes	DRILL DOWN TO THE TRANSACTIOANL LEVEL. AS AGAINST TO THE AGREGATED DATA IN CASE OF OLAP BASED APPLICATIONS
5	Ranking	Yes	YES
6	Sorting	Yes	YES
7	Restrict results by date range	Yes	YES
8	Includes dashboard	Yes	YES
9	Time trending (for example, over 12 months)	Yes	YES AS MANY YEARS AS THE DATABASE HOLDS
10	Pareto filtering (automatically groups the categories that amount to less than 20% of a row or column total into "other")	Yes	YES
11	Calculations	Yes	YES
12	Counts	Yes	YES
13	Percentage of total	Yes	YES
14	Allocations	Yes	YES
15	Total and Sub-total	Yes	YES
16	Create OLAP cubes	Yes	NOT REQUIRED
17	Data extraction and transformation tools	Yes	INTEGRATED
18	Incremental extraction	Yes	YES
19	Impact analysis - objects impacted by change	Yes	YES
20	Can schedule process to run overnight	Yes	YES
21	Formatting	Yes	YES
22	By dimension	Yes	YES: N DIMENTION
23	By level	Yes	YES
24	By cell	Yes	YES
25	Zero-row suppression	Yes	ZERO/NULL FIELD SUPPRESSION
26	Conditional formatting by cell, e.g., highlight cells below threshold	Yes	YES
27	Returns text (e.g., if value > 90, returns "excellent")	Yes	YES
28	French version	Yes	UNICODE: CAN BE IMPLEMENTED IN ANY LANGUAGE
29	Screens	Yes	YES
30	Reports	Yes	YES
31	Documentation	Yes	YES
32	Metadata	Yes	YES
33	OLAP functionality	Yes	NOT REQUIRED
34	Skip missing levels - support ragged dimensions	Yes	YES
35	Write back	Yes	NO
36	With audit trail	Yes	YES
37	Includes charting tools	Yes	YES
38	Access multiple cubes at a time	Yes	YES
39	Excel client	Yes	YES
40	Internet Explorer client	Yes	YES
41	Netscape client	Yes	Firefox
42	Geographical representation	Yes	YES
43	Drag and drop	Yes	YES
44	Attach notes to cube	Yes	CUBES NOT REQUIRED SAVES TIME AND MONEY
45	Attach notes to cells	Yes	CELLS NOT REQUIRED SAVES TIME AND MONEY
46	Password security	Yes	YES: UP TO THE FIELD LEVEL( BETTER THAN THE LISTING BELOW)
47	By company	Yes	YES
48	By department or division	Yes	YES
49	By menu	Yes	YES
50	By function (view, add, change, delete)	Yes	YES
51	By dimension	Yes	YES
52	By level	Yes	YES
53	By cell	Yes	YES
54	Respect active directory authentication	Yes	YES: CAN BE INTEGRATED WITH ACTIVE DIRETORY AUTHENTICATION FOR A SINGLE LOG ON PER USER
55	Respect database security	Yes	YES
56	Portal generation that includes HTML reports	Yes	YES
57	Report writer	Yes	NOT REQUIRED: INTEGRATED FEATURE
58	Web-based	Yes	YES
59	Generate cube	Yes	NOT REQUIRED: OVERCOMES CUBE DRAWBACKS
60	Drag and drop	Yes	YES
61	WYSIWYG report design	Yes	YES
62	Technology	Yes	YES (AQL)
63	API-enabled (SDK provided)	Yes	YES
64	Web-based (only need browser at client)	Yes	YES
65	For entire application	Yes	YES
66	Support for .NET web services	Yes	YES
67	Support for Java web services	Yes	YES
68	Transfer data to:	Yes	yes
69	Report writer	Yes	QLIKVIEW IS NOT A REPPORT WRITER HOW EVER IT SUPPORTS GENERTING REPORTS AND THIS FEATURE IS INTEGRATED PART OF QLIKVIEW.
70	Excel	Yes	YES
71	PivotTable	Yes	YES
72	HTML	Yes	YES
73	PDF	Yes	YES
74	PowerPoint	Yes	COPY PASTE (CTRL+C, CTRL+V)
75	XML	Yes	YES
76	Views	Yes	YES
77	Crosstabs	Yes	YES
78	Charts	Yes	YES
79	Both crosstabs and chart at the same time	Yes	YES
80	Work flow	Yes	Open to function with third party tools
81	Alerts can be automatically triggered	Yes	YES IN THE FORM OF POPUPS AND CAN BE USED TO AUTOMATE SENDING OF MAILS, ACTIVATE/RUN OTHER APPLICATIONS
82	Dynamically summarizing data without having to rebuild data?		ANALYSIS AT TRANSACTION LEVEL, WITH DRILL DOWN CAPABILITY FROM THE CONSOLIDATED ANALYSIS
83	Ability to pre-calculate results (aggregate) when they are available, or dynamically generating result from atomic information when necessary		YES
84	Ability to control and design cube aggregations		DOES NOT REQUIRE CUBES WHICH CAN HANDLE 3-10 DIMENTIONS ONLY. QLIKVIEW IS BASED ON AQL THAT CAN HANDLE AS MANY DIMENTIONS AS THE FILEDS AVAILABLE
85	Ability to monitor cube performance and tune the cube aggregations accordingly		DOES NOT REQUIRE CUBES WHICH CAN HANDLE 3-10 DIMENTIONS ONLY. QLIKVIEW IS BASED ON AQL THAT CAN HANDLE AS MANY DIMENTIONS AS THE FILEDS AVAILABLE
86	Can the cube be built using either of ROLAP, MOLAP, HOLAP techniques		DOES NOT REQUIRE CUBES WHICH CAN HANDLE 3-10 DIMENTIONS ONLY. QLIKVIEW IS BASED ON AQL THAT CAN HANDLE AS MANY DIMENTIONS AS THE FILEDS AVAILABLE
87	A hybrid database that stores data in relational form and multidimensional form, as appropriate		QLIKVIEW DOES NOT NEED THIRD PARY DATABASE.
88	Is 'What-if" analysis supported		YES
89	GUI based Data Mining Capabilities		YES
90	Enterprise Platform		YES
	Statistical Analytical Modeling		YES
91	Statistics		YES
92	Data & Text Mining		YES
93	Model Management & Deployment
94	Forecasting		YES
95	Econometrics		YES
96	Quality Improvement
97	Operations Research
98	Data Quality		AQL TAKES BUSINESS INTELEGENCE ONE STEP FURTHER THAN THE TREDITIONAL DATA WAREHOUSE ARCHITECHTURE - OLAP BASED SOLUTIONS
	Initial implementation time		1/10 time than Cognos and BO
	Initial implementation cost		1/6 time to implement than Cognos or BO
	Time to proficiency		Only five day Application development training required. Time to become proficient is in days not in month that is 1/6 than Cognos and BI
	Project Success rate		98 % in Comparison to 38 % in OLAP based BI Software.
	TCO		1/2 than any BI tool
	Value		Twice than Cognos and BO