Execute MDX from SQL Proc

------------------------

Can someone shed some light? I have searched hi and low for an answer to my dilemma. I have an mdx query that works fine in my olap environment but when I try to run the same query from sql 2000, one of my calculations comes back as a long int and includes (E-02) at the end. Can you please review my store procedure and provide some insight?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE GetWeeklyTrendData

@.Level int,
@.year int,
@.week int,
@.PrintMDX int = 0

AS

SET NOCOUNT ON

DECLARE @.MDX varchar(4000),
@.CubeQuery varchar(4500), @.Leaf int, @.Tier int

BEGIN
SET @.MDX = '''
WITH
MEMBER [Measures].[OrgName] As ''''[GL].CurrentMember.Name''''
MEMBER [Measures].[LevelSKey] As ''''[GL].CurrentMember.Properties("Key")''''
MEMBER [Measures].[OrgType] As ''''Iif(IsLeaf([GL].CurrentMember), 1, 0)''''
MEMBER Measures.Lookp AS '''' LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''

MEMBER Measures.Trend AS '''' Measures.Customers / LookupCube("cubeName", "(Measures.Customers, " + [GL].CurrentMember.UniqueName + "," + [Week].CurrentMember.UniqueName + ")" )''''
, SOLVE_ORDER = 1, FORMAT_STRING = ''''Percent''''

SELECT
{
CrossJoin
(
{ [Week].[' + CAST(@.year AS VARCHAR) + '].[Week ' + CAST(@.week AS VARCHAR)+ '].Lag(12) : [Week].[' + CAST(@.year AS VARCHAR) + '].[Week ' + CAST(@.week AS VARCHAR) + '] },
{ Measures.OrgName, Measures.LevelSKey, Measures.OrgType, Measures.Customers, Measures.Lookp , Measures.Trend
}
) } ON COLUMNS,
NON EMPTY
{ [GL].&[' + CAST (@.Level AS VARCHAR) + '].Children } ON Rows
FROM [cubeName]
WHERE

( [Monthly Income].[All Monthly Income].[Y],[Contact Events].[All Contact Events].[Y],
[Other Financial].[All Other Financial].[Y],[Bankers Notes].[All Bankers Notes].[Y],
[Employer Name].[All Employer Name].[Y] )
'''
END
SELECT @.CubeQuery =
'
SELECT *
FROM OPENQUERY(ReportDBMart, ' + @.MDX + ')'
IF @.PrintMDX = 1
PRINT 'MDX: ' + @.MDX

EXEC(@.CubeQuery)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The following is the value that I get back.
9.4993517502742597E-2 and is derived from Measures.Trend.

My guess is that it doesnt recognize FORMAT_STRING = ''''Percent'''' or its trying to return a numeric value inside a varchar. Any thoughts?You've got a real number expressed in scientific notation. The 9.4993517502742597E-2 is exactly the same thing as 0.094993517502742597

-PatP