Using Pivot table data outside of an SQL Server machine

Hi all,

I have on an SQL server an excel with connection to the server. The connection can be made only through the SQL Server machine.

I would like to be able to use the excel file with the pivot table data on another machine.

The problem is that when trying to collapse/expand a field in the table on another machine, the excel tries to connect to the server and update the data - I get a message saying "Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the <SQL Server Name> server."

How can I make it work outside the SQL server machine?
Is there a way to disable the connection to the server on every collapse/expand action so it will work offline (Actually that can save time for me also when working on the SQL Server machine itself)?


Thank you,

Moty

I will appreciate any response to the question - Even an answer saying that such a possibility doesn't exist, so I will stop looking for a resolution for this issue.


Thank you,

Moty

Why can't you set up the Sql server to be accessible from other machines?

Because of security reasons, I can't set up the Sql server to be accessible from other machines.


Again - I will appreciate any response to the question - Even an answer saying that such a possibility doesn't exist, so I will stop looking for a resolution for this issue.


Thank you,

Moty

Well then your only option is to store the data in the workbook, then build the pivot table based on that. Would that work?

Hi, I would have taken my data into access and built a pivot table using external data source (i.e. my access file) and then taken my Pivot table on any machine to work on it.
Best Regards/VKS

Thank you Kyle and VKS for your answers.

Kyle - That's what I currently do, but I would like to save time and not do that in that way.
VKS - I'm not familiar with Access.

I don't really see how access would help.

moty, so currently you have a query table set up in excel and just refresh it - then just refresh the pivot table? If so, I'm not really sure how you expect to save time, it's a single button click

@ Kyle : Its not really the access. Idea was to use external data to create the pivot table so that it can be shared and opened on any machine irrespective of the fact if all the machines are on the network or not. OP can use the excel/text file as well.

True, although storing the data in the workbook itself is more portable - or you could use power pivot