October 15, 2009 at 4:46 am
Dear All,
I have a trouble with filtering out members in MDX implementing dynamic security and need quite a good help badly.
I have the following dimensions:
- Product
- Employee
And 2 measure groups:
- Sales
- Employee_Product
Employee_Product is based on a fact table which contains data on permissions for a specified employee to view sales of a specified product.
==================================================================
This query works properly:
select
[Measures].[Employee_Product_Count] on columns
, non empty(
filter(
[Employee].[EmployeeId].members
, [Employee].[EmployeeId].currentmember.membervalue = username
)
) on rows
from [Sales]
and returns only a single row for the username.
===================================================================
This one also works properly:
select
[Measures].[Sales] on columns
, non empty(
filter(
[Product].[ProductID].members
, [Measures].[Employee_Product_Count] > 1
)
) on rows
from [Sales]
and returns only products with records in Employee_Product.
===================================================================
However combining both doesn't work correctly:
select
[Measures].[Sales] on columns
, non empty(
filter(
[Product].[ProductID].members
, [Measures].[Employee_Product_Count] > 1
and [Employee].[EmployeeId].currentmember.membervalue = username
)
) on rows
from [Sales]
and returns empty set.
Perhaps I need some specific relation between dimensions and/or measure groups.
October 16, 2009 at 8:43 am
I am not quite sure yet but after setting relationship between [Sales] and [Employee] as Many-to-Many simply this one works properly:
select
[Measures].[Sales] on columns
, non empty(
[Product].[ProductID].members
) on rows
from [Sales]
where
filter(
[Employee].[EmployeeId].members
, [Employee].[EmployeeId].currentmember.membervalue = username
)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply