Introduction To Indicators With Reporting Services

In this walkthrough, we’ll take look at using the Indicator visualization in SSRS. Indicators are a great tool to have at your disposal, particularly if you’re creating “dashboard” reports. The general idea of a dashboard is to give the user a high level overview of how areas of interest area performing, and indicators are a really great visual aid to help users quickly identify the current performance of a metric. Let’s take a look at setting up a simple indicator in SSRS report builder. (This walkthrough does assume some general knowledge of SSRS and how to create a data source and data set)

  1. Open Report Builder, and create a new blank report
  2. Create a data source connected to a SQL Server data source (any database will do, we’ll actually be hardcoding our data in the data set)
  3. Create a data set in report builder and paste in the following SQL into the query window:

    SELECT 'Category A' 'Category', 33 'Value' UNION SELECT 'Category B', 66 UNION SELECT 'Category C', 99 UNION SELECT 'Category D', 20

  4. Add a table to the body of your report
  5. Drag “Category” and “Value” from the dataset you created in step 3 to the first two columns of your new table. If you run/preview your report at this point, your table should look like the image below

    clip_image001

  6. Back in the Report Builder design view, navigate to the insert tab, and select the Indicator Visualization

    clip_image003

  7. Place the Indicator into the 3rd column of your table. This should bring up a new window that allows you to choose from a variety of different visualizations. In this example we’re going to use the classic red, yellow, green circles from the “Shapes” category. Make this selection (shown below) and then click “OK”

    clip_image005

  8. Left click twice into the table cell that contains the Indicator to select it. The “Gauge Data” window should appear once it’s been selected
  9. Click the “Gauge Properties” button on the “Gauge Data” window.

    clip_image006

  10. Click on the “Values and States” menu option
  11. In the dropdown labeled “Value” select [Sum(Value)] from the available options
  12. In the dropdown labeled “States Measurement Unit” select “Numeric” from the available options

    clip_image008

  13. Click “OK”
  14. Preview the report.

At this point your table should look like this:

clip_image010

Note that if we look back to our “Indicator Properties” screen and compare the “Start” and “End” values (often called thresholds) for each differently colored indicator, we can see that the red indicator has an end value of 33. Since “Category A” falls into the range of 0-33 it’s marked with a red indicator in our table. One important thing to note about the list of start/end values for each indicator listed on the “Indicator Properties” screen is that they’re evaluated in order from top to bottom, so the order that they appear in is important. If there happen to be ranges that overlap, SSRS will only look as far as the first matching range and then look no further (sort of similar to a switch statement). To further illustrate this point, we could set the “Start” value to zero for all three indicator colors in the properties window and our table would look exactly the same when the report is run. The order of the indicators can be easily modified by simply using the up and down arrows found on the “Indicator Properties” screen.

The “Indicator Properties” screen also allows you to modify just about everything else you might want to modify about your indicator states, such as the color and shape that is presented. If you’re feeling really feisty, you can even add additional indicator states by simply clicking the add button. Who knows, maybe a blue sad face emoticon will be a really useful range/state to add. While you can go kind of crazy with the colors and shapes if you want, make sure to keep the overall goal of your report in mind. Indicators are really good at enabling users to quickly identify good/mediocre/poor performing areas “at a glance”, but you need to keep the number of states low. Also red/yellow/green are pretty much universally accepted as bad/ok/good. Creating an indicator that has 20 states and every color under the rainbow will probably be pretty useless to the report user.

I think that about wraps up our intro to indicators, hopefully you found it useful. Until next time, happy reporting!

Create a dashboard from your SharePoint list data using Excel Services and the Excel Web Access web part

If you use SharePoint in any capacity, chances are you’re storing data in SharePoint lists somewhere. Of course once you’ve got data inside lists, someone is going to want to report off of it. In this post we’re going to take a look at how you can create dashboards inside of SharePoint that pull in data from SharePoint lists using Excel Services, web part pages, and the Excel Web Access web part. Now there are several other technologies we could use like Reporting Services, PerformancePoint, PowerPivot, or Power View to create dashboards, but some of those technologies require enterprise licenses for SQL or the PowerBI functionality in Office 365. In this example I wanted to keep it simple, and use technologies that people are more likely to have. (Although you do still need to have a SharePoint enterprise license in order to use Excel Services.) Our basic approach for building this dashboard will be:

  1. Review our sample SharePoint list and test data
  2. Connect Excel to our SharePoint list and build reports
  3. Display our Excel reports via a web part page and the Excel Web Access web part

1 - Review our sample SharePoint list and test data

Let’s start off by taking a look at our SharePoint list that we’ll be pulling data from.

clip_image001

Our SalesTestList is pretty simple. It tracks the sales amount for a given client. It also tracks when the sale happened and what category the sale belongs to. The month and year columns are derived from the SalesDate column using the calculated field functionality, so users only have to enter date information once (when they pick the SalesDate). The month and year column make it easy to roll up the sales amounts for different time periods in our dashboard. The formula we used to create the calculated field columns for Month and Year are as follows:

MONTH: =TEXT(SalesDate,"mmmm")

YEAR: =TEXT(SalesDate,"yyyy")

Several other formulas for calculated columns can be found here: http://yalla.itgroove.net/2012/09/sharepoint-calculated-column-formulas/

Now that we’re familiar with our SharePoint list and test data, let’s take a look at how we can pull this data into Excel.

2 - Connect Excel to our SharePoint list and build reports

The charts we’ll be building for our dashboard will be pulling in data from our SharePoint list via the “From OData Data Feed” option in Excel. To create a single chart, that shows the sales amount by category and month, follow these steps:

  1. Open Excel 2013 and choose “Blank Workbook” as your template
  2. Click on the “Data” tab on the ribbon at the top
  3. In the “Get external data” grouping, click on the “From other Sources” button
  4. Select “From OData Data Feed” from the list of options

    clip_image002
  5. In the textbox labeled “Link or File:” you’ll need to put the URL to the listdata.svc for your site. This is done by simply taking the url to your site and appending /_vti_bin/listdata.svc to it. So for example if the SharePoint site that my list is in has a URL of https://TestSite.SharePoint.com the URL I’ll need to enter is https://TestSite.sharepoint.com/_vti_bin/listdata.svc

    clip_image003
  6. Click the “Next” button on the data connection wizard
  7. If prompted to sign in, use credentials that have access to your list
  8. Choose the list you want to connect to from the available options on the data connection wizard

    clip_image004
  9. Click the “Next” button on the data connection wizard
  10. Click the “Finish” button on the data connection wizard
  11. Choose “PivotChart” on the import data screen then click the “OK” button

    clip_image005
  12. If all has gone well, you should now be looking at a blank pivot chart in Excel. On the far right hand side you should see a window labeled “PivotChart Fields” that has a list of all the columns in the SharePoint list that you’ve connected to. To begin creating our chart, we simply need to start selecting the values that we want to display in our chart. In our example we want to show the user the sales amount by category and month, so we’ll want to select the following fields:

    SalesAmount
    CategoryValue
    Month
  13. Finally we’ll want to move (drag with mouse) the “CategoryValue” Field to the “Legend (Series)” box so that our PivotChart window in the bottom right looks as follows:

    clip_image006

Ultimately our chart should now look similar to the following:

clip_image007

Finally we’ll want to give our chart a name so we can easily differentiate it from any other charts we create. To name your chart complete the following steps:

  1. Select the chart by clicking on it with your mouse
  2. Click on the “Analyze” tab on the ribbon at the top of the screen
  3. On the far left, type your chart name into the textbox labeled “Chart Name” (in my case the name will be “SalesByCategoryAndMonth”. It’s important that you give your chart a name, as we will be referencing this name later when we display our chart in a web part)

    clip_image009

From here all we need to do is save our workbook and upload it to a SharePoint document library for users to view. It is important to keep in mind that the users viewing the workbook will need to have access to view the list that the workbook is pulling the data from if they want to refresh the chart. (You can check out my full post on configuring the data refresh here) With a little more work we could throw together additional charts in Excel and have our dashboard look something like this:

clip_image011

When/if you create additional charts against the same data source, don’t create a new data source within excel for each one. All you need to do to create additional charts using the same data source is:

  1. Highlight a blank cell in Excel
  2. Click on the “Insert” tab in the top ribbon
  3. Click the “Pivot Chart” icon
  4. Select “Use an external data source” in the pivot chart wizard
  5. Click the “Choose Connection” button in the wizard
  6. Select your existing connection from the “connections in this workbook” section

    clip_image013

Once you’ve got all the charts you need for your dashboard, simply upload your workbook to a SharePoint document library.

3 - Display our Excel reports via a web part page and the Excel Web Access web part

Now that we’ve got our Excel workbook pulling in data from our SharePoint list, and the appropriate charts built, the next thing we’re going to do is build a web part page that we can use to show the charts. Some might argue that this is somewhat unnecessary, and that the user could just open the Excel workbook to view the dashboard. That solution would definitely work, but there are a few reasons why you might want to display the charts in a web part page. For starters, when you open a full workbook in Excel services, the entire browser pretty much turns into Excel, and you lose all the SharePoint navigation links. Another reason might be that you want to show other SharePoint objects on the page in addition to the charts. Whatever your reasons are, here’s how we can go about displaying our charts on their own web part page:

First things first, let’s create a new web part page by following these steps:

  1. Navigate to the /pages directory of our SharePoint site (for instance if your site is https://TestSite.sharepoint.com your pages directory should be at https://TestSite.sharepoint.com/Pages)
  2. Click on the “Files” tab on the top ribbon
  3. Click the “New Document” dropdown
  4. Click “Page”

    clip_image014
  5. Give your page a Title, Description, and URL Name in the available text boxes
  6. Select “(Welcome Page) Blank Web Part page” as the “Page Layout”

    clip_image016
  7. Click the “Create” button

Now that we’ve create our web part page, we need to navigate to it (https://TestSite.sharepoint.com/Pages, then click on the link for the page you just created) and edit the page to add the Excel web part. To do all of that, follow these steps:

  1. With your new web part page open, click the “Gear” icon at the top right hand corner of the page
  2. Select “Edit Page” from the dropdown

    clip_image017
  3. On the new ribbon at the top of the screen click the “Insert” tab
  4. Click the “Web Part” button
  5. Select “Business Data” from the Categories window
  6. Select “Excel Web Access” from the Parts window
  7. Click the “Add” button on the far right of the window

    clip_image019

Once we click the Add button, we should see our new “Excel Web Access” web part on the screen as shown below.

clip_image021

To finish configuring the web part to show the Excel chart we created earlier, complete the following steps:

  1. In the Excel web part, click the link labeled “Click here to open the tool pane”
  2. In the new menu on the right hand side of the screen, locate the textbox labeled “Workbook” at the top of the screen. Use the adjacent browse button to select your uploaded Excel workbook
  3. In the “Named Item” textbox, type the name for your specific chart (naming your chart in Excel was covered in section 2 of this walkthrough). It’s my experience that using the browse button for this textbox usually doesn’t work. You just have to type the name of your chart in manually, so be careful and make sure you type it correctly.

    clip_image022
  4. Scroll to the bottom and click the “Apply” and then the “OK” Button

    clip_image024

With any luck, you should now be seeing your chart displayed in the web part. Keep in mind that there are a ton of options in the window we used to configure the web part to point to our chart. You can control what controls show up for the user, the size, the title etc. I’ll leave it to you to play around with all of those options. To get the options window back, simply click on the web part and look in the far right hand corner for a dropdown triangle, click it, then select “Edit web part”.

Next we’ll want to save and publish our web part so everyone can see it. To do that, follow these steps:

  1. In the upper right hand corner of the screen, click the “Save” button
  2. Once the page is saved, there will most likely be a yellow alert bar near the top of your screen, click the “Check it in” link
  3. Add any comments you want to on the “check in” window then click the “Continue” button
  4. Now you’ll most likely have a yellow alert bar near the top of your screen telling you the page is not published. Click the “Publish it” link in the yellow bar

That’s it! You’re showing SharePoint list data on your web part page. Now if you wanted to get a little more elaborate you could add the rest of your charts and mess with some of the size controls to get something like this:

clip_image026

Or even crazier still you could start to add filters/parameters to the screen, but that’s another post entirely. At any rate, we can now share our SharePoint list data in chart form. Hope this was helpful, and happy reporting/dashboarding!