For a OLAP based report I needed to get numbers for an employee for 4 weeks this year and the corresponding 4 weeks last year, and show it in a Reporting Services report based on a matrix. Creating the basic mdx was not the hardest part, but getting the numbers for both years was a problem. Some employees are fairly new, and haven't worked those weeks last year etc. So my result was a bit lopsided, not always returning 8 weeks altogether. And this caused a problem in the report, where I wanted to calculate the difference between this year and last year. So for an employee that had only worked this year, the difference in hours registered was 0, not say 17.
I solved the problem in 2 steps:
1): Pad the resultset from the OLAP cube with 0 instead of null (or no) values. I got a tip from Deepak Puri:
Cell Calculation [ForceNull] for '(Measures.AllMembers)' as '0', CONDITION = 'IsEmpty(CalculationpassValue(Measures.CurrentMember, -1, RELATIVE))'
This gave me 0s instead of no values, and my result set was a bit more balanced.
Unfortunately, the 0s returned seemed not to be in a numeric format, which caused some problems when I tried doing SUM() on them in my report. I got lots of nasty #Errors instead of numbers. They all went away when I added a CINT() to my calculation.
The final expression in my matrix looked like this:
=iif(InScope("matrix2_DateWeek_Year"), iif(InScope("matrix2_DateWeek_Week"), Fields!Measures_Fakturerbare_timer.Value, SUM(CINT(Fields!Measures_Fakturerbare_timer.Value))), iif(InScope("matrix2_DateWeek_Week"), First(Fields!Measures_Fakturerbare_timer.Value, "matrix2_DateWeek_Week") - Last(Fields!Measures_Fakturerbare_timer.Value, "matrix2_DateWeek_Week"), "x"))
It writes the number on the data field, does sums for row subtotals, calculates the difference between last year and this year on the column subtotal and writes X when none of these apply.