How Can I Schedule a Deski Report in Infoview that has a VBA Macro

[Former Member]

Hi experts,

i've made a report that has a vba Macro. I published it in infoview. If I launch it manually (clicking on the refresh button) the macro works normaly. If I schedule it the macro doesn't work.

What can I do?

Is it possible to make a program that simulate the opening and click on the refresh button?

Or i there is another solution?

Here is my Macro:

Private Sub Document_AfterRefresh()

Dim filtervar As Variant

Dim filterText, Filter As Long

Dim DocName, NewName As String

Dim Pathing, TodaysDate As String

Dim folderName As String

TodaysDate = Format(Date, "DDMMYYYY")

Pathing = "
Ktsdwh0\c$\OutputBO\"

DocName = ThisDocument.Name

filterText = "Dpre Cod Produttore"

filtervar = ThisDocument.Evaluate("=<" & filterText & ">", boUniqueValues)

For Each sepval In filtervar

Filter = sepval

For n = 1 To ThisDocument.Reports.Count

Call ThisDocument.Reports(n).AddComplexFilter(filterText, "=<" & filterText & "> = " & Filter)

ThisDocument.Reports(n).ForceCompute

Next n

MkDir (Pathing & Filter & "\")

NewName = Pathing & Filter & "\" & Filter & "-" & TodaysDate & "-" & DocName 'for Prova_Prod.rep

' NewName = Pathing & Filter & "-" & TodaysDate & "-" & DocName 'for Prova_Prod.rep

ThisDocument.ExportAsPDF (NewName) 'to Save as PDF

Next

For n = 1 To ThisDocument.Reports.Count

Call ActiveReport.AddComplexFilter(filterText, "=<" & filterText & "> = <" & filterText & ">")

' To delete a Complex Filter, you set it equal to itself...

ActiveReport.ForceCompute

Next n

End Sub

Please Help!

Best regards

Camillo

[Former Member]

Hi Camillo and Joao,

Could you please try the following solution to resolve the issue.

In this scenario, I'd recommend testing first by adding "-type OUTPROC" to the Desktop Intelligence Report Server and the Desktop Intelligence Job Server command-line.

Settings steps:

1) Open CCM (Central Configuration Manager)

2) Stop both servers, viz, Desktop Intelligence Report Server and the Desktop Intelligence Job Server.

3) Right click on the server (individually) click properties, click inside 'Command' text box

4) At the end of all the commands, add "-type OUTPROC" without quotes for both servers

5) Start the servers

Try scheduling/refreshing the reports from Infoview and see whether you still face same issue.

Regards,

Sarbhjeet Kaur

[Former Member]

Hello, I tested including "-type OUTPROC" but it doesn't work

Regards

[Former Member]

Hello,

I only want to schedule a document and save it refreshed in pdf, xls and rep format in a directory of my PC. In BO 6.5 It was possible in scheduling, but in XI, I didn't discover a way to do that with scheduling, for that I was trying to do that with VBA, but it is possible in another way, I am receptive to another ideas.

Best Regards

[fritzfeltus]

You could write a program object using the Enterprise SDK. Instead of scheduling the deski report with a macro, you would schedule the program object, which refreshes the deski and creates instances in your file system.

Information on our SDKs can be found here:

[Dev Library XI 3|https://www.sdn.sap.com/irj/boc/businessobjects-sdklibrary]

[Dev Library XI R2|http://devlibrary.businessobjects.com/BusinessObjectsXIR2/en/devsuite.htm]

If you have any issues, just log a post here:

[Former Member]

Hi Joao,

Could you please test the following solutions.

1. make sure that Fixpack 2.4 is installed

Macros donu2019t run in any XI R2 SP2 installation below that:

- Open the ccm and look at the versions. Fixpack 2.4 has the following version numbers for the servers mentioned:

BOBJDesktopIntelligenceCacheServer Version: 11.5.8.998

BOBJJobServer_DesktopIntelligence Version: 11.5.8.826

BOBJDesktopIntelligenceReportServer Version: 11.5.8.998

2.. Check that Microsoft Office is installed on the server (or at least u201CVisual Basic for Applicationsu201D from the Microsoft CD)

3.Check that VBA is enabled

VBA can be disabled in general:

http://support.microsoft.com/kb/285884

4. Check that APC62.dll is present

Verify that C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\APC62.dll is present. If not, copy if from a different computer.

5. Reregister APC62.dll

Issue the command: regsvr32 "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\APC62.dll" to re-register the library.

(Test if you can open Word or another Office application and create a macro if possible.)

6. Check users can run and edit VBA code in DESKI

In the CMC, ensure that 'Run VBA code' and 'Edit VBA code' are selected:

- Log on to CMC as Administrator

- Click on Business Objects Enterprise Applications

- Click on Desktop Intelligence

- For a user experiencing this problem, click on Advanced (Net Access)

- Make sure that the options to 'Edit VBA code' and 'Run VBA code' are explicitly granted

- Log in to Desktop Intelligence and check whether you can access the macro.

7. Check account rights for the DESKI servers

In the CCM, check that the DESKI job server and the DESKI report server are running on the local admin account:

- open the CCM and go to DESKI job server and afterwards to DESKI report server

- under "properties", see whether log on as "LocalSystem" is selected

8. Check that the DESKIReportServer can run VBA

In the CMC, check that on the DeskiReportServer the option u201Cenable VBA script runningu201D is ticked

Regards,

Sarbhjeet Kaur

[fritzfeltus]

Once again...

The AfterRefresh event not working on Windows 2003 Server SP2 is A KNOWN ISSUE!!!

Technical Escalation ID: 5000056471

Macros will work fine on Windows 2003 Server without SP2

This issue occurs because of a change in the operating system behaviour after installing Microsoft 2003 Server Service Pack 2.

[clinton_novosad]

Knowing that it does not work on Windows 2003 Server SP2 (Technical Escalation ID: 5000056471) at this point, will it work under Windows 2008 Server? Or does Server 2008 have the same behavior as the 2003 server?

[fritzfeltus]

Windows 2008 is not supported. Check supported platforms here:

http://service.sap.com/bosap-support

[Former Member]

Can someone give me an help on how to do what I was doing with vba in .NET or JSP, and how to apply it on BO?

[Former Member]

Hi Sarbhjeet Kaur

i'm trying to schedule a deski report wich is using a simple macro (duplicates tabs).

i'm facing the same issue, the deski is running fine except in infoview.

I'm using BOE X31 with hotfix 1.2

win 2003 server (64 bits) (sp2 i have to check it)

is it supposed to work with this version ?

thank you

[Former Member]

Hi Philippe,

If you are using Windows 2003 with SP2 then it might be the issue as already logged for BOXIR2.

You can test the issue by using only Windows 2003 without SP2.

In case your operating system is only windows 2003 then following information and code might be helpful for you to resolve the issue.

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.

#Region u201CWeb Form Designer Generated Code "

'This call is required by the Web Form Designer.

<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init

'CODEGEN: This method call is required by the Web Form Designer

'Do not modify it using the code editor.

InitializeComponent()

End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim myInfoStore As InfoStore

Dim myEnterpriseSession As EnterpriseSession

myInfoStore = CType(Session("InfoStore"), InfoStore)

myEnterpriseSession = CType(Session("EnterpriseSession"), EnterpriseSession)

Dim DOCUMENT_NAME As String = "document_name"

Dim query As String = "Select SI_ID, SI_NAME, SI_SUBJECT, " _

& "SI_COMMENTS, SI_OWNER From CI_INFOOBJECTS Where SI_KIND = 'FullClient' " _

& "AND SI_INSTANCE=0 AND SI_NAME='" + DOCUMENT_NAME + "'"

Dim myInfoObjects As InfoObjects = myInfoStore.Query(query)

Dim myInfoObject As InfoObject = myInfoObjects(1)

Dim myDeskiDoc As FullClient = CType(myInfoObject, FullClient)

Dim mySchedulingInfo As SchedulingInfo = myDeskiDoc.SchedulingInfo

mySchedulingInfo.Type = CeScheduleType.ceScheduleTypeOnce

mySchedulingInfo.RightNow = True

Dim myFullClientFormatOptions As FullClientFormatOptions = myDeskiDoc.FullClientFormatOptions

myFullClientFormatOptions.Format = BusinessObjects.Enterprise.Desktop.CeFullClientFormat.ceFullClientFormatFullClient

myInfoStore.Schedule(myInfoObjects)

End Sub

End Class

I hope this will help you.

Regards,

Sarbhjeet Kaur

[Former Member]

Any news on the fixes to this issue?

I have 2 setups where it doesn't work

1) XI R2 SP 2 where it runs on the client but not when scheduled. I know the code is ok because I have run it on an earlier version of XI R2 successfully.

2) XI R3.1 here - I get a Run-time error '20': Invalid Index - perhaps the syntax needs to be different in XI R3.1?

The code is below - anyone got any ideas?

Thanks,

James

Private Sub Document_AfterRefresh()

Dim Doc As Document

Dim Rep As Report

Dim Sup As DocumentVariable

Dim i As Integer

Dim Arr As Variant

Set Doc = ThisDocument

Set Rep = Doc.Reports.Item("Index")

Rep.ExportAsHtml ("I:\BusinessObjects\Tomcat\webapps\ROOT\CSF_PRINTS\Index.htm")

Set Sup = Doc.DocumentVariables.Item("Order and Sequence")

Arr = Sup.Values(boUniqueValues)

For i = 1 To UBound(Arr, 1)

Set Rep = Doc.Reports.Item("CSF form")

Rep.Duplicate

Set Rep = Doc.Reports.Item("CSF form (1)")

If Arr(i) = "" Then Rep.Name = "Blank Order" Else Rep.Name = Arr(i)

Rep.AddComplexFilter "Order and Sequence", "=<Order and Sequence>=" & Chr(34) & Arr(i) & Chr(34)

Rep.ExportAsPDF ("I:\BusinessObjects\Tomcat\webapps\ROOT\CSF_PRINTS\" & Arr(i) & ".pdf")

Rep.Delete

Next

End Sub

[Former Member]

Ok - can confirm that XI R3.1 works fine scheduling macros - the error I got was my failure to recreate the document exactly. Haven't been able to test if the hotfix works on SP 2.

[fritzfeltus]

Hi James,

If you're scheduling macros in BusinessObjects Enterprise XI Release 2, you should be running at least SP4.

Cheers,

Fritz

[fritzfeltus]

What is your operating system? Is it Windows 2003 with SP2?

There is a known issue with scheduling, AfterRefresh and Win2k3 SP2. This is currently with the QA team.

[Former Member]

Hello I Have a similar problem and my operating system is precisely Windows 2003 with SP2.

Any news about this or exist any workaround to this problem ?

Thanks

[fritzfeltus]

This is a known issue, and is currently being investigated by Business Objects.

Technical Escalation ID 5000056471