I’m hoping someone can come to my rescue here! I’m using BO XI r2 and I need a way of exporting a BO report section-by-section into Excel file so that each section of the BO report becomes a separate tab in the resulting Excel file. I know this report will need to be built in Deski (or at least I assume so given the lack of macro support in Webi) and I have written simple macros before in BO, but I am struggling a bit on this one.
As a bit more detail, the report our users will run will be sectioned by cost centres in a single report tab. There may be up to 20 or so sections in the BO report depending on which area of the business is running the report. When the report is run we then need it to export to Excel and have each section of that report exported into a series of sheets, 1 sheet for each section, into a single workbook. So what they will end up with, hopefully(!), is a single Excel file that has 20 or so tabs in it where each tab is a different cost centre.
I’ve had a good hunt around the forum and I can’t seem to find anything that fits the bill. Is it even possible to do this or should I look to find another solution before I spend too much time on this?
There is no way to do this using DeskI SDK but you might be able to workaround it using the Excel API.
You can basically export the entire DeskI report to an Excel file.
Then using the using the Excel API go through the report and separate the sections into individual worksheets.
An alternative is to create a WebI document and use the Report Engine SDK to extract the each section using the ReportParts object.
You can then save each section into an Excel file.
However, you will still need the Excel API to import all the individual Excel file into a worksheet in one Excel file.
Thanks for the reply darcstorm, that was what I feared!
I think I’ve resigned myself to having the report created as a one page Deski or Webi report that is sectioned by cost centre that the user can then apply an Excel macro to. As you say, the Excel API will be the bit that takes each ‘section’ from the sheet and copies them one by one into individual tabs. This is probably going to be the easiest solution for my users to work with I reckon.
I must have missed that in my thorough scouring of the forums…
Now I just need to get it into a Deski report that the users can run with a prompt to let them select the Cost Centres and that will do my job very nicely. I’m sure they’ll find something else wrong with it once I’ve done though…
(BOB member since 2007-07-05)
(BOB member since 2008-07-22)
B!