question

Hi, I have a query which gives 4 counts using beloq mdx query. I need to implement running total for NEW_CUSTOMER_CNT, NEW_SUBSCRIPTION_CNT, FREE_SUBSCRIPTION_CNT and PAID_SUBSCRIPTION_CNT. I do not know how to achieve this. I googled and found 'WITH MEMBER' does running total but how to implement I dont know. Could anyone please help? create PROC [ssrs].[CUSTOMER_SUBSCRIPTION_MONTHLY_REPORT_BY_BU] @FROM_DT VARCHAR(50), @TO_DT VARCHAR(50), @BU_CODE VARCHAR(20) AS BEGIN IF CAST(@FROM_DT AS DATE) < CAST(@TO_DT AS DATE) BEGIN DECLARE @SQL NVARCHAR(MAX)='' DECLARE @Filter NVARCHAR(1000) = '' IF @BU_CODE <> 'All' BEGIN SET @Filter = '&[' + @BU_CODE + ']' END ELSE BEGIN SET @Filter = '[' + @BU_CODE + ']' END CREATE TABLE #TEMP_CUSSUB_CNT ( [BU] VARCHAR(20), [YEAR] VARCHAR(10), [MONTH] VARCHAR(10), [MONTH_NAME] VARCHAR(20), NEW_CUSTOMER_CNT INT, NEW_SUBSCRIPTION_CNT INT ) CREATE TABLE #TEMP_SUB_CNT1 ( [BU] VARCHAR(20), [YEAR] VARCHAR(10), [MONTH] VARCHAR(10), [MONTH_NAME] VARCHAR(20), FREE_SUBSCRIPTION_CNT INT, ) CREATE TABLE #TEMP_SUB_CNT2 ( [BU] VARCHAR(20), [YEAR] VARCHAR(10), [MONTH] VARCHAR(10), [MONTH_NAME] VARCHAR(20), PAID_SUBSCRIPTION_CNT INT, ) begin try SET @SQL =' INSERT INTO #TEMP_CUSSUB_CNT SELECT * FROM OPENQUERY ( [TV4_ANA],'' SELECT NON EMPTY { [Measures].[CustomerStatusCount], [Measures].[CustomerSubscriptionCount] } ON COLUMNS, NON EMPTY { ([dim_business_unit].[Bu Code].[Bu Code].ALLMEMBERS * [dim_date].[Calendar Year].[Calendar Year].ALLMEMBERS * [dim_date].[Calendar Month].[Calendar Month].ALLMEMBERS * [dim_date].[Month Name].[Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( { [dim_business_unit].[Bu Code].'+ @Filter +' } ) ON COLUMNS FROM ( SELECT ( [dim_date].[Date].&['+ @FROM_DT +'T00:00:00] : [dim_date].[Date].&['+ @TO_DT +'T00:00:00] ) ON COLUMNS FROM [TV4 DW] ) ) '')' exec sp_executesql @SQL end try begin catch end catch begin try SET @SQL =' INSERT INTO #TEMP_SUB_CNT1 SELECT * FROM OPENQUERY ( [TV4_ANA],'' SELECT NON EMPTY { [Measures].[CustomerSubscriptionCount] } ON COLUMNS, NON EMPTY { ([dim_business_unit].[Bu Code].[Bu Code].ALLMEMBERS * [dim_date].[Calendar Year].[Calendar Year].ALLMEMBERS * [dim_date].[Calendar Month].[Calendar Month].ALLMEMBERS * [dim_date].[Month Name].[Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( { [dim_subscription_type].[Description].&[Free] } ) ON COLUMNS FROM ( SELECT ( { [dim_business_unit].[Bu Code].'+ @Filter +' } ) ON COLUMNS FROM ( SELECT ( [dim_date].[Date].&['+ @FROM_DT +'T00:00:00] : [dim_date].[Date].&['+ @TO_DT +'T00:00:00] ) ON COLUMNS FROM [TV4 DW] ))) '')' exec sp_executesql @SQL end try begin catch end catch begin try SET @SQL =' INSERT INTO #TEMP_SUB_CNT2 SELECT * FROM OPENQUERY ( [TV4_ANA],'' SELECT NON EMPTY { [Measures].[CustomerSubscriptionCount] } ON COLUMNS, NON EMPTY { ([dim_business_unit].[Bu Code].[Bu Code].ALLMEMBERS * [dim_date].[Calendar Year].[Calendar Year].ALLMEMBERS * [dim_date].[Calendar Month].[Calendar Month].ALLMEMBERS * [dim_date].[Month Name].[Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( { [dim_subscription_type].[Description].&[Paid] } ) ON COLUMNS FROM ( SELECT ( { [dim_business_unit].[Bu Code].'+ @Filter +' } ) ON COLUMNS FROM ( SELECT ( [dim_date].[Date].&['+ @FROM_DT +'T00:00:00] : [dim_date].[Date].&['+ @TO_DT +'T00:00:00] ) ON COLUMNS FROM [TV4 DW] ))) '')' exec sp_executesql @SQL end try begin catch end catch SELECT ISNULL(A.BU, B.BU) BU, CAST(ISNULL(A.YEAR, B.YEAR) AS INT) YEAR, CAST(ISNULL(A.MONTH, B.MONTH) AS INT) MONTH, ISNULL(A.MONTH_NAME, B.MONTH_NAME) MONTH_NAME, ISNULL(A.NEW_CUSTOMER_CNT, 0) NEW_CUSTOMER_CNT, ISNULL(A.NEW_SUBSCRIPTION_CNT, 0) NEW_SUBSCRIPTION_CNT, ISNULL(A.FREE_SUBSCRIPTION_CNT, 0) FREE_SUBSCRIPTION_CNT, ISNULL(B.PAID_SUBSCRIPTION_CNT, 0) PAID_SUBSCRIPTION_CNT FROM( SELECT ISNULL(A.BU, B.BU) BU, CAST(ISNULL(A.YEAR, B.YEAR) AS INT) YEAR, CAST(ISNULL(A.MONTH, B.MONTH) AS INT) MONTH, ISNULL(A.MONTH_NAME, B.MONTH_NAME) MONTH_NAME, ISNULL(A.NEW_CUSTOMER_CNT, 0) NEW_CUSTOMER_CNT, ISNULL(A.NEW_SUBSCRIPTION_CNT, 0) NEW_SUBSCRIPTION_CNT, ISNULL(B.FREE_SUBSCRIPTION_CNT, 0) FREE_SUBSCRIPTION_CNT FROM #TEMP_CUSSUB_CNT A FULL OUTER JOIN #TEMP_SUB_CNT1 B ON A.YEAR = B.YEAR AND A.MONTH = B.MONTH AND A.BU = B.BU )A FULL OUTER JOIN #TEMP_SUB_CNT2 B ON A.YEAR = B.YEAR AND A.MONTH = B.MONTH AND A.BU = B.BU ORDER BY 1 ASC, 2 ASC, 3 ASC DROP TABLE #TEMP_CUSSUB_CNT DROP TABLE #TEMP_SUB_CNT1 DROP TABLE #TEMP_SUB_CNT2 END END