Running Total handling Null Values


I have written an artilce on running total in SSAS 
Regarding this recently I got a problem by mail for running total in SSAS by one developer from U.K.


Problem  :
I work with Internet Sales Amount/Fiscal hierarchy in Date Cube dimension
When dragging across Running Total I have all the member of the Date/Fiscal Hierarchy showing up, even if there is no sales amount
---------------------------------------------------------------------------------------------
CREATE MEMBER CURRENTCUBE.MEASURES.RunningTotal
 AS sum(
{NULL:[Date].[Fiscal].CurrentMember},
[Measures].[Internet Sales Amount]),
FORMAT_STRING = "Standard",
VISIBLE = 1 ; 

 


As an immediate response I gave the following solution 

WITH MEMBER [Measures].[Sum from start]
AS
iif (isempty(([Measures].[Internet Sales Amount],[Date].[Calendar].
CurrentMember)) ,null,SUM ({NULL:[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount]))

SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Month].members ON 1
FROM [Adventure Works]

 
Only one point to take care is if Internet Sales is not available in past for some time periods that it will so null for that time periods, else its a good and neat and simple solution for proper data.
 
 

Cheers 
Amish shah
 

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts