FAQ: Reporter

A “combined block” that contains data from more than one data provider can only have objects that meet one of the following criteria:[list]1. A dimension object that is linked
2. A detail object associated with a linked dimension object
3. Any measure object
4. Any additional dimension object (unlinked) from a single (one only) data provider[/list]You may not include:
[list]1. Unlinked dimensions from two (or more) data providers
2. Detail objects that are not associated with a valid dimension object that can be used[/list]Note that measure objects are always allowed, but may or may not provide the proper value based on the linked (or lack of of linked) dimensions.

Cindy Clayton (BOB member since 2002-06-11)

system · August 13, 2002, 4:30pm

Why don’t my prompts come out in the order I want them to? Any workarounds?

Report prompts appear alphabetically. Try beginning your prompt text with 1, 2, 3, etc. to force them into logical order. Using a period to begin a prompt is a good, virtually hidden method of forcing a particular prompt to the top of the list.

Cindy Clayton (BOB member since 2002-06-11)

system · August 13, 2002, 4:33pm

Explain “You are not authorized to use this document”

The most common reason for this is that the document was - at one point - exported to the repository. Then it was deleted. At that point, it is a “secured” document (since it was exported) and yet there is no record of who has access to it (since it’s missing from the repository). Thus you, even as the owner, do not have the authority to access the document. A General Supervisor can open the document and save it for all users to remove the security information and allow access to anyone.

Another reason why this error can occur is if you totally delete your repository. This will cause this error even if the document was never exported. If you have a document that was created while you were logged in, and then you send it to someone that does not have a repository (and therefore cannot log in), they get the same message.

More information on this message is available here.

Cindy Clayton (BOB member since 2002-06-11)

system · August 19, 2002, 12:39am

What does “Some obsolete objects have been removed…” mean?

This message means that one or more of the objects used in your report are no longer available in the universe. A Designer may have deleted them. Deleting objects is generally a bad idea. Hiding them removes them from use in future reports but preserves them in current ones.

Another possible reason for this error is that a previously linked universe has now been ‘included’ instead. Objects from the kernel universe will report this error. Evidently the inclusion process assigns new ids to objects from the kernel universe. One way to work around this error is to provide both the original linked version and an included version of the universe in the repository simultaneously. Users can then use the … button in the Data Manager to point the report to the included universe using class and object names rather than ids.

There are a couple of ways to determine which objects are being removed from your query. Use the DataProviderSql function to display the sql BEFORE accessing the query panel. Compare this sql to what is written AFTER the message appears. Another method is to use the Data Manager to see a list of query objects.

Cindy Clayton (BOB member since 2002-06-11)

system · August 20, 2002, 12:32pm

Saving / Exporting to MS Excel

There are several ways to get Business Objects data into Excel. Which method works best probably depends on what you want to do with it once you get it there!

Copy and paste a Table from Business Objects to Excel

This will allow you to take a Business Objects Table into Excel. This keeps the data as displayed (objects, variables, filters, etc.) and will bring the data as it appears in Business Objects into Excel. It will not preserve formatting or formulas from Business Objects.

From the Business Objects Edit Menu, select Copy All. This will paste your data to the clipboard. Depending on how large your Business Objects Table is, this can take a while!
Open Excel.
To maintain text attributes in Excel (such as keeping leading zeros) select the columns that you want to be treated as text. With the columns selected in Excel choose Format Cells Text.
From the Excel Edit Menu, select Paste Special.
Choose Text and click OK.

Export a Data Provider from Business Objects to Excel

This process will allow you to take the raw data from a Business Objects data provider and put it into Excel.

From the Business Objects Data Menu, select View Data. This will bring up the Data Manager window where you can see the Data Providers for your report.
Click on the Data Provider you want to export.
Make sure the Results Tab is highlighted. There will be buttons on the bottom which include Options, Refresh, Purge, and Export.
Click on Export.
The Export to External Format screen will appear. Make sure Export to Local File Format is highlighted.
Specify the name and path of the file where you want your data to be stored in Name. You can use the Browse button to locate the path.
Specify the format of the file where you want your data to be stored in Format. On the pull down menu, choose Microsoft Excel Files (*.xls).
If you choose the option Delete Spaces it will delete the spaces before and after the delimiters that separate the columns of data.

Eileen King (BOB member since 2002-07-10)

system · August 20, 2002, 12:39pm

How can I link or “attach” report blocks so that they are positioned in relation to the each other?

This process will allow you to position the report blocks relative to other report blocks.

From the Business Objects Report screen, double-click on the edge of the block you want to position. This will bring up the block format window where you can set Formats for tables in your report. Click on the Appearances tab. Alternate method: click on the block, and select Format + Block (Table, Crosstab, or Chart depending on the block type selected).
You will see the Position screen. Horizontal or vertical position allows you to set the table’s position relative to the margins, upper section, another table, or center across the page (called markers). The space between the marker and the table is relative to the upper left corner of the selected table.
To move the table to the left and right, use the Horizontal Position Relative to option. To table up and down, use the Vertical Position Relative to option.
You can enter a number (positive or negative) to set the horizontal space between the marker and the table. You can also enter a number to set the vertical position between the marker and the table.

Eileen King (BOB member since 2002-07-10)

system · August 20, 2002, 3:09pm

Why can’t I see all of the data returned by the cube in my report?

Business Objects seems to be hiding data. I get 9 rows in the cube, but only 3 rows show up on the report. What would cause this?

There are a couple of reasons why you may not be seeing all the data in your cube within your report.

One possibility is that the data is being filtered. You can check to see whether any filters are excluding rows.

Click on the block so that it is highlighted.
From the Business Objects Format Menu, select Filters.
The Filters On dialog box displays the global and block-specific filters applied to the report.
If you have no filters applied, nothing will appear in the Folders. The global folder will display any filters that have been applied to the all blocks. The block-specific folder is named based on the block name and will display any filters that have been applied to that block.
The Values dialog box displays the values available for the variable. The highlighted values are those included in the filter. Any that are not highlighted will be filtered out.
If you want to remove a filter, click on the filter and then click on Remove.

BusinessObjects, by default, shows only unique rows of data. If the values in your block would result in the same row being displayed more than once it will only show that row once. However, you can force the block to display duplicate rows.

Click on the block so that it is highlighted.
From the Business Objects Format Menu, select Format Table.
The Table Format dialog box will be displayed. Click on the General tab.
Under the Display box is the option “Avoid Duplicate Rows Aggregation”. If you check this box, all rows will be displayed without aggregating. (This option is not available for a crosstab).

Eileen King (BOB member since 2002-07-10)

system · September 3, 2002, 1:05pm

How can I send a report from Business Objects to another Business Objects User?

Open Business Objects.
Open your report in Business Objects.
While you are in Business Objects, Click on File…Send To…Users. This will bring up a Send box.
Click on the To button under Send To. This will bring up a list of Business Objects users and groups. The groups are presented first and then the individual users.
Click on the list.
If you type a “B” that will take you down the list to the first “b” which is the Bob group.
Using the scroll bar on the right side, you can move down the list of users. I am Bob.
Double click on “Bob” and that will add me to the Document Recipients.
Click “OK”. This will take you back to the Send box.
Click “OK” on the Send box.
The computer will think for a second and then a box will pop up saying “Export was successful”.
Click “OK”.
You’ll be back in Business Objects with your report. You can then close Business Objects.

Eileen King (BOB member since 2002-07-10)

system · December 19, 2002, 3:18pm

I can’t create user defined objects for a particular universe. I can create them for other universes, so it’s not a permission thing. Any idea what’s happening?

Sure! BusinessObjects keeps all of the user defined objects (UDO’s) in a particular file. This file has the same name as the short name for the universe, with the file type of UDO. For example, the short name (file name) for Island Resorts Marketing is BEACH.UNV. There will be a file named BEACH.UDO that contains the UDO’s for that universe.

However, sometimes - generally when the first UDO is being created - BusinessObjects has a problem accessing that file. If you check and see if there is a zero-byte file for your UDO’s for that universe, you should delete it. Once you’ve done that, you will generally be able to go back into BusinessObjects and create User Defined Objects for that universe.

This never seems to be a problem except for the very first object. Once that first object has been created, you’ll never have a zero-byte file from that point forward.

Bob (BOB member since 2002-06-06)

system · April 12, 2003, 10:04pm

How can I make a table from two data providers and only show data common to both?

Once they are linked, each object of linked data providers contains ALL of the values from BOTH queries. If you want to isolate the display to show only records that are in both queries, you need to filter for non-null values from some other non-linked value.

For example, assume you have measure M1 from query 1, and measure M2 from query 2. Measures work the best, but detail object can be used also. You need to create a custom filter (not a pick list) using the following steps.

Click on M1
Click on Format + Filters from the menu. Do NOT use the filter toolbar button.
Click on Add, and select M1
Click Define, and enter the following expression:

= Not (IsNull())

Repeat for measure M2. Now you will only see rows that have entries from both queries.

Cindy Clayton (BOB member since 2002-06-11)

system · April 12, 2003, 10:18pm

The Unhandled Exception Error (C0000000005) is caused by a number of different things. It seems to be a kind of catch all for those errors that BO doesn’t have a more specific error message for. You can upgrade to higher service packs to get this message less often but it still occurs even within 5.1.5. Here are a number of causes and potential resolutions.

Part of the Business Objects NT profile becoming corrupt.
Remove the following registry key:

Go to registry key HKEY_CURRENT_SOFTWARE/Software/Business Objects/BusinessObjects/5.0/USERNAME User Prefs

Something under here becomes corrupt. This can happen in many circumstances, but in particular where a low spec machine (e.g. 64Mb - this should ring alarm bells) is trying to open a ‘large’ report - sometimes as little as a couple of Mb. When USERNAME signs on to BO again, the key is recreated with the correct values.

Refreshing a report.
In fact, the refreshing completes and you see the message ‘Disconnecting…’. This is BO, signing out from the database which it has been getting data from - normally you wouldn’t see this unless BO is going slow. The fact that you see this, then get the bug is again a PC resource problem. Try it on a faster better specified machine to prove this.

Tidy up the report, remove unused variables, formulas as well as objects from the different queries. Encourage the user to purge the report, so when they pick it up next time the PC doesn’t use much resource opening it. Sound them out on scheduling the report and getting the final result sent to them.

Importing universes
Another reason for C0000005 is a bug specific to the version of BO we have, 5.1.2. This is the problem with importing universes. Basically, each changed version of the universe has a counter which is incremented each time you save and export. Something goes wrong with this counter so BO is not happy to get the new universe. Mostly this is solved by deleting the existing universe from the C:\ drive and then forcing and import. There is a fix (which I have never got to work) which is to import and export twice. Don’t forget about Tools/Universe - you can try and force an import from here. This was supposed to be fixed in 5.1.3.
An old or corrupt dll
If you look at the information given when you get the unhandled exception error there is a bit of hex and references to a bunch of dlls. Occasionally, one of those dlls is a duff version - try tracking that down. Also, could have problems with duplicate version of dlls, same file, e.g. ociw32.dll in more than one directory.
Missing default printer
Sometimes the absence of any default print driver can lead to this error (at least up to ver 5.1.7), when Business Objects is rendering some graphics. As a remedy install a regular printer as your default printer (even if you do not have any physical printer).
Miscellaneous resolutions
Try a full reinstallation of BO. i.e. deinstall, make sure no trace of files, folders and registry entries before reinstalling. Reduce the graphics acceleration level via control panel - not a problem on this site, but worth knowing about.

Check out this thread as well.

Cindy Clayton (BOB member since 2002-06-11)

system · April 12, 2003, 10:24pm

What are the available methods and their associated advantages/disadvantages to get BO data/tables/graphs into an Excel sheet ?

1. In BO: Edit-> CopyAll ; in Excel: Paste Special → text
Advantage: retains sections, filters, groupings, formula/variable results
Disadvantage: Colours are lost, no export of graphs
2. In Bo Data → View Data → Results tab: Export → File Format: MSExcel
Advantage: directly creates an excel file, exports whole datacube
Disadvantage: loses all formatting and calculations
3. In BO: Save As .txt ; In Excel: Open created .txt file;
Advantage: retains formatting (sections, filters, groupings) and formula/variable results.
Disadvantage: loses colours and graphs, two-step procedure, need to re-do for each report in the BO document
4. In BO: Save As HTML; In Excel: Open created HTML file;
Advantage: only mehtod that retains all formatting and calculations incl. graphs, saves all reports in the BO document in one step;
Disadvantage: two-step procedure; creates the HTML files in a sub-folder structure
5. In BO: click exactly on border of a table → Copy; in Excel: Paste;
Outcome: pastes static image file of that particular table into Excel / Powerpoint etc.
6. In BO: Edit → CopyAll; In Excel: Paste Special ->Picteue (Enhanced Metafile).
Outcome: Copies the whole report as picture into Excel / Powerpoint etc.
7. In BO: use the available BO_VBA methods DP.ConvertTo() or Report.ExportAs() - for Advanced Users
8. Use the ‘Copy to DDE’ option (Data-> View Data → results Tab → Export; in Excel: Paste Special → Paste Link)) to dynamically update the datacube results in Excel
9. Embed a BO report by calling it in Excel via Insert-> Object → Create from File (Mark Option ‘Link to file’); Disadv.: not very reliable

Cindy Clayton (BOB member since 2002-06-11)

system · April 12, 2003, 10:29pm

How do I get rid of or change the message ‘No Data To Fetch’?

The VBA code below refreshes the report and supresses all messages from the application, including ‘No Data To Fetch’…


Dim DP As DataProvider

Private Sub Document_BeforeRefresh(Cancel As Boolean)
    Set DP = ActiveDocument.DataProviders(1)
    Application.Interactive = False
    DP.Refresh
    Application.Interactive = True
End Sub

Here is a report level solution from Andreas for supressing the message…

In your reports you would have to filter out these additional rows.

Another report level solution doesn’t supress the message box but provides a user note on the report…

Cindy Clayton (BOB member since 2002-06-11)

system · May 6, 2003, 9:12pm

How can I import data from Excel and keep the numbers numeric?

Submitted by Dave Rathbun. If you work with reports that include data from Excel, there is a common problem where numeric data that should be treated as character data is imported as numeric. If it comes in with the wrong type, it may be a problem for aggregation, linking, or other variables. So I wrote the following really quick macro that can be used in any Excel sheet to convert a numeric value to a text formula.

Sub Set2Text()
    
    ActiveCell.Select
    While Not (IsEmpty(ActiveCell))
        ActiveCell.FormulaR1C1 = "=" &amp; """" &amp; ActiveCell.Value &amp; """"
        ActiveCell.Offset(1, 0).Select
    Wend
        
End Sub

Simply enter this macro in your worksheet, highlight the first cell, and run the macro. It will go down the column until it reaches a blank cell replacing 1234 with =“1234”. This data will be recognized as character data when the Excel data is imported into BusinessObjects.

Submitted by Brie Anne Clark :

I wrote a macro much like yours a while ago, but mine just adds an apostrophe to the beginning of the cell value to convert it to text.


Sub AddSymbol()
    Do Until ActiveCell.Formula = ""
        ActiveCell.FormulaR1C1 = "'" &amp; ActiveCell.FormulaR1C1
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Submitted by Dwayne Hoffpauir on 3/17/04:

This is the method that I use for Excel personal data providers, and at the risk of jinxing myself, it never fails:
Instead of using a “live” Excel file to build the data provider, create a sample file. Same column headings, but use dummy data.

Put alpha characters in the columns you want to be alpha, numerics in numeric, and so forth. Maybe 20 rows, but every row the same. It’s usually columns that have ambiguous data (could be alpha, could be numeric) that confuse BusObj.

After creating the data provider, you can now put “live” data in the Excel file, and the definitions will “stick” from now on.

Anita Craig (BOB member since 2002-06-17)

system · June 6, 2003, 11:28am

Help, my Slice and Dice panel or my Query panel has disappeared.

Press and space. This displays the standard windows menu. Now select Maxsimize and the appropriate window should now be displayed.

If you are using Citrix you may need to edit the registry in order to alleviate the problem. The appropriate key to delete is:

HKEY_USER\Software\Business Objects\BusinessObjects\5.0"UserName"

where “UserName” is the BusinessObjects login of the user encountering the problem.

Nick Daniels (BOB member since 2002-08-15)

system · June 9, 2003, 7:33pm

Is it possible to make a copy of a data provider in the same or in a new document?

The current versions of BusinessObjects (up to and including version 6.0.0) do not include this functionality. However, we do have some code on this site which will allow you to achieve this. See here for details.

What about 6.1.x?

In 6.1a (or later) if you already have 1 data provider and want to create a similar second data provider then you can do the following: Go to Data > New Data Provider in the menu. Then on the options displayed select “Use an existing query to build a new one”. A list showing all the available data providers is displayed. You can select the one which you want and click OK and a similar new data provider is created.

Nick Daniels (BOB member since 2002-08-15)

system · June 18, 2003, 1:48am

Why do I get a Syntax Error (DMB0007) when I try to Sum() Where ( > 2000)?

The syntax of WHERE Clause is:
measure Where (dimension = (number or character string or date, dimension = …))

This means that the WHERE Clause in Business Objects Reporter only allows the “=” operator, and the right side of the equation has to be a constant as well (and not another report variable etc.).

For example:

Sum(<Sales>) Where (<Year> = 2000)

will work

Work around for original problem:
Create another local report variable “VAR Flag Is Year greater than 2000” defined as:

= (<Year> > 2000)

This will be a boolean variable returning 1 or 0.
Make sure that the universe object is of Data Type Number.

Now modify your original formula to use:

Sum(<Sales>) Where (<VAR Flag Is Year greater than 2000> = 1)

Cindy Clayton (BOB member since 2002-06-11)

system · July 13, 2003, 6:20pm

How can I supress the ‘No Data To Fetch’ message?

There are multiple methods of supressing the ‘No Data To Fetch’ message.

Programmatically…
If you copy this code into the ThisDocument module, it should work. To implement the code, you will need to have your Report which gives you the No Data to fetch message open. Click on Alt + F11, and the Visual Basic Editor will open. On the left hand side you should see a list of all the documents you have open. Under the name of the document which gives you the message you will see a icon called ThisDocument. Double click on it and on the main window, a blank sheet should appear. It not only suppresses the “No Data to Fetch…” message, but any prompts and so forth are also suppressed. It even turns off the hourglass cursor, so it can be confusing to the user.

Private Sub Document_BeforeRefresh(Cancel As Boolean)
    Application.Interactive = False
End Sub

Private Sub Document_AfterRefresh()
    Application.Interactive = True
End Sub

Note that this code is executed only when a full refresh is performed. If you run queries individually (from the query panel or Data Manager) the above events are not triggered.

By creating a union query…
One trick used to get around this problem is to create a class of “report objects” in the universe consisting of blanks and zeroes not attached to any table. Also create at least one of these objects that references a dummy table such as SYSDUMMY1 in DB2 (v5 and higher) or DUAL in Oracle. The objects that don’t reference a table will not parse in Designer, but they work fine as long as at least one object in the query does reference a table.

You can then take a report where you don’t want to see the “No data to fetch” message and create a union query using the blanks, zeroes and the object referencing the dummy table. This will guarantee that at least one row will be returned by the query. The zeroes added to numeric columns will not influence the result. Create a report filter to hide the union row. This technique can be used for long-running reports that contain multiple data providers where processing should not stop if one of the queries does not return data.

Using a dummy table and contexts…

The way to get the No Data to Fetch message to disappear is to put a dummy table in your database with a single row with a single column with a value of 1 or use a system table such as Oracle’s dual table. In your universe, add your dummy table as a class with a single object. Put a self-join on the dummy table. In the object definition put in the where clause that the column equals 1, so that the value will always be found by the sql. Put the self join in a separate context. It should be the only join in the context. Do not include the join in any of your other contexts.

Include the dummy object in your result set in your dataprovider when you don’t want the No Data to Fetch message to appear. The dummy object in its own context will cause B/O to create two datacubes for the query because two different contexts are used. Because of the self-join always being true you will always get some result. Just hide or delete the dummy column in your report.

Displaying a friendly user message if no rows are returned…
If you simply wish to display a friendly message on the report when no rows are retrieved, check here for one method.

Cindy Clayton (BOB member since 2002-06-11)

system · July 13, 2003, 7:00pm

How do I make an object that selects the current date?

Create an object in Designer that uses your databases’ method of selecting the current date. In Oracle, your object would select ‘sysdate’. The object will not be attached to any table and as a result will not parse successfully. Never fear . It will work when combined with other valid objects attached to tables.

Cindy Clayton (BOB member since 2002-06-11)