MDX Query

Hi,

I have an Dimension with name Employee and in it there is Attributes Employee ID, Employee Name, Parent Employee Id etc. I want to Reterive the Details of the Employee and their reportees. I have Given Dimension Security as the User who enters the system can see only his and his reportees details.

I need an MDX Query to Display only the details of the user who acessing the system and the employees reporting to him.

Please Help me to solve this..................

Thanks in Advance................

If you have implemented dimension data security on the Emloyee dimension then you should see only the allowed members when you drag the Employee dimension.

|||

Hi Teo,

Thanks for the reply...

I have to give the Dimesion Data Security to hide the data of others who were not reporting to the person who enters...

for Eg:

Dimension: Employee

EmpKey EmpName ParentEmpKey

1 aaa -

2 bbb -

3 ccc 1

4 ddd 2

5 eee 4

I want to give security like this....

assume that Employee ddd is logged into the system then it should displays only the details of ddd, eee

assume that Employee bbb is logged into the system then it should displays only the details of bbb,ddd, eee

assume that Employee aaa is logged into the system then it should displays only the details of aaa,ccc

assume that Employee ccc is logged into the system then it should displays only the details of ccc

To implement this i need the MDX Query to be placed in Dimension Data Security of the Role.

please help to solve this....

|||

This question should have posted to the Analysis Services discussion list. Your allowed set MDX expression should be something like:

LinkMember
(
Exists
(
[Employee].[EmpName].[EmpName].Members,
StrToMember("[Employee].[Login ID].&[" + Username + "]")
).Item(0),
[Employee].[Employees] //parent-chid hiearchy here
)|||

Thanks Teo......

This is working fine.........

But one thing for eg. the Employee is involved in sales and the report to be taken as the empolyee name and the order quantity, then if the employee has not done any sales even though he is under some employee and in the report his name is displaying..

to ur understanding:

EmpName OrderQuantity Parent Employee

aaa 1 -

bbb 2 1

ccc null 1

when selectin the employee aaa the result displaying as...

aaa 1

bbb 2

total 3

but i want the result as..

aaa 1

bbb 2

ccc 0

total 3

|||

Try removing the Non Empty clause from the query.

|||

Thanks Teo...

But I didnt add Non Empty Clause in the Query