Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: 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 doesn’t recognize FORMAT_STRING = ''''Percent'''' or its trying to return a numeric value inside a varchar. Any thoughts???

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've got a real number expressed in scientific notation. The 9.4993517502742597E-2 is exactly the same thing as 0.094993517502742597

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •