Category Archives: Dashboards

Excel Tip #23: Adding a Trendline to Your Chart

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Value of a Trendline

Adding a trendline to a chart visually projects the current trend over time or other axis. Adding a trendline to a chart is very easy. I am going to look at two usages – one over time and one over age ranges. Excel supports a variety of trendlines to meet specific data stories. I typically use linear and logarithmic depending on which seems to show the trend the best. However, Excel also supports polynomial, power, exponential and moving average trends. If you want to know more about the formulas and the properties applied check out Microsoft’s support article on trendlines.

Adding a Trendline Over Dates

This is the most common use of a trendline. As you use columns or bars to represent data for each time period, you add a trendline to see how the data is changing. First, create a chart with data represented over time. In my example, I am looking at game usage on cell phones as a percent of polled users over time. Here is my chart without the trendline.

image

In order to add a trendline, click the cross image next to the chart. It will open up the Chart Elements menu. In this menu click the arrow by Trendline to see your options. If you just select Trendline, it will add a linear trendline by default.

image

Once you select trendline, you can hover over the various trendline types to see how they will be displayed on the chart. In order for this to work, you must have the Trendline option selected. Here is my chart with the linear trendline applied.

image

Adding a Trendline Over Age Ranges or Non-Date Data

The other option is to use a non-date based trendline. In order to make sense the axis should still be sequential in nature. In my example, I am going to swap the date out for the age range of the responses. I am also using the Exponential trendline in this.

image

As you can see here, we can see that younger users are more apt to play games. The trend works because the data being reviewed is sequential.

Multiple Trendlines

You can also have more than one trendline on a chart. There are two scenarios that I will show here. In the first scenario, you can add two types of trendlines to your chart. To add another trendline, you go to the cross and select the trendline type you want to add. If you get the wrong one created, unselect the trendline option and reapply. Here is my Game Usage by Age example with the linear trendline added.

image

You can also do this with multiple data points. I am going to add Internet Usage % to the chart and compare as well. When you select the Trendline option, it will open a new dialog box allowing you to select the metric you want to trend. So, in our use case we select Game Usage % for the first trendline. It will match the trendline color to the bar color. Now open the Chart Elements menu and select the arrow and choose linear again. This will let you choose the value to be trended. We then select the Internet Usage % and we get two linear trendlines on the chart.

image

image

Here you can see the year the trends “cross”. It allows for some other interesting visualizations you can add to your Excel dashboard.

Excel Tip #21: Hiding Scrollbars in Excel Services Web Part

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Issue – Scrollbars in Excel Services Web Part

In SharePoint and Office365, we have the ability to add Excel dashboards we have created to our BI site. This is easily done by editing your dashboard page and adding the Excel Services web part. Here is the example I use based on the MyVote application analytics. When deployed as an entire workbook, you will see the tabs at the bottom and the vertical and horizontal scrollbars.

image

The first place to look to solve this issue is in the web part properties.

image

In the first group of settings, we can hide the toolbar. In our view there is no recognizable or relevant changes. We had navigation only turned on, but have now changed it to None. We will skip the Navigation options and check out the next two sections – Appearance and Layout.

image

Here we can adjust the size in appearance. We made this change, but the scroll bars did not go away.

So what do we do now?

Using Named Ranges

After doing a lot of searching online, I came across this option. By using Named Ranges in Excel we can hide scrollbars. While other options were sometimes brought up, the named range option has worked as expected.

Creating a Named Range

Our first step is to create a named range. Highlight the cells you want to include in your web part. In my case, I am highlighting the A1 through M39 range. Once you have the area selected, choose the FORMULAS tab and click Define Name. You can also create ranges using the Name Manager. The name manager is most helpful when modifying or removing existing ranges.

image

In the New Name dialog, specify a name. In my case I use Dashboard if only one Excel range will be used. If you plan to create more ranges for implementation throughout SharePoint, the key is remembering what you named the range. In the web part property settings, there is no look up for the ranges. You will need to get the spelling correct in order for it to be used.

image

Making the Named Range Visible

In order to make named ranges available in Excel Services, you need to change your Browser View Options. You can find the Browser View Options on the FILE menu in Excel. When you open the FILE menu, you will see Browser View Options at the bottom. This option controls how Excel operates in Excel Services on SharePoint and Office 365.

image

In the dialog you will see two tabs – Show and Parameters. For this post we are only concerned with Show. I will expand on this fully in a later blog. In our scenario, we need to change from Entire Workbook to Items in the Workbook. Then we can select the named range we created in Excel. Once you have done this, the only part of the workbook that will be visible in a web part is the named range. You will get an error unless you specify the range.

image

Once you make the change, save the workbook back to Excel.

Updating the Web Part to Use the Named Range

The next step is to update the web part to use the named range. Go back to the page we are working in and edit the web part. (Be aware if you only have one named range exposed to Excel Services, it will update the site accordingly.) In the Named Item area add the name of your named range and it will be what is shown in this web part.

image

Resetting the Size to Eliminate Remaining Scrollbars

The final step is to go into the Appearance section and updating the Height and Width settings until the scrollbars are gone.

Limitations Using Named Ranges

While this does solve a very annoying visual issue and user experience is improved, we do lose some functionality. For example, if you happen to use links in your worksheets to link to other worksheets within the workbook, these no longer work. Also, if you want to use multiple sheets on your dashboard or portal, you will need to add additional web parts or pages to support other named regions. Overall, the user experience trumps these limitations and lead to a really nice dashboard.

Here is the cleaned up dashboard view:

image

Excel BI Tip #20: Wingdings–an Excel Services Supported Indicator Alternative

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Wingdings? Really? How did we get here?

As you have seen from previous tips, I have been working with customers to build dashboards using Excel 2013 in SharePoint 2013. I am a big fan of conditional formatting. However, one of my customers wanted to use a specific design which used triangles as images on their dashboard to indicate whether the trend was improving or worsening. What you may not know is that drawing shapes and textboxes are among the objects not supported in Excel Services.

Here is what it looks like in Excel:

image

Here is what it looks like in Excel Services – note the warning bar at the top:

image

This led me to my first option – use indicators in conditional formatting, it has a similar image.

image

As you can see it starts out fairly small, not the large shape we want to display. So, we added the indicator into a merged cell and increased the font size. You can see the image is pixelated.

image

Making matters worse, when you upload it to Excel Services it will not honor the font size.

image

Winging it with Wingdings

For some reason, yet unknown, it occurred to me to use Wingdings. Wingdings are TrueType fonts which make them “scalable” because you can specify the font size. In this case we are looking for an upward facing triangle and down facing triangle. So the first thing we needed to do was try to find out if those symbols existed. Here is one of the clearest cheatsheets I found for Wingdings font set: http://speakingppt.com/2011/10/31/finally-a-printable-character-map-of-the-wingdings-fonts/. Bruce has created a PowerPoint slide which is easy to follow. Whether you use, his or look it up on your own, you will find that in Windings 3, the letters “p” and “q” are the directional triangles that we need (p and q). Now let’s build our visualization with Wingdings (and no, I can’t believe I said that as well).

The key to using wingdings is that you need place the font representing what you are looking for in the field as shown here. You can see that the value in the cell is “p” but the wingding font gives us the triangle.

image

Not only can you affect size, you can change the color.

image

But the end goal was to have this work in Excel Services. So I can save this sheet to my Office 365 SharePoint site. As you can see here, it works as desired.

image

Here is how you can put this to practical use. Let’s say we want to use a green smiley (Wingdings – J) and a red frown (Wingdings – L) based on our value. Greater than or equal to .5 or 50% will be smiling and less than .5 will be frowning. Assuming that the value we are evaluating is in K5, we would use the following formula to set the value:

=IF(K5>=0.5, “J”, “L”)

This sets the text value that we want to use. Because we are using a character value in the field, we can use conditional formatting to set the appropriate color by using the Highlight Cells Rules – Text That Contains… option. You will create two rules, one for J and one for L. You can use a default setting or create a custom format to change the color.

image image

By using Wingdings and Webdings, you will be able to further enhance your dashboards with a variety of symbols. I hope you have fun with your dashboards and get to tell your users or designers that, yes, you do use Wingdings! Enjoy winging it!

Note: The target environment needs to support the Wingding fonts. We have seen this not work when using iPads for instance. Be sure to consider and test your target environments for this solution.

Excel BI Tip #19: Using the TEXT Function

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Formatting In Cells with the TEXT Function

This is a shorter tip. Sometimes the data you use in a cell needs to be formatted. This is particularly helpful when coupled with the CUBEVALUE function from the previous tip. The TEXT function allows you to format values to the pattern you want. Here are some of examples of using the TEXT function.

  • Number formatting – 1000 to 1,000
    • =TEXT(1000,”#,#”)
  • Number formatting with a decimal – 10.1 to 10.10
    • =TEXT(10.1,”#,#.00″)
  • Date formatting – 10/1/2014 to 2014-Oct
    • =TEXT(“10/01/2014″,”YYYY-MMM”)

As you can see, the standard Microsoft formatting can be applied to values. Here is what it would look like when used with the CUBEVALUE function.

=TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)

This function will not work in the pivot tables themselves because cell formulas cannot be created in pivot tables. However, when you decompose a pivot table, you can use the CUBEVALUE and CUBEMEMBER functions as values to be formatted.

One caveat is that the TEXT function returns the value as a string or text data type in Excel. That means this function is best used to create headers and text with values in it. If you wanted to create a label plus a value in a field for a list of various metrics, you can concatenate the text with the resulting TEXT function.

=”Average Poll Count for Men: ” & TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)

This would return a value such as “Average Poll Count for Men: 8.0” in the cell.

While a simple tip, it can add significantly to the look and feel of your Excel based dashboards.

Excel BI Tip #18: Using CUBE Functions to Break Out of Pivot Tables

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Need to Break Out of Pivot Tables

Pivot tables are a great way for users to interact with data from an analytical source such as SSAS Multidimensional Models, SSAS Tabular Models, and Power Pivot Models. Users can connect to the data model and start working with the data. However, when building dashboards, we often need to display content in different ways – such as a header or clarifying value on a sheet. I have done this by creating a single cell pivot table but I do not think that is the best way to accomplish this. What I really wanted to do is put the value into a cell, but have it still honor slicers or filters. In this post, we will take a simple pivot table with a slicer and decompose it using CUBE functions within Excel and create dynamic cell values which respond to the slicer even after the pivot table has been decomposed.

A Look at Our Starting Point

We are going to start with a simple pivot table with a matching slicer. In the example, we have categories on columns and the gender of poll authors on rows. We are measuring the number of polls created. The slicer will show which polls have had a response [1] versus those with no responses yet [0].

image_thumb[1]

We are going to decompose this table and show how it allows you to move the cells around while retaining the connection to the underlying data.

Decomposing the Table

The first step is to select the table and open the analyze tab on the ribbon. (NOTE: I have reduced the size of my window to make the next steps clearer. The ribbon will adjust with the size of the window.)

image

On the Analyze ribbon select OLAP Tools and Convert to Formulas.

image

The result is that your pivot table will “disappear” and all the selected values will be created using formulas as shown here.

image

The next sections will look at how the table was converted and how we can use the results.

A Look at the Formulas

The pivot table is converted to formulas. You can see the formulas for any of the cells by clicking the cell with the formula bar visible. Let’s look at how each area was converted. We are going to start with the first cell on the upper left and work our way through the cells from left to right and then down the rows.

“Count of Poll ID”

This is the name of the measure we added to the pivot table. Here is the formula:

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[Count of PollID]”)

The CUBEMEMBER function returns the name of the member, in this case, “Count of PollID”. The basic syntax includes the name of the connection as it is referred to in the data connections of the workbook – “ThisWorkbookDataModel”. In our example, the name is that of the Power Pivot model in the background. If you connect to a server based model, you would provide a name with the connection or use the default name. The next portion is the member.

One important note, if you are familiar with MDX and multidimensional models, you will understand the syntax quite quickly. If you have never worked with MDX, you may find the syntax complex or confusing. The Measures dimension referred to here, will apply to any values in the Values section of the Pivot Table fields window.

Finally, you can add a caption if you choose to the value. This will change what is shown in the field. For instance, if I add a caption of “Poll Counts”, it will show “Poll Counts” in the field. This on way to clean up a database name to look more user friendly.

“Column Labels” and “Row Labels”

These are converted to text values in the sheet and provide no value

“Entertainment”, “Fun”, and the Other Category Headers

These are created the same way as the Count of PollID was created. Here is the formula for “Entertainment”.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Category].[CategoryName].&[Entertainment]”)

As you can see the connection is the same, however the structure of the value is different. In this case, it is [Dimension].[Hierarchy].[Member]. The ampersand (&) signifies that the member is the key. Sometimes a number will show up here. You can see the structure that is used based on what is displayed in the Pivot Table Fields window as shown here.

image

 

“Grand Total” (Column and Row)

The Grand Total headers both use captions. The actual syntax uses the “ALL” member of the hierarchy being displayed. Here is the example from the column Grand Total header.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Category].[CategoryName].[All]”,”Grand Total”)

Those familiar with MDX will note that this is a standard way to roll up the data in a cube. Excel uses the same key word here to roll up all the values from the category to total.

“F” and “M”

The gender labels also use the CUBEMEMBER function as shown here.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Poll Owner].[Gender].&[F]”)

As you can see, it uses the Poll Owner as the dimension and Gender as the hierarchy.

The Values or Numbers in the Table

The values section is the last part of the conversion or decomposition to review. It is also the most interesting. Up to this point we have been using the CUBEMEMBER function. The values use the CUBEVALUE function. For those familiar with MDX, each value cell represents a tuple. A tuple is an address for a value in a cell of an analytic structure. When you click inside the cell, then inside the formula bar you can see how the value is created.

image

Here is the formula:

=CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,C$5,Slicer_Poll_Has_Submissions)

Let’s break this down now.

The first parameter is the connection name which is the same as we saw in the CUBEMEMBER function. Next we have three cell references. $B$4 refers to value or [Measures].[Count of PollID] which is the first cell we evaluated. The next reference, $B8, is to row header or [Poll Owner].[Gender].&[F]. The third refers to the current column header which is Entertainment or [Category].[CategoryName].&[Entertainment] which is in cell C$5. The final reference is to the slicer. We will discuss that more in detail in a moment.

The actual value being sent to the underlying data model is

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Count of PollID]”,[Poll Owner].[Gender].&[F],[Category].[CategoryName].&[Entertainment],[Poll].[Poll Has Submissions].[ALL])

What is important about understanding this is that you can change everything around. You can move the headers, etc, and refer directly to the measure you want or you can move header and use the new cell reference. The cell does not even need to be on the same sheet which is the ultimate level of flexibility.

A quick note on the slicer. The is a filter object that has background reference to the data. The name used in the formula is “Slicer_Poll_Has_Submissions”. It is constructed from the name of the slicer as found in the Slicer Options dialog with a Slicer prefix and underscores to replace spaces. Our slicer is named Poll Has Submissions and was converted to Slicer_Poll_Has_Submissions in the formula.

image

Converting Filters

We used a slicer to provide built in filtering to our formulas. If you have a filter you will be provided with a choice. You can either leave the filter intact or convert the filter. Let’s look at both options.

image

Here is the pivot table we will be using for these examples:

image

The filter is for Poll Has Submissions. We are looking at completed polls for each category.

Leaving the Filter

image

As you can see the filter remained intact while the rest of the table was converted to formulas. The primary difference is that the filter reference in the formula for CUBEVALUE is the cell that the filter shows the value in.

=CUBEVALUE(“ThisWorkbookDataModel”,$C$13,$B18,C$15)

image

This is nice if you want to use the filter format and not a slicer to enable users to filter the value. You can also map that same cell to the other values we were looking at as it returns the member value just like the slicer. Because the filter still functions as a pivot table, when you select the filter you get the Pivot Table ribbon. From here you can move the filter to the position on the worksheet you desire.

Converting the Filter

When you choose to convert the filter, it converts the filter value to the currently selected value. In the case of this example we have All selected and the ALL member is selected.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Poll].[Poll Has Submissions].[All]”)

What Does It Mean to Me?

By pulling apart an existing table that has data you want to display, you are able to move the cells around and be more creative in your dashboard design. For example, we can highlight the number of polls related to sports created by men. Then we can create an entire dashboard with other details around this without dealing with a pivot table.

image

Have fun creating more creative dashboards with these functions.

Excel Apps – Not Quite Ready for Primetime

While this is not a regular Excel tip, but it is about Excel. In my Excel BI Tips series, I am always looking for ways to build Excel dashboards or do BI work with Excel that will help everyone. In this case, I am going to discuss a new feature in Excel 2013 and Office 2013 and some of the drawbacks we discovered while trying to bring dashboards into production on SharePoint.

Excel Apps, What Is That?

With the introduction of Office 2013 and SharePoint 2013, Microsoft added the capability to create apps that can be used in the various Office applications to provide enhanced capabilities. I was most interested in the ability to bring in new visualizations in Excel that could be used for creating dashboards on my projects. One of the key advantages of using them, was that they worked when deployed to Excel Services in SharePoint without installing anything on SharePoint.

Here some examples of visualizations I planned to use.

Gauges by DataVis Design

image

People Graph by Microsoft

image

Modern Chart by Microsoft

image

Bing Maps by Microsoft

image

There are a number of other visualization options that are free or for some charge as well as other functions. You can find more of them and more information about Office Apps here.

Initial User Experience Is Poor

After getting a couple of these visualizations in a dashboard over the period of a couple of weeks were were ready to deploy the dashboards for user acceptance. Each user who opens the dashboard will have to clear the following install message from each app when they load the dashboard.

image image

While not a “big” deal for savvy users, this is really an unacceptable user experience for less savvy or less patient users. Furthermore, this could easily turn into a support nightmare as each new user is likely to call or email support regardless of the amount of instructions provided. Given that some of our audience was likely going to be executives, we determined that this would not work for us and would actually reflect poorly on our project.

Ongoing User Experience Issue

So, if you decide to move forward with these apps, you dashboard can look pretty good. However, this brings up a more long term issue. Each of the visualizations created have one or more settings buttons that remain visible, even after deployment. For instance, the gauges have a “gear” and a “question mark.” One the first requests we got from power users reviewing the dashboard was to hide them. As far as I can tell this is not possible. Next, the question was “why doesn’t the question mark contain information about the metric being displayed?” Great question, but the question mark is there to provide information about the gauge not the content. Once again, users don’t need that information. These issues reinforced our decision to remove them from our executive level dashboards and not recommend their use in other dashboards.

image

image

Concluding Thoughts

I am not sure if the problem lies in the way the apps were created or with what Microsoft has enabled in the API designs. In the end, these visualizations need to have a “deployment view” or something similar that will hide all this as well as deploy cleanly for end users. These apps do provide some cool visualizations that are not readily available elsewhere, but they need to be cleaner or more elegant for use in general dashboards deployed in SharePoint. Understanding these nuances will hopefully help you make the better decisions about dashboard design in Excel with Office Apps.

Exploring Excel 2013 for BI Tip #16: Exposing “Values” from a Tabular Model

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

From Power Pivot to SSAS Tabular

As companies move through the cycle of building Excel based solutions for business intelligence and analytics, they eventually end up with a SQL Server Analysis Services Tabular Model. The tabular model comes into play when you need more data in your model or want to support more granular security.

Up to this point, users have been happily using Power Pivot models in Excel to build their analysis solutions. However, once the model is deployed to tabular some functionality or interaction with the model changes in significant ways.

To summarize this point, power users or data modelers will create Power Pivot models in Excel. These models may or may not be deployed SharePoint, but they need to take them to the next level. You can migrate a Power Pivot model to tabular with ease by using the import option in SQL Server Data Tools.

image

Interacting with Power Pivot

I started by creating a simple Power Pivot model using Adventure Works DW data based on the Internet Sales fact table. I am using seven tables in my model as shown here.

image

I am not going to add any calculated measures to the model because Power Pivot allows me to use the data as it sets. Next we create a pivot table based on this model. I dropped the Fiscal Year onto rows and added OrderQuantity and ExtendedAmount to the values region. When OrderQuantity and ExtendedAmount are added to the pivot table, Excel defaults to a sum calculation when working with the data. Basically Excel creates the calculation for you based on what it knows about the data.

The point here is that I have data that can be used as values without doing any additional work with the model. I saved the workbook, closed Excel and moved on to the next step.

Interacting with Tabular

First we need to convert the Power Pivot model to a tabular model. Which is done by importing the model we just saved in SQL Server Data Tools. Once we have the project open, we need to deploy the model to a SSAS tabular instance so we can connect to it with Excel.

image

Now that it has been deployed to SSAS we can reopen our workbook and add a connection to the tabular model. In the field list we notice three differences now that the model is tabular.

1. The SUM symbol (sigma) is used to highlight values or measures that can be calculated.

2. The values we created in the Power Pivot model show up here.

3. In the Values section, “_No measures defined” is shown.

image

When working with multidimensional models, the Values section are represented the same. That makes sense as the connection that Excel is using is based on MDX not DAX. This significantly changes the user experience.

Let’s add a new measure to our Power Pivot model and try to do the same in the tabular model. We can still drop the DiscountAmount into the values section in our pivot table based on Power Pivot. However, when we try to do the same on tabular we get an error saying that we cannot add it to that area of the report.

image

In order for us to use DiscountAmount as a measure we will need to create an OLAP measure (See Excel Tip #8 for details) to use it in this Excel workbook or we will need to add it as a calculated measure in tabular and redeploy for it to be available.

What’s Happening

Because Excel treats a tabular model the same as a multidimensional model in SSAS you will need to add calculated measures for all measures you want to use as values in pivot tables in Excel. Multidimensional models are highly structured using the dimension and measure group techniques. While tabular “feels” like Power Pivot, to be used by Excel it needs to appear structured like multidimensional cubes.

Making this more interesting is that Excel uses MDX to communicate with tabular models, not DAX. As a result, we are able to use the OLAP tools in the PivotTable Tools ribbon.

image

This option is not available when working with Power Pivot models in Excel.

Impact to Users

Overall the impact to users, in particular power users and report builders, is that they have less “freedom” to design when using a tabular model. If they want to add more calculations, they need to be familiar with MDX. Furthermore, if they want the calculations to be generally available they need to work with IT to deploy updated models.

Hopefully we will see DAX supported interaction with SSAS in the future, but for the moment you need to understand how tabular and Power Pivot differ when using pivot tables in Excel.

Exploring Excel 2013 for BI Tip #15: Locking Slicer Position

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Issue

A picture, or in this case two pictures, are worth a thousand words. I created a pivot table from Power Pivot and then added two slicers above the pivot table. The pivot table contains a date hierarchy which can be expanded and collapsed. During this process the slicer moves around which is not optimal when you are creating a visualization in Excel such as a dashboard. Here are the screenshots which highlight the issue.

How I set it up:

image

What happens when the date gets expanded:

image

How to Fix the Slicer Position

Right click the slicer you want to keep from moving, in my case that is the second one. I first looked in the settings, but saw nothing. I stumbled onto the Size and Properties option which opened the Format Slicer slide out menu. If you expand the Properties section select “Don’t move or size with cells” option, the slicer will no longer move.

image

This is just one more way to use slicers to improve the user experience in your Excel dashboards and reports.

Exploring Excel 2013 for BI Tip #14: Sparklines and Pivot Tables

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Sparklines and Dashboards

There are a lot of visualization possibilities with Excel. When creating dashboards, sparklines are a good visualization of what happened over a data series. My goal was to add sparklines to a pivot table so it could be added to a dashboard. After many failed attempts, I was able to get the following to work.

On the INSERT tab, you will find the Sparklines options. In my pivot table I am going to add Line and Column Sparkline visualizations using the MyVote submission counts.

image

Here are the steps that I used to add this visualization to my pivot table.

First, I created a pivot table with Submission Count as the measure, the rows were the Poll Categories, and the columns are the quarters of the year. Here is what the original data looks like.

image

In this case, I kept the Grand Totals for both columns and rows turned on. I am going to use these areas as the targets for the sparklines. I am going to use lines for trends over time on the Grand Total column. Then I am going to use the column visualization to show the category distribution on Grand Total row.

Adding the Line Sparkline

To add the line sparkline, select all of the data cells (no grand totals). Next, select the Line Sparkline option. This will open the Create Sparklines dialog. In the dialog, you can see the Data Range is already populated with the highlighted cells. The Location Range is empty as shown below.

image

Next, you select the columns in the Grand Total column, and that cell range will be added to the Location Range field. This will put the sparklines in those columns and they will match the data trend. For clarity, the final step would be to change the column name to “Trend” and change the font color to white so the text is not seen. Here is the result.

image

Adding the Column Sparkline

Next up, we will add the Column Sparkline. Highlight the same cells as before. Once the cells have been highlighted, select the Column Sparkline option. Select the Grand Total row for the location. This will show the distribution within the quarter for the categories. Changing the font to white does not hide the value in this case. I actually reduced the font size to 1 to make it nearly invisible. (There is no transparent font available.) Here is the result.

image

I also added lower right corner by selecting the Grand Total column cells as the data and that cell as the location to get a consistent look at distribution. One other note, the Grand Total row is called “Trend” as well because they have to have the same name. But, overall, this was the look I was working toward.

Limitations and Nuances with Sparklines

Now for the stuff that doesn’t work as you would like. Sparklines are technically not part of the pivot table. As a result, the table needs to be static in shape. This means rows and columns need to stay the same in count and position.

I am going to add a category slicer to my example. When I select the Entertainment category, all of the sparklines are “stranded” in space. Quarter 2 disappears because it has no data and as a result the trendlines are no longer in the table. This is also true for the columns as four categories are eliminated by the filter. Worse yet, if you look at the filter, you will notice we have no poll submissions in the News category. When that is added the sparklines will end up in the last data row as opposed to the Grand Total sections.

image

Sparklines are a nice tool to have, but you need to understand what is the best way to use them in the context of what you are doing.

Reference and Credit

I ran across this during my search for how sparklines work in pivot tables: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-you-insert-a-sparkline-into-a-pivot-table/e072570d-b367-41f1-b2d6-2dbe939db311.  As I note with the limitations to my solution, the forum post above calls out some alternatives which allow for more dynamic approaches, but they also involve coding. Furthermore, the comment from Andrew Lavinsky (MVP) confirmed that this was possible and that it is supported in SharePoint Excel Services.

Exploring Excel 2013 for BI Tip #12: Icon Sets

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using Icon Sets

So far we have covered the data bar and color scale options in conditional formatting. Up next is Icon Sets. This conditional formatting option can be very valuable when creating dashboards using Excel.

image_thumb1_thumb

Icons in Brief

Excel has a number of icon sets that can be used to visualize status and trends. These are organized in Excel into groups based on their usage as shown in the image below.

image

Your specific use case including the data measured and the message to communicate will determine which icon set you choose. Often icon sets are used in scorecards and with key performance indicators (KPIs).

Creating a KPI with an Icon Set

When making a choice on icons, it is important to keep in mind what you want to communicate and who your audience is. In the following example we will look at setting up a KPI or status icon set using the tri-color, differing shape set.

image

This is a good choice to represent data that can be shown as “good”, “warning”, or “bad” status.

Using shapes as well as colors is particularly helpful if you have color blind users.

In my example, I am using a pivot table using Power Pivot data. I have a series of poll questions and the number of submissions. Let’s start with the default implementation. First, click into the cell with the value (in my case, Submission Count). Next, select Conditional Formatting > Icon Sets > <<select the shape noted above>>. This will put an indicator in the field as noted below.

image

As you can see only the field selected has the icon set. What we need to do is apply the formatting to the entire column. That is done by clicking in the cell and then clicking the formatting button that pops up by the cell.

image

This button will show the following selections for applying formatting rules:

  • Selected Cells (currently selected)
  • All cells showing “Submission Count” values
  • All cells showing “Submission Count” values for “PollQuestion”

Obviously, you would see your value column and your reference column names. If you have nested reference columns (e.g. a poll type) you would want to select the third option so it only was measured against the values in scope with the initial reference column. The second option will apply the formatting to the entire column no matter the level in the pivot. For this example, we will use the third option. Now look at the column results:

image

As you can see, the formatting has been applied to the entire column. Curious how it was applied? In the ribbon, select Conditional Formatting > Manage Rules. In that dialog, you should see the rule you created. With that rule selected (which is the only rule if you started new), select Edit Rule. In the resulting dialog, you can see that you can change how the rule is applied, the rule time, and the description. Here we will focus on the description. In the description area, you can see the Icon and the values that are applied. By default, it groups it in to percents. This is done by taking the maximum and minimum values and dividing the values into thirds and applying the value.

image

You can change the type from Percent to Number, Formula or Percentile. I often find that I need to use a number for that goal. However, if you use a formula you can refer to a sheet with goals, for instance, that let’s you do some what if scenarios as well.

You can also show the icon only which gets closer to the look and feel of a scorecard. I encourage you to explore the possibilities and create scorecards that can be used by your executives, managers, and other users who need to get an overview of how things are going.