Missing values

[MKA]

Hi,

I'm creating a dimensional Framework and I can't find a solution to the following problem. I'm hoping you encountered the same problem and found a solution to it.

I'll try to explain with an example:

Year: 2009
Budget revenue: 100.000
Actual revenue:no fact records.

When I put this in a report, the following is shown:

         Budget      Actual
2009   100.000

When however I want to calculate the difference, it is also null because of the missing actual revenue value.

I know you can solve this in report studio by adding an if clause to the actual revenue measure, but I want it to be solved in FM, so then end user doesn't have to create the if clause for each report.

I tried:
  -  the missing value char (in FM and Report studio)
  -  adding the if clause in the measure in FM
  -  adding isnull and coalesce in the measure in FM
  -  adding the isnull, coalesce and even the if clause in the query subject

but nothing seems to solve this problem.

Any ideas?

Thanks a lot.

[blom0344]

A dimensional / DMR model will always be based on a relational source. You need to make sure that you solve your problem there. There is a whole set of solutions, but we need to know a bit more (examples) to make a good suggestion.

[I have solved these issues with union constructions / cross joins etc, but the real deal is to fix it in the appropriate spot]