OBIEE 12c: An Analysis On An Essbase ASO Cube Returns No Results When A Grand Total Is Added And Filtered by a Single Member (Doc ID 2651982.1)

Last updated on MARCH 25, 2020

Applies to:

Symptoms

There is a dashboard prompt and an analysis on a dashboard, where the data source is an Essbase ASO cube.

See there is one column prompt, on the Supplier member, in the dashboard prompt.

See the analysis is filtered by the dashboard prompt, and returns results - Product and multiple calculated columns - in a Pivot Table view.

For example, see calculated columns with formulas like the following:
  FY Costs = (IFNULL(FILTER(<measure> USING ((<account> = ...) AND (...) AND (...)))),0)
  FY Units = (IFNULL(FILTER(<measure> USING ((<account> = ...) AND (...) AND (...)))),0) + (IFNULL(FILTER(<measure> USING ((<account> = ...) AND (...) AND (...)))),0)
  FY Costs Per Unit = (IFNULL(FILTER(<measure> USING ((<account> = ...) AND (...) AND (...)))),0) / ((IFNULL(FILTER(<measure> USING ((<account> = ...) AND (...) AND (...)))),0) + (IFNULL(FILTER(<measure> USING ((<account> = ...) AND (...) AND (...)))),0))

With no Grand Total in the Pivot Table view, results are returned when the Analysis is filtered by any value. For example:
  Select Supplier A and B in the prompt, and click Apply >> See results returned
  Select Supplier A (only) in the prompt, and click Apply >> See results returned
  Select Supplier B (only) in the prompt, and click Apply >> See results returned

With a Grand Total in the Pivot Table view, results are not returned when the Analysis is filtered by a single value. For example:
  Select Supplier A and B in the prompt, and click Apply >> See results returned
  Select Supplier A (only) in the prompt, and click Apply >> See No Results
  Select Supplier B (only) in the prompt, and click Apply >> See No Results

When a Grand Total is present, it is observed that results will be returned in all cases if each measure column has Aggregation Rule (Totals Row) set to Sum (instead of Default) in the Criteria tab > Column > Edit Formula; however, some Grand Total values (like FY Costs Per Unit) are incorrect.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.