Chapter 15. Integrating Visio and Excel Services

In Chapter 14, “Building a Management Dashboard Solution,” we developed a management dashboard using SQL Reporting Services and PerformancePoint Services. In this chapter, we create a new dashboard to display Contoso sales data using Excel and Visio Graphics Services. Before we move forward with this chapter, make sure these services are installed and configured correctly on your SharePoint farm.

Deployed Solution

In Chapter 14, the dashboard we developed was composed of several different business intelligence (BI) components. Performance Point Service Scorecard, Analytic Chart, and a SQL Server Reporting Services Report were used to prepare the dashboard. In addition, the dashboard contained a filter on the Calendar Year connected to each of the dashboard components. This new dashboard will use the Visio Web Access web part to display a Visio web drawing on a dashboard page. Drawing will be using an Excel Services data sheet as a back-end data source.

Creating a Visio Web Drawing with Microsoft Visio 2010 Premium Edition

Before we start, we need to prepare an Excel sheet that we will deploy on a SharePoint 2010 document library. We will use this Excel sheet as an external data source for our web drawing. You can either create this sheet manually by executing a shared SQL query against ContosoRetailDW database or access this sheet from the book’s resource material. This Excel sheet shows annual sales data, by continent, for all years:

SELECT  C.ContinentName
    , D.CalendarYearLabel
    , SUM(A.SalesAmount) AS SalesAmount
    , SUM(A.ReturnAmount) AS ReturnAmount
    , SUM(A.DiscountAmount) AS DiscountAmount
    , SUM(A.TotalCost) AS TotalCost
    , SUM(A.SalesAmount)-SUM(A.TotalCost) AS Margin
FROM          dbo.FactSales A
INNER JOIN    dbo.DimStore B
ON    A.StoreKey = B.StoreKey
INNER JOIN    dbo.DimGeography C
ON    B.GeographyKey = C.GeographyKey
INNER JOIN    dbo.DimDate D
ON    A.DateKey = D.Datekey
GROUP BY  C.ContinentName
        , D.CalendarYearLabel
ORDER BY  C.ContinentName
        , D.CalendarYearLabel

When the Excel sheet is ready, publish it on a SharePoint document library. Also, verify that you can open this Excel sheet in the browser by clicking the sheet name in the document library.

Start Microsoft Visio Premium Edition and create a blank drawing with landscape page orientation. Page orientation can be changed from the Design tab on the top menu bar. When you have a blank drawing page up, follow these steps to connect your drawing to the SalesByContinent.xlsx Excel sheet:

Figure 15.1. Excel sheet in a browser.

1. Click the Data tab (available in the top menu bar).
2. Click Link Data to Shapes.
3. Select Microsoft Excel Workbook in the Data Selector window and then click Next.
4. Provide the complete path of your Excel workbook stored at your SharePoint document library (for example, http://sp2010bi/Documents/SalesByContinent.xlsx) and then click Next.
5. Choose the actual worksheet that has data from among the names on the drop-down menu, and then click Next. You can also provide a range of data instead of an entire worksheet.
6. Choose your unique identifiers in the dataset. For SalesByContinent, select ContinentName and CalendarYearLabel as unique identifiers and then click Finish.

   You will see your sales data at the bottom of your blank drawing. We will use this data now to link it to actual shapes.

7. Change the background of your drawing page to the world background by using the Background option under the Design menu.
8. Rename your drawing page 2007. For each year, we will create a separate page. In total, we will have three pages, named 2007, 2008, and 2009.
9. Drop the shape of your choice on your Visio web drawing. I have used the building 1 shape available under Landmark Shapes. You can access Landmark Shapes from the More Shapes menu available on left side of the window (More Shapes, Maps and Floor Plans, Maps, Landmark Shapes (US Units)). You will need three shapes on each page for three different continents. Place the shape in such a way that they represent continents on your drawing.
10. Link shapes to the data rows by dragging rows to actual shapes. Doing so links data in Excel to actual shapes on your drawing. For example, the Data row for Asia 2007 will be linked to the building 1 Shape on page 2007 placed over Asia. (See Figure 15.2)

    Figure 15.2. Visio web drawing in a browser.

    

11. By default, not every measure will appear when you create a link between shapes and data rows. You will need to use Edit Data Graphic option on the Data menu to add the rest of the measure items to your drawing (if required) and update their formatting.
12. Add a text header on each page to explain to users what data they are presenting.
13. Publish the drawing to your SharePoint document library as a Visio web drawing. When the sheet is published, it will look something like Figure 15.2.

At this point, we have our Visio web drawing ready. Our next task is to create the dashboard page, which we will use to display this drawing using the Visio Web Access web part. To create a new dashboard page, follow these steps:

1. Click Dashboard link from the menu bar on left of your Business Intelligence Center home page.
2. Click the Document button under Library Tools. Click the New Folder button. Type Visio Dashboard in the Name text box and click Save.
3. Click the Visio Dashboard folder, and then click the New Document button. An option to create a web part page appears on the menu. Click Web Part Page to create a new web part page.
4. Name your web part page Sales By Continent. For layout, choose Header, Left Column, Body, choose Dashboards from the document library drop-down menu, and then click Create.
5. Click the Add a Web Part link available in the Body of the Page. Select Business Data from the Categories menu and then select Visio Web Access from the Web Parts menu. Click the Add button to add the Visio Web Access web part to the body of the page.
6. Click the Click Here to Open the Tool Pane link to open your SalesByContinent.vdw Visio web drawing as a web part in the body of your dashboard page. The drawing will look like Figure 15.3.

   Figure 15.3. Visio Web Access web part.

   

7. Click the Stop Editing button on the top ribbon bar to stop editing the page.

At this point, we have our Visio Web Access web part page. Now we will add a custom list to our site and connect that list item to the shapes in our Visio web drawing:

1. Select More Options from the Site Actions menu. Create a custom list called Calendar Year.
2. Create a Year column in your list, and then add this list to your Visio web part drawing page, as shown in Figure 15.4. Insert 2007, 2008, and 2009 as list items to the Calendar Year list.

   Figure 15.4. Custom SharePoint Foundation list.

   

Our next step is to link our SharePoint Foundation list with the Visio Web Access web part:

1. Click Page, Edit Page.
2. Select Connections from the menu in the upper-right corner of your Visio web part. Select Get Web Drawing URL and Page Name From and click the Calendar Year custom list.
3. Select Year from the Provider Field Name and Page Name from the Consumer Field Name drop-down menu. Then click Finish.

After you click Finish, a Select column appears on your Calendar Year custom list with a Link icon. You can click that Link icon to pass the associated year value to your web drawing. Drawing filters the page based on the year value you pass to the drawing.

We will now create another SharePoint Foundation list for continents. This time when we will select a continent, the actual continent shape will be highlighted in our web drawing:

1. Create a custom list called Continents. Create two columns in this list: Continent and ShapeName. Continent will have values such as Asia, Europe, and North America. ShapeName will have corresponding shape names (for example, Building 1). Enable the Shape Information window and then click the actual shape to identify each shape name.

   Figure 15.5. Custom SharePoint Foundation list: Calendar Year.

   

2. Now link your drawing to this new list. This time choose Get Shapes to Highlight from the Connection menu. Select the Continent list to pass the value of the ShapeName field from the SharePoint Foundation list to your web drawing.
3. Click Finish. A red border appears around the shape whose value is selected on the Continents list.

Figure 15.6. Custom SharePoint Foundation list: Continents.

At this point, we have a dashboard that displays sales data by continent for three years. This dashboard is using data from an Excel sheet hosted on the same SharePoint site. It is also using SharePoint Foundation lists to interact with a Visio web drawing on the same dashboard page.

Summary

This chapter showed how you can use data-driven Visio web drawings on a dashboard page using Visio Web Access web parts. This chapter also covered how to use SharePoint Foundation list items to either select the page on your drawing or highlight the shapes on your drawing using values passed from the list. This technique can be used as a filter.

The Visio web drawing we created displays sales data by continent. The solution utilizes the Visio Graphics Service and Excel Services to provide the proper visualization of business performance data by continent.

Best Practices

The following are best practices from this chapter:

• To open a Visio drawing in a browser via SharePoint site, you must save your drawing file as a web drawing (.vdw file).

• When connecting to a SQL Server database from a Visio web drawing, use an ODC file stored on the same SharePoint site as your Visio web drawing. This helps you with maintaining a single connection file for all Visio web drawings.

• The Automatically Link button under the Data menu lets you quickly link your data rows to shapes. It is handy when dealing with many data rows.

• It is important to use legends when defining data as nontext. You can create a legend quickly by using the Insert Legend button under the Data menu.

• You can copy and paste shapes to quickly create shapes of the same size on your drawing.