Hide Columns from Reporting Applications
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
When using a PowerPivot workbook as a data model for a Power View report or an Excel pivot report, you can specify whether to hide individual columns so that they do not appear in the reporting application. Hiding columns improves the usability of a model by limiting the field list in the report to just those fields that are of interest to a report author. You might hide redundant columns that are exposed through other tables, or hide columns that contain fields that might be not relevant to report authors. For example, if you are building a model to compare production costs for different products over time, you might hide columns that do not provide insights into that business problem. Similarly, if you have a data column (sales) that provides values used in a measure (sum of sales), you might remove the data column if you are using the measure in your report.
Hiding a column does not remove it from the model. A hidden column can still be used as a sort column in a table. It can also be referenced in formulas that define calculated columns or measures. The ability to hide columns is not intended to be used for data security, only to simplify and shorten the list of columns visible in the PivotTable field list and PowerPivot window.
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
To hide a column
In the Data View of your PowerPivot Window, click the table tab at the bottom of the window to select the table for which you are hiding a column.
Right-click the column, and click Hide from Client Tools. You can hide multiple columns at a time by holding down the Ctrl key or the Shift key.
The column is grayed out to reflect that it is hidden to reporting clients that consume the model. Hidden columns are grayed out in the model to indicate their state, but remain visible in the Data View so that you can continue to work with them.
Measures cannot be hidden and will remain visible and functional in the model even when you hide associated columns. For example, if you create a measure named Count of EmployeeID, but then proceed to hide the EmployeeID column, the measure Count of EmployeeID remains operational in the workbook.
To hide an entire table, you can hide all of its columns. There is no command to hide the table itself. A table is only fully hidden when it contains only columns, and you have hidden all of them. If the table contains a measure, which cannot be hidden, the table continues to appear in the client application field list, as a container for the measure.
Note
If you have many columns that you need to hide, you can create a perspective instead of hiding and unhiding columns. A perspective is a custom view of the data that makes it easier to work with subsets of related data. For more information, see Perspectives Dialog Box.
If you hide columns that are actively used in a report layout (for example, hiding all of the columns in a Date table after using Calendar Year as a slicer in Excel), the report layout is preserved. The difference in the model after hiding the columns is that the columns no longer appear in the field list. You cannot use the now-hidden columns when making further changes to the report layout. Using the previous example as an illustration, if you delete the Calendar Year slicer, you won’t be able to recreate that slicer because Calendar Year is no longer visible in the field list.
See Also
Concepts
Create a Reporting Services Report Using PowerPivot Data