Solved: Creating a VBScript for EDX - Qlik Community

Report Inappropriate Content · ‎2012-12-17

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

fosuzuki · ‎2012-12-20

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

View solution in original post

fosuzuki · ‎2012-12-18

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

Report Inappropriate Content · ‎2012-12-19

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!!!"

TO

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.

Report Inappropriate Content · ‎2012-12-19

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

fosuzuki · ‎2012-12-19

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

Report Inappropriate Content · ‎2012-12-19

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)?

fosuzuki · ‎2012-12-20

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).

Report Inappropriate Content · ‎2012-12-20

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.

Report Inappropriate Content · ‎2012-12-20

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

Report Inappropriate Content · ‎2012-12-20

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.