Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.

Excel has sub-optimal internal security. Anything that we can give you can be
defeated (with code easily available on the net). My recommendation would be
to ensure that only source data that the sales person is entitled to see is
included in the file that you give them. Not knowing where you source data is
coming from I really can not comment on the best way to proceed from here...
--
HTH...

Jim Thomlinson


"SAM" wrote:

Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.

That is fine. I just need a way to test until we move the report to a better
secured method but currently I need to have the reports in excel to only show
data associated the sale person when they open the worksheet and cannot
access others.

I thought I could hide the employees dimension but that will remove the
current employee from the worksheet as well.

"Jim Thomlinson" wrote:

Excel has sub-optimal internal security. Anything that we can give you can be
defeated (with code easily available on the net). My recommendation would be
to ensure that only source data that the sales person is entitled to see is
included in the file that you give them. Not knowing where you source data is
coming from I really can not comment on the best way to proceed from here...
--
HTH...

Jim Thomlinson


"SAM" wrote:

Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.

There is no secure way to ditribute this file with the pivot table as the
pivot table holds the data for all of the employees. You can add whatever
protection you want but that protection is easily defeated. Perhaps you could
write code to iterate through all of the Employees and copy the sheet (values
and formats) to a new book. Name the book the employee name and save it with
each itteration. I have done something similar in the past for calculating
empoyee commissions and it worked very well. The code to iterate through each
member in a dimension is fairly easy... let me know if you want some help
with that...
--
HTH...

Jim Thomlinson


"SAM" wrote:

That is fine. I just need a way to test until we move the report to a better
secured method but currently I need to have the reports in excel to only show
data associated the sale person when they open the worksheet and cannot
access others.

I thought I could hide the employees dimension but that will remove the
current employee from the worksheet as well.

"Jim Thomlinson" wrote:

Excel has sub-optimal internal security. Anything that we can give you can be
defeated (with code easily available on the net). My recommendation would be
to ensure that only source data that the sales person is entitled to see is
included in the file that you give them. Not knowing where you source data is
coming from I really can not comment on the best way to proceed from here...
--
HTH...

Jim Thomlinson


"SAM" wrote:

Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.

Thanks Jim. Yes could you provide sample code?

I created dimension security on the Virtual Cube for the Sales dept, when I
test the role it works but it is not enforce in excel as you have indicated.

I would like to try created different worsheets for each sales person to see
only their data. Then I will see if this satisfies their request.

I hope your suggestion will help with this request until we implement a
better web report solution for them. I am doing the same that you have done,
employee's sales and commission.

"Jim Thomlinson" wrote:

There is no secure way to ditribute this file with the pivot table as the
pivot table holds the data for all of the employees. You can add whatever
protection you want but that protection is easily defeated. Perhaps you could
write code to iterate through all of the Employees and copy the sheet (values
and formats) to a new book. Name the book the employee name and save it with
each itteration. I have done something similar in the past for calculating
empoyee commissions and it worked very well. The code to iterate through each
member in a dimension is fairly easy... let me know if you want some help
with that...
--
HTH...

Jim Thomlinson


"SAM" wrote:

That is fine. I just need a way to test until we move the report to a better
secured method but currently I need to have the reports in excel to only show
data associated the sale person when they open the worksheet and cannot
access others.

I thought I could hide the employees dimension but that will remove the
current employee from the worksheet as well.

"Jim Thomlinson" wrote:

Excel has sub-optimal internal security. Anything that we can give you can be
defeated (with code easily available on the net). My recommendation would be
to ensure that only source data that the sales person is entitled to see is
included in the file that you give them. Not knowing where you source data is
coming from I really can not comment on the best way to proceed from here...
--
HTH...

Jim Thomlinson


"SAM" wrote:

Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.

Hi Jim,

I'm using this the code at the dimension level -

Filter([Employees].[Name].Members,[Employees].CurrentMember.Properties("Employee Login") = UserName
OR
[Employees].CurrentMember.Properties("Manager Login") = UserName)

"Jim Thomlinson" wrote:

There is no secure way to ditribute this file with the pivot table as the
pivot table holds the data for all of the employees. You can add whatever
protection you want but that protection is easily defeated. Perhaps you could
write code to iterate through all of the Employees and copy the sheet (values
and formats) to a new book. Name the book the employee name and save it with
each itteration. I have done something similar in the past for calculating
empoyee commissions and it worked very well. The code to iterate through each
member in a dimension is fairly easy... let me know if you want some help
with that...
--
HTH...

Jim Thomlinson


"SAM" wrote:

That is fine. I just need a way to test until we move the report to a better
secured method but currently I need to have the reports in excel to only show
data associated the sale person when they open the worksheet and cannot
access others.

I thought I could hide the employees dimension but that will remove the
current employee from the worksheet as well.

"Jim Thomlinson" wrote:

Excel has sub-optimal internal security. Anything that we can give you can be
defeated (with code easily available on the net). My recommendation would be
to ensure that only source data that the sales person is entitled to see is
included in the file that you give them. Not knowing where you source data is
coming from I really can not comment on the best way to proceed from here...
--
HTH...

Jim Thomlinson


"SAM" wrote:

Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.

Do you know how to lock one cell versus the entire worksheet?

I don't want the sales person to be able to select another sales person from
the filter on the pivot table but if I protect the entire worksheet, they
cannot filter on the time and that is needed.

"SAM" wrote:

Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.

I found that even if I unlock the desired cell wen I protect the worksheet,
it still does not provide what is needed because if they want to choose a
another time frame, pivot table has to refresh which violates the protection
worksheet.

It doesn't seem to be possible to accomplish what is needed. If you have any
ideas please let me know.

"SAM" wrote:

Do you know how to lock one cell versus the entire worksheet?

I don't want the sales person to be able to select another sales person from
the filter on the pivot table but if I protect the entire worksheet, they
cannot filter on the time and that is needed.

"SAM" wrote:

Is there a way to enforce domain in excel worksheet?

I have created a Sales report against a cube and want to enforce security
for each sales person to only see their data based on their login.