Troubleshooting

Exchange rate conversion is not done properly #

Example #

When running a report the conversion is not done using the correct rates.

Solution #

There is a column named TO_TIME_STRING in the EXCHANGE_RATE table. The purpose of the upgrade procedure explained in this chapter, is to populate this column with correct values. If this upgrade process is not carried out - you will experience wrong exchange results in reporting. Technically, you can start using the 2.5 on-line system even if this upgrade process is not completed, but it is not recommended

The entire upgrade process will take anywhere from 1/2 hour to 5-6 hours - depending on how many Exchange Rates exist in your target 2.5 database (1/2 hour for a few hundred exchange rate, to up to 6 hours for 600,000 exchange rates). These estimates are approximations. The actual elapsed time will be influenced by your technical environment (server, database, client PC).

Background #

This section explains why the Exchange Rate upgrade is necessary. You do not have to read this section in order to carry out the upgrade, but it is recommended.

In earlier releases of SICS/nt there was a performance problem in reporting related to exchanged amounts.

In 2.5, this issue has been addressed and improved. There have been certain changes to the Exchange Rate model itself, and to the way the on-line system and the reporting tool interacts with the Exchange Rate data.

The most important change is the introduction of a new TO_TIME_STRING column in the EXCHANGE_RATE table. There is already a column named TIME_STRING in the table. Together, the two columns indicate for which period of time each exchange rate applies.

For a day rate, the TIME_STRING will contain the date for which the rate applies (on format yyyymmdd). The TO_TIME_STRING will contain the last day that the rate applies to. Which date this is, depends on the TIME_STRING of the next available exchange rate, for the same combination of Primary-, Secondary Currency and Exchange Rate Category.

Example: There is a day rate for USD against GBP for March 1^st 1999, with category “Day Rate”. The next available “Day Rate” for USD against GBP is for March 8^th 1999. Thus, the TIME_STRING of the first rate is “19990301”, and the TO_TIME_STRING is “19990307”.

The most recent day rate will have the value “99991231” in the TO_TIME_STRING column.

For a period rate, the TIME_STRING will contain a string of the format SyyyyP, where S = the code value of the Period Set, yyyy = the year and P = the code value for the Period. (Example: For Period Rate “3^rdQuarter 1999” the value will be “Q1999Q3”). The TO_TIME_STRING will similarly contain the last period for which the rate applies.

When Exchange Rates are loaded into the system (either using the Migration Tool Kit or the Exchange Rate Loader) the TO_TIME_STRING is created automatically.

When rates are created or edited on-line, the system will also make sure that the TO_TIME_STRING values for existing data are kept up-to-date.

The SICS/nt Database Upgrade utility will add the TO_TIME_STRING to the EXCHANGE_RATE table. However, the utility will NOT populate the column with proper values.

Desktop Intelligence File Menu has disappeared #

BusinessObjects XI R2

1. in regedit {delete} the folder “Desktop Intelligence” and all subfolders:
   HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\default\BusinessObjects\Application Preferences\BusinessReporter\Desktop Intelligence

2. Restart DeskI and all menus will be reset to original. “

Web Intelligence does not trigger “Cartesian product” message #

Instead of showing this message when running a query, the query runs “forever” and you have to cancel the query, or it runs but returns a Cartesian product, or even an incorrect result.

#COMPUTATION #

• #COMPUTATION in aggregations such as minimum revenue per city
• #COMPUTATION in cumulative aggregations such as running average revenue per city
• #COMPUTATION in non-aggregate formulas, for example using IF THEN ELSE statements.

#COMPUTATION in aggregations #

The expression “aggregations” refers to any calculation containing an aggregate function such as Sum, Count, Min, Max etc. In reports, aggregations let you answer questions such as “What is the minimum revenue per city for each region?”.

This section provides a description of why #COMPUTATION occurs in aggregations, with examples, and offers solutions.

Note! The information presented here requires that you understand BUSINESSOBJECTS extended syntax

Description of #COMPUTATION in an aggregation #

In BUSINESSOBJECTS, #COMPUTATION occurs for the following reason in an aggregation:

The output context is not included in the input context. This section describes three cases where you may encounter the problem:

• In a formula containing an input context and an output context.
• In a formula containing either an input or an output context.

#COMPUTATION with only one specified context #

You can define an output context without an input context and vice versa. For example, the following formula

=Min(<Revenue>) In <Year>

Year is the output context, while no input context is defined. The formula calculates minimum revenue per year.

When you specify only an output context as in the formula above, the closing parenthesis comes after the measure, not after the output context. If you put the closing parenthesis at the end of the formula, you’re telling BUSINESSOBJECTS that the specified dimension is in fact the input context. In this case, BUSINESS OBJECTS takes the local context, e.g., the dimensions in the block, as the output context. If the dimensions in the block are not present in the output context defined in the formula, you get #COMPUTATION.

Example #COMPUTATION from an aggregation for minimum revenue per year

The formula

=Min(<Revenue>) In <Year>

shows the minimum revenue per year in a table containing Year, Quarter and Revenue. Year is specified as the output context. However, if the formula is changed to

=Min(<Revenue> In <Year>)

i.e., the closing parenthesis is placed after Year, not Revenue, BUSINESSOBJECTS reads Year as the input context, and takes Year and Quarter as the output context, because

• No output context is specified, so
• The dimensions in the block are the output context by default.

#COMPUTATION in a formula in which you define an input and an output context #

You define an input and an output context when you do not want a result based on the default contexts in the report. #COMPUTATION occurs when the output context is not included in the input context.

Example #COMPUTATION in a formula in which you define an input and an output context

Your report displays revenue per quarter per year in a table. The Customer dimension is available in the report but is not currently displayed. You want to find out the top revenue per customer per year, so you define the following formula:

=Max(<Revenue> In <Customer>) In(<Year>,<Customer>)

You add this formula to the table, but #COMPUTATION appears instead of maximum revenue. This is because the output context (Year, Customer) contains a dimension (Year) that is not specified in the input context (Customer).

Solution #1 for #COMPUTATION in an aggregation

First, remember the golden rule: the output context must be included in the input context. The following procedure shows you how to fix any of the #COMPUTATION problems described above:

1. Display the Formula Bar (Formula Bar command, View menu), then click the cell containing the formula.
2. Check the following questions:
   Does the output context contain dimensions that are not in the input
   context? Example:

_=Min(<Revenue> In (<Region>,<City>)) In(<Year>,<Quarter>)

_ Does the output context contain more dimensions than the input context?
Example:

_=Min(<Revenue> In <Region>) In(<Region>,<City>)

_ If only one context is specified, is the closing parenthesis in the right place?

For example:
BUSINESSOBJECTS then makes the dimension(s) in the local context, e.g., the table in which the calculation is inserted, the default output or input context - whichever is undefined in the formula.

3. Edit the formula in the Formula Bar, then press Enter.

Solution #2 for #COMPUTATION in an aggregation #

You can have BUSINESSOBJECTS write the formula for you, by using the Define As Variable command on the Format menu. Here’s the procedure:

1. Select the Report command from the Insert menu. A new report tab appears in the document.
2. Insert a table containing the dimensions and measure you need for the calculation. To do this:

Select the Table command on the Insert menu.
Click Use existing data from the document, then click Begin.
Hold down the Ctrl key, click the dimensions and measure you need, then click Finish.

3. Click inside a measure, e.g., Revenue, then select the Calculations command on the Insert menu. The Calculations dialog box appears.
4. Check the calculation you need, then click OK. The result of the calculation appears below the table.
5. Click the result of the calculation, then select the Define As Variable command on the Formula menu. The Define As Variable dialog box appears.
6. Check Evaluate the formula in its context.
7. Enter a name for the variable, click OK, then click the tab of the initial report.
8. Click the cell where you want the calculation to appear, then select the Variables command on the Format menu. The Variables dialog box appears.
9. Select the variable you created, then click Insert or Replace.

#COMPUTATION in cumulative aggregations #

The expression “cumulative aggregations” refers to any aggregation containing a running aggregate function such as RunningMax or RunningAverage. In reports, cumulative aggregations let you answer questions such as “What’s the running percentage of revenue per city for each year?”.

Description of #COMPUTATION in a cumulative aggregation #

#COMPUTATION can occur in cumulative formulas where a reset context is defined. A reset context consists of a dimension, and resets a running calculation to zero when the value of the dimension changes.

#COMPUTATION occurs in cumulative aggregations for the following reason: The reset context is not included in the output context.

Example #COMPUTATION resulting from a running sum with a reset context #

You’ve displayed running totals per country per year, and reset the calculation per country. The formula is as follows:

=RunningSum(<Revenue>;<Country>)

You now set a break on Year, because you want to display the running total for each year. You copy the formula from the running total column and paste it in the break footer. #COMPUTATION appears.

Why? Because the reset context in the formula you pasted is Country, but you placed the formula in the footer of the break on Year. Thus, the reset context (Country) is not in the output context (Year).

To fix this error, you need to display the Formula Bar and change the reset context from Country to Year. The winning formula is:

=RunningSum(<Revenue>;<Year>)

Alternatively, you could set the break on Country, but you would obtain a different result.

Solution for #COMPUTATION in a cumulative aggregation

To avoid #COMPUTATION in a cumulative aggregation, the reset context must be included in the output context. In other words, the dimension or dimensions after the semi-colon (;) in the formula must also be listed after the operator (In, ForEach or ForAll) on the right of the formula.

Reminder: If no output context is defined in the formula, the calculation is made using the local context.

To fix your formula:

1. Display the Formula Bar (Formula Bar command, View menu), then click the cell containing the formula.
2. Check that the dimension in the reset context is also specified in the output context. The following table will help you determine this:

3. Edit the formula in the Formula Bar, then press Enter.

#COMPUTATION in non-aggregate formulas #

#COMPUTATION can occur in formulas that do not contain aggregate or running aggregate functions.

Description of #COMPUTATION in a non-aggregate formula #

In non-aggregate formulas, #COMPUTATION occurs because the output context is not included in the input context.

Example #COMPUTATION caused by a conditional formula in a break footer #

You can use an IF THEN formula to set a condition for displaying data in a report. The following formula

= If (<Year>=“FY95”) Then <Revenue>

displays the measure, Revenue, only when 1995 is also displayed. In the report illustrated here, the formula is inserted in a break footer, and this causes #COMPUTATION.

Why? The input context required to display revenue for 1995 is Year, whereas the output context of the break footer is Resort. To fix this error, you add Year to the output context using the ForEach operator:

=(If (<Year>=“FY95”) Then <Revenue>) ForEach <Year>

Finally, you add Max (or Min) to return the single value you need:

=Max((If (<Year>=“FY95”) Then <Revenue>) ForEach <Year>)

BUSINESSOBJECTS now displays Revenue for 1995 in the break footer.

Solution for #COMPUTATION in a non-aggregate formula

You must:

1. Edit the formula so that the output context is included in the input context, as described in the example above.
2. Add Max or Min to the beginning of the formula, followed by an opening parenthesis.
3. Add a closing parenthesis at the end of the formula, then press Enter.

#MULTIVALUE #

This section provides descriptions, examples and solutions for #MULTIVALUE in BUSINESSOBJECTS:

• #MULTIVALUE in aggregations such as maximum revenue per year
• #MULTIVALUE in break headers and footers
• #MULTIVALUE in a cell at the section level.

#MULTIVALUE in aggregations #

The expression “aggregations” refers to any calculation containing an aggregate function such as Sum, Count, Min, Max etc. In reports, aggregations let you answer questions such as “What is the minimum revenue per city for each region?”. This section provides a description of why #MULTIVALUE occurs in aggregations, with an example, and offers a solution.

Description of #MULTIVALUE in an aggregation #

#MULTIVALUE occurs in aggregations because the output context is not included in the local context.

Example #MULTIVALUE in an aggregation #

The aggregation

=Sum(<Revenue>) In <Year>

was placed in the column on the right. BUSINESSOBJECTS makes the calculation on the local context (i.e., the dimensions in the table, Region and City) but the formula contains the output context (Year). In one column, you can calculate revenue per city per region, or revenue per year, but not both! #MULTIVALUE means what it says: the calculation cannot return multiple results in one column.

Solution for #MULTIVALUE in an aggregation

To avoid #MULTIVALUE in an aggregation, the output context must be included in the local context. In other words, the dimension(s) specified on the right of the formula after In or ForEach must be present. To fix your formula:

1. Display the Formula Bar (Formula Bar command, View menu), then click the cell containing the formula.
2. Check the following questions:
   Does the output context contain dimensions that are not in the block or section in which the formula is inserted? For example, if the following formula appears in a block that does not contain Year, you’ll get a #MULTIVALUE:

_=Min(<Revenue>)In(<Year>)

_ Does the output context contain more dimensions than the local context? For example

_=Min(<Revenue>) In(<Region>,<City>)

_ returns #MULTIVALUE in a table containing only one of these dimensions.

3. Edit the formula in the Formula Bar, then press Enter.

#MULTIVALUE in break headers and footers #

#MULTIVALUE can occur when you insert a variable in a break header or footer in a table or crosstab.

Description of #MULTIVALUE in a break header or footer #

A break splits up the values of a variable and thus enables you to make calculations. By default, any column or row header is a break header, so your tables and cross tabs have break headers even if you have applied no breaks.

A break footer is a cell at the bottom of each value of the break. Users typically display text or calculations such as running totals in break footers. #MULTIVALUE occurs in a break header or footer:

Example #MULTIVALUE in a break footer #

Imagine a table that shows the running total revenue for two customers. When you insert Age in the break footer, BUSINESSOBJECTS returns #MULTIVALUE: This error occurs because variables with a 1:1 relationship, i.e., Customer and Age, are inserted at the same break level. By default, a break is based on one variable only.

Solution for #MULTIVALUE in a break header or footer

You fix this problem by including the variable from the header or footer in the break definition. Here’s how to do it:

1. Click inside the table or cross tab containing the break, then select the Breaks command on the Format menu. The Breaks dialog box appears.
2. Click the icon of the break concerned, then click Edit. A dialog box listing all the variables in the report appears.
3. Click the check box next to the variable you want to display in the break footer (Age in the example on page 49), then click OK.
4. Click OK in the Breaks dialog box. BUSINESSOBJECTS displays the variable in the break footer.

Note! If #MULTIVALUE appears in a column or row header where no break has been applied, you can fix the error by applying a sort. In this case, you cannot add the variable to the break definition, as in the procedure above, because the column or row header corresponds to the global break; you cannot add variables to the global break in BUSINESSOBJECTS.

To apply the sort:

1. Click the cell containing the error.
2. Select the Sort command on the Insert menu.

#MULTIVALUE in a cell at the section level #

Description #

A report section displays data in a master cell and in a block or blocks. If you use two variables at the section level, #MULTIVALUE can occur.

Example #MULTIVALUE in a section containing Name and Address #

You want to display customer names and addresses in a section, and the customers' and revenue details in a table. You build a table containing Customer, Address, Invoice Date and Revenue, and then drag Customer out of the block to create a section.

The next step is to drag Address out of the block, and drop it next to Customer. What you get is a #MULTIVALUE.

Why? Because by default, there is only one master variable per section. When you place a second variable at the section level, BUSINESSOBJECTS thinks “ah, multiple values occur here” so returns #MULTIVALUE.

Solution for #MULTIVALUE at the section level #

You can fix #MULTIVALUE at the section level by applying the Min or Max function, which forces BUSINESSOBJECTS to display only one value. This solution works great for master variables with a 1:1 relationship, such as Customer and Address (unless your customers have more than one address). If the variable returning #MULTIVALUE contains more than two values, you will only be able to display the first and last of these by applying Min or Max.

1. Click the master cell displaying #MULTIVALUE.
2. In the Formula Bar, type Min or Max after =, then add parentheses, like this:

=Min(<Address>)

3. Press Enter.

Description #

Simply means that the cell is not wide enough to display the data it contains in full.

Solution #

Double-click the cell’s right border. BUSINESSOBJECTS widens the cell to auto fit the data.

#DIV/0 #

Description #

Occurs when a formula performs a division by 0. if one of the values in column A = 0 and the corresponding value in Column B = 5 and when I divide 5 by 0 I get a “#DIV/0” in the calculated column. I just want to show a zero in this case. For example, the formula

=<Revenue>/<Quantity Sold>

returns 20 if Revenue is 100 and Quantity Sold is 5. But if Quantity Sold is 0, then the result is #DIV/0.

Solution #

Using an IF THEN ELSE statement, you can set up a value or text, e.g., “No Sale”, which will appear when a division by zero occurs. To do this:

1. Switch on the Formula Bar command on the View menu.

2. Click inside the cell where #DIV/0 appears.

3. Write the following formula in the Formula Bar:
   =If IsError (<VariableName>) Then “No Sale” Else (<VariableName>)

4. Press the Enter key.

Tip: You can use an IF THEN ELSE statement such as the one in the above procedure to return default values for errors other than #DIV/0.

#ERROR #

Description #

This error occurs when the definition of a formula or a variable within a formula is incorrect. For example, the formula that returns percentages based on a measure, such as

=<Nb Customers>/Sum(<Nb Customers>)

returns #ERROR if the measure, in this case Nb Customers, itself contains an error.

Solution #

You need to break down the formula into its component parts in order to find which part contains the error. Here’s how:

1. Insert a new cell in the report (Cell command, Insert menu).
2. Select the cell containing the error, then select Copy on the Edit menu.
3. Select the new cell, then select Paste on the Edit menu. The formula containing the error appears in the new cell.
4. Click the new cell, then in the Formula Bar, select one part of the formula.
5. Copy and paste the selection into the cell where the error first occurred, then press Enter.
6. Repeat this step until you find the part of the formula that contains the error.
7. Fix the error, then paste the whole corrected formula back into the cell where the error first occurred.

#IERR #

Occurs in complex “formulas within formulas”. The three common causes are:

• Formulas combining measures and dimensions, where a dimension is missing from the calculation context
• Aggregations containing multiple formulas
• Formulas with complex WHERE clauses.

Tip: When you make calculations by combining formulas, #IERR may occur because the formulas within the formulas contain errors. If none of the solutions in this section get rid of #IERR, try breaking down the formula into its component parts, and test each one.

#IERR in a formula combining measures and dimensions #

Description #

BUSINESSOBJECTS supports aggregate formulas that contain both dimensions and measures. For example, the following formula displays revenue for customers called Prince:

=If(<Customer>=“Prince”) Then <Revenue>

BUSINESSOBJECTS qualifies any formula containing a measure (e.g., Revenue) as a measure. This qualification requires that all dimensions are present in the calculation context - the local context if none is specified. #IERR may occur when a dimension required to compute the formula is missing from the context.

Solution #

You add the missing dimension to the context using the ForEach operator, and apply the Sum function. So, if

=If(<Customer>=“Prince”) Then <Revenue>

returns #IERR, the formula you need is as follows:

=Sum((If(<Customer>=“Prince”) Then <Revenue>) ForEach <Customer>)

#IERR in an aggregation containing a complex formula #

Description #

An aggregation such as Min, Max etc., used on a formula that already contains a formula, may produce #IERR.

Solution #

The trick is to turn the formula within the formula into a variable, then rewrite the whole formula using the new variable.

Remember that a variable is a formula with a name. So, once you have made your variable, to include it in your formula, all you have to do is write its name, rather than a complex formula within a formula. Here’s an example.

Example Solving #IERR by turning part of a formula into a variable #

You want to know the week your top ten customers placed their first order. In a table containing the list of customers, you add a column and insert the following formula:

=Min(Week(<Order Date>))

If #IERR occurs, the first thing to do, using the Variables command on the Format menu, is to create a variable called WeekOrderDate from the formula

=Week(<Order Date>)

Then, rewrite the original formula using the new variable, as follows:

=Min(<WeekOrderDate>)

Please note that the original formula may work. The purpose of this example is to show how to fix #IERR should it occur in similar formulas.

#IERR in a formula using WHERE #

Description #

The WHERE operator lets you specify values of a dimension to include in a calculation. For example

=(<Revenue>*2) WHERE (<Customer>=“Prince”)

shows revenue at 200% for customers named Prince. #IERR can occur in WHERE clauses that contain complex formulas.

Solution #

The trick is to turn conditions specified after WHERE into variables. Then, you can rewrite the whole formula, using the variables instead of the original formulas in the WHERE clause.

This is the same solution as for #IERR in an aggregation containing a complex formula. Refer to “Solving #IERR by turning part of a formula into a variable” for more information.

Prevent losing leading zeroes when exporting data #

Description #

How can I prevent losing leading zeroes when exporting data to Excel? For example 000232 becomes 232.

Solution. #

One thing you can do to prevent the Description of losing the leading zeros is to save your file as *.txt from BO and then open the *.txt file with Excel and set the column as Text in the import Wizard.

Only title on the first page #

Description #

I have a six-page report but the title only prints on the first page. How do I get the same title to repeat on every page?

Solution. #

If you go into the “View” menu and choose “Page Layout” your report will now have dotted lines showing the margins, header and footer of your report. Anything you put in the header or footer of the report will repeat on every page.

Avoid duplicate prompts on the same condition #

Description #

I have a report with multiple queries, which contains prompts on the same condition. How do I avoid duplicate prompts on the same condition?

Solution #

When you create prompts in multiple queries use the same wording for the prompt and you will only be prompted once. You can also use the “Show List of Prompts” option instead of “Type a New Prompt” to select an existing prompt message. You will be prompted once and the value will be sent to both queries.

Wrap in a formula string #

Description #

How do you force a wrap in a formula string?

Solution #

Use the Char() function with the parameter set to 10.

Example: =“Sales Report”+Char(10)+“as of “+FormatDate(CurrentDate() ,“Mmm dd, yyyy”).

If you have been using Char(13) to accomplish the same thing you are better off using Char(10) since Char(13) will not work if you publish the reports out to WebIntelligence users.

Apply filter on a table within my report #

Description #

I need to apply two filters on a table within my report. The Description is that when I apply a filter BusinessObjects will automatically filter on the first condition “AND” the second condition. I need to have the first condition “OR” the second condition met. For example, show me all Leather products or products that had at least $500 in sales.

Solution #

The solution is to create a logical statement that incorporates the two filters and then filter on the logical statement. Create a new field and build the following formula to select records that meet the initial criteria: If (Line=“Leather” or Revenue>500) Then “Y” Else “N”. Now all you have to do is apply a filter on the new field where the value=“Y”. Finally you can hide this field from the Table Options screen.

Start on a new page for each section #

Description #

The report I’m working on has more then one section. I want each section to start on a new page and have each section’s page number start at 1.

Solution #

On the menu bar select Format -> Section and choose “Start on a new page” box. Add a new cell to the report. In the cell create the following formula

= PageInSection(1). Select Format -> Cell, choose “Repeat on Every Page” box. All page numbers will be reset to 1 in the beginning of the next section.

UserResponse() #

Description #

When I use the UserResponse() function to return numeric parameters from a prompt, I am not able to reference those numbers in a formula. For example, if I prompt a user for a minimum revenue amount I cannot reference that number in the following formula. =UserResponse(“Query 1”,“Minimum revenue amount”)*50%

Solution #

The reason is that UserResponse() is a character function and will return the prompt value as a character. In order to use these numbers in calculations you must first convert them to numbers using the ToNumber() function. The following formula will work great: =ToNumber(UserResponse(“Description Query 1”,“Minimum revenue amount”))*50%

Filter on a negative #

Description #

I want to filter negative values from my report dynamically.

Solution #

First you need to add a new variable < Filtered Expense > using Sign() function:

= Sign(< Expense Amount >). This function return a number that represent a sign of your numeric object, 1 for positive, -1 for negative, 0 for zero. Go to Format -> Filters -> Global. Click on Add. Select your new variable. Select 1. Now you can refresh a report and you will always have filter on positive values.