Breaking Symmetry in Reports

Symmetry in reports refers to those reports in which more than one measure is reported on for each combination of horizontal and/or vertical dimensions. Using the Contoso data set, suppose I wanted to display the Sales Amount and the Return Amount for each year broken down by the sales channel. I might set up my pivot table definition as shown in the following figure.

This definition would result in a pivot table that would look something like the following:

This pivot table shows me the return amount and the sales amount for each year in the database along with a total for each of these fields summed across all three years. But what if my management did not want all of that information. What if they only wanted to see the annual sales amounts for all three years and the returns for only the most recent year. In other words, they do not want to see the totals across all three years and they do not want to see the return amounts for 2007 or 2008. This means I need to remove the columns marked in the following image.

Let’s first look at the total columns on the right of the table. These are actually quite easy to remvoe because Excel provides within the PivotTable Tools Design ribbon the ability to select which subtotals and which grand totals to include or remove. To accomplish this, I can begin by clicking anywhere within the pivot table and selecting the Design ribbon as shown in the following image.

In the Layout group on the left side of the ribbon, I can use the dropdown for Subtotals and Grand Totals to customize which totals to include in my report. In this case, I want to remove the grand totals for the rows while retaining the grand totals for the columns. I can do this by selecting the option: On for Columns Only.

Selecting this option immediately removes the two rightmost columns. However, I still need to remove two of the three return amount columns for the years 2007 and 2008.

My first thought might be to simply hide the columns. However, the problem with this method is that it is somewhat obvious that a column is hidden by looking at the letters defining the columns (where is column C?) and someone could easily unhide these columns. Also, in this case, hiding the column also removes the year headers and in the case of the first column, the column label, both of which are undesirable consequences of hiding columns.

A better method is to define a set that only includes the columns I want to keep. Sets can be created for many purposes related to limiting the columns or rows displayed in a table. In this case, I can easily define a set that excludes the columns for the Return Amount values for both 2007 and 2008 while leaving the Return Amount in 2009. To create a Set, select the Analyze ribbon in the PivotTable Tools group as shown in the following image.

From the Fields, Items, & Sets dropdown, I select the option to create a set based on columns since I want to select which columns appear in the resulting table. If, on the other hand, I wanted to limit which channels appeared, I could create a set based on the row items.

In the dialog that appears, you see a row for each of the columns in the current pivot table. To remove a row, simply click to the left of the row to select the row and then click the Delete Row button. Don’t confuse the terminology here. Delete Row refers to the row selected in the dialog and has nothing to do with rows in the pivot table. Also, I recommend that you provide a meaningful name for the set rather than Set1, Set2, etc. so that you can easily tell what each set does simply by reading the set name.

Using this technique, I can remove the rows that calculate the Return Amount for the years 2007 and 2008 as shown in the figure below. Note however, that I can also add rows to the pivot table definition (which would appear as columns because I am defining a set based on columns). In the first column, I can include the year for the data and in the second column I can select from the dropdown any numeric value in the pivot table fields to be displayed. Note that I can also create copies of rows (columns in the table) and I can rearrange the order of the rows (columns in the table). You can create multiple data sets with different combinations of rows (columns).

This capability exists in PowerPivot tables, not in standard Excel Pivot tables.

After clicking the OK button, my pivot table now looks like the one shown below (after some additional cleanup). In this table, the first two years display a single value, the sales amount. For the last year, the table displays two values, return amount and sales amount. Tables which do not have the same values within each column grouping are officially referred to as Asymmetric Reports as opposed to Symmetric Reports which have the same measures displayed within each of the column groupings.

Hope you found this useful. C’ya next time.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s