TRUTH: Business Objects Does Not Create an Excel File, Help!
A couple weeks ago we received the best email question so far. Not only did it ask for more details with regards to one of the articles, but it provided insights worthy of making it its very own standalone article. Truthfully, I would like to publish it here to share the knowledge contained inside (this is always the best way to answer questions sent to us), but also to hopefully bring visibility to a very worthy question that you may be able to answer. Please read this one, it is good, and provide any comments or questions you might had in the comments below.
Our Guest Author wrote the following [minimal edits for context clarification from Julian are in brackets]:
Subject: [Excel] Format issues when Schedule a DESKI report in BOXIR2 SP3
We are trying to schedule a DESKI Report 3 times a week in Excel format to our clients. DESKI report has 1 data provider and 1 report tab only. We are on BOXIR2 SP3.
[A client] has an automated process, which reads the files we send and save it somewhere. They have a problem in reading Excel file which we send it through our BO scheduler. I tried to schedule DESKI document through InfoView using both SMTP and FTP [delivery] options.
From the InfoView -> scheduler
1. Excel format is generating Excel readable file — but they want exact Excel file.
2. Text file format is generating ‘Tab’ delimiter file
3. There is no option for CSV from scheduler [link to related article]
I am really kind of stuck in the middle (between clients, business users, and my manager). Clients want either an Exact Excel file or CSV file on a schedule basis. As there is no other way to schedule a report in CSV Format [link to related article], we have to write macros/scripts to do this. Which I am ready to do, [but] our management and team don’t want to do this workaround. [They want to] avoid macros as it will increase maintenance and users have to depend on IT for this.
New Knowledge Alert – Business Objects Cannot Create True Excel Files, only Excel Readable Files
I have opened a ticket with BO support and this is the answer I got from them.
Business Objects uses its own dll files for exporting a report to an excel format. The file created is not a native excel file and is simply excel readable.
Any file converted to excel using a tool other than excel will always create excel readable file which would be different (but not too much) from the native one created in Microsoft Excel.
Business Objects does not require Microsoft to be installed for this conversion to take place as it relies on its internal files for the conversion. BusinessObjects is designed only for creating the reports, though it can export the reports to the other formats.
But since all the exported files contain similar information such that Microsoft Excel can interpret and render the files, your module should be able to access it. The files exported from Business Objects would not differ greatly from an excel file created in Microsoft. I am not sure what these internal differences would be.
[The following response captures the client's requirement/comments]:
I think we are stuck in this issue now because [BO] was intended to create an “Excel-readable” file, not create an Excel file. My Java module [is] trying to read an Excel file, not an Excel readable file. There [is] nothing wrong at both ends.
Unfortunately, the data file just cannot serve the purpose that I am looking for. We would need to find some other options now.
I am not sure if [BO] is able to create a “CSV” file? This is a general purpose type of file format and would not have any format issue in reading. Also, you did provide a TEXT file for my testing, but it’s not a fixed length TEXT file. Seems that tab was used as a delimiter in that text file. I wonder if you are able to create a text file without delimiter?
I know that we can save a report in CSV format also in a fixed length text format – through DESKTOP Intelligence
but is there a way to schedule this.
Is a schedule CSV option available in BOXIR3.1???
Please let me know if there is a way to schedule a DESKI report in CSV format.
Responses from BusinessObjectsTips.com’s Julian
The more I think about this I have to say that macros are the only solution that will meet the needs client, unless they can somehow do SDK integration of some sort and pull the data right off the report.
Another solution might be to create an Excel “converter” application. This could be a standalone Excel file with a macro in it that loads BO’s native Excel readable format and outputs a true Excel format file. Perhaps a standalone executable would be better because it would allow for scheduling (perhaps using a BO Program Object). I don’t really know how to create such an application, but this actually would not require any BO skills.
To the author, please share your progress with us all. To the rest of our audience please share your ideas and comments. Thanks!
We struggle with similar issues on a regular basis here. Our primary issue is actually the row limit in Excel.
There is one possible work-around to the user’s issue, depending on the size of their data. Concatenate all of the fields together with a text delimiter using a variable. It is ugly, it is terribly error-prone during development, and it may not solve the issue with Java not being able to read the file. But, it will export in a manner that looks like a CSV. We used this manner specifically with a series of reports in which the system that was consuming them required a pipe delimiter.
Oh, and I can confirm that CSV is not a schedule option in 3.1, having just completed the upgrade. Still just WebI, Excel, and PDF. The reason for this, I am given to understand, is specifically so that people are not using BOXI as a poor man’s ETL tool. In other words, it is a political decision by the company, not a technical limitation.
Thanks for the further enlightenment! My sources told me that CSV scheduling was in 3.1. Let me check back with them and see if this is 3.1 SP2 or just another broken promise.
I’ve encountered this issue over the years while working with BusinessObjects tools, even dating back to the pure Crystal days. Just a few weeks ago, I had to satisfy a similar requirement for a client that needed to present a detailed GL report to auditors and strongly preferred Excel – because it would make everyone’s life easy to slice and dice the data.
First of all, the report was developed using Crystal Reports. Don’t throw up your hands just yet because this is not DeskI… The same ideas could be implemented to a certain extent in DeskI or for utmost simplicity, the report could be developed in Crystal, if the client here uses Crystal Reports in their development shop. Crystal Reports is a good reporting tool in case because of the level of control the developer has in customizing the output.
Now the solution: as you can imagine, simply scheduling the report and selecting ‘Excel Data Only’ (which is a particular export option for Crystal that strips out all formatting and renders only raw data) didn’t produce a great Excel file: some of the column headers were moved to the wrong columns etc… So here’s what I did to produce a 100% Excel-friendly output that the Auditors were happy with:
+ Because the report formatting would need to be completely stripped (no headers, footers, logos etc…), but because we didn’t want to end up with 2 copies of the report (maintenance cost), I added a parameter to the report so the user would select the format to output the data: 1 – Default (pretty Crystal Report); 2 – Excel-Friendly. For the Excel-friendly option, I formatted all the Report Sections and/or report objects that I didn’t want to expose on the finished report, for example the Report Headers, Report Footer, SubReports etc…)
+ I put all the column headers in 1 section (Page Header 1b) and made sure the text in each of the header fields wrapped with no forced line breaks. If I had column header names that were long, I entered the long names, took out trailing and leading spaces, then shrunk the text box back to a smaller size that would allow me to fit all the fields into the page.
+ I made sure all the fields in the Page Header and Details sections “touched”, using vertical guidelines. This is the most important requirement to output Excel-friendly reports!!!!!
+ I made sure all the data fields (which were aligned with the column headers in the Page Header) were in 1 section on the report – be it Details or Group Header. I resized all the fields to have the same height.
With all these, I empowered the user to very easily and quickly generate the report in either format: one that board-room ready and had great presentation and style and another that was ready for an analyst to go after in Excel.
NOTE: I have not encountered any Excel-file limitations (even in Office 2003). Excel has a limitation of about 65K lines (or thereabout). This report was able to output in 7 worksheets when it hit the per-page limitation. The only problem is that the 2nd sheet and subsequent ones do not repeat the page headers. But this is a small price to pay for getting the report in almost perfect condition in Excel.
Great idea! I wish I had thought of it. I am not quite clear on how you execute the report formatting differently based on a prompt variable value (thinking from a WebI/DeskI point of view). Please enlighten us.
Hi all,
I have come across the same issue in my project.
But the thing is I don’t have an idea to automatically save the excel report in a path once after every schedule is run.
Can anyone take me through that process.
Regards,
Judith
Hi Judith, The only way to do this is with a destination. UNC, FTP, and email might get you there, but you may need to struggle with your infrastructure to make this work. For example, some new Microsoft SharePoint installations can receive new docs to their document libraries through an email address.
I would need to look more into this to provide more details (perhaps a new article). Please let us know how your investigation/progress is going.
We had this exact problem with our SQL Server DataMart based on an excel file output by a webi schedule.
I used an activex script to open the file in excel (which will read the excel readable file) and simply save it again and close.
Seems to work perfectly and SQL server can now read the file properly.
Another problem with the Excel DLL we seem to experience is where it just outputs one blank cell to excel for no reason. Report seems to run fine, takes the normal time but then will not output properly. Its driving me up the wall.
ActiveX code below.
‘**********************************************************************
‘ Visual Basic ActiveX Script
‘************************************************************************
Function Main()
Filename1= “G:\OrderMart-TempTest\MasterReport-Provide”
FilenameFull = Filename1 & “.XLS”
FileNameOutput = Filename1 & “-Converted.XLS”
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
If objFSO.FileExists(FilenameFull) = True Then
Dim xlApp, xlWB, vPath, FSO, f, fl
Set xlApp = CreateObject(“excel.application”)
Set FSO = CreateObject(“scripting.filesystemobject”)
xlApp.DisplayAlerts = False
Set xlWB = xlApp.Workbooks.Open(FilenameFull)
‘xlWB.SaveAs Filename1 & “.csv”, 6 ’6=xlcsv
xlWB.SaveAs FileNameOutput , 1
xlWB.Close False
xlApp.DisplayAlerts = False
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
Set vPath = Nothing
Set FSO = Nothing
Set f = Nothing
objFSO.DeleteFile FilenameFull
Main = DTSTaskExecResult_Success
else
If objFSO.FileExists(FileNameOutput) = True Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End if
End If
Set objFSO = Nothing
End Function
Hey John, thanks for the code. BTW, this might not help, but I always make sure I use relative positioning with my report tables/charts to be sure that there are not unwanted gaps between table and margins. Also you probably noticed that you have to align columns and tables vertically or you make a huge mess of miniature columns when exporting to Excel.
Hi,
I have question.. what if the generated excel file is missing some data? Like it’s all empty I only have the selected Location and time stamp but all the expected data is missing.
What do u think is the problem? Or maybe I should ask where do u think I should start looking for the cause?
Thanks!
/bong
Hi bong, here are a few ideas:
1) make sure the text is not white in color and invisible at first glance
2) try a “Save as PDF” and see if you notice anything different in the PDF output
3) Make sure the data is not down much lower than the first page
4) Save the Excel output as CSV and then close and open the CSV in a text editor, is there any data?
5) If all else fails, I wonder if the server has a corrupt Excel writing DLL/library, I don’t know where it is but I could look for the info if you need it.
Hi Julian,
Thanks for getting back to me.
Let me check and hope it will be solved.
/bong
I have the smae problem, but it seems to occur ONLY when there is a sufficiently large volume of data in the output file. Instead of outputing the 10000 rows and 50 columns it outputs one blank cell with a width of about 100 in Cell A.