Getting Started With DAX Functions

If you are serious about using PowerPivot, you need to learn a little about the DAX language at the very least to create calculated columns.  Last week I introduced the basic syntax of DAX.  This week, as promised, I will help you get started with DAX functions.  Am I going to teach you everything you need to know about all of the DAX functions?  No, that would be too ambitious for a single blog entry.  However, I do want to guide you down the path of some of the most common DAX functions that you may find useful when building calculated columns.

First, you should know that DAX functions can be grouped into 8 groups (some people say 7 depending on whether they lump the Date and Time functions together with the Time Intelligence functions which are significantly more complex) as shown here:

  • Date and Time
  • Filter
  • Information
  • Logic
  • Math and Trigonometry
  • Statistics
  • String
  • Time Intelligence

The only group I explore in any detail here will be the Date and Time functions.  Why only look at one out of 8 groups?  Well, first off, this group holds common functions used to manipulate dates and time, extract parts of dates and time, and calculate some date offsets.  Second, after showing how easy these initial functions are to understand, I will provide you with links so you can explore some of the other function groups on your own.

So for the date and time functions, I will give you a basic list of the functions and short description of what they do.  I may even add a few comments about some of the functions that you may not have seen before, but most stand on their own because variations of them have been around for years.  So without further delay, let’s look at the functions that make up the Date and Time group.

 =DATE(<year>,
<month>, <day>)
This function returns a date value from integer arguments for the  year, month and day.
=DATEVALUE(<date_text>) This function takes a date formatted as a string such as “10/29/2011”  and converts it to a datetime value.  This function is useful when you data source has dates stored as  strings rather than date fields.
=DAY(<date>) Returns an integer representing the day of the month from the date argument  which can be a column or date string.
=EDATE(<start_date>,<months>) Returns a date the indicated number of months in the future or past  from the current date.  If the date  would be after the last day in the destination month, it automatically  reverts to the last day in the month.   For example both EDATE(“1/30/2011”,1) and EDATE(“1/31/2011”,1) would  return “2/28/2011”.
=EOMONTH(<start_date>,<months>) This function differs from EDATE in that it always returns the date  of the last day in the month that is <months> before or after the  <start_date>.  For example,  EOMONTH(“1/16/2011”,1) will return “2/28/2011”.  This is a very interesting function if you need the last day of the month for billing purposes.
=HOUR<datetime>) Returns the hour number (using military time of 0 (12:00 AM) to 23  (11:00 PM)) of the datetime argument.
=MINUTE(<datetime>) Returns the minutes number (0 to 59) of the datetime argument.
=MONTH(<datetime>) Returns the month number (1=January to 2=December) for the datetime  argument.
=NOW() This functions has no arguments, but returns the current date and time.  Note that using this function will result in the value being updated every time the formula is refreshed which may not be what you want.  However you can decimal numbers to the result of this function to  calculate a date offset.  For example,  =NOW()+5.0 returns a datetime value 5 days from now.
=SECOND(<datetime>) Returns the seconds number (0 to 59) of the datetime argument.
=TIME(<hour>,< minute>, <second>) Returns a time value in which the time is calculated from the three  arguments for hour, minute, and second.
=TIMEVALUE(<time_text>) Converts a time supplied in text format to a time value in datetime  format such as =TIMEVALUE(“16:30:00”) for 4:30 PM.
=TODAY() This function is similar to =NOW() but returns only the date, not the  current date and time.  All times are represented as 12:00 AM.
=WEEKDAY(<datetime>) Returns a number from 1 to 7 that identifies the day of the week for  the datetime argument.  Unless you have  changed your computer’s settings, a value of 1 corresponds to Sunday.  Given the week day number of a given date, it is possible to create an expression to calculate the date for the Monday of that week.  Think a bit about it.  You may need to also look at the =IF() function to do this, but nesting functions is what DAX is all about.
=WEEKNUM(<date>,<return_type>) This function returns a number that identifies the week  of the  current year.  The key to understanding  this function is that it identifies the week that contains January 1 as the first week of the year even if that week begins with a date in the prior   year.

The <return_type> value of   1 (the default) begins the week on Sunday.  The <return_type> value of 2 begins the week on Monday.

=YEAR(<date>) Returns a four digit integer representing the year for the date argument.
=YEARFRAC(<start_date>, <end_date>, <basis>) Calculates a fraction representing the portion of the year between the two dates.  The values of <basis> determines the number of days in the year based on the  following values:

0          US (NASD) 30/360

1          Actual/actual

2          Actual/360

3          Actual/365

4          European 30/360

Knowing the basis of the calculation, you could multiply the resulting fraction to get the number of days between the two dates.

You probably notices that some of these functions are very similar to functions you already know in Excel, SQL or other development language.  However, several such as =EMONTH(), =EOMONTH, and =YEARFRAC() are new but are fairly simply to understand.  Some of these functions could help you build a date dimension table based on dates in a fact table if such a table does not already exist.  We’ll get to the details of how to do that later after we learn a few more functions so be patient.

Details about the Date and Time function can be found at: http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-date-and-time-functions.aspx as well as http://technet.microsoft.com/en-us/library/ee634786(SQL.110).aspx

Now that I have shown you how easy it is to figure out what some of the DAX functions do, I’m going to give you a homework assignment.  In the following list, I have included links for several other common function groups used in creating calculated columns with PowerPivot for Excel.

For Information Functions, go to: http://technet.microsoft.com/en-us/library/ee634552(SQL.110).aspx

http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-information-functions.aspx

For Logical functions go to: http://technet.microsoft.com/en-us/library/ee634365(SQL.110).aspx

http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-logical-functions.aspx

For Math and Trigonometry functions go to: http://technet.microsoft.com/en-us/library/ee634241(SQL.110).aspx

http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-math-and-trigonometric-functions.aspx

For Statistical functions go to: http://technet.microsoft.com/en-us/library/ee634822(SQL.110).aspx

http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-statistical-functions.aspx

For the next several weeks, there are some other topics I want to cover while you spend some time exploring and working with these functions before moving on to some of the more complex DAX functions and how to begin nesting them to produce interesting results.  One last point that deserves repeating, while the Time Intelligence functions are actually related to the Date and Time functions, they are significantly more complex to understand so we group them separately and will examine them at a later time after exploring some of the other function groups.

Advertisements

Training – Expense, Employee Perk, or Business Necessity

Depending on your point of view, you might see employee training as any of these three.  Taken in isolation, employee training does cost money.  There is the actual cost of the training of course, but there may also be travel expenses and room and board expenses if the training is offered out of the immediate area.  Then there is the ‘lost’ productivity while the employee is away from their regular duties.  Finally, some managers are afraid that by providing training, they just make employees more ‘marketable’ to other companies.

On the other hand, an employee may see the investment that the company makes in their training as an indication of their value to the organization.  Maybe salaries have been frozen and one of the ways that management can say thank-you to a productive employee is to send them to a training event to learn additional skills.  Everyone likes to feel appreciated and when someone invests time or money in you, most people will feel that is a benefit received from the organization that shows that management values them.

Of course a company will only invest in an employee in the hope of getting an employee that performs existing tasks better, faster, or more accurately or they can perform new tasks that will ultimately improve the bottom line of the organization.  Even using training, such as attendance at a conference, with no directly measureable return can help the business if it prevents an employee from looking for employment somewhere else where they think they will be appreciated more.  Losing the skills and knowledge of a good employee costs money to replace.  The cost of recruiting new talent can be as much as 20-30% of the employee’s salary.  And if the company has a high turn-over rate, they may need to offer higher salaries to attract new employees who may see that as an indication of problems with the organization.

In the Information Technology business, the one constant is change.  I’ve gone through several paradigm shifts in my career from programming in a version of mainframe BASIC that only allowed at most two characters to define a variable name, to FORTRAN, a little COBOL, Integer BASIC on an Apple II with a whopping 16 K of RAM, ASP and then ASP.NET, dBASE, FoxBase, FoxPro, SQL Server and most recently SharePoint.  In my experience, companies that did not change and adapt fell to their competition.  Employees who did not change and adapt found themselves working in other careers.

No matter how you look at it, training is a necessity whether you manage your own learning goals or your company supports and helps you obtain the training you need to stay current and relevant.  Trained employees can offer their organizations more value.  In exchange, training employees are generally more happy (read as not looking for a job elsewhere) because their job satisfaction is higher when they feel competent to perform the required tasks.

And if your organization does not provide training and you don’t have a lot of spare cash, there are many good books, webcasts and on-line tutorials.  Don’t overlook the value of local user groups.  Finally take the time to attend a local free day of training at a Code Camp, SQL Saturday, or SharePoint Saturday event near you.

Introduction to Using DAX in PowerPivot

No, I’m talking about the stock market index in the German market.  DAX stands for Data Analysis Expressions and is used in PowerPivot to create calculated columns. DAX has a  lot of similarities to Excel formulas and as we go through the discussion this week and at least next week, you will see a considerable overlap in the way you can use DAX.  However, there are also a lot of differences.  In fact, there are a lot of very interesting additions to DAX when we look at functions next time.

But first you need to understand the basics.  No, we are not going to write a ‘Hello World’ program, but we will look at syntax because you cannot create a valid DAX expression unless you understand the syntax.  So let’s get started.

Just like any other expression in Excel, DAX formulas always begin with an equal sign.  After the equal sign you will find a combination of common expressions, references to columns and table, and of course DAX functions.  Expressions must resolve to a scalar value.  Therefore an expression could be as simple as a reference to another column, even another column in another table in your PowerPivot database.  So how do you reference columns and tables.

Each table you add to your PowerPivot database must have a name.  Furthermore, that name must be unique within the database.  For now, let just say that table names must consist of alphanumeric characters (I’ll list the restrictions later).  You can even have spaces within the table name.  However, my personal preference is not to include spaces but rather to use Pascal case to combine multiple words.  For example, some common table names include:  ‘Sales’, ‘Employees’, ‘GeographicAreas’, and ‘StudentTestScores’.  Notice that I’ve places each table name inside a set of apostrophes.  For single word names with no spaces, this is not necessary, but it is still a good habit to get into.

Within each table, each column must have a name that is unique within that table.  However, you can use the same column name in different tables of the database.  Again, my best practice recommendation is to only use the same name for a column in two or more tables if that column is used as a linking column when defining relations between the tables.  Column names can also consist of any alphanumeric column including spaces.  As with table names, my preference is to eliminate spaces and use Pascal case to create my column names.  When used in a DAX formula, column names are typically enclosed in square brackets such as: [TotalProfit], [Tax], [SATMathScore], and [LastName].

Putting both of these naming conventions together, you may have column references such as: ‘Sales’[TotalRevenue], ‘Employees’[LastName], and ‘StudentTestScores’[SATMathScore].  You don’t necessarily need to use fully qualified names such as these (having both the table and column name).  If the column is unique or if you are referencing a column name in the current table where you are adding the formula, you can use just the column name.

Table and Column Naming Rules

  • Leading or Trailing spaces are not allowed unless enclosing the name in quotes or brackets.
  • Control characters are not allowed
  • Special characters such as the following are not allowed: . , ; ‘ : ^ / \ + * | ? & % $ ! + { } [ ] ( ) < >

Referencing a single column or table by itself really a trivial case and the real interest is in combining columns together and using them in functions.  The easiest way to create an simple expression is with the basic operators shown in the following table:

Operators

Arithmetic:

  • + Addition
  • – Subtraction
  • * Multiplication
  • / Division
  • ^ Exponentiation

Text Concatentation

  • & Concatenate two text values

Logical Operators

  • && And
  • || Or

Comparison Operators

  • = equal
  • > Greater than
  • < Less than
  • >= Greater than or equal to
  • <= Less than or equal to
  • <> Not equal to

Using these operators, you can create a simple expression to calculate profits based on sales and costs such as: ‘Sales’[Revenue] – ‘Sales’[Costs]. 

As with most expressions, calculations proceed from left to right with exponentiation performed first, then multiplication and division and then addition and subtractions.  So if you wanted to calculate your profit as a percentage of sales, you might enter: ‘Sales’[Revenue] – ‘Sales’[Costs] / ‘Sales’[Revenue] * 100.  However, you would not get the results you expected because the system would first divide Costs by Revenue, multiple by 100 and then subtract it from revenue.  What you need is to apply standard precedence characters, the parenthesis as in: (‘Sales’[Revenue] – ‘Sales’[Costs]) / ‘Sales’[Revenue] * 100.  Now costs are subtracted from revenues first with the result divided by revenue and then multiplied by 100.

As you may have guessed DAX expressions recognize specific data types.  Most of these data types are similar those found in Excel.  PowerPivot does its best to use these data types when you bring data in from other sources converting to data types it recognizes automatically.  These data types include:

Boolean True and False
Currency -922,337,203,685,477.5808 to 922,337,203,685,477.4807
Date Valid dates after 3/1/1900
Decimal 64-bit with a range of -1.79E+308 to -2.23E-308, 0, 2.23E-308 to 1.79E+308
N/A Blank, similar to SQL Null.  Test for blank with the function ISBLANK()
Text max length of 268,435,456 unicode characters
Whole Numbers 64-bit with values from (-2^63) to 2^63-1)

 Most of the time, DAX will implicitly convert values based on the needs of the current function.  For example, using the concatenation connector between two integer values results in a string as in: 12 & 34 = “1234”.  Similarly, it will automatically try to promote one or more values in an expression until the expression can be performed as in: “12” + 3 = 15.  Unfortunately, you do not have the ability to explicitly perform a data type conversion at this time.  So in the last example, you cannot include the expression in a type conversion function to return a string of “15”.

In the next entry in this series, I’ll talk about the functions available in DAX and give you links to on-line resources that define the functions.

Responsibility but No Authority?

For this week’s mid-week non-technical discussion, I want to ask if you have responsibility but no authority.

Have you even walked into your team’s weekly meeting and heard your boss tell you that you are now responsible for Project XYZ?  In the meantime you are thinking that this project spans a half dozen different departments, each with projects of their own.  How are you going to get them to cooperate on your project?  You remind yourself that you are so low on the corporate organization chart that they had to add another sheet of paper beneath the rest of the chart.  Well maybe it is not that bad.  Maybe your manager is just trying to help you grow into a better position.  Maybe if you go to them for guidance and suggestions, they will help you out.  Or maybe they are just trying to dodge the bullet from hell (otherwise known as your executive suite) should the project fail which they are betting it will. 

Ok, let’s ignore that last option for a moment because I’m hoping you are not in such a vindictive organization.  On the other hand, failure of a project does not mean that you are a failure unless you miss the opportunity to learn from it and set a personal plan so that it will not happen again.  But that is another topic for a future discussion.  Let’s get back to the topic at hand.  If you have been given responsibility, but no authority, start by listing all of the risks not having the authority poses to the success of the project.  Prioritize those risks and take the top three or four and try to come up ways to counter them.  If they involve things outside of your control, identify who does have control over those factors.  Set up a meeting with those people and ask them how they can help you reduce those risks for benefit of the entire organization.  People will respond better to being asked for their help and opinions than being told what to do and by when.  But what if they refuse to meet with you?  Ask your boss to help set up the meeting and possibly for them to at least attend long enough to show to the others in the meeting that he is giving you the authority through him to make the project successful.

As you work on more projects, success in earlier projects generates its own authority.  People always want to be on a successful team and if you can develop that reputation in the projects you have done, you will also generate an implied authority to match your responsibilities.  Authority and leadership are linked.  But your position in the organization is not necessarily tied to your ability to show leadership, to take responsibilities and to succeed.  And if your organization is resistant to letting you lead by example and to succeed in your responsibilities, maybe it is time to go back and read my article on Jobs vs. Career.

Formatting For Clarity

Today we are going to take a look at conditional formatting in Excel. Why spend time on conditional formatting.  The simple answer is that it makes it easier to interpret your data.  Management is busy (yes, really) so anything you can do to make it easy for them to interpret your data makes more likely they will interpret it correctly.  Formatting is a major component to displaying business intelligence data.  This time I’m going to show some of the formatting features of Excel that make interpretting the data to get at the real information easier.

I’m going to use an example with student test scores (Well, I do work at a school district so what did you expect?). Legal note: The student names and test score are fictitious.) The formatting concepts shown here will apply just as easily to the spreadsheet data at your organization.

The table below shows the initial list of 10 students and the results of three tests, each based on a maximum of 100 points.

Let’s suppose you wanted to find all of the student test scores that were less than 70. These are students that may need additional help. You can visually scan through the data to spot all the test scores less than 70, but it is easy to miss some of the scores, especially if you can imagine having a table of 25 students per class and 5 classes.

Conditional formatting provides an easier way to spot the low scores. To begin, select all test score cells that you want to check. In our figure that would be the range B3:E12. Then from the Home ribbon, find the Conditional Formatting button. This button should have a dropdown component to display more options. (Full Disclaimer Note: For those of you who may be wondering why my images look a little different than your images, I captured my images from Microsoft Office 2011 for the Mac).

Highlight Cell Rules

In the dropdown that appears, position your mouse over the Highlight Cells Rules option. Note the right pointing arrow which indicates that this menu option has another fly-out menu which automatically appears when you position your mouse over the menu option. From this list, let’s select the Less Than… option.

This option opens a dialog that less us specify how we want to change the formatting of cells that have a specific less than condition. The Mac version of Office displays several more options in this formatting rule (see image below) than the Microsoft Office 2010 for PCs version. In fact, the PC version only lets you enter the value to compare the cell values to and a dropdown list of pre-defined formatting options along with a custom option to let you define your own color formatting. Because we want to change the background color of those cells which have text score less than 70 to make them more visible, I entered the value of 70 and chose the format option: yellow fill with dark yellow text.

(A nice feature of the Mac version of Excel 2011 is the sample of the format.)

When you click OK, Excel formats only those cells in the highlighted range with test scores less than 70 as shown in the image below making it easy to spot the low test scores.

Now suppose that knowing the test scores below 70 are not enough. You also want to highlight test score below 60, perhaps showing them with a red background. Your first thought might be to go back to add another Highlighting Cells rule for cells that are less than 60 and color them red. On pressing Ok, you would see the following change to your spreadsheet. Everything is great, right?

While it make look ok, it is only correct because of the order in which you entered the rules. Had you entered the below 60 rule first and then entered the below 70 rule, your spreadsheet should have looked more like the following figure.

In other words, it would look like you had entered only the below 70 rule. The reason is that the order in which rules are entered is also the order in which they are applied to the spreadsheet. If you could slow the process down (or move at hyper speed mode like Superman), you would see that Brett S’s first test appears briefly in red before being changed to yellow.

Color Scales

Another way you can format the cells in a spreadsheet based on their numeric values is to use the Color Scales option in the Condition formatting dropdown. For this option, the fly-out displays different color combinations. These are gradients of either 2 or 3 colors. Excel does not ask how to apply the colors. It just acts over the range of numbers from the highest value to the lowest and defines the colors in a continuous gradient as shown in the next figure.

While a color gradient like this may be great when displaying your comfort level across daily temperatures, it may not be what you want for test scores. After all, a test score of 84, a ‘B’, for Phil C is almost yellow while a score of 89 which is more green. Furthermore, a score of 78, a ‘C’ is only slightly more orange than the score of 84.

 

 

 

What can you do? Select the cell range again and open the Conditional Formatting dropdown menu. At the bottom of the menu select Manage Rules.

 

 

 

 

In the manage rules dialog, you should see your rule. (If you have multiple rules defined already, you have to first select the rule you want to work with.) Click the Edit Rule button in the bottom left corner of the dialog. This opens the Edit Formatting Rule dialog shown below.

Notice that currently the color scale is defined as a continuous gradient from the highest value to the lowest value with the third color, yellow, at the midpoint (50%) of the value range. You can change this to use absolute values. For example, the following figure shows how to set the colors to specific values rather than percentages.

The nature of the 3-color scale will still create a gradient, but by managing the default rule definition, you can create your own custom gradient as shown in the following figure.

You can also display icons associated with the numbers in a set of cells. These icons are often used in KPI (Key Performance Indicator) found on management’s dashboards to help focus attention on the status of items. Let’s use the Avg Score column to display a set of icons to indicate how well each of the students performed over the three tests.

After selecting the data in the Avg Score column, open the Conditional Formatting dropdown menu and select Icon Sets. The flyout menu shows the different built-in icons that you can use. Icon sets contain from three to five icons. Let’s choose the 3 icon stoplight set. If we apply the icon set directly to the score range, Excel tries to divide the range into three evenly sized numeric ranges. In our case, each numeric range is (98.7-64)/3 or about 11.57. This explains why 88.7 is green while 83.7 is yellow since the green range is from 98.7 down to (98.7-11.57) or 87.13. There is no gradient in the icons like there was in the previous example. Therefore, every value in a range will display the same icon. The figure to the right shows the icons displayed on the left side of each cell in the selected range.

Again, if you don’t like the default, you can go into Manage Rules, select the rule you want to change and edit the rule as shown in the next figure. Let’s define any score above 80 to be green, scores between 60 and 80 to be yellow and scores below 60 to be red.

 

 When you click OK, you can see the updated icons for Avg Score show no red icons because no student’s average score in this set falls below 60.

 

Sparklines

The last feature of formatting I will cover this time is Sparklines. Sparklines were ‘invented’ by Edward Tufte and was available as an add-in for Microsoft Office 2007 called TinyGraphs. As of Office 2010, Microsoft included Sparklines their version of Sparklines in Excel, not as an add-in, but as an option you can select out of the Insert dropdown menu.

The Insert Sparklines dialog lets you select the range of data you want to use when creating the Sparkline and the data range where you want to display the Sparkline. In this case, we want to select all of the test scores for all the students again as the data range for the Sparklines. We will place the Sparklines in the column to the immediate right of the Ave Score column. Excel figures out how to use the source data based on the relative orientation of the place where you are putting the Sparklines. Since we are defining a column in which to place the Sparklines, it automatically groups the value in each row of the data source to represent a single Sparkline in the output range. The figure to the left shows how I defined the ranges for my test score data.

 

In this last figure of this blog, you can see the Sparklines for each student.

The cool thing about Sparklines is that they show at a glance the data trend for the data. In this case, we can quickly identify students who have been improving (the Sparkline slants up as you look at it from left to right) and students which may be falling behind as shown by Sparklines that slant downward. The advantage of Sparklines is that they are ‘mini’ line charts that show the data trend. Imagine if you had to create a separate traditional Excel chart for each student to visually show the same type of chart. Not only would it take long, but it would take substantially more screen space. Sparklines are a great feature for dashboards to show trends in measured values over time among other uses.

Why is conditional formatting and Sparklines important? They are used in dashboards to help management quickly interpret the data displayed.

That’s it for this time. The next technical blog will begin a series looking at the DAX language as it relates to creating formulas for calculated columns in tables of the PowerPivot window.

Job or Career?

At the last SQL Saturday, I got into a discussion with one of the attendees as whether they were looking at SQL Server as a job or as a career.  At first they did not get it, but as I explained further, the light started to come on.  I’m going to try to reproduce the core argument here for the benefit anyone else who might be asking the question, ‘Do I have a job or a career?’

The person I was talking to said that they are the same thing.  Their career is their job and their job is their career.  But I suggested to them that there is a very big difference between a job and a career.  I started with two simple statements.  A job is where you go to work each day and do what the boss tells you to do without question or thought about why you need to do it.  A career is where you wake up each morning eager to make a difference by doing things that need to be done, for the right reasons, and that you enjoy doing because at the end of the day they make you feel like you accomplished something.

Occasionally these two definitions merge and your career is your job, but for most people, I suggested, that is not the case.  Many people go to jobs where they do the same things every day with no variation, no challenge and little sense of how what they do fits into the bigger picture.  They become bored and start watching the clock to see how long it is until they can go home or keep asking, ‘Is it Friday yet’? But what do they do when they do go home?  Sit in front of the TV and veg?  Or do they pursue hobbies or other interests, do they take time to learn new things, do they volunteer their time for some organization or cause?

If you think of your job as just a job, maybe you need to look at what you do outside of your job for the answer.  If you just sit in front of the TV with a bag of chips, maybe you need to ask yourself what else you might enjoy doing and then go out and do it.  If your outside activities are not satisfying you, maybe it is time to stop doing them and try to find other activities.  Once you find an activity that you really like ask yourself if there is a way to make money doing that activity.  Maybe at first it is just a little extra cash, but maybe you can come up with a way to turn it into a business that you really enjoy.  When that happens, it may be time to say ‘Good Bye’ to that old job and ‘Hello’ to that new career.

One final point, find something where you can make a difference.  If your boss doesn’t let that happen, use your free time over the next several months to begin making that transition.  No, you do not need to make a hasty decision, but you cannot wait forever for change to just magically happen.  Life is too short to be stuck in a just a job.  Find a career.

C’ya next time for Conditional Formatting in Excel.

Any Way You Slice It

For the last year, I’ve been doing sessions on PowerPivot for Excel at many SQL Saturday events in Florida.  How does that relate to SharePoint you may ask?  Excel documents can be published to SharePoint if you have the Enterprise edition of SharePoint and if you have PowerPivot for SharePoint installed on your server as well, you can manipulate the Pivot tables and charts to drill down through information as well as change the filter or slicers on the chart.  What are slicers?  Glad you asked.

Prior to Microsoft Office Excel 2010, Pivot Tables and Pivot Charts only had Report Filters.  As the name implies a Report Filter allowed the user to select one of the dimension fields to filter the data displayed in the chart or table.  As shown in the figure to the right, it would show the name of the selected field in one cell with a dropdown list in the next field of the possible filter values.

By default all of the data is selected.  However, using a tree structure, you can see the possible values for the field.  In this case, there are only three continents listed, each of which is also selected.  If you first make sure the checkbox ‘Select Multiple Items’ is selected, you can choose to deselect any of the continents by unchecking the corresponding checkbox.  For example, you could deselect Asia and Europe to show only data for North America.  (Note, you can also toggle the All checkbox and then just select North America as well.).

When you click OK, the dropdown rolls back up and you see the name of the selected filter value.  Or do you?  If you select more than one item, and close the dropdown, you see instead of the selected values, a placeholder that simply says: (Multiple Items) as shown in the following figure.

This is not very helpful when viewing the pivot chart or table to understand what is really going on.  Of course you could always open the dropdown to examine the filter (note the drop down arrow shows a funnel to visually indicate that a filter is in effect).  However, that requires extra keystrokes.

Another problem with the Report Filter feature is that you cannot select one of the dimension fields that you are already using in the chart or table to define a filter.  So if you are using Continent Name as one of your vertical or horizontal dimensions, you cannot filter the report by Continent Name.

Finally if you had several pivot charts or pivot tables in the same worksheet or even in the same workbook, there was no way to tie them all together with a single filter that you could change in one place and affect all of the charts and tables.  Rather you had to add a report filter definition for each chart and table and if you changed the value that you were filtering on, you can to remember to change each of the other report filters for their corresponding chart or table. 

Enter Microsoft Office Excel 2010 and a new feature called slicers.  In some ways a slicer is like a report filter in that it limits the data included in the current table or chart.  For example the following figure shows a slicer for Continent Name as displayed for a pivot table.

In the figure to the right, you see that all three continents are shaded indicating that they are all selected.  However, you can easily select or deselect a continent by simply clicking on its name.  By simply clicking on the name of a continent, you select it and deselect any or all other continents.  That is great when you want to look at one value from your filter criteria at a time, but what if you want to look at more than one?

Great question, you can click the name of the first filter value then press and hold the shift key while you click on the name of the last filter value you want to include.  This action includes all of the values between and including the two values you selected, a contiguous selection.  Since the field values are listed alphabetically, this is great if the values you want to include follow one after the other, but what if they don’t?

Then you can click on the name of the first filter you want and press and hold the ctrl key while you click on the name of each of the subsequent filter values you want to include.  This action allows you to select non-contiguous values, but the downside is that you want to select each value that you want.

If you want to get fancy, you can begin with a contiguous selection with the shift key and then add to it by switching to the ctrl key to add outliers.

But the real benefit is that the slicer always shows all of the values that you have included in your filtered pivot table or chart by highlighting the value.  Furthermore, if you have multiple slicers like the following figure that shows countries and continents and you select a continent, countries not in the selected continent are listed separately and dimmed to indicate that the raw data includes records with those values, but the other filter criteria have eliminated those records as possible values.

Slicers also remove the constraint of using the same field as a dimension in the pivot table or chart as well as a filter criteria in the slicer. 

Finally, if you want to use the same slicer that you defined on the worksheet that contains your pivot table for your pivot chart on a different worksheet (or even in the same worksheet), you can click anywhere within the pivot chart (or a second pivot table) and then using the Options ribbon in the PivotTable Tools group, select the bottom portion of the Insert Slicer button as shown in the figure to the left.

 

Interestingly, the corresponding option for the pivot chart (remember to click on the chart first) is the Analyze ribbon in the PivotChart Tools.  (Seems like someone was not talking to someone else during the development process.)  Anyway, from the resulting dropdown select Slicer Connection.  If you choose Insert Slicer you can just adding a slicer specifically for that table or chart.  The resulting dialog will show all of the slicers defined in the current workbook with the field name and the worksheet where they are defined.  The figure below shows an example of the Slicer Connections dialog.

Simply click the checkbox of the desired slicer to reuse it for the current table or chart.  You can even include slicers defined on multiple worksheets.  Now when you change the slicer on the original worksheet where you defined it, it will change the filter for the data in that table or chart and all other tables or charts that you have connected to it.

In future weeks, I’ll explore more features in PowerPivot and SharePoint mixed with an occasion opinion piece for something that is bugging me.  See you later.

Public Sector Salaries – Worth the Investment?

Recently a writer for the Orlando Sentinel, Scott Maxwell wrote an article called, “Public schools, teachers: Worth fighting for.”  Unless you have living under a rock (or out of the country), you know that public schools all over the country are hurting for funds, but Scott Maxwell is referring specifically to schools in Florida.  He argues that teachers in Florida are not paid what they are worth.  I suppose another way to say that is that teachers in Florida are not paid enough to attract the best of the best.  He also states that over the last few years that the average salary for teachers has dropped.  Well, I don’t know how many school districts have actually decreased teacher salaries. Perhaps some have.  Perhaps he simply means that compared to the cost of living index, salaries have dropped.  Or maybe he means that many good teachers are leaving the field and others are retiring early often to get better paying jobs in the private sector.

Salaries in the private sector have always been higher than salaries in the public sector, at least the public school sector, but recently the gap has become a widening abyss.  Interestingly, salaries in other governmental organizations for the same or similar job duties are typically higher than in schools (especially in the technology areas).  Ok, to be fair, that is typically in the Federal positions as opposed to local government.  But I find it insulting to hear politicians like Mike Huckabee not make that distinction and simply label all public employees as overpaid.

On the other hand, Scott Maxwell in his above mentioned article quotes the statistics that Rick Scott (Governor of Florida) raised his chief of staff’s salary by 26% to $189,000 and that House Speaker Dean Cannon and Senate President Mike Haridopolos paid more than 60 of their top staffers $100,000 plus salaries.  Yet these same state legislators cannot find the money to keep the schools funded and to keep good people, teachers, technical staff, management and others from leaving education for more lucrative careers.  Are they representing us?  Are they representing you?

It doesn’t matter if you have kids in public schools or not.  Do you want Florida to be able to attract top talent, both employees and companies who could provide jobs to support our economy, or do you want to see all of our best talent move out of the state?  Contact your state representatives and tell them what you think.  If they don’t listen, fire them at the next election.

Well, what are you waiting for…

Who Has Been Looking At My Site?

I recently wrote the following article for our in-house work newsletter and thought others might benefit from it as well. 

In the past (the olde SharePoint 2007 days), we had been using a third party tool to analyze the web logs to determine who had been visiting our portal. These reports were available from an internal site and were created monthly to show the prior month’s activities.  Each report was archived so that we would have a history record even though we could not keep the activity logs on our server indefinitely.  Unfortunately, this utility was no longer supported when we moved to SharePoint 2010 this summer.

However, SharePoint 2010 itself provides equivalent analytic reports that can answer even more questions then our original traffic reports because you can run these reports at the site collection or even the site level.  To get to these reports, you may need to be at least a site hierarchy manager. Then to generate the reports, follow these steps:

1) Navigate to the site or site collection you want to run reports against.

2) Click Site Actions —> Site Settings

 

 

3) Next locate the Site Actions section and click on Site Web Analytics reports

 

 

 

 

4)  The default report that appears is a summary report showing:

¨ Number of pages views

¨ Average number of page views per day

¨ Total number of daily unique visitors

¨ Average number of unique visitors per day

¨ Total number of Referrers

¨ Average number of referrers per day

¨ And total number of sites under the current site

…but there is more

5) Notice the navigation menu along the left side of the page (shown to the left here as well).  This menu shows the other detailed reports available to you broken into two categories: Traffic and Inventory.

Each of these reports begins with a chart at the top of the generated page that by default represents data from the last 30 days. The chart only shows the top 20 of whatever functionality or feature that is currently being counted in that report.  However, beneath the chart you will find a detailed list of ALL data in sequence from the largest to the smallest.  Of course, this requires multiple pages and the details only shows 20 items at a time. (Can you guess were we are going with 20?)  At the bottom of the report is a page selector to allow you see the rest of the pages.  The interesting feature here is that as you select subsequent pages to see the details, the chart at the top of the page is redrawn to show only the items currently visible in the detailed  report section.

But if you want to see more than just the last 30 days (or perhaps only the last week), you can click the Change Settings link in the blue banner at the top of the report.  A ribbon appears that lets you select predefined periods (Preceding Day, Preceding 7 Days, and an option to custom select your date range. 

From this ribbon you can also change the report scope, apply filters, and change the number of items reported on each page.  You can also download a copy of the data to Excel and create alerts and report schedules that can email you a copy of selected reports on a regular basis that you define.

If you are the owner/site hierarchy manager of a site collection, you may also want to click on Site Collection Web Analytics Reports.  These reports, while similar to the site level reports, include additional reports on searches that have been used as shown in the list to the right. This lets you see what searches people have use frequently and can help you decide for which terms you may want to define Best Bets.

These reports add great new functionality that we did not have in SharePoint 2007.  However, what if we wanted to generate overall portal analytic reports.  As mentioned earlier, our previous history reports no longer work with SharePoint 2010.  However, in addition to the site and site collection reports described here, there are overall portal reports that provide the same information.  Unfortunately, these are not accessible by the average user.  In fact, they can only be accessed by your SharePoint administrators since they are run from SharePoint Central Administrator.

Some final comments.  One of the interesting things that we learned is the distribution of browsers that are being used to access our portal.  While the vast majority of the browser accesses are through IE 7 and IE 8, there are also a fair number of accesses from IE 9, IE 6 and even IE 10 (which is in very early beta).  Of the non IE browsers, Safari leads the pack followed by the other browsers such as Chrome, Mozilla, Opera and others.  Interestingly Safari leads the non-IE browsers by a considerable margin probably because it is the most common mobile device browser in use currently.  Hope you enjoy this new capability to see how all of your pages are faring in the real world.