Business Objects Tips and Tricks for Web Intelligence Reports – October 2009
Happy Halloween to all. I am going to try to start publishing short to medium length lists of my favorite tips. These have helped me a lot over the years and I think if I just try to jot them down as I use them I can end up with a fairly decent list of Business Objects tips and tricks that I can post with some kind of regularity.
- Use the “View my documents…” preferences setting of “fullscreen browser window”: There are actually two options and I believing that I am a power user, prefer to use the “in multiple fullscreen browser windows, one window for each document” option. If you don’t use one of these “fullscreen” options you are probably working less efficiently than you would otherwise. Do yourself a favor and try this tip out!
- Save New Versions Often: For many years, in full client (a.k.a. Desktop Intelligence) and in Web Intelligence Report creation and modification I have regularly used the “Save As” option every time I make a change that is just a bit more complex than a few formatting changes. I use the technique of starting with a base report name and then appending the date numerically to it. I don’t get down into a numerical representation of the time as it would be tedious I just use letters, starting with “a”. For example, the first version saved off today would be saved as the name “My Awesome Report 20091029a”. Doing the date and the letters in this format allows for simple alphabetical sorting within the InfoView or CMC object list. Later if the report becomes corrupt, or my great idea causes a huge mess I just start stepping back in version until I find the last known good one.
- Relative Positioning of Tables, Charts, and Cells: If you haven’t yet used this feature then your report design must be rather limited or just simple. I relatively position all of my tables, charts and cells. Always. Even if there is just one table on the report table I use relative positioning to get teh table exactly where I want it in relation to the page margins. Many times I am mixing tables, cells, and charts on the same report tab/page and I use relative positioning to make sure they never bleed together and that they are always spaced the same no matter how much data I pull in. My favorite trick is to create multiple tables and relatively position them to look like a single table. This is great when trying to make BO’s reports look and feel like the business user thinks they should.
How do you do this Business Objects magic trick. Easy, just click on the table, chart, or cell until you see a border around the object with a very small checkered pattern (with tables you need to click the border, with the other just click the object anywhere). Then right-click (with table, right-click the border) and select “Position”. Then your choices are flexible: horizontal, right and left, and vertical, top and bottom. Each one allows the space to be defined in pixels (px). Experimentation will pay off in allowing to to create some very professional looking reports and possibly even satisfy some otherwise impossible business requirements!
- What you see is not always what you get: For 98% of the components of a report what you see in the Java Report Panel is what the users will see in the regular HTML viewer they will use to open, refresh, and view the report. But there is that 2% that is not really quite the same (I just picked a low number, don’t quote me on the number). Most notably the prompts; they do not work exactly the same. Date prompts have different controls and selected dates in the HTML viewer will append the time to the date.
- Where is my data cube? What exactly was returned by the query?: Open the WebI report in the HTML viewer and click on Document > Save to my computer as… > CSV. The output will include all of the data from each query. The first query’s data followed immediately by the second query’s data ans so on. This is a good way to check things out without any concern about whether a BO table with all of the query objects is actually aggregating the query’s data when you don’t want it to.
- Cross-tab tables with totals in the first column: OK, this is nitpicking, but I keep finding business requirements that specify the totals should be in the first column and then the cross-tabbed data. Try to do this and you will find yourself in cross-tab hell. That is unless you click on the last dimension on the left and then add a column after. You just added a column in the “dimension” zone and now you can throw a measure variable in it. However, be prepared to format the column quite a bit to get it to look like a measure column; by default it will inherit the formatting given to measures.
- Alerts don’t work on dimensions: Yeah, this one sucks. I try about once a year to get an alerter to allow me to manipulate a measure in the header, for example create borders around quarters. It won’t do anything. Alerters are intended to bring attention to or apply condition formatting to measures. The alternative is to come up with some compromise involving semi-complex variables using the dimension objects.
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!
WebIntelligence Report’s Save As CSV Has a Problem?
Like a good obedient BO disciple I switched from using BO 6.5′s “full client” (now called Desktop Intelligence) to using BOXI’s Web Intelligence tool (also called WebI). To Business Objects’ credit, they have put much of Desktop Intelligence’s functionality in to WebI. But there is one disorienting difference.
BOXI Web Intelligence “Save to CSV” Is Different
My recollection tells me that if I exported a BO 6.5 Full client report to CSV that I received a CSV file with the same content that I would get through a Microsoft Excel export. Sure formatting, such as colors, column width, font, etc. were lost, but the order of the columns and even the report level variables in the table were exported to CSV from full client. Maybe, I a wrong about this (please point this out if I am), but that is the way I and some colleagues remember it.
BOXI Web Intelligence (and perhaps BO 6.x Web Intelligence) also has the ability to export to a CSV file, but this export behaves very differently. This export behaves much like the BO 6.x “View Data” or the “Data Cube” functionality. The BOXI WebI CSV export is a dump of the exact results of the first query of the report just as they were received from the database.
WebI CSV is a Query Result Export, NOTHING MORE
When you export a WebI report to CSV you may not get what you expect. The results should be identical to those you would get if you took the query and ran it directly against the database. This may be disappointing to some because it does not include any report-level aggregation, calculations, sorting, breaks, character formatting, or column ordering. HOWEVER, if a report developer is debugging a query or a universe object this output can be invaluable. For example, it can quickly uncover issues (such as Cartesian products) that dimensions can hide in a table. There are many uses for this output.
Why Does WebI’s CSV Have This Limitation?
I don’t claim to have inside information, but I can guess at two reasons for this limitation.
- Report Development Debugging: I know I already mentioned this, but I really want people to use the CSV export to understand more about their query and report. This previously perceived limit is actually advantageous functionality in the right hands.
- Discouraging ETL Use of Business Objects: If CSV were to output the aggregation and calculated columns of a report it would be readily used by end users as an ETL tool (Extract, Transform, Load). Some users still use it this way, but large scale use would explode if CSV exports were more than query dumps.
What Can I Do If I Want a CSV Export of My WebI Report Table’s Data?
There is almost always a workaround. If you must have your table’s data in CSV format then the solution is simple: export to Excel, open Excel output, save to CSV. You’re welcome!
