Can someone with the SP2 beta installed try the following and tell us if the formatting bug has been fixed (using the provided Adventure Works DB) ?
with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000
select [Measures].[DP] on 0,
[Reseller].[Business Type].members on 1
from [Adventure Works]
On SSAS 2005 SP1, I get scientific notation for the 2nd and 3rd rows returned by the query (as in 2.2076E-05)
This is a major issue for us as we tend to run SSAS queries from SQL Server using OPENQUERY.
For instance, the following query fails because of this bug:
select convert(money,isnull(substring("[Measures].[DP]",1,50),0))
from openquery(olapserver,'with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000
select [Measures].[DP] on 0,
[Reseller].[Business Type].members on 1
from [Adventure Works]')
This bug is not fixed in SP2 CTP 1 November.
Regards
Thomas Ivarsson
|||FYI: Results
DP
All Resellers 0.000214378
Specialty Bike Shop 2.2076E-05
Value Added Reseller 8.0309E-05
Warehouse 0.000111993
I appologize, but what is exactly the bug here ?
The results seem to be absolutely correct to me...
|||There is no bug in AS. It is merely a formatting issue. The following should work as expected.
select convert(money,convert(real,("[Measures].[DP]")))
from openquery(olap,'with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000
select [Measures].[DP] on 0,
[Reseller].[Business Type].members on 1
from [Adventure Works]')
|||It is a bug, there is no reason why the same measure should come back sometimes as a decimal, and sometimes as a real number. The problem seems to be that it refuses to round small numbers to 0.
Try formatting this measure using FORMAT_STRING="#,#" in the MDX query, and you'll find that it still comes back in scientific notation.
|||The Specialty Bike Shop rows should show 0.000022076 instead of 2.2076E-05
|||Sorry - but I disagree with you. There is a difference between cell properties VALUE and FORMATTED_VALUE. VALUE contains just, well, the value of the cell. The data type for it is VARIANT in OLEDB, and there is no formatting involved. FORMATTED_VALUE is a string which represents visual formatting of the VALUE. If you don't specify FORMAT_STRING, then the default FORMAT_STRING is used - "Standard". The formatting is actually done by OLEAUT32 function VarFormat, and it decides that if there are so many leading 0's after the decimal point, it formats using scientific notation. There is nothing wrong about it. You can specify your own FORMAT_STRING of course and dictate the rules.
So the conclusion is that there is no AS bug here - and Michael solution should work for you.
Mosha.
|||How do you explain the following:
1) Requesting 5 digits after the decimal point returns the proper formatting:
with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000, format_string="#,#.00000"
select {[Measures].[DP]} on 0,
[Reseller].[Business Type].members on 1
from [Adventure Works]
Returns:
All Resellers .00021
Specialty Bike Shop .00002
Value Added Reseller .00008
Warehouse .00011
2) Requesting no digits after the decimal point returns wrong formatting:
with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000, format_string="#,#"
select {[Measures].[DP]} on 0,
[Reseller].[Business Type].members on 1
from [Adventure Works]
Returns:
All Resellers 0.000214378
Specialty Bike Shop 2.2076E-05
Value Added Reseller 8.0309E-05
Warehouse 0.000111993
This is how formatting works - for different format strings you get different results - that's the reason why there are different format strings in the first place - so people can choose how they want the results to be formated. Note, that there is nothing special here about MSAS - any Windows application which uses standard Windows formatting functionality (starting with Visual Basic 3) - is going to behave the same.
You assumption that #,# returns "wrong" formatting is not correct. This is how #,# is designed to work. If this isn't what you want - use #,#.00000 or anything else that suits you.
|||I'd be interested to know how you come to the conclusion that #,# should return scientific notation.
Please refer to VB Language Reference at the following link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafmtuserdefinednumericformats.asp
If I wanted scientific notation, I would specify 0.00E+00
I specified #,# in the format string, and this didn't work for 2 of the 4 returned values. Did you really look at the 4 rows returned by the query I gave as an example?
|||Since the #,# doesn't specify what happens after the digital points - the OLEAUT32 decides to use scientific notation if there are more or equal than 4 leading zeros, and not scientific notation if there are less than 4 leading zeros. I thought I mentioned that already in one of the earlier replies. Using 0.00E+00 will force scientific notation always.
You can easily test this by using
with
member measures.x1 as 0.0001, format_string='#'
member measures.x2 as 0.00001, format_string='#'
select {x1,x2} on 0
from [Adventure Works]
HTH,
Mosha (http://www.mosha.com/msolap)
|||I can see your point, but I don't agree with your interpretation that #,# doesn't specify what happens to the digital point.
#,# specifies that there is NO decimal point, and that all values less that 0.5 should be rounded to 0. I have tried the following in VBA (Excel 2003): MsgBox ("*" & Format(0.00000006, "#,#") & "*"), and got "**" as a result, which means the format returned an empty string.
If I try MsgBox ("*" & Format(0.00000006, "#,0") & "*"), I get "*0*". Under no circumstances does the format decide on its own to return scientific notation.
I'm not in the office right now, so I cannot try the MDX query with "#,0" which IMO should return "0" for all 4 rows.
