Hi Maher,
Please find below the text from SAP Support Notes, you should be able to solve the problem based on following. (1606502 - BI 4.0: #UNAVAILABLE in Web Intelligence report using BEx query):
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 SAP BusinessObjects BI Platform 4.0 SP0.x Reproducing the Issue 1. In Information Design Tool, under Repository Resources, create a BICS OLAP 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. Run the query.
4. In the Design view, create a new variable and add it to the report block or define and add a filter, etc.
5. All measures (key figures) display #UNAVAILABLE.
6. Refresh the query, but #UNAVAILABLE is still displayed for all measures. Cause This 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):“Smart measures” are measures whose values are calculated by the database (relational or OLAP) on which a universe is based. They differ from classic measures, which are calculated from the detailed values returned by the database. The data returned by smart measures is aggregated in ways not supported natively by the Web Intelligence component of the SAP BusinessObjects Business Intelligence platform."
The caveats to using smart measures that will cause the #UNAVAILABLE error acan be summarised as follows:
Using a formula that may induce aggregation.
- Filtering on a dimension which is not in the dimension context of the smart measure (i.e. not in block).
- Applying a filter on a smart measure AND on a dimension in the dimensional context of the smart measure.
- Aggregating (or delegating) filtered data.
- 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 formula If [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.
- There is a workaround for cases which do not require aggregation: Define the formula as variable whose qualification is a measure and be sure that the used dimension is included in the block with the variable (you can hide that column for a better display).
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.
Resolution
See individual entries for possible workarounds.
Please note: some of the workarounds suggested might not work depending on the complexity of the data manipulation within the Web Intelligence report.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.