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. .



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.


Last Night of Vacation in Ft Lauderdale

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.

Governance For All

I was thinking about SharePoint governance today and its effect on the success or failure of SharePoint. Fundamentally, SharePoint governance is a document that forms an agreement between the people who support SharePoint and rest of the organization. It then dawned on me that I could substitute SharePoint for any other system. However governance is an umbrella covering many things such as policies, rules, roles, and responsibilities. Within that umbrella of governance, I would like to think of these four areas as represented by the four legs of a chair, the chair being the system. Failure of any one leg may not make the chair crash, but it will make it less stable. Failure of more than one leg can cause the chair to collapse.

The same is true of SharePoint governance. Without adequate policies, rules, roles, and responsibilities, your SharePoint implementation may be on the verge of collapse. The hidden problem at many organizations is that even if you initially create a sound governance policy, if it is not followed and enforced, it may be as bad as not having a governance policy at all. What may begin innocently enough as a minor exception to the rules or policies here and there, can quickly slide down the slippery slope to total chaos. Stay with me a minute.

While governance may seem to be filled with a lot of rigid policies and rules, governance, whether for SharePoint or for any other major system in your organization, must be flexible. It must be almost an organic thing. By that I mean that it must be adaptable to the changing needs of the organization. A change in needs might be something major such as the addition of outward facing web sites to your existing farm of collaboration sites, or it may be something as simple as a font change used for the body of content. The thing to remember is that all change sets a precedence. The question you should ask is whether that is a good precedence that you want to set or a bad one?

Once policies and rules are established and made part of the governance, then changes or exceptions to these policies need to be very carefully vetted and then added to the governance so it does not open the door to other changes that may be less desirable. For example, if the initial governance called for Arial fonts, exceptions might be made for other similar fonts such as Calibri. However, as soon as you begin allowing Times Roman or Impact, you are on the path to allowing Broadway, Playbill, and even Comic Sans Serif. But governance can stop that that by specifying that any font from a limited list of fonts can be used, but only one font can be used per page or per subsite. The same can be said for colors, page layouts (themes), and other visual effects. In addition, the governance should define a method to request changes to policies and rules. Perhaps those requests get funneled through a single person, but more likely they should go to a committee that decides on any governance changes.

However, even if you have all the policies, rules, roles and responsibilities well defined and you have a committee to review and approve or reject any changes to the governance, if you do not have upper management’s full support of the governance, your efforts are in vain. Like a dragon without fiery breath or at least large sharp teeth, users will soon realize that they can get away with anything with no more consequences than your pitiful cry to never do that again. You must have management’s support to enforce the governance.

The problem is that maybe management just came back from a conference, or perhaps it was from a free seminar provided by an alternative vendor who provided a lunch ‘n learn at the local steak restaurant. While the product demoed does basically the same thing as your current product, marketing demos are very good (or they should be) at highlighting the best features of the product making it look like the new and most desirable product on the market. Rarely does the demo go into how much time or skill was needed to set up the demo. Of course demos are meant to look flawless and so simply that your dog could do it with one paw tied behind its back. It is your job to be able to tell the difference between a well-crafted demo and a system that is truly easy to use before your organization starts spending money on something it really does not need.

But here is another big ugly secret. No system can be successful without governance and top management support. Individuals resist change. They will refuse to learn how to use a system. They will claim a system is too hard to learn or to use. They will blame their own mistakes on the system or on the restrictive governance policies or the lack of training that fits their exact circumstance. Management must lead from the top down with strong reasons why the policies and rules will serve the organization as well as the users better. Governance gives them a way to make that happen by formalizing the policies, rules, roles, and responsibilities of any major undertaking. Governance is leadership by getting out in front of everyone and saying, ‘Follow me and be successful.’

Well I’m off to another SQL Saturday this weekend again, this time in South Florida. How does this fit into today’s mini topic? At SQL Saturday there will be hundreds of individuals who as DBAs or developers use SQL Server and have policies and rules they follow when using SQL Server. They have specific roles defining what they can and what they cannot do. They have certain responsibilities to perform specific tasks using SQL Server. What makes SQL Server work or not work within their organizations is largely dependent on how well they embrace the governance around creating, using and maintaining databases and the processes that access those databases within their organizations.

You see it is not just about SharePoint or SQL Server or any other specific product. The need for governance applies across all products and activities in the organization as does the need for management support of that activity. No organization would arbitrarily switch from using SQL Server to Hadoop to Informix to ingres to Oracle to Postgres to Access or any other database system every couple of years. Rather, they would choose a product that best fits their needs and wring out all the value they can from it by insuring that their staff is trained and supported in its use. Don’t expect a new product to somehow produce magical results. If anyone is responsible for magically results, it is an organization’s people. What does your governance say about how you use technology and your staff?

C’ya next time.

The Role of Role Playing Dimensions

Perhaps you have heard the term Role Playing Dimension in regards to PowerPivot and/or Analysis Services cubes. This terms refers to the ability of one dimension to be linked to more than one fact in the fact table. Now strictly speaking, there is still only a single primary link between the dimension and the fact table and that link is used as the default link when aggregating data by that dimension. However, some dimensions, such as date dimension, often can be used against multiple fields in the fact table. For my example, I will use the Adventure Works DW data set that I use in many of my SQL Saturday presentations since it represents a typical sales database. Another good sample database is the Contoso dataset. Using the reseller sales table as my fact table (FactResellerSales), I can proceed to pull into my Excel PowerPivot table this fact table along with the dimensions for Product, Product Category, Product SubCategory, and Date. Notice that there is at least one relationship between each table and one other. This primary relationship is represented by a solid line in the figure below. However, there are three relationships between the FactResellerSales table and the DimDate table. One of the relationships is represented by a solid line and the other two are represented by dotted lines. Back in the source database, these three relationships are represented by regular indexes between these two tables. There is no indication to define which relationship is more important than the other. So how does PowerPivot select which one is the primary relationship between the two tables? The best I can figure out is that it is based on the order of the three different date keys in the fact table. In this case the OrderDateKey appears first and is therefore selected as the primary or Active relationship.

Looking at the raw sales data for a moment, I can see that the dates in the OrderDateKey, DueDateKey, and ShipDateKey are different with the order date occurring first followed by the due date and then the ship date. This would make sense in the real world. Unfortunately, if I did not have the ability to use role playing dimensions for each of these dates, I would either have to deal with only a single relationship between the date dimension table and one of these dates such as the order date or have multiple date dimension tables, one for each relationship. If I only used a single date dimension table, I would have to pick one date in my sales table to relate that dimension. Then any sum I calculated such as the sum of the order amounts, the sum of the ship amounts, or the sum of the due amounts would be associated with a single date such as the order date even though the due date may not occur until the next month or even next year. Unfortunately, this sample data does not show many such cases. However, I will show you one case at the end of the blog.

Our pivot table would then look something like this:

However, that would not be correct since we did not ship the items on the same day as the order nor was the amount due on the same day as the order. Therefore, we need another way to relate these two table when performing a summing calculation. That other way includes the use of the USERRELATIONSHIP() function which lets us define for the purposes of a single calculation which relationship we want to use. For example, to calculate the sum of the amounts ordered, we can sum the Sales Amount field by the OrderDateKey field as shown in the following equation:

When placed in our measure area of the FactResellerSales table, I can see that this calculation returns a value of over 80 million. (I will format as currency this in a moment.)

Similarly, the Shipped Amount can be calculated by summing the Sales Amount using the ShipDateKey as shown in this equation:

Finally the Due Amount can be calculated with the following equation using the DueDateKey field.

After formatting the measures as currency (which saves me time by not having to format this data in the resulting pivot tables separately), the measures appear as this:

Now I can proceed to build the pivot table by clicking on the PivotTable button in the Home ribbon and then selecting

PivotTable from the dropdown.

Excel prompts me to create the PivotTable in either a new worksheet or an existing worksheet. I will select a new worksheet here.

After clicking OK, Excel creates my empty PivotTable and opens the Field List to let me begin defining my PivotTable.

I choose a very simple layout with my calculated measures going across the top of the table and a hierarchy of time coming down the rows of the table.

By default when displaying the names of the months, Excel displays them alphabetically. Typically this will not please your manager. Therefore using a technique I discussed in an earlier blog, I define a column that contains the month number as the column by which I want to sort the column names.

Keep in mind that you only have to do this once. All subsequent PivotTables and PivotCharts will use the sort order assigned to the column EnglishMonthName without my having to do anything else.

Now the months appear in the correct order.

But more importantly for this demonstration, if I drill down into any of the months, I will see the sum of the order amounts, shipped amounts, and due amounts correctly summed by actual dates. Some positions will naturally be empty if there was no order, ship, or due activity for that day.

As promised, the following figure shows an example where orders were taken on two different days within the month but shipping and due dates only occurred once. The point being that the individual sums are correct for the days in which they appear.

Looking at the orders in April of the above figure, you may question the math saying that $2,204,542 plus $82 is $2,204,624, not $2,204,623. This is the result of rounding each individual amounts to whole dollars and is not an indication that Excel cannot perform math. All sums are calculated on the actual dollar amounts and then rounded.

So this is one simple example of how to use role playing dimensions. Dimensions other than dates can serve as role playing dimensions, but most people will encounter the need for role players when working with dates.

C’ya next time.


Get every new post delivered to your Inbox.

Join 103 other followers