Problems with FORMAT_STRING, VBA functions and NON_EMPTY_BEHAVIOR

Consider the following requirement: “We want our measure to be truncated to 4 decimal places without any trailing 0s after the decimal point and a comma as a thousands separator.”

First, let’s focus on the truncation part. If we want that to happen for a measure, we can do it through the following formula, as I have described previously:

SCOPE([Measures].[Our Measure]);
This = Fix([Measures].[Our Measure]*10^4)/10^4;
END SCOPE;

This takes care of our truncation. So far so good.

Now let’s have a look at the formatting. If we want to apply custom formatting through FORMAT_STRING for a number such as 12345.1234, which states: “#,0.####”, in order to obtain 12,345.1234 we run into a problem. The same FORMAT_STRING expression applied to 12345 gives us 12,345. – including a trailing decimal point (in our case a trailing period). There is no way to get rid of it through FORMAT_STRING. Even in the specifications for FORMAT_STRING it is pointed out that:

If the format expression contains only number sign (#) characters to the left of the period (.), numbers smaller than 1 start with a decimal separator.

This holds true for #s to the right of the period, as well.

What we can do in this case is either conditionally format the number, with some sort of a rule, which checks if the number is whole or not (I will avoid that), or we can use the VBA Format function like this:

SCOPE([Measures].[Our Measure]);
Format([Measures].[Our Measure], “#,0.####”);
END SCOPE;

This yields the correct result, so in the end, we can do:

SCOPE([Measures].[Our Measure]);
This = Format(Fix([Measures].[Our Measure]*10^4)/10^4, “#,0.####”);
END SCOPE;

That may be achieveing the result, but let’s look at perfromance of a calculated measure utilising this approach. The first thing I noticed when I implemented this is the huge increase in query processing time and the large number of 0 valued cells. It turned out that the VBA functions in MDX do not skip empty (NULL) cells. If you try Fix(NULL), you’ll get 0. So, after Fix-ing our measure, we get 0s for every empty cell in our cube. The same is valid for Format.

Next step was trying to find a way to skip these empties. I tried:

SCOPE([Measures].[Our Measure]);
This = Format(Fix([Measures].[Our Measure]*10^4)/10^4, “#,0.####”);
NON_EMPTY_BEHAVIOR(This) = [Measures].[Our Measure];
END SCOPE;

but it did not work. I still got the 0s in my result set. I suppose that it got ignored by SSAS. Because of this issue I decided to write an IIF statement like this:

SCOPE([Measures].[Our Measure]);
This = IIF([Measures].[Our Measure] = 0, NULL, Format(Fix([Measures].[Our Measure]*10^4)/10^4, “#,0.####”));
END SCOPE;

This also worked. However, now we have an IIF statement, which serves no purpose other than filtering our empty cells, because of the VBA functions’ behavior. It would be interesting if there is any other way of implementing this, avoiding the problem. It would be nice if:

1. VBA functions can skip empty cells
2. FORMAT_STRING behaves just like Format
3. NON_EMPTY_BEHAVIOR actually works with SCOPE (in queries)

Please comment if you have a solution for the problems above and let me know if you would like to see the above issues fixed (I am considering raising a feedback/recommendation issue on Connect).

Advertisements

2 thoughts on “Problems with FORMAT_STRING, VBA functions and NON_EMPTY_BEHAVIOR”

  1. Possibly a silly question, but might it be possible to do the formatting on the client side report, rather than in the MDX?I've found that reporting services format strings are more reliable than MDX ones for numeric formatting as described in your post. Excel can be configured to use a custom cell formatter which should also behave properly with the specified format string.My $0.02 only… your mileage may vary.CheersOzziemedes

    Like

  2. There is no problem if the result set would be used in SSRS – I would definitely apply more complex formatting in the report itself.However, I had the issue with Excel users. I had no control over how they configure Excel and they wanted everything to work with no involvement on their part. Seemingly most of my users prefer to think that way – they do not want to fiddle with settings…

    Like

Comments are closed.