topic Total with Missing Values in calculated measure on OLAP cube, specifically at the top rollup level in SAS Web Report Studio

Total with Missing Values in calculated measure on OLAP cube, specifically at the top rollup level

ccaulkins9 — Wed, 01 Mar 2017 22:58:29 GMT

Not sure if this is necessarily the place to look for MDX help, but still worth a shot as I noticed a user question back in 2011:

(user is still using SAS 9.2 so OLAP Cube Studio 4.2)

An OLAP Cube has a calculated measure which contains ClosingPeriod MDX function to calculate a rollup from the date level to the National level, i.e. detail to summary level or drilldown to rollup. the calculation returns with values at the drilldown/detail but returns missing value when presented as the National rollup level.

Any experience with this particular error is welcome.

Re: Calculations are showing missing at "level 1" of time dimension, when filter applied

ccaulkins9 — Thu, 02 Mar 2017 20:35:33 GMT

In researching even further it turns out that the "ClosingPeriod" may not have much to do with the "error" that I tried to present here. As the change in subject would seem to suggest, there is a filter applied to the Web Report to show totals/sums/counts for a specific month. When filter is removed, all months appear in the report and calculations are shown, no problem. I think I may try to make yet another post out of this.

Re: Calculations are showing missing at "level 1" of time dimension, when filter applied

ccaulkins9 — Tue, 07 Mar 2017 15:03:58 GMT

Final wrapup observation: at the end of the day, the time dimensions were overlapping (interview period v. month/day) and OpeningPeriod gave the complete aggregate 100% of the time vs. ClosingPeriod in the MDX calculated member on the OLAP Cube.