I want to export data from two different universes out of Business Objects to Microsoft Access.I tried to export my Data to a dbase file first, but it doesn’t work: BO always tells me that a field is ‘too long’ or something. Can you tell me how to export the Data into a DBase file perfectly or is there a direct way to bring Data from Business Objects into an Access-Table?
It also didn’t work with Excel, as there are too many data sets in my< universes!
Any help fur proceeding would be very appreciated!
I tried also this possibility, but then the columns did not stay as they were in BO but got moved and therefore the first column contains now data fom the last column of the row above! All the ata sets are mixed up then…
Though I have not really tried doing that, VBS might be able to do that. From inside MS Access, you may be able to access BO FC using VBA. Then open the report with VBA and access the table programatically.
Set repReport = ActiveReport
For y = 1 To repReport.GeneralSectionStructure.Body.Count
Set itmParts = repReport.GeneralSectionStructure.Body.Item(y)
Select Case itmParts.Type
Case boTable
Set itmTable = repReport.GeneralSectionStructure.Body.Item(y)
Set itmTableData = itmTable.Pivot
Set itmData = itmTableData.Body(1)
For z = 1 To UBound(itmData.Values(BoAllValues))
'You would be moving through all of the values here
Next z
Set itmTableData = Nothing
Set itmTable = Nothing
End Select
Next y
Other then that, I remember some posts in past that had VBA code for accessing cells in a BO report.
It does sound like you need to point an access query at whatever database your BO universes are referencing and cut out the middle man. It maybe that in access you need to go via [get external data] and then [link tables]. I’m no Access expert I’m afraid but that’s the way we do it for our Sybase tables.
Possible reason:
The name of one of the columns might be too long or contain special characters.
Now the real question is:
Why do you need the data in MS Access in the first place?
As already has been pointed out Business Objects Reporter is no ETL tool (Extract Transform Load tool such as Informatica or Data Integrator).
As others pointed out, you can bypass BO and talk directly to DB. To do that
i. You need to connect to DB from your Desktop. You will need to configure proper ODBC drivers and DSN for that. Your DBA should be able to help you out in that.
ii. Then in excel, create a new mdb file. Then goto File->Get ext data->Link tables. Follow the GUI for getting the tables in your DSN that points to this DB.
iii. Now you should select ALL tables that you will need.
iv. Design the report in BO and note the SQL that is generated by BO. Copy that SQL. @ results in prompts in Access as well. Though you will need to tweak the SQL sometimes.
v. Run SQL in Access, you should see the result.
For VBA , Excel. I think you should have a look at VBA help in MS Office. And then BO docs on VBA related to BO Full Client.
BO 6.x.x and Access (at least 2003 version, not sure about others) support XML. You can save a report as XML, and them import that XML in Access.
The middle with BO is needed because people who administer the original Database are afraid if we link the tables directly to Access we could change the original Database!
@Andreas We need the Data in Access because users should type in data sets easier via Access… the whole handling would be a lot of better with Access…
You will need to create this macro in Access to create an instance of BO.
Private Sub CommandButton1_Click()
Set BOApp = New busobj.Application
BOApp.LoginAs "user", "pwd", "FALSE"
Set BOApp.Visible = True
MsgBox "BO is available now"
BOApp.Quit
End Sub
You might want to look at VBA help inside Access / MS office if you are not sure about how to create and use this code in Access.
You need to reference BO controles in the document before using this code.
While you are editing this code, you will be inside VBA editor, from there, go to Tools->References and select both BO options. You need FC intstalled before this will work.
And this code will just create BO and kill it. You will need to add code for reading data. That code has been mentioned earlier.
(BOB member since 2004-02-20)
(BOB member since 2003-08-19)
(BOB member since 2002-06-20)
