Scientific Number Formatting
-
I run the MDX query from a SQL stored procedure. The results that I get sometimes return in the scientific format like this: 8.5652588276452779E-2 or 8.2360569935143943E-3
If I pass the results directly to the ASP page or Crystal reports that's how they are displayed.
I need to display these numbers in a 2 decimal format: 0.09 and 0.01
Right now we implemented a DLL to format the numbers.
Is there a way to pass them to the report directly? There has to be an easier way.
Edited by - ikorenbl on 09/12/2003 10:25:08 AM
-
Have you tried formating the values within the measure properties (ie in the cube/model)? This would be the first place to start, this will ensure that the formatted value returned will always be of the format you require.
HTH,
Steve.
Steve.
-
Thanks for your reply.
I tried to find an MDX function that would do that, but was unsuccessful. Here are some more details about this problem.
What happens is the data comes back from the cube in the nvarchar format which I cannot immediately convert to decimal or numeric. I'm forced to use a temp table and convert into varchar(100) first. Meanwhile, the results still show like 8.2360569935143943E-3
When I try to convert this column to decimal, I get the following error message on the first row that has the number in the scientific format:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Moreover, when I try to pass these numbers as nvarchars to Crystal and use CDble() function, it produces an error. I need to format it before I pass to Crystal or ASP.
-
Not sure I follow you re: formatting. Did you go to the actual cube itself (ie using Analysis Manager -> Edit Cube) and set the format of the measure/s in question? This shouldn't require an MDX function to format them, there is even a drop down to select some standard formats (currency etc).
HTH,
Steve.
Steve.
-
You can also use any VBA or Excel functions in calculated measures
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
Steve,
I tried to set the formatting within the cube - Cube -> Edit. That works only within the cube. I can get this data to show the way I want to.
But I'm running an MDX query using the SQL stored procedure. When I get the resultset back in QA, all the formatting is lost and the numbers show as 8.2360569935143943E-3
Does it make sense?
-
quote:
But I'm running an MDX query using the SQL stored procedure. When I get the resultset back in QAWhat stored procedure are you using? The only way I can think to execute MDX from QA is to pass a string to something that used sp_OA_ methods to create a cellset and flatten it, which is an odd way to do it.
Could you paste some of the code you are using to access your MSOLAP server here?
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
select @mdxstr = 'select * from openquery(linked_server_OLAP, "' + @your_mdx + '")'
execute (@mdxstr)
-
Cool, but boy thats ugly. You're getting the same resultset as if you just got a OLEDB recordset object straight from MSOLAP, which is what I guess it's doing
I have a similar problem working with OLAP in C#, as there is no ADOMD.NET as yet (and interop of this .dll leaks far too much for enterprise work) and have ended up altering my MDX to produce a flat rowset that I then have to programatically pivot back.
ouch!Unless you are tied to using openquery I'd use a ADOMD.CellSet instead, as it gives you a lot more control, including cell(x,y).value vs cell(x,y).formattedvalue, which I think would fix your problem.
Otherwise create a calculated member in the cube editor using VBA functions to format. You can even create your own functions in VB or C++ and call them if you register them, although that might be overkill in this case.
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
Depending on the requirement, if you're into using beta s/w, the beta for ADOMD.Net is out, which would remove any interop issues.
The whole pass through query thing works a bit strangely. A valid piece of MDX that allows formatting of returned measure (with member [measures].[bob] as '[measures].[store cost]' , format_string = '#,##0.#0' SELECT {[measures].[bob]} ON 0 FROM [Sales]) in MDX sample app doesn't format when run thru the linked server.
Steve.
-
If you're using "old"/classic asp to produce reports, why not use adomd directly to query the AS server?
Steve.
-
quote:
if you're into using beta s/w, the beta for ADOMD.Net is out, which would remove any interop issues.Yeah, but it wasn't a year ago when I really needed it! Is ADOMD so far down MS list of priorities that they leave it two years! I guess so
I think it's going to come into its own with Yukon, but for now everything seems dependant on XMLA, which has the (massive) overhead of HTTP transport, great for distrubuted apps but rubbish for our IIS cluster and OLAP cluster in the same room
quote:
A valid piece of MDX that allows formatting of returned measure ... in MDX sample app doesn't format when run thru the linked server.Thats odd. Maybe it just always gives the underlying data? MSOLAP is OLEDB compliant and so has to return something broadly similar to a rowset, but it doesn't have to do it well as everyone is expected to use ADOMD
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go.
ouch!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply