Export to Excel Macro (Businss Object 5.1.1)

Some times we need to work with old versions like I was working with BO 5.1.1 and I was missing export to Excel functionality. I created below macro to export BO report to Excel.

This Macro is created in Excel. So Copy this macro in Excel and add Business Object references before running the macro. Excel and Business objects should be installed in same machine.

Sub ExportToExcel()
On Error Resume Next

Dim app As New busobj.Application
Dim doc As Document
Dim Temptxtfilename As String
Dim Textfilename As String
Dim Counter As Integer
Dim myReports As Reports
Dim ws As Worksheet

'It will ask for temp file name. This will be used for exporting BO report to textfile
Temptxtfilename = Application.GetSaveAsFilename
Textfilename = Mid(Temptxtfilename, 1, InStr(1, Temptxtfilename, ".", vbTextCompare) - 1)

'Now we will open Business Objects, It will ask you username and password
app.Visible = True
app.Interactive = True
app.LoginAs

'Now It will ask you to open the Business Objects document which you want to export in Excel
Set doc = app.Documents.Open

For Counter = doc.Reports.Count To 1 Step -1

'Export Individual report to text file
Temptxtfilename = Textfilename + "(" + doc.Reports.Item(Counter).Name + ").txt"
doc.Reports.Item(Counter).ExportAsText Temptxtfilename

'Add a worksheet in existing Excel
Set ws = ActiveWorkbook.Worksheets.Add
ws.Name = doc.Reports.Item(Counter).Name

'Import data from text to excel
With ws.QueryTables.Add(Connection:= _
"TEXT;" + Temptxtfilename, Destination:=Range( _
"A1"))
.Name = doc.Reports.Item(Counter).Name
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next

'Closing of document and Business Objects application
doc.Close
app.Quit

'Excel is still open so you can save it

End Sub

Factors Affecting Business Object Report Refresh

Following are the few factors affecting Business Object Report Refresh
- Network path
- Location of Server and workstation (Geographical locations)
- Server Configurations.
- Database Design
- Indexes
- Views
- Connection Driver
- Number of Users
- Parallel users
- Number of Joins at Universe level
- Predefined functions and aggregations.
- SQL Query.
- Number of rows in the result.
- Report level variables
- Number of data providers
- Cross-tab Reports


The following are the few Input required for improving Business Object report refresh time
- Good Network Bandwidth
- Fast Network path
- Small distance between all the components like Client, Database and Webi.
- Good server configuration for Web, Application and database servers.
- Good Database design
- Indexes according to reports.
- Views according to reports.
- Native drivers for database connections.
- Number of parallel users.
- Array Fetch size

The following are the areas where we can improve

- The fields used in joins are indexed
- Views are to be created for complex joins and complex calculations.
- Native Drivers for database connections should be used
- Avoid outer joins
- The string manipulations and calculations may be done on database load.
- Trim off blank spaces
- Make User/Group structure as flat as possible.
- Use proper User rights according to available resources.
- Array fetch size should be according to report requirements.
- Aggregation reduces the number of rows returned by queries.
- Use of predefined conditions.
- Avoid multiple data providers.
- Create report specific objects and conditions
- Use Query Optimization technique.
- Eliminate overhead objects
- Allow duplicate rows.
- Replace formula with variables in reports.
- Use cross-tabs reports instead of tables with a lot of columns defined by variables.
- Avoid Grouping variables
- Custom LOV’s
- Create multiple data provider in case of complex queries.
- Limit amount of data.Reducing formulas/local report variables and complex filters