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
 AS sum(
[Measures].[Internet Sales Amount]),
FORMAT_STRING = "Standard",
VISIBLE = 1 ; 


As an immediate response I gave the following solution 

WITH MEMBER [Measures].[Sum from start]
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.

Amish shah

No comments:

Post a Comment

Popular Posts