topic Re: Open a document on the access point and make selections with vb script in QlikView

Open a document on the access point and make selections with vb script

giakoum — Thu, 06 Feb 2014 11:53:31 GMT

Hi community.

I used the script provided by QlikTech to open a document, browse through objects and sheets, apply selections and close it again. This is used for precaching a document on the server so that the 1st user opens it quickly. It works fine if the document is opened by the desktop client.

Question :

Can it be done so that it opens on the access point instead of the desktop client? qvp call opens the file from the server but still it is opened in the desktop client (Like the open in server desktop functionality). I need to open it in the access point. Something like :

set doc = x.OpenDoc("c:\program files\internet explorer\iexplore.exe qvp://server/dashboard.qvw") but this does not work.

Script :

set x = CreateObject("QlikTech.QlikView")
set doc = x.OpenDoc("qvp://server/dashboard.qvw")

loop_through_objects(doc)
doc.CloseDoc
x.Quit

Sub loop_through_objects(doc)
For i = 0 to doc.NoOfSheets - 1

doc.ActivateSheet i
'Selection loop thru the field Region for example
Set val = doc.Fields("Region").GetOptionalValues
For y = 0 to val.Count - 1

INH = val.Item(y).Text
doc.Fields("Region").select INH
Objects = doc.ActiveSheet.GetSheetObjects
For j = lBound(Objects) to uBound(Objects)

set table = Objects(j)
select case Objects(j).GetObjectType
case 1,4,10,11,12,13,14,16,20,21,27
On Error Resume Next
CellRect = doc.GetApplication().GetEmptyRect()
CellRect.Top = 0
CellRect.Left = 0
CellRect.Width = table.GetColumnCount
CellRect.Height = table.GetRowCount
set CellMatrix = table.GetCells(CellRect)
For RowIter = 0 to CellMatrix.Count - 1
Next
End Select

Next

Next
doc.Fields("Region").clear

Next
End Sub

Re: Open a document on the access point and make selections with vb script

Peter_Cammaert — Thu, 06 Feb 2014 12:55:24 GMT

AccessPoint documents cannot be modified, even when you open them using your Desktop as an AP client.
VBScript macros in many cases do not run on the server. If you use the AJAX-client as default AccessPoint client, then VBS simply won't exist. Even the use of some triggers is restricted.

You may want to consult the QlikView Reference Manual Ch 80 Using Macros in QV Documents on the QV Server for an overview what is/isn't possible in the server environment.

Peter

Re: Open a document on the access point and make selections with vb script

giakoum — Thu, 06 Feb 2014 15:02:50 GMT

Thank you Peter.

this is not a macro within the document I am talking about. This is VB script in a vbs file executed outside the document, just calling in the document as an object.

That is why OpenDoc is used and not ActiveDocument.

Re: Open a document on the access point and make selections with vb script

Peter_Cammaert — Thu, 06 Feb 2014 15:32:09 GMT

So what are you up to? Opening a document in the AccessPoint/QVS and walking sheets and making selections creates a different Document State that applies to... the current user only. If your User1 is someone else, no luck...

A macro running outside the desktop uses COM objects and OCX calls to open and manipulate a QlikView document. Those functions only exist in the IE plugin or in the desktop itself. Seems logical to me that those are the ones being used as QV interfaces.

BTW when you use "qvp://servername/document.qvw" in your OpenDoc call, you are still opening your document in the AccessPoint - or better - from the QlikView Server because even the AccessPoint does nothing else than pass document information from QVS to the client. So that logic is sound.

Or am I still not on the right track?

Peter

Re: Re: Open a document on the access point and make selections with vb script

giakoum — Thu, 06 Feb 2014 15:37:37 GMT

Hi Peter.

If the caching applies to only the current user , then you are right, nothing accomplished. But I believe that the caching applies to all users that will use the document. See page 37 of the attached document.

Re: Open a document on the access point and make selections with vb script

Peter_Cammaert — Fri, 07 Feb 2014 07:50:12 GMT

Great, I learned something today. Didn't know you could manipulate QVS cache management.

You're clearly in a situation where your end-users might gain a lot from such techniques. Although cache management may look pretty unpredictable from the outside as it is highly load-related. The system decides on what to keep in the cache and what to throw out when memory runs low.

Did you do any performance improvement assessments with the working version of your set-up?

I would very much appreciate it if you keep us posted about your advances.

Good luck,

Peter

Re: Open a document on the access point and make selections with vb script

giakoum — Fri, 07 Feb 2014 08:11:26 GMT

Thank you Peter. I will keep you updated. The qvw file itself has been performance tuned to the max. At least, so we believe

Re: Open a document on the access point and make selections with vb script

simondachstr — Fri, 07 Feb 2014 08:49:28 GMT

Peter, unfortunately you can not manipulate QVS cache management. The only thing you can do is the so-called cache warming, making some pre-selections so the aggregated data gets loaded into cache and an then be shared among other users.

Re: Open a document on the access point and make selections with vb script

simondachstr — Fri, 07 Feb 2014 08:50:46 GMT

Have a look at this:

http://community.qlik.com/docs/DOC-5812

JMeter virtually simulates a user logging into the document no the browser and making selections, however automated.

Re: Open a document on the access point and make selections with vb script

marcus_sommer — Fri, 07 Feb 2014 09:45:59 GMT

Perhaps it could be an alternative to try it with a hyperlink to the vbs-file. Within a pivot-table and clicking on the link it worked for me in fat-client without confirmation and in IE and AJAX with prompting about the execution - maybe this could be solved per security settings. Maybe it is possible to put these logic in any actions therewith it could execute without knowing through the users.

- Marcus

Re: Open a document on the access point and make selections with vb script

Peter_Cammaert — Fri, 07 Feb 2014 09:52:22 GMT

How about kicking the VBS-script on the server from a Supporting Task that is chained to the end of the Document Reload. Doh, requires a Publisher and a CAL for the service account.

Peter

Re: Open a document on the access point and make selections with vb script

marcus_sommer — Fri, 07 Feb 2014 10:27:11 GMT

I don't use the publisher which afaik is the only possibility to trigger external events/batches in a chain of reloads. Only with the server it isn't possible - it needs external schedules/triggers (perhaps windows scheduler) and external checks and error handling. You need always the proper access rights within the file-system and a CAL.

- Marcus

Re: Open a document on the access point and make selections with vb script

giakoum — Fri, 07 Feb 2014 13:32:05 GMT

That would be my final solution if nothing else comes up. but I hate the idea of assigning a cal to the service account.

Re: Open a document on the access point and make selections with vb script

giakoum — Fri, 07 Feb 2014 13:33:17 GMT

Hi Martin.

This looks promising. Not sure if I can install anything on the server but lets see.