VBA in BOb 4

[GBall]

I've had a look at the VBA environment within BOb, just to see what's available, but I can't find any events on which to base any code, which makes it kinda useless ?
If anyone can enlighten me, I'd be grateful.
thanks,
Graham

 

[bensonpa]

Document events are usch as 'AfterRefresh', 'BeforeSave', 'BeforeClose', 'BeforeRefresh' etc. Is this what you were asking? Double-click on 'ThisDocument' in the project window and change the drop-down on the code window from 'General' to 'Document' and you will see these events on the next drop-down.

 

[GBall]

Yes, I did spot those, but I was expecting something more along the lines of MS Access report events.
These don't appear to be amazingly useful.
thanks anyway,
Graham

 

[GBall]

A problem.
Since my foray into the vba area, every time I open the report now, I get the enable/disable macros dialog, even though there aren't any in the report and I didn't save any code in the VBA environment.
Any ideas how I can stop this before I roll it out to users ?
thanks,
Graham

 

[BeckyL]

I am working on a project right now where our intent is to use BOb to run several iterations of a report using an array to change criteria for the report each time it is run. I can make the code work but am having trouble finding a way to make the report accept the changing criteria. I have requested assistance from BOb but have not heard anything. Otherwise the VBA in BOb will be too limited for our purposes. Has anyone else tried anything in BOb with VBA?

TIA
BeckyL

 

[ATP]

BeckyL

I've done this in v4.1 of business objects using the scripting language. Although not quite VBA the principle should be the same.

The report I run has the last condition set to a supplier. The script then reads through a file to run the report for each supplier in the file.

In order to change the criteria in the report I delete the last condition and build a new one with the supplier from the file.

Example
-------
Rem ***************************************************************************
Rem *** Script to produce HTML for the Supplier Spend Report
Rem ***
Rem *** Author A.T.Pearson 15/11/2000
Rem ***
Rem *** File : Supplier_Spend.spt
Rem ***
Rem *** Notes
Rem *** -----
Rem ***
Rem 1 This works on the principle that the report has only 2 conditions
Rem *** the last condition (2nd in this case) is the condition that is deleted
Rem *** and re added
Rem ***
Rem 2 Make sure the input file does not have a blank line as the last record
Rem *** as this will try and create a query for it.
Rem ***************************************************************************

sub main

'********************************************************************************
' Declare
'********************************************************************************
Dim myApp As BOApplication
Dim myDocs As BODocuments
Dim myDoc As BODocument
Dim myReps As BOReports
Dim myRep as BOReport
Dim myDPs As BODataProviders
Dim myDP As BODataProvider
Dim myQueries As BOQueries

'********************************************************************************
' *** Set Environment
'********************************************************************************

Report = "Supplier_Spend"
Param_File = "H:\ProcNet_Parameters\" + Report + ".txt"
Output_Dir = "h:\BO_Report_Drop\Procurement\"
SheetNames = "Detail Spend|Spend By Site|Spend By Category|Ordering Patterns|Spend By Month"

'********************************************************************************
' This line is required for testing, when issued to DAS the report
' will already be open
' Application.Documents.Open(Report)
'********************************************************************************



'********************************************************************************
' Set current Document
'********************************************************************************

Set myApp = Application
Set myDocs = Application.Documents
Set myDoc = Application.Documents.Item(Report)
Set myReps = ActiveDocument.Reports
Set myRep = myReps.Item(1)
Set myDPs = myDoc.DataProviders
Set myDP = myDoc.DataProviders.item(1) ' 1st provider


'********************************************************************************
' Alter Query
'********************************************************************************
'
Dim myQuery1 As BOQuery
Dim myConditions1 As BOConditions
Dim myCondition1 As BOCondition
dim cnt as integer
dim Supps(400) as string
dim HtmlFile as String


'********************************************************************************
' Load Supps from File
'********************************************************************************
cnt = 0

Open Param_File for input as #1

do while not EOF(1)

cnt = cnt + 1
Line input #1,Supps(cnt)

loop

'********************************************************************************
for a = 1 to cnt

'Up load into Memory
myDP.Load

'Define Query
Set myQueries = myDP.Queries

'to AddQueryTechnique.
Set myQuery1 = myQueries.Item(1)

'Define the conditions of the first query.
Set myConditions1 = myQuery1.Conditions

'Remove 2nd condition (Supplier id)
myConditions1.remove(2)

'Add a condition
Set myCondition1 = myConditions1.Add("SUPPLIERS", "Supplier Id","In List",Supps(a),"Constant&quot

' Unload the data provider from memory and refresh it.
myDP.Unload
DoEvents
myDP.Refresh

'Export as HTML
HtmlFile = Output_Dir + mid$(Supps(a), 1,8)
'--------------------------------------------------------------
' Format of HTML command
' ----------------------
'
' objectvar.ExportShhetsAsHtml(fileName, "Sheet1|Sheet2|..."
' , graphs, borders, background
' , foreground, font, freeform, frames
' , autoRefresh, busobjdoc,[HtmlLayout])
'-----------------------------------------------------------------
call myDoc.ExportSheetsAsHtml(HtmlFile,SheetNames,1,1,1,1,1,1,1,0,0,0)

next a
end sub

 

[GBall]

ATP,
very interesting and useful information - thanks.
Just one question, how do you run this ?
Is it triggered by an event or is it run in batch with a command-line parameter ?

Graham

 

[ATP]

Graham,

This is run via the document agent server. It uses the option, when sending a report to the DAS, to run a script against the report.

ATP.

 

[BeckyL]

Thanks, ATP for the excellent example!

We are using BOb 5.0 and there is a Broadcast Agent to which we send reports. Is that the same as Document Agent Server?

 

[ATP]

Yes,

Although I haven't seen Broadcast Agent yet, I do know that it replaces Document Agent Server.

 

[BobWard]

BeckyL.

From reading above the example should do what you want aslong as the spt converts to vba ok. Version 4 of BOb used its own scripting language (spt) wher as version 5 uses vba. Depending on what how many interations of the report you want to run and who they are produced for this solution may suit your purpose, however this method has been found to be intensive to maintain and expand as reports change, new ones are required and user requirments change. You may benefit from looking at info-portal and information delivery tool which complemnts BusinessObjects to provide personlised content. It address issues created from the scripting approach. More details can be found at

http://www.blueedgesoftware.co.uk.

Ive seen this solution impelmented and can add great benefit in cases like yours aswell as simplfying user interation with the html.

 

[Hawkeye97]

Thanks for the tips and examples. I'll start coding up now and see what happens.

Cheers again, nice one

Hawkeye )