Webi reports showing all numbers as ############

[Former Member]

Hello,

I am upgrading a BO 3.1 Webi report to BO 4.1 using Upgrade Management Tool, there was no error in the upgrade process.

However when I run the Webi report, all numbers in the Webi report are showing as ############.

When I refresh the Webi report, all numbers still are still showing ############.

What could be the reason and how could I fix the issue.

Thanks!

George

[Former Member]

I followed the suggestions to open the report in Webi Rich Client, make the column wider. Now I see the ############ are actual #UNAVAILABLE.

So the actually question is all numbers are showing as #UNAVAILABLE.

Any ideas?

Thank you very much guys!

George

[former_member202257]

Please check note 1606502 for more details as to why this occurs and how to eliminate it.

[Former Member]

Hi Shwetha,

I don't have SUser id, so is there a way I could read note 1606502?

My email: georgemeng2000@gmail.com

THanks!

George

[former_member202257]

Symptom

• You create a Web Intelligence report based on a BEx query (using the BICS connector) and during the design process, #UNAVAILABLE is displayed for all measures (key figures).
• You try refreshing the report, to no avail.
• You try using contexts for the measures, to no avail.

Environment
Reproducing the Issue
CauseThis behaviour is by design.#UNAVAILABLE appears when it is not possible to calculate the value of a smart measure because it is managed by the back-end - database-delegated.  As per the Web Intelligence 4.0 documentation Using functions, formulas, and calculations in Web Intelligence, which is available through the BusinessObjects Support portal on Service Market Place (SMP):

1. Using a formula that may induce aggregation.
2. Filtering on a dimension which is not in the dimension context of the smart measure (i.e. not in block).
3. Applying a filter on a smart measure AND on a dimension in the dimensional context of the smart measure.
4. Aggregating (or delegating) filtered data.
5. Using the OR operator in nested filters.

Below are some illustrations drawn from the product guide. 1. Smart measures and dimensions containing formulas If a formula or variable appears as a dimension in the calculation context of a smart measure, and the formula determines the grouping set required by the measure, the values of the smart measure cannot be displayed.

For example, a report contains a variable, Semester, with the formulaIf [Quarter] = "Q1" or [Quarter] = "Q2" Then "H1" Else "H2"Placed in a block, the Semester variable returns the following result:

Semester	Revenue
H1	#UNAVAILABLE
H2	#UNAVAILABLE

2. Smart measures and filters on dimensionsIf you apply a multi-valued filter to a dimension on which the value of a smart value depends, but the dimension does not appear explicitly in the calculation context of the measure, the smart measure cannot return a value and the cell displays #UNAVAILABLE.#UNAVAILABLE appears because the measure must be filtered in the report and then aggregated, but a smart measure cannot be aggregated after a report-level filter is applied. Calculating the measure would be possible by adding a query filter to the generated SQL, but this solution carries the risk of impacting other reports based on the same query.Note:

• A multi-valued filter filters on multiple values using operators such as Greater Than, In List or Less Than. You can apply single-valued filters such as Equal To without generating the #UNAVAILABLE error.

Example: A smart measure and a filter on a dimension

A query contains the Country and Resort dimensions and the Revenue smart measure. Country and Revenue are displayed in a block. If you apply a report filter restricting the values of Resort to "French Riviera" or "Bahamas Beach", #UNAVAILABLE appears in the Revenue cells.

Country	Revenue
France	#UNAVAILABLE
US	#UNAVAILABLE
Sum:	#UNAVAILABLE

If you restrict Resort to "Bahamas Beach" only, the values are displayed.

Country	Revenue
US	971,444
Sum:	971,444

3. Filters on smart measuresIf you apply a filter to a smart measure in a context where the smart measure is aggregated at different levels of detail, the smart measure returns the #UNAVAILABLE error when it is aggregated at a less detailed level.

Example: Filering a smart measure

You have a block displaying revenue by country and resort, where Revenue is a smart measure.

Country	Resort	Revenue
France	French Riviera	835,420
US	Bahamas Beach	971,444
US	Hawaiian Club	1,479,660
	Sum:	3,286,524

#UNAVAILABLE appears because the revenue in the block is aggregated by the grouping set (Country, Resort), but the total revenue in the footer, which is also impacted by the filter, is aggregated at a less detailed level than (Country, Resort).4. Smart measures and nested OR filters

Nested OR filters in which at least one of the filtered dimensions does not appear in a block generate the #UNAVAILBLE error for a smart measure in the block.

Example:

• The filter [Country] = "US" OR [Country] = "France" returns #UNAVAILABLE for a smart measure that uses the [Country] dimension if [Country] does not appear in the block.
• The filter [Country] = "US" OR [Year] = 2008 returns #UNAVAILABLE for a smart measure that uses the [Country] or [Year] dimensions if either [Country] or [Year] does not appear in the block.

[former_member202257]

The below threads may also be useful :

Additional note 1603634 - BI 4.0: #UNAVAILABLE in Web Intelligence report using BEx query when adding a variable bas...

Symptom

#UNAVAILABLE is displayed for all measures (key figures) when a variable based on a dimension is created and added to a report based on a BEx query.

Environment

SAP BusinessObjects BI Platform 4.0 SP0.x

Reproducing the Issue

1. In Information Design Tool, under Repository Resources, create a BICS connection to a BEx query.
2. Launch Web Intelligence from BI Launchpad and create a new document based on that BEx query.
3. In the query panel, drag and drop some dimensions and measures. Execute the report.
4. In the Design view, create a new report variable using a dimension.
5. Add the newly created variable to the report block.
6. All measures (key figures) display #UNAVAILABLE.
7. Refresh the query, but #UNAVAILABLE is still displayed for all measures.

Cause

This behaviour is by design.

Resolution

The following explanation has been provided by Product Group:

"Imagine the formula for the dimension returns the fist letter of the country:
All countries starting with 'A' would aggregate into a single line: but this is local aggregation and the measure are defined to be aggregated on the back end.
When you are sure that the formula will not produce aggregates (1 value of the formula <-> 1 single value for the dimension) then you can use the workaround with the detail*.
This will not be fully addressed with 4.1: only for measures that aggregate on 'Sum'. They will be no longer delegated, but aggregated locally with 'Sum'."

*Possible workaround: define the variable as a detail, and associate it to the original dimension. Included it in the block along with the original dimension, which needs to be formatted to be hidden.

[Former Member]

Hi Loic & Anuj,

It is not column width issue, the numbers are pretty small, it should have enough space to display.

Plus, it is not only one place, it is all numbers in the Webi report.

Thanks!

George

[Former Member]

Hi George

Can you try format number option after right clicking on the column.

-Anuj

[Former Member]

Hi Anuj,

I was told once it is security issue. But forgot the details.

It should not be the format issue, because there are some zero in the table.

Thanks!

George

[former_member183904]

sometimes the query objects are not aligned, click edit on webi 4.1 report to see if ts pointing to the right query. if it is. copy the webi report and then re assign it to the same query see if that works.

[Former Member]

Hi George

Please increase width of the report columns. It will resolve the issue.

-Anuj

[LepreuxLoic]

Hi George,

It could simply be that columns are not large enough to display values with font parameters.

Did you try to increase width of columns or change font size ?

If this doesn't work then you should try to create a new report with a basic table to make sure it works fine there.

Regards,

Loic