| Raghu 2006-03-28, 11:30 am |
| Thank you for the reply. Unfortunately, me measures list is quite lengthy. In
that case I would have to check every measure before deciding whether to put
null values for EmployeeHierLevelNum
or other aliased measures. Otherwise
the logic would be flawed and I'd endup with bad data (Supposing I had Order
Count as another measure. If I only checked Dollar Sales, and Bob had Dollar
Sales null but a valid value for Order Count, EmployeeHierLevelNum
would show
null when in fact NON EMPTY clause would not exclude this row.)
Another drawback of this approach is that I would have to repeat the logic
for every aliased measure, and every time my measures list changes, I'd have
to update all my WITH MEMBER clauses.
I was hoping if there were some way to change my query so that I wouldn't
have to adopt these laborious methods, yet get the query working.
Raghu
"Deepak Puri" wrote:
> Hi Raghu,
>
> One way to filter out rows wih no data is to set the Dimension Property
> measures to null when there is no data:
>
> WITH
>
> MEMBER [Measures].& #91;EmployeeHierLeve
ls] as
> 'iif(IsEmpty([Measures].[Dollar Sales]), NULL,
> [Employee Dim].[Employee hier].LEVELS.COUNT)'
>
> MEMBER [Measures].& #91;EmployeeHierLeve
lNum] as
> 'iif(IsEmpty([Measures].[Dollar Sales]), NULL,
> [Employee Dim].[Employee hier].CURRENTMEMBER.LEVEL.ORDINAL)'
>
> MEMBER [Measures].& #91;NumOfDescendants
] as
> 'iif(IsEmpty([Measures].[Dollar Sales]), NULL,
> Count([Employee Dim].[Employee hier].CHILDREN))'
>
> SELECT
> NON EMPTY { [Measures].& #91;EmployeeHierLeve
ls],
> [Measures].& #91;EmployeeHierLeve
lNum], [Measures].& #91;NumOfDescendants
],
> [Measures].[Dollar Sales] } ON COLUMNS,
>
> NON EMPTY { (DESCENDANTS([Employee Dim].[Employee hier].[Level 02])) }
> ON ROWS
> FROM [Customer Sales Cube]
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***
>
|