Not able to schedule deski report with custom Macro

[Former Member]

Hi All

We have repprts which attached macro in 5x. which is working fine on BCA (5.5.7) , after migration into BOXI R3 not able schedule i.e macro is not working .

When we are running the Deski Report with attached Macro , report is working fine . but when we have schedule , macro is not working

Macro code -

Private Sub Document_AfterRefresh()

Dim DirFile As String

Dim DirFile_pitts As String

Dim DirFile_eur As String

Dim DirFile_sng As String

Dim DirFile_cinn As String

Dim DirFile_sng_old As String

Dim File As String

Dim Path As String

Dim Path1 As String

Dim Path_pitts As String

Dim Path_eur As String

Dim Path_cinn As String

Dim Path_sng As String

Dim Path_sng_old As String

Dim Dir1File As String

Dim flag As Boolean

Dim vars As DocumentVariables

Dim var As DocumentVariable

Dim doc As Document

Set doc = ThisDocument

Set vars = doc.DocumentVariables

Dim LoadWeek As String

' Dim Year As String

Dim i As Integer

Dim myarr_week As Variant

Dim arr_size_week As Integer

Dim myarr_yr As Variant

Dim arr_size_yr As Integer

Dim k

For i = 1 To vars.Count

Set var = vars.Item(i)

If var.Name = "Loadweek" Then

arr_size_week = UBound(var.Values(BoAllValues))

myarr_week = var.Values(BoAllValues)

End If

' If var.Name = "Current Year" Then

' arr_size_yr = UBound(var.Values(BoAllValues))

' myarr_yr = var.Values(BoAllValues)

' End If

Next i

For k = 1 To arr_size_week

LoadWeek = myarr_week(k)

'Else

'MsgBox "invalid Value"

' 'MsgBox week

Next k

' For k = 1 To arr_size_yr

' Year = myarr_yr(k)

' 'MsgBox Year

' Next k

'Local Path

'DirFile = "C:\Arvind\REPORTS\dashboard\"

'for BO Xi Test

DirFile = "
swpit101v.global.sabic-ip.com\www_geam-p_BCA\pacificEDW\scm\"

'BOXI Path

Path = DirFile & "GEP_Australia_SCM_Dashboard" & "_" & LoadWeek

'BOXI Flag

flag = Application.ActiveDocument.ActiveReport.ExportAsHtml(Path, 1, 1, 1, 1, 1, 1, 0, 0, 0)

End Sub

Please help to fix this and suggest us

Thanks in advance

Ajay

Call me my # +91-011-9899059037

Edited by: ajay gupta on Feb 10, 2009 12:51 PM

Edited by: ajay gupta on Feb 10, 2009 12:53 PM

Edited by: ajay gupta on Feb 10, 2009 3:26 PM

[Former Member]

Hi Ajay,

Following information might help you to resolve the issue.

Info1:

Stop the service then add -type OUTPROC on the commandline for Deski Job Server

Re-start the Deski Job Server and then schedule the deski doc which has macro in it and check whether or not it executes the macro

Info2:

In XIr2 you can run a Desktop Intelligence report in 3 ways. The report can be run from the DeskI client, it can be refreshed through Infoview, or it can be scheduled (through Infoview or the CMC). It is possible to run VBA macros in all 3 ways of running the reports, but there are some limitations on the macro if it is not run from the DeskI client.

If your macro is not written to work within the limitations that Infoview and the scheduler place on it, the report will run successfully, but the macro will not run. This means that even if your report and macro work perfectly in the DeskI client, they may not work when you run that report from Infoview or when you schedule the report.

Cause

The reason behind there being limitations on the macro when running it through Infoview and running it when scheduled has to do with how the report is processed. In the DeskI client all of the report processing is done by the client application.

When a report is refreshed through Infoview the report is processed by the "Desktop Intelligence Report Server". When a report is scheduled it is processed by the "Desktop Intelligence Job Server". These two servers are separate applications

from the DeskI client, and are designed for server processing, not client processing like the DeskI client.

Resolution

There are a number of things to check when setting up a macro to work through Infoview or scheduled. The first thing is that the macro needs to be triggered to run. This is done by putting a call to the macro in the Document_BeforeRefresh or the Document_AfterRefresh event handler.

The report server and job server are designed to process one report at a time. Through a macro it is possible to open more than one report, and do various processing with those additional reports. Since the report and job servers are not designed to process more than one report, your macro should not open an additional report.

The fact that only one report is processed at a time also affects the syntax you should use when referring to a document or report object. In the DeskI client you can use the ThisDocument object to refer to the report which is running the macro. You can also use the ActiveDocument object to refer to the report that is currently displayed.

Most of the time these two objects will point to the same report. When the report server or job server process a report there would never be the possibility for a different report to be active, so the ActiveDocument object does not exist. This also applies to the ActiveReport object.

Instead of using ActiveDocument your macro code should use the ThisDocument object. Instead of using the ActiveReport object you should use ThisDocument.Reports.Item(1) to refer to this first report tab in a report.

These items are the most common issues that will cause a macro not to run through Infoview or when scheduled. The best way to debug a macro which is not working in either of those places is to include code which will log the macro's progress to a file. This makes it easy to determine exactly which line of code is causing issues so that it can potentially be corrected.

Regards,

Sarbhjeet Kaur

[Former Member]

That was a nice explanation Kaur. I have a different scenario.

The macro functionality is, opens an existing report and supplies the Customer id ( the customer id's are stored in a txt file ) as promt value to the report and refreshes the report and saves the report as pdf in c:\pdf\. This process is repeated for the no of customer id's in the txt file.

I'm using a batch file to run the report which contains macro code. This batch file is added as program object to CMC and scheduled to run. But when scheduled the report's state is in running for a long time and when I try to refresh the History page i'm getting the error "javax.servlet.jsp.JspException: Conversion Error setting value '''' for ''''. "

What might be the problem? Kindly help me.

Edited by: Bhuvan Radhakrishnan on Feb 19, 2009 2:05 PM