This is what I did:

1) I create a database view on the SQL server (very long and complex query)
2) I open Excel, create a PivotTable and indicate "External Data Source"
then click "Get Data", choose a DSN, select View, bring all fields, Filter by
"Company = Microsoft"

I want to distribute that Excel, but the problem is that anyone can edit the
Filter and get all the data.

Once the Filter (where clause) is defined inside Excel, I want to be able to
password protect that screen so no User can change it.

Regards,

Javier


"Dick Kusleika" wrote:

Javier

You have a pivot table based on external data, or you have an external data
table in Excel and pivot table based on that?

How are the legitimate changes to the WHERE clause being made now? Are you
only concerned that all the records may be shown, or do you want to limit
wha't shown to one of a few criteria?

I think this can be done, but depending on how you answer the above, I don't
know how easy it will be. You should be able to check the WHERE clause in
code to make sure it's only returning what you want it to return.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Javier wrote:
I'm geting external data for a Pivot Table using the 'Get External
Data' option, this works fine and retrieves the information from a
SQL 2000 database view.

In order to don't retrieve "all" the records, I use a filter (ie.
where Company equal to Microsoft), that way the User only sees that
amount of data that he is supposed to see.

The problem I have is that a power User that knows excel will be able
to edit/remove that filter and see "all" data.

Of course I can create different database views (ie. one for each
filter) and apply proper access rights to that view (ie. trusted
connection) to the proper User.

But that means high maintenance.

Is there a way to password protect (or something like that) the actual
query/filter so no power User can see/change it ?

Or is there another way that you can suggest to approach this problem.

Please advise.

Thanks,

Javier