-
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.
-
How's your ssas db look like? Not sure if anyone can help without proper info.
-
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
-
Forum Rules
|
|