Hiding / Suppressing Confidential Fields From Pivot Table Source
Nov 11, 2013
Context: I'm pulling a data set "A" into excel from MS SQL (currently 48,000 rows ... comes back in < 10s). I will add a field "Cost" to data set "A" that uses info from that data set to look up a value in data set "B" which is in another Excel Sheet and perform a calculation. The sheet that contains data set "B" may or may not be in the same file - haven't decided yet but I'm not really concerned about that.
I actually have the calculated "Cost" field added to the end of data set "A". When I refresh the data, the fomulas recalculate.
My intent from here is to present the data in a pivot table. I will add a "Refresh Data" command button that:
1. Updates the data from MS SQL
2. Refreshes the pivot
I've done 1&2 many times before and have code I'll reuse.
The problem: I cannot present a pivot sourced straight off the amended data set "A" because it contains fields that should not be accessible by the audience for this report (as a matter of policy). I haven't found a way to hide/suppress source fields in a pivot table so I'm guessing that it is not possible. So I need to somehow get to a dataset that I can use to source my pivot which does not include the confidential data but includes my calculation.
Options I'm considering:Via VBA: Create a copy of Data Set A (including my cost field) as Table C on another sheet and remove the confidential columns from the table. The Pivot Table would be sourced against TAble C. My assumption is that I'd have to be very careful with how I clear out and re-populate table C as not to have to "start over" with my Pivot Table each time it is refreshed. Ideally the pivot design would look just as it did before the user hit the "Refresh Data" button (other than it has the updated data).Order my fields in data set A (with the calculated field) such that I can define a named range that is the source for pivot table. The range would exclude the confidential fields. The Refresh Data macro will just need to resize the named range after the data is refreshed from SQL server. With this option, I just have trouble trusting that excel will never arbitrarily change the field order when I refresh the data connection.Create Data Set C using another query from SQL Server that only has the fields that I want available in the pivot plus a "sumproduct" formula that gets cost from Data Set A. The Refresh Data macro would refresh A then C then the Pivot table. What I don't like about this is having to keep the 2 queries in sync -- there is not a large risk of these queries needing to change, but you never know.
View 4 Replies
ADVERTISEMENT
Jun 22, 2006
I have a question regarding pivot tables. how do I hide a field but still maintain that filter? I have a field in the Column Fields titled "International". Right now I've filtered out only the YES entries, but I don't want to see a "YES" column beside each. When I try to hide the column the filter is reset and all entries, international or not, are seen. Can I hide the column but maintain its filter?
View 6 Replies
View Related
Mar 14, 2013
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then
Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X)
end if"
View 1 Replies
View Related
Jan 4, 2010
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies
View Related
Feb 8, 2014
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
View 2 Replies
View Related
May 2, 2007
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;
Sub RemoveGhostPivotItems()
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
ghost.Delete
Next ghost
Next Count
pt.ManualUpdate = False
End Sub
My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............
View 2 Replies
View Related
Oct 5, 2011
There is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
View 2 Replies
View Related
Jul 14, 2014
I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?
View 2 Replies
View Related
Sep 7, 2006
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
Thanks in advance for any help,
Jon
View 9 Replies
View Related
May 9, 2012
I have a macro that produces pivot tables, hiding the blanks in the process. It has failed today for the first time, which I think is because there were no blanks, so nothing to hide....
How can I fix the code so blanks are hidden only if necessary...
Code:
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Email or Call")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("wk_range")
.PivotItems("(blank)").Visible = False
End With
View 1 Replies
View Related
May 17, 2006
Whenever I click on a pivot table, the pivot table toolbar pops up. Generally, I like this, but it confuses my users of this workbook. Is there a way to hide the toolbar just for this workbook?
View 3 Replies
View Related
Jul 20, 2009
I'm trying to loop through all pivot fields in a table and change the format to be 0 decimals and comma seperated.
Here's my
View 3 Replies
View Related
Dec 19, 2011
I am looking to hide or delete the extra worksheet that is created when creating a Pivot table Macro. When I create a Pivot Table, it creates Sheet1 with it blank.
Is there a code that I can add to hide or delete the blank worksheet (sheet1)? Each workbook that the macro is distubuted in has a different workbook name, ex South_Report.XLSM, West_Report.XLSM.
View 4 Replies
View Related
Jan 3, 2014
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
View 2 Replies
View Related
Nov 28, 2012
My data has a field for "Donor Type." There are about 2 dozen types of and about half of them start with the letters CF. When I do a pivot table, instead of getting 2 dozen rows of data I'd like all the CF donors to be in the same row. Is there a way to do that without changing my data? Some sort of filter or grouping within the pivot table?
View 1 Replies
View Related
Jun 7, 2013
I have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.
Day
Count of Posts
Sum of Impressions
Impressions Per Post
Monday
52
1,881,468
[Code] ........
View 1 Replies
View Related
Feb 9, 2008
Found what it think is the correct methodology (Pivot table) for doing this, but I'm struggling to apply it.
I have a very simple excel 2007 document that has one column with multiple rows;
blue
Blue
green
red
red
green
Green
blue
red
Blue
blue
Bleue
Rouge
red
Bleue
I would like the easiest way to count the total of each and sort in highest first,
e.g.
red 4
blue 3
Blue 2
green 2
View 9 Replies
View Related
Feb 1, 2010
I have written code to add datafiels to pivot table but I need to remove the ones that are their prior to adding.
Does anyone have a technique using vba to do this.
View 9 Replies
View Related
Oct 10, 2007
Im using the following VBA code, but the on error resume does nothing, excel throws out the error 1004 "Unable to get the pivotfields property of the pivottable class" (i know why the error is caused and how to fix it but my error trapping wont work). Is there some option which i have turned off which prevents me from trapping errors?
Dim ws As Worksheet
Set ws = Excel.Sheets("Sheet1")
On Error Goto err:
If ws. Range("IV1").Value = "dog" Then
ws.PivotTables("PivotTable1").PivotFields("Price Euros").Orientation = xlHidden
Else
ws.PivotTables("PivotTable1").PivotFields("Price Dollars").Orientation = xlHidden
End If
err:
msgbox "invalid"
View 5 Replies
View Related
Jun 19, 2008
I'm trying to create a Pivot table with various columns comparing information of executives from various Private Equity Firms. Some of the additional columns are Industry Specialty, Harvard Grad Year, My companies contact, etc . . .
The goal is to allow me to then query this information so if I wanted to see all Bain Capital employees who graduated with an MBA in 1990 and their specialty.
My question comes in trying to set up my source list for the data under Industry Specialty and my companies Contact. Is there a way I can set a value for these cells and mark them as an "AND" in the source if there are multiple contacts or industries that one of the Private equity executives is linked too, that would then allow them to be displayed as so when I create the pivot table?
Attached is .zip for further clarification.
View 9 Replies
View Related
Aug 24, 2009
I was wondering if anyone knows how to change the souce data for a Pivot Table?
Eg if I have the Pivot table looking at Columns A & B lines 1 - 20 and I want the Pivot to also include Column C and lines 21 - 50, how would I do this? for a chart I can just right click and select the "Source Data" option but it seems that this is not available for Pivot Tables.
View 3 Replies
View Related
Apr 10, 2013
A co-worker of mine set up a pivot table using data saved on an external data source on a shared drive. I would like to create a similar pivot table myself, linked to his pivot table so when he gets updated data, my pivot table also will update accordingly. My question has to do with the pivot table data source. How do I copy the connection?
View 3 Replies
View Related
Jul 18, 2007
How can I possibly find the source path of a file used to build a pivot table. So basically the pivot table is using an external source and I'm unable to find it.
View 2 Replies
View Related
Aug 1, 2012
Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.
The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..
The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.
I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?
View 5 Replies
View Related
Jun 17, 2009
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
View 2 Replies
View Related
Aug 28, 2008
I have pivot tables in diff pages
im trying to make a button to reset all the fields in all the pivot tables..
i tried recording a macro to do this but it only works for page fields..
it wont work for the column fields.. act, adopt and check are page fields at the top... region is a column field ... goto_AQcheck is a macro to switch to that sheet which is assigned to another button....
this isthe code it gave:
View 13 Replies
View Related
Jan 21, 2012
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date
-------------------------------------------------------------
John , 100 , $150 , 2011-04-01 17:07:50.0
John , 100 , $250 , 2011-04-01 17:07:50.0
Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase
-----------------------------------------------------------
John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
View 2 Replies
View Related
Mar 29, 2012
I have a pivot table listing different company names in the first column under 'row labels' and there are calculated fields, a count and an average in columns B and C respectively. Is it possible to insert another field in column D that calculates the difference between values in column B and C (even though they are calculated fields themselves)?
View 3 Replies
View Related
May 23, 2007
Worksheet A contains two columns, that maps individual operations to their unitary cost:
COLUMN 1 = a list of operations
COLUMN 2 = the cost associated with each operation.
I can do a vlookup on this worksheet to retrieve the cost of each individual operation.
On another worksheet, I have a pivot table with a field that produces the sum of operations performed by type of operation, eg.
operation 1 was performed 5 times
operation 2 was performed 7 times
operation 3 was performed 4 times
I want to add a calculated field in the pivottable, that would output the total cost per operation. The formula for the calculated field in thepivot table would be
= operation * vlookup("name of operation","range for lookup table",2,0)
... unfortunately, "references, names and arrays are not supported in pivottable formulas".
View 9 Replies
View Related
Sep 26, 2007
I have a pivot table which draws data automatically from a database
What I would like it for the customer field of the pivot table to only equal the customers which are present in another worksheet (Column A:A)
View 9 Replies
View Related
