topic Creating a VBScript for EDX in QlikView

Creating a VBScript for EDX

Mon, 17 Dec 2012 14:50:53 GMT

Hi All,

I am very new to QlickView, I have vast experience in using Business Objects but this is my first assignment with QlikView. We are upgrading to QV11 and one of the hurdles I am facing is to update the report "Reload" button which executes a "Task" in QV10. Apparently from reading blogs and community articles I have realized that there are a few .NET C# based resolutions; however in my case we don't want to use that route. So any pointers or any guide with this would be really helpful.

Regards,

Nitin

Re: Creating a VBScript for EDX

fosuzuki — Tue, 18 Dec 2012 17:50:56 GMT

Hi Nitin,

I've experienced your problem. The QV10 VB macro script to trigger an EDX task (via http post) doesn't work anymore in QV11.

Instead, you should use a different macro script. These links really helped me migrating the VB code:

Qlikview 11 EDX http://community.qlik.com/docs/DOC-2650

Qlikview 11 EDX Using EDX in QlikView 11 http://community.qlik.com/thread/47353

The basic idea is that you have to use an .EXE file to help you trigger the EDX task.

Let me know if you need further help.

Hope this helps you

Fernando

Re: Creating a VBScript for EDX

Wed, 19 Dec 2012 14:34:39 GMT

Thanks Fernando,

What if I have to execute the .bat file from a button that I create on a qvw file. I have copied the exe on my QlikView Server and I am writing a small Macro to execute the exe. I keep running into an error. I have tried to execute the EXE in command Line from the server and the task was executing perfectly fine. The following is the code I am using to execute exe on the server (which should trigger the task):

sub QmsEdxCmd

dim strTaskName

'dim strTaskPassword

dim strServer

dim strCommand

'strTaskName= ActiveDocument.Evaluate("vTask")

strTaskName= ActiveDocument.Variables("vTask").GetContent.String

'MsgBox(strTaskName)

'strTaskPassword= ActiveDocument.Variables("vPassword").GetContent.String

strServer = "http://<servername>:4799/QMS/Service"

strCommand = "C:\ProgramData\QlikTech\QMSEDX\qmsedx.exe -task="&strTaskName&" -qms="&strServer

'MsgBox(strCommand)

'-password="&strTaskPassword"

Set WshShell = CreateObject("WScript.Shell")

'WshShell.Run(strCommand),0,false

WshShell.Run "cmd /K C:\ProgramData\QlikTech\QMSEDX\QMSEDX.exe -task=TASK_APIGuide"

WshShell.Echo "Task Executed!!!"

end sub

Following is the error message that I get:

Macro Parse Failed. Functionality was lost

QmsEdxCmd

Error: Permission Denied

I have checked my permissions and made sure that I am in the servers Admin Group as well as in the QlikView EDX group.

If I change the last part of the code from

Set WshShell = CreateObject("WScript.Shell")

'WshShell.Run(strCommand),0,false

WshShell.Run "cmd /K C:\ProgramData\QlikTech\QMSEDX\QMSEDX.exe -task=TASK_APIGuide"

WshShell.Echo "Task Executed!!!"

Set WshShell = CreateObject("WScript.Shell")

WshShell.Run(strCommand,0,false)

WshShell.Echo "Task Executed!!!"

I get the following error message:

Macro Parse Failed. Functionality was lost

QmsEdxCmd

Error:

(Nothing mentioned in the Error)

I am not sure what wrong am I doing but seems like I am missing a very critical piece of the puzzle. Any help would be appreciated on this.

Re: Creating a VBScript for EDX

Wed, 19 Dec 2012 14:35:35 GMT

So in other words I am not able to execute the .EXE from my Macro

Re: Creating a VBScript for EDX

fosuzuki — Wed, 19 Dec 2012 16:01:25 GMT

In the WshShell.Run function, are you also indicating the task password? The above code doesn't, and it is a mandatory parameter..

Re: Creating a VBScript for EDX

Wed, 19 Dec 2012 23:05:42 GMT

Okk I did some research and found out that the Password is not a mandatory field; however just to cover that base I changed my code to:

sub QmsEdxCmd

dim strTaskName

dim strTaskPassword

dim strServer

dim strCommand

'strTaskName= ActiveDocument.Evaluate("vTask")

strTaskName= ActiveDocument.Variables("vTask").GetContent.String

MsgBox(strTaskName)

strTaskPassword= ActiveDocument.Variables("vPassword").GetContent.String

MsgBox(strTaskPassword)

strServer = "http://win-q5gscfalk54:4799/QMS/Service"

strCommand = "\\win-q5gscfalk54\C:\ProgramData\QlikTech\QMSEDX\qmsedx.exe -task="&strTaskName&" -qms="&strServer&" -password="&strTaskPassword

MsgBox(strCommand)

'-password="&strTaskPassword"

Set WshShell = CreateObject("WScript.Shell")

MsgBox("I am here")

WshShell.Run(strCommand),0,false

'WshShell.Run "cmd /K \\win-q5gscfalk54\C:\ProgramData\QlikTech\QMSEDX\qmsedx.exe -task=TASK_APIGuide"

MsgBox("Task Executed!!!")

end sub

And still not able to execute it. I am trying to run it from a button in a qvw file through Access Point using IE plugin, I want to understand how would this code execute? Would it be on server side or on the client machine(that is mine)?

Re: Creating a VBScript for EDX

fosuzuki — Thu, 20 Dec 2012 10:54:44 GMT

I'm pretty sure I've seen the mandatory pwd somewhere but couldn't find it... but you're right, I've taken a look at the server reference manual and it says it is an optional parameter. Maybe I'm crazy, took much work...

If you're using IE Plugin, the VBScript macro will run on the client machine. So the qmsedx.exe, which is located in the server, has to be in a shared folder accessible by the clients that will run the edx. And the macro must reference this shared folder (for example \\server\sharedfoldername\qmsedx.exe).

If you're using Ajax, the macro will run on the server side (i'm not 100% sure, maybe someone can confirm this...). So the code will reference the local path to the file (for example c:\ProgramData\QlikTech\QMSEDX\qmsedx.exe).

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 14:03:06 GMT

The code you're executing is probably running locally and should run the .exe on the server.

You mentioned you were trying to do this through the access point, can you run the script if you open the QVW in your client and click the button there?

I'm sort of in a same boat as you. Except I'm only using a .bat file on the server with the .exe. I have an external system that is going to execute the .bat file after a batch job is done and the .bat file will kick off a specific QVD refresh in Publisher.

At the moment, I am getting an access denied problem as well. I think this is because the service account for the external system that will run the .bat file is not in the admin/edx group. I will try this today and get back to you.

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 14:48:57 GMT

Hi Ethan,

Thanks for replying to me, I tried running the exe from my local machine using the QV Client; however I realized that while doing so we have to pass a -qms with server API details eg(http://<servername>:4799/QMS/Service) & this would try to invoke a connection using "http protocol" which is not allowed in QV11. So the exe will not run locally from a client machine.

Let me know if you don't agree with my understanding on this and feel free to correct me. I have tried to run the following command:

C:\QMSEDX>qmsedx.exe -task=TASK_Name -qms=http://<servername>:4799/QMS/Service

Thanks,

Nitin

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 15:19:42 GMT

I'm not trying to run anything from within a QVW, so your situation may be different.

I currently have the .exe file on the Qlikview server, as well as the .bat file. Here is the script from the .bat file.

\\<servername>\qliktech\SourceDocuments\TRIGGERS\QMSEDX.exe>qmsedx

-qms="http://<servername>:4799/QMS/SERVICE" -task=EDX_QVD_TEST -password=@123

Make sure that the location of the QMSEDX is correct based on where/what you're running the .bat file from.

This works when running it locally on the server. It does not work yet when using the external system. As I mentioned before, this could be ebcause the service account of my external system needs to be put into the qlikview admin/EDX group, which Im working on getting right now.

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 15:31:42 GMT

Okk, now I understand your Use Case. In my case we want to trigger a Task from a report and that task can trigger a .bat file. That's the reason I have kept my QMSEDX.exe in the server and trying to execute it from a report. One thing that I have learned is that probably the location of the my file is not correct. Let me put that under Source Documents.

I see you want to trigger a Task that is defined in the .bat file. How (what mechanism) are you planning to trigger the .bat file?

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 15:41:52 GMT

I am going to use an external scheduling system that can be used to schedule any sort of job, UNIX, java, etc.

After our batch jobs are done early in the morning, the external scheduler will send a signal to the .bat file to execute it, thus kicking off the Qlikview Publisher job associated with the QVD's that contain data from the batch jobs.

My main purpose behind this isnt to refresh QVW's but to sync QVD refreshes off of batch jobs.

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 15:45:41 GMT

You may want to approach this differently. I believe you may be able to just specify the name of the QVW you want to refresh in this situation. You don't need to involve the scheduler at all.

Just try the QVW name and no password. See if that works.

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 15:45:57 GMT

I got it... Our main purpose here is to have a refresh on Demand kind of feature, so a user can refresh a document on its own willor execute a task on its own will. I am not too sure why QlikView has made this so complicated.

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 18:55:24 GMT

Did just the QVW name work for you?

Come the time this is resolved, please make sure you mark correct/helpful answers as I've seen questions about EDX happening daily on the community. It will help everyone else out.

Re: Creating a VBScript for EDX

Thu, 20 Dec 2012 18:58:15 GMT

No it didn't work. I am running out of options and I think I might have to go the API route, writing one in .NET seems to be the only possible way to me.

Re: Creating a VBScript for EDX

fosuzuki — Fri, 21 Dec 2012 00:28:42 GMT

Hi Nitin,

I used the EDX in a QVW's on-demand reload by a click in a button in the same QVW's interface. This is what you also need right? It seems that you are almost there, don't give up... I also almost gave up trying to make it work..

Here is the code I got working in my qvw (it seems that yours is ok...):

function RequestEDX(sEDXTask)

dim strServer

dim strTaskName

dim strCommand

dim strReturnValue

strServer = "http://servername:4799/QMS/Service"

strTaskName = sEDXTask

strCommand = "\\servername\QMSEDX_CommandLine\qmsedx.exe" & _

" -qms=" & chr(34) & strServer & chr(34) & _

" -task=" & chr(34) & strTaskName & chr(34) & _

" -password=xxxxx" & _

" -timeout=600"

Set WshShell = CreateObject("WScript.Shell")

strReturnValue = WshShell.Run (strCommand, 0, true)

RequestEDX = strReturnValue

end function

Oh, do you have the QMSEDX.exe.config file in the same folder as the QMSEDX.exe file? This is important. This file comes in the same package of the exe file. You have to edit this QMSEDX.exe.config file and insert your server's name:

<endpoint address="http://yourservername:4799/QMS/Service" binding="basicHttpBinding"

bindingConfiguration="BasicHttpBinding_IQMS" contract="QMSAPI.IQMS"

name="BasicHttpBinding_IQMS"/>

<endpoint address="http://yourservername:4799/ANY/Service" binding="basicHttpBinding"

bindingConfiguration="BasicHttpBinding_IQTService" contract="QMSAPI.IQTService"

name="BasicHttpBinding_IQTService" />

</client>

These are the steps I'd recommend you doing/checking:

1. Ensure that the EDX trigger is working locally in the server. It seems that you already got this right?

2. Share the folder containing the QMSEDX.exe file, granting permission to the users that will have this EDX reload mechanism

3. In the client machine, try to map the the server's shared folder to check if it is accessible by the current user

4. Create a .bat file with the command line, referencing the QMSEDX.exe file in the server's shared folder, run it in the client machine, and check if the task was triggered

5. In the QVW, go to the macro module and select the 'System Access' options.

6. In the server, go to System > Setup > QVServer > Security > Allow unsafe macro execution on server

7. In the client's IE, go to Tools > Internet Options > Security > Local Intranet > Sites > Advanced > Add the website to the zone > Insert your server's path (http://servername/qlikview).

8. Clear the Browser's history

9. When navigating in the QVW with IE, type CTRL+SHIFT+M and select the System Access option.

Well, I'll wait for your feedback and if it still not works, I'll try to remember if I missed something...

Fernando

Re: Creating a VBScript for EDX

Thu, 27 Dec 2012 18:30:14 GMT

Hi Fernando,

Thanks for the encouraging words and sharing your expertise over this matter. I tried everything that you mentioned and was still not able to get it running; hence I started to peel off all the layers involved in the entire solution and realized that the port 4799 needs to be opened and listening, in my case although the port was open on the server, it was not listening any requests made to it. Hence I had to enable port 4799 to be able to receive and listen to any new requests/calls made to it.

Once I was able to do "telnet" from my machine on port 4799 on my server, I was successfully able to execute QMSEDX.exe from my PC

Nitin

Re: Creating a VBScript for EDX

fosuzuki — Wed, 02 Jan 2013 10:48:30 GMT

Great Nitin! Glad I could help you, though you fund the solution by yourself...

Regards

Fernando

Re: Creating a VBScript for EDX

Fri, 04 Jan 2013 06:22:28 GMT

Hi,

I too am trying something with manual reload, with partial success. My problem statement is related to the next step after reload.

Possibly, someone could help me out here.

This is what I want to achieve: Trigger reload on button click on server. After reload finishes, a msgbox should appear indicating status, and also qvw document should be refreshed , so that the effect is immediately visible, without the user having to do a manual refresh.

This is how I went about solving the first issue: 'reload on button click'

I use qlikview version 9, which does not have qmsedx.exe..,so using edx is slightly different, I guess.

But, it didn't work and another method gave me the results.

So, this is what finally worked for me:

I wrote a macro:

Sub External2

Set WshShell = CreateObject("WScript.Shell")

Command="D:\testserver.bat"

status=WshShell.Run(Command,0,1)

if(status=0) then

WshShell.Popup "Reload Completed"

end if

ActiveDocument.SetClearState

End Sub

where the content of server.bat is

"C:\Program Files\QlikView\Publisher\Distribution Service\QlikviewDistributionService.exe" -r="C:\QlikViewMount\RN60-ConsolidatedCostofProgrammingReport.qvw"

My problem is in IE,while calling the macro on button click , the server times out(though reload happens and I have to open the doc again)..I believe it is becaue of the run command WshShell.Run(Command,0,1)

if I give msgbox "Reload Completed" instead of WshShell.Popup , the reload happens and server does not timeout, but, the msgbox is not displayed and also

ActiveDocument.SetClearState is not executed and I have to refresh the document manually to see the changes.

I tried Wscript.Sleep method, but qlikview gave me 'Wscript object not found' error.

Is there any way I can solve this?

Thanks and Regards,

Anju