locking one parameter in a pivot table

[miconian]

Every week I send a report out to a bunch of sales reps. The report is basically a pivot table that pulls from a data worksheet. Each rep gets the same pivot table setup, with a single parameter changed: their own name, of course.

The reps are not allowed to see each other's numbers. So in order to enforce that rule, each pivot table is "hardcoded" i.e. copied and paste-values back onto itself, thus eliminating the actual pivot table, and turning the report into just a bunch of cells in the shape of a pivot table.

But... I want the reps to have pivot tables. They each have an opinion about how they would like to arrange the data. I want to give them the ability to play around with it.

The problem is, of course, that if I send them actual pivot tables, they will also look at each other's data. Is there some way around that? Can I prevent them from changing one particular field, but have free reign over the rest of the table?

I realize that I could just give each one their own raw data only, but this is being automated, and I am not ready to tackle that problem yet.

Thanks!

 

[SydneyGeek]

Where is the data being sourced? Are you pulling from a database, or another workbook?

You could, for example, use MS Query to pull the data into a Data sheet and use a parameter to define which rep's data is refreshed. That parameter can be a cell, and you should be able to lock the cell.
Then you can base a pivot table on that data, and let the reps play.

Denis

 

[miconian]

The data is being pulled from a third-party application. It is ultimately just a front end for a SQL database, but I don't have the ability to query the database directly, or the clout to deploy MS Query. It's just me, Excel, and Outlook (I also have MS Access, but don't have much experience with it yet).

 

[SydneyGeek]

A work-around to make your report behave like a database:

Note: This assumes that (1) the workbook you get the data dump from always has the same name and (2) you completely replace the data each time.

1. Save the data workbook in a network directory that the sales reps can access. In a new workbook use MS Query to pull the data from the workbook. [Before you do it though, pick a cell like, say, Z1 that will not be overwritten when the data comes in. Enter the name of one of the reps. Then Data > Get External data > New database query (2003 or earlier), Data > Get external data > From other sources > Excel (2007/10)
2. Browse to the data file via the dialog that opens up. You may get a message that no data was found; in that case check the option to detect system tables and your worksheet(s) will show up.
3. Double-click the sheet that you want to use. Click through the options until you get to the last screen about editing the query.
4. Choose to edit the query and click OK. When it shows up, click the button to show criteria. In the first criteria field pick the sales rep field, and in the blank row beneath type [Sales Rep Name]. Close the Query dialog (the main dialog, not the internal boxes)and you will get a pop-up asking for the sales rep name. You should get the option to nominate a cell; pick the place where you typed the name. Check the boxes below it so the query refreshes when you update the cell.

If you got that far you're almost done.
Build the pivot table, using the data table as the source. In 2007 and higher, the imported data is in Table format and the pivot table will resize its data source to match the table data.

Refresh the data, save the file with a new rep name, and loop until you finish.
Now protect the sheets with the data. In my test with 2010 the pivot table continued to function.

Next month's cycle is a case of saving the new data file over the old, refreshing the workbooks, locking them again and sending them out.

Denis

 

[miconian]

I'm using Excel 2007, and the data query menus must be different. While I understand your overall idea, I'm having trouble implementing.

I used MS query to pull the data from the network, and to have it appear as a pivot table in the local document. But I still don't see how to limit the sales rep. I don't see an option to 'edit query' at any point in the process.

 

[miconian]

Never mind, I found it... I am in the MS Query interface. Will report back once I attempt the other steps.

 

[miconian]

Okay, here's where I'm at.

I have succeeded in using MS Query to pull the data into a local document, and to edit the query to limit it to one particular sales rep, and to display it as a pivot table. As required, the pivot table only allows the selection of that one rep. Which is great. I can also put [Sales Rep Name] in the value field as you suggested, and I do get prompted for the name of the rep.

Minor problem #1: When I then enter the rep's name, I get a warning that says "this query cannot be edited by the query wizard." Nevertheless, the system seems to accept my entry, because the data that comes up locally is indeed filtered to only have the data for the rep I chose.

Minor problem #2: You said "You should get the option to nominate a cell; pick the place where you typed the name. Check the boxes below it so the query refreshes when you update the cell." But I see no such option. When it asks me for the value, it does so in a small dialog box with a single field and no options except a submit button and a cancel button.

Related question: When I'm constructing the query, should I choose to make it read-only? Or does it not matter?

 

[miconian]

Also, I have tried this a bunch of times now, and sometimes none of the actual data comes through.. just the column headers. And sometimes only some of the data comes through, leaving some columns empty, or with the wrong data.

Is this because I'm querying Excel? Would it work better if I exported the original data into an Access file, and queried that?

 

[miconian]

Having played with this some more, I think the trick is to adjust the parameters within the Excel query filtration menus, as opposed to the MS Query interface. I guess it just likes that better for some reason. More updates if I have issues.

How can I prevent a savvy rep from building their own query and getting someone else's data by reverse-engineering the process?

Thank you so much for this solution overall... it makes a big difference!

 

[SydneyGeek]

You're welcome.

Take a look at this page; you may find it easier to set up the parameter part with screenshots.
http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP010216113.aspx

As for reverse engineering the process, if you protect the page the rep will be unable to get to the connection properties without unprotecting first. So, hang on to the password and you have a bit more security.

Denis