Results 1 to 3 of 3

Thread: Convert SQL script to MDX

  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Question Convert SQL script to MDX

    Pls help in converting this sql script into mdx query...

    IF (@Attribute='Category')
    BEGIN

    SELECT
    @totalSalesU_BP=SUM(SalesUnitsBP),@totalSalesD_BP= SUM(SalesDollarsBP)
    ,@AttributeSalesU_BP=SUM(CASE WHEN Attribute=@AttributeValue THEN SalesUnitsBP ELSE 0 END)
    ,@AttributeSalesD_BP=SUM(CASE WHEN Attribute=@AttributeValue THEN SalesDollarsBP ELSE 0 END)
    ,@totalSalesU_RP=SUM(SalesUnitsRP),@totalSalesD_RP =SUM(SalesDollarsRP)
    ,@AttributeSalesU_RP=SUM(CASE WHEN Attribute=@AttributeValue THEN SalesUnitsRP ELSE 0 END)
    ,@AttributeSalesD_RP=SUM(CASE WHEN Attribute=@AttributeValue THEN SalesDollarsRP ELSE 0 END)
    ,@RankU_BP=MAX(CASE WHEN Attribute=@AttributeValue THEN RANKBP_U ELSE 0 END)
    ,@RankU_RP=MAX(CASE WHEN Attribute=@AttributeValue THEN RANKRP_U ELSE 0 END)
    ,@RankD_BP=MAX(CASE WHEN Attribute=@AttributeValue THEN RANKBP_D ELSE 0 END)
    ,@RankD_RP=MAX(CASE WHEN Attribute=@AttributeValue THEN RANKRP_D ELSE 0 END)
    FROM
    (SELECT
    ProductLevel1AttributeValue Attribute
    ,SUM(CASE WHEN [Type]='BP' THEN SalesInUnits ELSE 0 END) SalesUnitsBP
    ,SUM(CASE WHEN [Type]='RP' THEN SalesInUnits ELSE 0 END) SalesUnitsRP
    ,RANK() OVER (ORDER BY SUM(CASE WHEN [Type]='BP' THEN SalesInUnits ELSE 0 END) DESC ) RANKBP_U
    ,RANK() OVER (ORDER BY SUM(CASE WHEN [Type]='RP' THEN SalesInUnits ELSE 0 END) DESC ) RANKRP_U
    ,SUM(CASE WHEN [Type]='BP' THEN NetSalesVaueinDollars ELSE 0 END) SalesDollarsBP
    ,SUM(CASE WHEN [Type]='RP' THEN NetSalesVaueinDollars ELSE 0 END) SalesDollarsRP
    ,RANK() OVER (ORDER BY SUM(CASE WHEN [Type]='BP' THEN NetSalesVaueinDollars ELSE 0 END) DESC ) RANKBP_D
    ,RANK() OVER (ORDER BY SUM(CASE WHEN [Type]='RP' THEN NetSalesVaueinDollars ELSE 0 END) DESC ) RANKRP_D
    FROM dbo.ClientTbl_SummarySalesWeekStores a WITH (NOLOCK)
    INNER JOIN dbo.ufn_RetailerWeeks(11,@Retailer,NULL,@dayweekid ) b
    ON a.RetailerKey=b.RetailerKey
    AND a.CalendarKey=b.CalendarKey
    AND (@LevelId='%' OR ProductLevel1AttributeValue IN (SELECT [value] FROM dbo.[fn_Split](@LevelId,',')))
    WHERE locationkey IN (SELECT locationkey
    FROM dbo.ClientTbl_DimStores

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    How's your ssas db look like? Not sure if anyone can help without proper info.

  3. #3
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Need Cube Structure to Write MDX ...

    To agree with my colleague, we would need to know the details of the cube structure, perhaps a great deal more, to construct a meaningful MDX query.

    Let us know if you can provide the needed information, and we'll try to go from there ...

    Thanks.

    Bill

Tags for this Thread

Posting Permissions

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