Category Archives: SharePoint

Excel BI Tip #26: Using a Data Spreadsheet or Tab

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!

Data Sheet or Tab in Excel

With a lot of the dashboard designs in Excel I work on, we often use CUBE formulas and other calculations and functions to get the data ready for the presentation area. One of the key things we do is create a sheet in the workbook, or tab, that will allow you to hold this data. This allows us to refer to cells on the data tab in our visualizations without trying to support visualization techniques along with calculations.

The most common scenario is when I want to present numbers in the visualization that are not in a pivot chart or pivot table. By keeping this in the data tab I have maximum flexibility in the visualization.

Let’s look at the following example using Adventure Works data (from http://msftdbprodsamples.codeplex.com/). We will create the following “data box” visualization using a data tab.

image

First, get the data into data sheet using a pivot table. Once we have the data we want to present there, we flatten the pivot table (see Excel BI Tip #18 for details). Now we can refer to the fields we need using the data tab. In the following images you can see the data box referring to data on the data tab which uses the CUBE functions to get the data.

image  image

As you can see, this allows us to contain a lot of data that is used for processing without cluttering up the visualization.

Hiding the Data Sheet from Users

Using a data sheet also means we need to hide this sheet from our users. You can hide the sheet in Excel directly. This is most useful when the workbook will be shared as a workbook. However, if you deploy the workbook to SharePoint or Office 365, you can use the Internet Settings to only make ranges or sheets visible depending on your implementation. I prefer this process as it allows dashboard designers to easily access the data without needing to be concerned with hiding the data sheet once they are done. (Refer to Excel BI Tip #21 for more about using ranges.)

When used in SharePoint or Office 365, their is no impact to the visualizations which use the data sheet. While not visible or available to the user, the data sheet stills supports the visualization as expected. In scenarios I have delivered, this technique has allowed for extensive data manipulation and formatting to present data in meaningful ways.

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.

Creating a SharePoint Server Farm on Azure from the Gallery

As many of you know creating a SharePoint farm for testing can be a daunting task. I volunteered to help troubleshoot an issue that was working with SharePoint Excel Services and it couldn’t be done in Office365. So, my first attempt was to grab the SharePoint Server 2013 Trial from Azure’s VM Gallery.

image

However, once I created the VM, it turns out that SharePoint is not installed, which is what I really wanted. To complicate matters further, the download stopped because IE was blocking file downloads. You can change that setting in Internet Explorer options on the Security tab. Select the Internet Zone and click on the Custom Level button. Scroll down to the Downloads section and enable File download. Restart IE and you can get the file downloaded. Of course, we have to ask, why isn’t it already enabled on the VM since that would be the obvious goal.

As I was troubleshooting that issue, I happened to check out the Azure gallery on the Azure site and found a SharePoint Server Farm gallery image that I could use.

image

image

I clicked on the Farm icon to see what it was. It does the multi-server farm install in Azure.

You start the process by clicking the green Create Virtual Machine button in the middle of the screen. And then you are off to the configuration parts. The next few screen shots will show you the basic configuration points used during the install. Click the button… and your journey will begin.

image

This will open up the preview portal from Azure with a blade for configuring your farm.

image

Add a group name and work your way through the configuration steps on the blade. It will create 3 VMs by default unless you select the Enable high availability checkbox under the password textboxes.

image

Each configuration step will open another blade in the portal allowing you to configure the various servers to be added to the farm.

image

Once you have configured the settings you are ready to create your farm. Click the Create button and the “magic” starts to happen.

image

You will see the following tile added to your Startboard.

image

It took a little more than an hour to set up the three servers required – domain controller, SQL Server and SharePoint server.

image

If you click on the new tile, you will get an overview of what was created including resources and estimated spend. The next step is to log into the instance and check out what is set up. If you click the Deployment history button and then the Microsoft.SharePoint.Farm tile, you can see the SharePoint Central Admin URL and the SharePoint Site URL. Each of these blades provide additional information about your environment.

image

Log in to Central Admin or the SharePoint site. And you now have a functioning SharePoint Farm in Azure. If you are using this as a testing platform be sure to manage your VMs (e.g. shut them down) to reduce costs.

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.

The Changing World of BI, A New White Paper for Magenic

MagenicLogo2012x70tallIn the ever changing landscape that is Business Intelligence (or is that Business Analytics?), a fellow business analyst from Magenic, Chuck Whittemore (B), and I authored a white paper based on our experiences over the past months.

What I think makes our work unique, a BI architect and a business analyst came together to show our worlds colliding in the age of modern BI tools.  While the goal has always been to bring the data to the users and let them work with it as creatively as possible, the tools to do this were IT focused.  What we see now is that with the advent of in-memory, client-side BI tools, users are now able to get to this on their own.  Microsoft has invested heavily in Excel to make it a first-class BI tool.  Our paper discusses this disruptive nature of the new tools and how Excel is being pushed to the next level.  After all, Excel is everywhere already.

Enjoy the read and I welcome your feedback.

The Changing World of Business Intelligence: Leading with Microsoft Excel