Tag Archives: pivot table

Excel, Programming

How to select slicer values with VBA

PitanHeader_short

Hello, everyone, and welcome to another edition of Pitan Pivot Mage!

Today we will cover how to select slicer values with VBA. This is especially useful if you have a report generated by OfficeWriter’s ExcelTemplate model and you can’t select the slicer values in the template file.

Intro

Let’s start out with an ExcelWriter report template. It has some data off to the side and a PivotTable that summarizes the test scores with averages. There are two slicers for filtering on age and gender.

Template

When it’s populated with data, all of the values in the slicers are automatically selected, so the default is a full view of the data.

RenderedReport

Writing the Macros

Whenever I need to write a PivotTable macro, I always start by recording myself performing the action I want and then generalize the code later. In this case, I want to select only “M” and “17” in my slicers for 17-year-old male students.

My first pass gives me some useful information:

RawMacro

The first thing I notice is the slicer name “Slicer_Age1” and “Slicer_Gender1”. This is the name for the slicer for formulas and VBA. To find the name of your slicer, select the slicer and go to slicer settings. This is on the left, under slicer tools:

SlicerTools_Settings

The slicer tool dialog lists the name of the slicer, but also the Name to use in formulas. This is the programmatic name for the slicer that we’ll use in the macro.

Slicer_Programmatic_Age

Then I was able to generalize the macro to take a slicer name, value, and whether it should be selected or deselected:


'Selects or deselects a value (slicerVal) in a slicer (slicerName)
Sub SelectSlicerValue(ByVal slicerName As String,
ByVal slicerVal As String, ByVal isSelected As Boolean)
ActiveWorkbook.SlicerCaches(slicerName).SlicerItems(slicerVal).Selected = isSelected
End Sub

To make things easier for myself, I  created a subroutine that would select 17 and “M,” while deselecting 18 and “F.”

'Selects slicer values to display only data for 17-year-old male students
Sub SelectMale17Profile()
SelectSlicerValue "Slicer_Age1", "17", True
SelectSlicerValue "Slicer_Age1", "18", False
SelectSlicerValue "Slicer_Gender1", "M", True
SelectSlicerValue "Slicer_Gender1", "F", False
End Sub

Calling the Macros

To make sure that the macro runs when the workbook is opened, I called the subroutine in the Workbook_Open() code.


Private Sub Workbook_Open()
SelectMale17Profile
End Sub

Handling the PivotTable refresh

As outlined in our tutorial for how to use PivotTables with ExcelTemplate reports, the PivotTable needs to refresh in order for the imported data to display; the easiest way to do this is to set ‘Refresh on Open’ under PivotTable Data Properties. This poses a problem because macros in Workbook_Open() execute before the the PivotTable refresh and the slicer macros will be unable to find the values to select. This results in a run-time error.

To avoid this problem, I wrote a quick macro that refreshes the PivotTable:

'Refreshes the PivotTable as a replacement for the 'refresh on open' property
Sub PivotTableRefresh(ByVal pivotTableSheet As String, ByVal pivotTableName As String)
ActiveWorkbook.Sheets(pivotTableSheet).PivotTables(pivotTableName).PivotCache.Refresh
End Sub

And added it before the slicer code in Workbook_Open().

Private Sub Workbook_Open()
'Refresh the PivotTable with a macro because it may not refresh first
PivotTableRefresh "Template", "PivotTable1"
'Select the desired slicer values
SelectMale17Profile
End Sub

There you go!

SelectedProfile

Downloads

A copy of the template file with the macros, a sample output file, and the code used to populate the template file are available for download here as a zip file.

Getting started:

Learn more about ExcelTemplate or try it out in a free trial of OfficeWriter today.

     OR     



Excel

Run-time error 1004 with PivotItems

I was working on some VBA code to hide all the values for a particular report filter, except for a specific one. The code looped through all the pivot items for a particular field, turned them off and then turned the one I wanted back on.

Sub SetFilter(ByVal Worksheet As String, ByVal pivotTable As String, ByVal pivotField As String, ByVal selectValue As String) 
      Sheets(Worksheet).Select 

      Dim pvtTable As pivotTable
      Set pvtTable = ActiveSheet.PivotTables(pivotTable) 

      Dim pvtField As pivotField 
      Set pvtField = pvtTable.PivotFields(pivotField) 
     For Each filterValue In pvtField.PivotItems 
          filterValue.Visible = False 
     Next 
     pvtField.PivotItems(selectValue).Visible = True 
End Sub

While testing the code, it worked until it hit the last filter value, at which point it would throw an exception: Continue reading Run-time error 1004 with PivotItems →

Business Intelligence, Excel

PowerPivot Part 3: Slicers

Hello everyone, Pitan here! This is the third chapter of my PowerPivot epic. Read the adventure from the beginning with PowerPivot Part 1: Bringing Data Together or continue on to your regularly scheduled programming with slicers!

Okay, so technically slicers aren’t specific to PowerPivot, but they are new to Excel 2010. Chances are if you are working with PowerPivot, you’ll want to know about slicers.

What is a slicer?

A slicer is a visual representation of a filter applied to your PivotTable or PivotChart. Rather than having to use the drop-downs for report filters, column labels, or column rows like this:

You can have an aesthetically pleasing slicer to show you at a glance what data is filtered:

How do slicers work?

Continue reading PowerPivot Part 3: Slicers →

Business Intelligence, Excel

PowerPivot Side Quest: How to Format a Slicer

Pitan here! In Part 3 of my PowerPivot blog series, I cover how to add slicers to a PowerPivot report.
This post covers how to format slicers in Excel 2010 – in particular, how to create a custom slicer style that can be applied to multiple slicers.

The first step is to select the slicer to activate Slice Tools tab in the ribbon.

There are default styles available, but in this case we want to make a customized slicer style. You can create a new style from scratch:

But you may find it easier to clone the style and then modify the style properties, which is what we will do in this example. Here is the slicer with the unmodified clone of the style:

Continue reading PowerPivot Side Quest: How to Format a Slicer →

Excel

Super Short Tips: Working with pivot tables in VBA

Sometimes you just need to write macros for pivot tables. It’s a fact of life.

Maybe your pivot table is dynamically populated with data and you need to make some custom changes once the data is in the table, but you can only do that after the file is opened in Excel and the pivot table refreshes. Perhaps your plagued with the error that happens when you open a file with a pivot table directly from Internet Explorer. Whatever your reason, you need to write some VBA for pivot tables.

Here’s my super short list of tips for working with pivot tables in VBA:

#1 – Record a macro of yourself to get started

If you know what you want to do, but you’re not sure what the VBA code should be, record a macro of yourself doing the desired action. Not only will this give you a hint about where to start in your VBA, but you’ll also verify the exact method calls for your version of Excel and your pivot table version. All of my pivot table macros start with me performing operations in Excel and then generalizing the VBA from there.

#2 – Base your VBA on pivot fields

Pivot fields come from the column names in the data source for your pivot table. Even as the data is changing, the pivot fields remain constant because the construction and layout of the pivot table depends on the pivot fields. The fact that they remain constant can be very useful when writing VBA.

If you create macros that are based on particular pivot table items, you can’t be sure that those values will be there when the workbook is populated. For example, you want to make sure that all the groups for a particular row label are collapsed when the user first encounters the pivot table. You can manually collapse each of these groups. Problem: you create a macro that collapses groups “A”, “B”, “C”, etc separately. What if group “A” isn’t in your data set when the report is generated? Excel will throw an errror.

You can take the chance that group “A” may or may not appear in your data set, or you can create a macro that focuses on the pivot field that group “A” belongs to, rather than the individual entries.

ActiveSheet.PivotTables("PivotTable1").PivotFields("PivotField1").ShowDetail = False

This will collapse all the groups within ‘PivotField1’.

In short, avoid making macros that are dependent on specific data, because you can’t be sure that the VBA will execute without error.

#3 – VBA for pivot tables in Excel 2003 is different than in Excel 2007/2010

Excel 2003 had our best friend, the Pivot Table Wizard. Everything for the pivot table was done through a wizard menu, so the VBA in Excel 2003 follows suite. Starting in Excel 2007, Excel reworked how users interacted with pivot tables, and the Pivot Table Wizard was banished for eternity. Thus, VBA in Excel 2007/2010 is drastically different.

If you have end-users who are going to be opening a report with pivot table VBA in Excel 2003, 2007, and 2010, you’ll want to detect what version of Excel is being opened, then execute the 2003 or 2007/2010 based on this.

To determine what version of Excel the VBA is executing in, check Application.Version. This will return a string representing the version of Excel (e.g. 10.0, 12.0, 14.0 etc.)

#4 – Pivot tables have versions too

Just as there are differences between XLS and XLSX files in terms of what features can be supported, there are differences between pivot tables created in XLS files vs those created in XLSX files. What’s more, there are also differences between pivot tables created in Excel 2007 and 2010. It’s important to note what version of pivot table you’re targeting.

Luckily you can check the PivotTable.Version property to see which of the versions your pivot table is.

So there you have it – my cheat sheet for creating VBA for pivot tables.

Excel, OfficeWriter

Adding a % of Total Column to a PivotTable

Intro

A customer recently asked for some help with adding a % of Total column for sales data without a pivot table (read about it with Excel and with ExcelWriter). But I wanted to see how the same table and column would be done with a pivot table. Enter Pitan, the Pivot Mage and here we go!

Solution

We start with a basic pivot table that already has the store names as row labels and the sales data as the data values. We want to add a column that shows each stores’s sales totals as a percentage of the sales total over all the stores.

1. Add a duplicate column of the values that you want to show the percentages for. In this case, it’s “Store Sales”, so there will be two columns of “Stores Sales”: one for displaying the raw values and one for calculating the percentages.

2. Display the field values as % of Column Total. This can be done two ways:

  • Right click on the field > Show Values As > % of Column Total
  • Use Value Field Settings to format the field
    1. Go to the field in the field view
    2. Select ‘Value Field Settings’ from the drop down
    3. Go to the Show Values As tab
    4. Select % of Column Total

 

 

 

 

 

 

 

 

3. (Optional) Rename the field as desired (from Value Field Settings)

4. Congratulations!

For more information about different field calculations, see Microsoft’s article on how to Calculate values in a PivotTable Report.

 

 

 

 

 

ExcelWriter, Knowledge Base, OfficeWriter

Shapes and PivotCharts stretch when placed next to a PivotTable

Problem

If a pivotchart or drawing shape is placed next to a pivottable, it stretched over many rows in the output file. Also, if a pivotchart or drawing shape is placed beneath a pivottable, it gets pushed down the worksheet.

Solution

The issue of shape behavior was addressed in OfficeWriter 4.6.0. Download the latest version of OfficeWriter.

Excel, OfficeWriter

Pitan Pivot Mage and the Quest to Slay the Bracket Problem

All is not quiet on the home front…

“Cannot open PivotTable source file” – If you have ever encountered this error message, then you know the frustrations of The Bracket Problem. If you haven’t run into this issue before, let me paint you a picture:

There some sort of pivot report on a server, for example, a report that’s being dynamically generated for Excel using OfficeWriter. An end user decides to download the report on her work machine with IE 7. When she opens the workbook, instead of the pivot table refreshing, she gets a warning message:

Cannot open PivotTable source file: ‘C:\Users\Pitan\AppData\Local\Microsoft\Windows\Temporary Internet Files\….\ [PitanPivotReport[1].xlsx]Sheet1’.

When she tries downloading the report using Mozilla FireFox, the warning message doesn’t occur.

This, my friends, is The Bracket Problem.

What trickery is afoot? Continue reading Pitan Pivot Mage and the Quest to Slay the Bracket Problem →

Excel, OfficeWriter

Pitan Pivot Mage: The Mystery of the ‘Replace Content’ Message

I was working with a formatted Excel report and I needed to add a pivot table. Easy-peasy, I added my lovely little pivot table with a pivot chart. Since I would be importing the data into the pivot table later, I made sure to place the pivot chart outside of the cells that the pivot table would occupy once it was populated with data (to avoid overlap). I saved my template and went to generate my report.

When I opened the output, I was greeted with a mysterious message from Excel:

 

I was baffled because I knew that nothing should overlap with the pivot table or pivot chart, even with after the data had been imported. Continue reading Pitan Pivot Mage: The Mystery of the ‘Replace Content’ Message →