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.
hello,
we have problems with excel exports too but they appear very localy only on one pc.They do not refer to the size of the result of a query.
Running the report on one pc everything works fine, running it on another one with technical identical features Excel cannot open the delivered file. It is unreadable.
Might be a simple problem but not for us.
Does anyone have had similiar problems and a solution?
Business Objects’ output of Excel does not depend on anything on the client PC. You will find that the PC that finds the Excel report unreadable would experience the same issue if another PC was used to receive the Excel file from BO and then forward it to that PC. In other words the solution lies on that PC. Are you sure that the PC with the trouble has Excel installed?
I have seen similar solutions with the addition of lookup tables in the Excel file to enhance the final output.
Hi Julian:
I am having problems with Web I not displaying the first row in a second page (or not displaying last row in first page) of a report.
Downloads to excel show all the data.
Have you experienced this problem before?
I am running Web i in BO XI R 2
Hi Rosemary I believe that there is a table option (select the outer border of the table to see the table properties) that allows you to specify to “repeat header rows on new pages”. As for displaying the last row… here are my thoughts:
1) If the page is sufficient to hold all of the table’s data than you will see the last row.
2) If the table spills over on to a second page and you still want to see the table “footer” on the first page… well if there is no setting for this than this is expected behavior. Please look at the table properties and see what you see.
Please share your findings here. Good luck!
Another question to do with Excel – I have a report layed out as a form. Users are required to refresh it, save to Excel and add some more information, before printing it. The layout of the form is such that Excel is forcing it over two page widths. Manually narrowing the left print margin in Excel will fix the problem, but I would like to do this be default. I have made the form as narrow as I can manage. Can I adjust any settings in BO, or is the Excel default template being used? I’m using BO XI R3/Webi
Hi Bob, I am not sure about this, but what if your report’s page size is passed into the Excel export? Try adjusting to an A3 or A2 page size or try landscape and see what happens. If that doesn’t do it then I don’t think there is a way to configure Business Objects to set the page width of the Excel output.
Thanks Julian – that does actually work – it gives me an option at least, although I would prefer A4/Portrait – I will check with my users and see if this will satisfy them
thankyou for the information. We are facing the same issue here. The scheduled status in infoview shows success but users are not receiving any email in excel format. However, if we select some other format they are able to receive it through email. But when we check the instace in history we can view the excel generated. It was working earlier but failing since last week.
Hi Julian,
as per customer requirement, in my webi (BO 3.1 SP4 FP3.6) have added “current date” and “last refreshe on” as report level variable and added in table greed as column, when exported this data to excel, both the columns are showing correctly but the same report when exported to csv, I am missing them from csv file: is this again a csv issue in BO webi? Any clues?
Hi Bhavna, excellent question! Exporting a report to CSV will export ONLY the data from the report’s queries. It will not export any report-level aggregations, calculated values, or formatting. CSV is really just a data dump of the query output. I think that the column order of the SQL statement (the query) determines the order of the columns in the output CSV file.
However, Excel and PDF exports are quite different. They are very much WYSIWYG. They export what ever you see in the WebI report viewer, almost precisely.
Hi Julian,
Even i have same kind of problem while exporting as CSV file.
But have never tryied to schedule report to local drive or local shared drive.
Can any one help to understand how it has to be done.
We are planning to build an web for all reports which current we have scheduled to mail ID.
Hi Leena
Depending on your environment, you may need to assign rights to the BO account to write to these locations. Personally, with just a few reports I want to save to disk on a regular basis, I find it easier to schedule them to send to email, then set up a macro in Outlook to save the file to disk when received. I use this to create date stamped versions based on the sending date of the email, and to create versions like ‘this week’ and ‘last week’
Clearly there are some issues here that BO need to fix. Does anyone know if they have any glimpse of intention to resolve these? Although I quite like Bus Obj as a tool, the company i work for are losing faith…
Thanks 🙂
Louis.
Hi, I am having the same issue as Elke above: hello,
we have problems with excel exports too but they appear very localy only on one pc.They do not refer to the size of the result of a query.
Running the report on one pc everything works fine, running it on another one with technical identical features Excel cannot open the delivered file. It is unreadable.
Might be a simple problem but not for us.
Does anyone have had similiar problems and a solution?
15. Julian says:
November 16, 2010 at 7:12 am
Business Objects’ output of Excel does not depend on anything on the client PC. You will find that the PC that finds the Excel report unreadable would experience the same issue if another PC was used to receive the Excel file from BO and then forward it to that PC. In other words the solution lies on that PC. Are you sure that the PC with the trouble has Excel installed?
Apart from having excel installed, was there any other solution to this issue?
One of my users is getting her columns showing merged cells when saving to excel, I was led to beieve it’s to do with section breaks or other formatting is this the case or is it a bug/quirk? We didn’t seem to have the problem in XI 2 (we’re currently on XI 3).
Hi Joe, I have seen some strange behavior in BO XI R2 and XI 3.1 occuring when tables of data and free cells do not line up exactly vertically. Could this be the case for your user?
Hi, sorry if posting this question in an inappropriate place?
I am a BA on a project that involves the export of data in Excel files via Business Objects. The client has asked us if the exported files can have the Filter options added to the columns to save that having to be something they do with every days export.
Is this something that’s possible when exporting via BO?
I did ask the BO team about it, and they said that MS Office is not on the machine running the export, so no.
Again, sorry for the possibly dumb question?
Unfortunately, that’s not possible at all. At least it is not possible to accomplish with businessobjects alone. The business objects server is merely creating an Excel-compatible file and it is true it is not running Microsoft Office.
I don’t know how you might be able to accomplish it, but perhaps if you had some external home-grown process that would pick up any Excel file written to a file share and then run an Excel macro against it which would enable those filters. Even if you got this to work it wouldn’t be very user-friendly because the users would have to go and find a file on the file share and get it there via a schedule.
Many thanks Julian, That was as I thought it would be, but thank you for confirming it. And yes, I could write some OLE Automation type stuff to do it, but the effect outweighs the benefit at this point.
Thanks Again.