Troubleshooting WebIntelligence formulas
Formula error and information messages:
In some cases a Web Intelligence formula cannot return a value and returnsan error or information message beginning with ‘#”. The message appearsin the cell in which the formula is placed.
#COMPUTATION occurs when a slicing dimension specified in the
function is no longer available in the calculation context of theblock where the function is placed.#COMPUTATION is also related to the misuse of context operators in aformula. For more information, see the
Using Functions, Formulas and Calculations in SAP BusinessObjetcs Web Intelligence
#CONTEXT appears in a measure when the measure has a non-existentcalculation context.#CONTEXT is related to the #INCOMPATIBLE and #DATASYNC error messages,whichappearindimensionswhenablockcontainsanon-existentcalculation context.In the case of #INCOMPATIBLE the context is non-existent because thedimensions are incompatible; in the case of #DATASYNC the context isnon-existentbecausethedimensionsarefrommultipleunsynchronizeddataproviders.
Example:Non-existent calculation context in a query
If a block based on the Island Resorts Marketing universe contains theReservation Year and Revenue objects, the #CONTEXT error message
#DATASYNC occurs when you place a dimension from a different dataprovider in a block containing dimensions from another data provider, andthe two data providers are not synchronized through a merged dimension.#DATASYNC appears in all dimensions in the block and #CONTEXT in themeasures.
Example:Dimensions from different data providers in a block
If a report based on the Island Resorts Marketing universe contains dataproviderswiththeobjects(Year,Revenue)and(Quarter),ablockcontainingYear, Quarter and Revenue displays #DATASYNC in the Year and Quarter columns because the two data providers are not synchronized through amerged dimension.
#DIV/0 occurs when a formula tries to divide a number by zero, which ismathematically impossible. Zero can never appear as a divisor.
Example:Determining revenue per item
You have a report showing sales revenues, numbers of items sold and therevenue per item (which is calculated by dividing the sales revenue by thenumber of items sold).You had a very bad quarter in which you didn’t create any revenue; theRevenue per Item column returns #DIV/0 for this quarter, because theformula is attempting to divide by zero; that is, divide the revenue by zeronumber of items sold
#EXTERNAL occurs when a formula references an external function that isnot available to Web Intelligence.
#INCOMPATIBLE occurs when a block contains incompatible objects.
Example:Incompatible objects in a query
IfablockbasedontheIslandResortsMarketinguniversecontainstheYear andReservationYeardimensions,thecolumnscontainingthesedimensionsshow #INCOMPATIBLE because these objects are incompatible.
#MULTIVALUE occurs when you place a formula that returns more than onevalue in a cell that outputs one value only.
Example:Multivalue in a cell
You have a report showing Country, Resort and Revenue and you add acell to the report containing the formula [Revenue] ForEach ([Country]).This cell returns #MULTIVALUE because Country has two values in thereport: ‘US’ and ‘France’.One cell cannot display the revenues for both the US and France. Placedoutsidethetable,acellcontainingrevenuecanonlyaggregatetherevenuesin the table in some way (for example by summing or averaging them).If the report is broken into sections on Country, the formula is correct whenplaced in a section because there is only one value of Country per section.Outside a section, however, the formula still returns #MULTIVALUE
#OVERFLOW occurs when a calculation returns a value that is too large for Web Intelligence to handle. This value, in exponential form, is 1.7E308 (1.7followed by 307 zeros).
#PARTIALRESULT occurs when Web Intelligence was unable to retrieve allrows associated with a report object.If #PARTIALRESULT occurs often in your reports and you have theapprorpiate security rights, modify the Max Rows Retrieved query propertyto allow Web Intelligence to retrieve more data. If you do not have the rightto modify the query, see your Business Objects administrator.If your report contains smart measures it is more likely to display#PARTIALRESULT because smart measures require Web Intelligence toretrieve larger amounts of data than classic measures.
#RANK occurs when you try to rank data based on an object that dependson the order of values. (Objects that use the Previous() function or anyrunning aggregate function depend on the order of values.) Ranking causesthese objects to recalculate their values, which then changes the ranking,resulting in a circular dependency. Such a dependency can occur either when you use the Rank dialog box to create a ranking, or when you use theRank() function.
Example:Ranking on running average or previous values
If you attempt to rank a block on a column that contains the Previous()function or any running aggregate function, the entire block returns#RANK
#RECURSIVEoccurswhenWebIntelligencecannotmakeacalculationdueto a circular dependency.
Example:Using the NumberOfPages() function
If you place the NumberOfPages() function in a cell whose Autofit Heightor Autofit Width properties are set, Web Intelligence returns #RECURSIVEbecause the placing of this formula in an autofit cell creates a circular dependency.WebIntelligencemustknowtheexactsizeofthereportbeforeit can return a value from the function, but the size of the cell (which affectsthe size of the report) is determined by the cell content.
#SECURITY occurs when you attempt to use a function for which you donot have security rights.
Example:Using the DataProviderSQL() function
If a user who does not have the right to view data provider SQL places theDataProviderSQL() function in a cell, the #SECURITY message appearsin the cell.
#SYNTAX occurs when a formula references an object that no longer existsin the report.
Example:Referencing a non-existent object
You have a report that originally showed Year, Quarter and Sales revenue,withanadditionalcolumnshowingdifferencebetweentherevenueandtheaverageyearlyrevenue.ThisfigureisgivenbythevariableDifferencefromYearly Average
#TOREFRESH appears in cells based on smart measures when the valuereturned by the smart measure is not available. This situation occurs whenthe “grouping set” containing the value is not available in the data provider.You remove the #TOREFRESH error by refreshing the data.
#UNAVAILABLE appears when Web Intelligence cannot calculate the valueof a smart measure.This situation occurs when Web Intelligence cannot display the values in afiltered smart measure without applying a filter to the query. Because thiscarries a risk of impacting other reports based on the same query, WebIntelligence does not apply the query filter.
#ERROR is the default error message that covers all errors not covered byother error messages
Comparing values usingWeb Intelligence functions
Comparing values using the Previousfunction
function returns a comparative previous value of anexpression. The value returned depends on the layout of the report.For more powerful comparison capabilities, use the
returns a previous or subsequent comparativevalue of an expression. The value returned does not depend on the layoutof the report.
Comparing values using the RelativeValue function
Comparing values using theRelativeValue function
functionreturnscomparativevaluesofanexpression.The function returns these values independently of the layout of a report.When using
, you specify the following:•Theexpressionwhosecomparativevalueyouwanttofind(theexpressionmust be a measure or a detail of a dimension available in the block)•The list of “slicing dimensions”•The offset.The function uses the slicing dimensions, the offset, and the “sub-axisdimensions” (which are implied by the slicing dimensions) to return acomparative value. The sub-axis dimensions are all the other dimensions inthe calculation context apart from the slicing dimensions.Expressed in general terms,
returns the value of theexpression in the row which, in the list of values of the slicing dimensions,is
rows removed from the current row, and where the values of thesub-axis dimensions are the same as in the current row.
All slicing dimensions must always be in the calculation context of the blockin which the function is placed. If a slicing dimension is subsequentlyremoved, the function returns #COMPUTATION.
In this example, the RelativeValue column contains the following formula:
•The expression is [Revenue];•The slicing dimension is [Year];•The offset is -1 (the function returns the immediately previous value inthe list).
Expressed as a business question, the formula tells Web Intelligence toreturntherevenuegeneratedbythesamesalespersoninthesamequarter in the previous year.Expressed as a calculation in words, the formula tells Web Intelligence toreturn the value of [Revenue] (the expression) in the row where the valueof [Year] (the slicing dimension) is the previous value from the list of valuesof the [Year] object, and where the values of [Quarter] and [Sales Person](the sub-axis dimensions) are the same as in the current row
Slicing dimensions and the RelativeValue function
functionusesthelistofvaluesoftheslicingdimensionsto find the comparative row. The function returns the comparative value of theexpressionspecifiedinthefunctionthatis
numberofrowsawayin the list of slicing dimensions.As a result, the sort order of the slicing dimensions is crucial in determiningthe function output.
Example : Multiple slicing dimensions
the RelativeValue column has the following formula:
•The expression is [Revenue];•The slicing dimensions are ([Year];[Quarter]);•The offset is -1 (the function returns the immediately previous value inthe list)