BO Recordsets or Looping through records?

[ChrisFields]

I am new to Business Objects but have done a lot of VBA Programming in Microsoft Access.

What I would like to accomplish is to be able to loop through the results of the sql created by the BO report in VBA, which I have done before using recordsets in Access.

Does anyone know if this is possible or can point me somewhere I can read up on it? I have searched the archive here and have had no luck.

Any help would be greatly appreciated.

Thanks!

 

[blom0344]

Not sure what you want with 'looping' through the dataset in BO, but you won't find much VBA in this forum.
Perhaps BOB forum has something:

http://www.forumtopics.com/busobj/viewforum.php?f=40

or otherwise:

http://www.islink.com/isi_library_downloads_BO_vba.htm

You may find BO quite a different tool if you have a lot of access experience.............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ChrisFields]

Thanks. What I wanted to do, was to be able to export the BO results into a pre-formatted excel sheet. I can do this in Access by using a record set and taking one record at a time and putting them in specific cells. It sounds harder than it is but I cannot figure out how to get to the data in BO line by line.

Even if I can get the data cube (I think it is called that) maybe that is somehow stored in an array.

Anyways, I will look at those links. Thanks again!! Also I will pass this site onto my co-workers as a reference in case they get stuck.

 

[blom0344]

That is a peculiar requirement. The datacube is merely the outcome of the SQL fired at the database, nothing more than a flatfile really. BO still offers Business Query as a module for this type of reporting, but I think it is hideously expensive. Why not directly use the SQL to fetch data from the database? BO as an intermediate platform does not make sense........
(I think version 6 allows you to directly save a .rep as an Excelfile including formatting)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ChrisFields]

Well, I am really just researching this to see if it is possible. Business Objects is really being pushed for this project. My area is only really a small part of a bigger group of people using this.

My group focuses on a specific set of reports that will be delivered to upper management, which they have giving them to them in Excel. The underlying data is being retrieved from Mainframe data dumps.

I am probably leaving out the majority of reasons for using this but I am newer to the project and just trying to figure out my part. I just thought if I they got the reports in Excel before, they liked them and we could reuse their templates, it would save some work and grief.

I did figure out how to do it but have not tested it out yet as I am about to but wanted to close this thread without people spending more time looking into it.

http://www.microsoft.com/europe/vba/downloads/business_objects_white_paper.doc

Here is a decent document and I am going to use the example on page 18 (of the document page numbers - 20/25 of all pages). It will allow me to step through the results of the query.

Thanks again guys!

 

[blom0344]

Chris,

This is interesting in itself, though what you are trying to accomplish could probably be done by buying some licences on Business query. There is little interest I think for this module which is used as an add-inn to Excel, using query panel from BO reporter as an interface to query.

Anyway, did you ever consider using microsoft query. It comes with microsoft office totaly free. I use it for 'refreshable' sheets for sales managers throughout europe. You can then use the retrieved dataset WITHIN excel and use VBA to do all sorts of formatting, ordering etc...

Whowever pushes BO for this type of 'collaboration' with Excel should reconsider the options...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ChrisFields]

Well, the original intent was not for collaboration with Excel. Sorry as I am leaving out some of the real reasons for looking at business objects as I am really new to this project and am only getting a small part of the scope for now.

This question really just stemmed from me looking into exporting only a few of the many reports that will be produced in BO that will given to a small set of users that are more familiar with Excel than BO and they can take what we give them and modify it easier.

This is only a small part of a much bigger project and I am only researching the possiblity of doing this as we are just trying to see some the limits of BO. This Excel thing may just go away.

Anyways, I did figure it out with some simple VB code so no additional money really needs to be spent but thanks for the replies, as it is nice to find an active BO Forum.

-Chris