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.


Show Report Filter Pages

One of the interesting things to see when working with Pivot tables is to look at the differences between regular pivot tables that Excel has been able to create since about 1997 with Excel 97 (actually Excel 5 in1993 had basic pivot table functionality, but no wizard yet) and Power Pivot tables which has been a relatively recent addition (Excel 2010 with an add-in). Standard pivot table functionality still exists in Excel 2013 and serves as an alternatively tool for simple pivot tables that only require a single data source and less than a million rows of data.

In regular pivot tables, you might want to create a pivot table and use one of the dimensions as a filter. In the following example, I use a version of the FactSales table ripped from Contoso and I load it directly into Excel as one of the worksheets. Then I click on the Pivot Table command from the Insert ribbon to create a basic pivot table.

I added the Sales Amount field to my Values area. Being a numeric value, Sales Amount makes a good choice as a measure for a pivot table. I then added the dimensions for store and product as my horizontal and vertical dimensions generating a reasonable pivot table. However, I also added channel as the filter.

By default, after adding a filter, the pivot table still displays all values for the filter in the table. However, by using the dropdown, I can view the pivot table with one or more of the filter values at a time.

With only four possible values, it would not seem to be a big deal to view each of the channels one at a time. However, Excel provides another way to display the pivot table showing the pivot table on a series of pages with each page representing a different filter value. To do this, I can go to the PivotTable Tools menu group and select the Analyze ribbon. Then select Show Report Filter Pages… from the Options dropdown menu as shown in the image below.

When I select this option, Excel displays a dialog that let me pick the filter I want to expand. In this case, I only have a single filter on the Channel field so I select that filter and click the OK button.

Excel then generates a separate worksheet for each of the filter values and labels the worksheet tab with the filter value so you can easily click on a table to view the pivot table with the selected filter value applied.

This option makes it easier to go back and forth between views of the pivot table with different filter values applied. If you give a copy of the workbook to someone who might not be as familiar with pivot tables as you are, you can expand out the pivot table by the filter values and then lock the workbook so they cannot accidentally make changes to it. It also makes it easier to simply print a series of the pages to your printer if hardcopy is necessary.

So using the same data, I attempted to add the original data to the Power Pivot Data Model by using the Add to Data Model option in the PowerPivot ribbon

I then defined a Pivot Table from the data model using the same fields as before. When I then opened the Options dropdown menu from the Analyze ribbon of the PivotTable Tools group as I did before, I was surprised by the fact that the option to Show Report Filter Pages was greyed out as you can see below.

Apparently, this option is not available when displaying the data through the Power Pivot model, but is available from the basic Pivot table model. I’m sure there is a way to brute force some code to expand each filter value into a table in a new worksheet and rename that worksheet appropriately, but that kind of defeats the purpose of creating a “BI Tool for the Masses” which is the goal of most of my Pivot Table blogs.

C’ya next time.

Dividing Up California

I just saw a news article about a Silicon Valley venture capitalist named Tim Draper who has been trying to get enough signatures to put a proposal on the state ballot to split the state of California into 6 separate smaller states. Although he did not get enough signatures for the November 2015 ballot, apparently, there have been over 800,000 signatures collected for the November 2016 ballot. The following table shows the six states (along with selected counties) defined from the south to the north along with their estimated population taken from a USA Today article.

Southern California (includes San Diego, San Bernardino, Riverside) 10,784,000
West California (includes Los Angeles, Ventura, Santa Barbara) 11,505,000
Central California (includes Fresno, Madera, San Joaquin) 4,197,000
Silicon Valley (includes San Francisco, Santa Clara, Monterey) 6,787,000
North California (includes Napa, Sacramento, Sonoma, Sierra) 3,811,000
Jefferson (includes Butte, Humboldt, Shasta, Trinity) 947,000


One argument being put forward is that because the state is so large, both in terms of land and population, that individuals would be better served by smaller states which would better represent the local population rather than being dominated by the coastal city populations around the Los Angeles, San Francisco and San Diego areas. I suppose that makes some sense as the people in these large densely populated areas would have greatly different needs from their state government from the needs of the sparsely populated northern part of the state or even the eastern more mountainous parts of the state.

While some people might appreciate having smaller government entities with more elected officials that are more directly responsible to the voters, the breakup would also lead to high costs overall as many efforts would have to be duplicated for each of the new states rather than consolidated within the single current state.

I could also imagine that such a change would affect the way presidential races occurred in the future as it would no longer make sense to focus on California as a single large state with about 55 electoral votes compared to Texas’ 38 or Florida and New York’s 29 each. Even within the state legislature, the dynamics of voting would change as the larger metropolis area would not have to compete for the passage of bills with the less populated portions of the state. Each state would be able to draft its own constitution as well as its own laws.

I suppose from one point of view, splitting California into separate smaller states is no more shocking than if the New England states had originally been a single state and then were split into individual states later. Wait a second, that is not as crazy as it sounds. Maine was originally carved out of Massachusetts, West Virginia was cut from Virginia as was Kentucky, and Vermont was created from land disputed between New York and New Hampshire. Furthermore, in recent years there has been talk about splitting New York into a northern and southern state, splitting Florida in to two pieces (maybe Miami should be the separate piece), dividing Maryland, Arizona and Texas.

Current polls indicate that the majority of Californians are against the breakup of their state, but the margin isn’t huge. With two years to go before the ballot is put before the people, there is plenty of time for the pro-breakup promoters to strengthen their arguments and gather converts.

On the bright side, there may even be more programming jobs as all of those applications with a fixed list of 50 states in a dropdown list need to be modified to account for the new states and their individual new state income taxes. It might also be a good time to invest in a flag company because there will be lots of new flags to buy if we have to increase the number of stars.

C’ya next time. .



By sharepointmike Posted in Opinion

Will Robots Save Us or Enslave Us?

Sounds like the lead-in to a new Hollywood movie. Like most computer people, I’m fascinated with the ability to program machines to do ‘work’ for us. The modern industrial factory could not compete with lower labor costs from oversees if not for the ability of robots who once programmed will work 24/7 without a break, sick day, or vacation. While it is true that those robots replaced the jobs of real people, it has always been argued that those people can be retrained and take new jobs in other areas. For example, some would argue that without automation, it would require more crew members to fly today’s large aircraft. Furthermore, the ‘robots’ can fly more efficiently saving fuel and time and respond to most situations faster than a human can. In fact, loss of the electrical systems on an airplane can lead to a disaster. Some of this technology is being added to automobiles to improve fuel efficiency as well as to prevent accidents.

Robots have also helped our military to enter areas that may have mines, booby traps, and enemy troops without risking lives. More recently, robot planes, drones, fly over enemy territory to locate enemy positions and to track their movements. The success of these robots led to the use of robots by police departments to assist in defusing suspicious packages by bomb squads, and even by fire departments to search for trapped survivors in a burning building.

Recently robots have been used to monitor traffic, identify traffic violations and automatically issue tickets based on the reading of the automobile’s license plate numbers. Even simple stop light cameras are a simplistic form of robot that is replacing the cop on the street or perhaps is freeing up their time to pursue more serious crimes (at least we could hope).

On the surface none of these technologies using autonomous robots or even human guided robots were initially seen as a problem. In fact, most were developed to help save lives and have performed well in that capacity. But like any technology, whatever mankind can create for good can be distorted (perverted) for bad.

Recent stories of how red light cameras were set up with shortened caution (yellow) lights just to increase the revenues needed to pay for the cameras has caused some communities to force their removal. The use of drone planes that are successfully used to monitor enemy movement is also being used by the police to find and track criminals, but the people fear they can also be used to spy on them. Some people say that if you are not doing anything wrong, why should you worry. However, it is the uncomfortable feeling of being watched without your knowledge that makes people’s spine tingle. Have someone sit and watch you do your normal job every day and most people would react poorly to that level of scrutiny.

To add on top of those concerns, the public recently learned that the NSA is monitoring all calls, not just the calls identified as belonging to criminals or potential criminals through the use of court issued warrants. Yes, the good part is that they are trying to identify terrorist activity before they act, but at the same time, most people again don’t like the idea of their phone calls being potentially monitored. The same can be said of monitoring email contents by providers who claim they are only doing it to provide more directed advertising based on that content. But who is really to know? Even the GPS in your phone has been shown to track your every movement and that data could be stored for years. Have you ever seen a police car along with highway with no one in it? Perhaps it is sitting there with its camera recording all of the license plates of cars passing by. Of course, it might be a good thing to identify the car of a criminal or locate a stolen car or even a silver alert vehicle. However, again people get that feeling that someone they do not know is always watching what they are doing and where they are going.

The use of cameras in stores, mall, subways, cities, etc. has increased over the last several years. But how effective is it? Images from cameras during the Boston Marathon bombing were insufficient to identify the bombers. The failure to identify the real bombers leads the rest of us to worry about false positives, identifying innocent people because they look a little like the criminal.

I suppose it all depends on how much relevance is given to these robots. After all, machines are not as good at determining the difference between the letter and the intent of the law. Take for example the stop light cameras. Do you program the cameras to record images of every car that goes through the red light even if the red light just changed a tenth of a second ago? What about two tenths of a second? Three? Similarly, for identifying speeders, how much over the speed limit warrants a ticket, 1 MPH, 2 MPH, 5 MPH, more? Does that answer make a difference if the speed limit is 20 MPH or 40 MPH or 65 MPH?

Actually researchers are currently looking into ways to program robots to interpret the intent of the law. Some purists say that anything that violates the law should be ticketed by the robots and let the courts decide. But would that put a huge burden on the already overloaded court system? Perhaps more open to interpretation is identifying erratic drivers. You know, the ones who weave from one lane to another just to pass a few cars while putting everyone else at risk. Are they in a hurry or just drunk? Does it matter? Will it identify the motorcyclist going down the freeway pulling a wheelie? Or how about that same motorcyclist squeezing between cars stopped at a red light just to get to the front of the line for when the light changes to green? How about those drivers who just don’t seem to like any shade of green and wait 5 seconds before they go at a light? Or how about the drivers making right hand turns from the left hand lane? Should they be ticketed? Can car-based biometrics along with GPS help identify drivers who are texting while they drive? What about the abuse of helicopters flying in circles around a development looking for criminals waking everyone up with their rotor noise and their blinding search lights shining through your bedroom windows in the middle of the night? (Or was that the alien abductors?) Will your ‘smart’ phone soon be monitoring everything you eat and do and report it to your doctor or your insurance carrier? If you had a bad night of sleep or if you just had an argument or received bad news, will your car refuse to start?

I could go on and on. But the point is this I suppose, when technology is used to work in dangerous areas to protect humans from entering those area or when the technology is used to provide unbiased law enforcement, most people will accept it. However, if the technology appears intrusive, is being used for hunting expeditions, not to solve a specific crime, or is viewed as being unfairly biased in any way, many people will reject it. Unfortunately for robots, the world is not black or white, but many shades of grey which for now they cannot interpret. These shades of grey will leave open the question of whether robots are good or bad as a topic for the movies for years to come.

C’ya next time.


By sharepointmike Posted in Opinion

What Defines a Great Leader?

It seems like there are a million different answers to this question and maybe they all have a grain of truth to them given a specific time, place and point of view. I guess the first thing that I have to admit is that what defines a great leader today may not be what defines a great leader several decades ago and probably will not define a great leader in the future. Or maybe what defines a great leader is merely a representation of my own viewpoint at the time that I am trying to answer that question.

In any case, I recently heard a presentation by Simon Sinek in which he postulated that a great leader is someone who makes their followers (employees) feel secure. They basically surround their followers with a circle of trust against danger to the organization posed by all external forces. I have to say, the image that first came to my mind when he said this was from the wild west days in which pioneers would circle the wagons against an Indian attack, but then maybe the analogy is not that far off.

He also brought up some other interesting questions. For example, why do we honor people in the military with medals for bravery when they sacrifice or at least risk their lives to save other members of their team, but at the same time we reward business executives with bonuses when they are willing to sacrifice others within their organization so that they can reap the rewards of success for themselves? It sort of makes me think of the most recent banking crisis which caused a loss of over $10 trillion by some estimates due to foreclosures, business closings and even business slowdowns because of the inability to borrow working capital. Yet these same banking executives basically have been able to walk away from the crisis keeping the bonuses they were rewarded when bank profits skyrocketed due to the heavy use of appraisal fraud to inflate home prices and liar loans which are loans made with little to no checking of the borrower’s ability to pay.

It seems like most business executives today are more concerned with winning awards, getting bonuses, and achieving recognition within the community than in the long term success of their organizations or their staff. At the same time, most employees suffer from some degree of fear of losing their jobs because their organization is being threatened by competitors both from within the country and often internationally. The more threatened they feel, the more energy they expend on trying to protect themselves perhaps by looking for a new job or by learning a new skill that they can use somewhere else. This time and effort could have been better used by their current organization to protect that organization from outside forces by developing new products, new services, and becoming more efficient. Instead, that time is lost and the organization becomes more threatened by outside forces.

On the other hand, organizations that take a more fatherly approach to support their staff, help them grow, provide opportunities, promote education of the worker, and celebrating their achievements while coaching their failures. These organizations tend to experience greater employee loyalty with each worker looking out for the benefit of the group rather than just themselves. Simon also talked about the fact that in a family, you would never layoff one of your children when times got bad. Rather you would find ways to survive together through the tough times. How many organization would not give a second thought to laying off a worker due to financial reasons or because they took a chance on a product or service and failed. How many entrepreneurs failed multiple times before they finally became an overnight success, yet in most organizations, fail once and you are out the door.

Developing an environment of mutual trust is Simon’s answer. In fact, that seems to be his main point. If someone trusts you, human nature would say that you would be more likely to trust them and go the extra mile for them. Similarly, if trust is absent you may only do what you absolute must do to keep your job. How many organizations who don’t understand this are left with managers who are authorities within the organization and still get people to do what they tell them to do, but they do not have managers who are true leaders to whom the workers respect, look up to, and work harder for the common good of the organization.

Anyway, I found the presentation have some interesting points to think about and wanted to share them along with some of my own with all of you.

C’ya next time.