In this exercise, you will step into the shoes of a business user working in the sales department at a fictitious bike retailer called Adventure Works. You have been tasked with creating a report for the department that will provide a breakdown of internet sales by customer attributes, product categories, and geography. After developing the report, you will need to deploy it to the company's existing SharePoint site where it can be accessed by other users in the sales department.
Before Power View reports can be created in SharePoint, a connection object must be created pointing to the data source, which in this demo will be an Analysis Services Tabular model. This task is typically carried out by IT ahead of time since it usually requires a bit more technical knowledge about the systems' infrastructure than can be reasonably expected of the business users.
However, for the sake of this exercise, we will quickly walk through the process of creating BISM connection file, which as you learned in the previous chapter, is only one of the methods for connecting to a tabular model from Power View in SharePoint.
Note
The following link contains detailed information with regards to creating BI Semantic Model (BISM) connection objects pointing to Analysis Services Tabular models: http://technet.microsoft.com/en-us/library/hh230972.aspx
Follow these steps:
On the New BI Semantic Model Connection page, fill in the values specific to your environment and click on OK to create the BISM connection file. Use the following screenshot for guidance, but keep in mind that the values for the last two fields are specific to my environment. If you are not sure what values to supply for your environments, review the instructions under the Connection Information section at the bottom of the New BI Semantic Model Connection page to determine the appropriate values for your environment.
New BI Semantic Model Connection page
Now that we have a BISM connection file pointing to the tabular model, we are ready to begin developing the Power View report. Follow these steps:
- Click on the name of the BISM connection file created in the previous section to open the Power View report design interface.
- Click on the textbox at the top of the canvas labeled Click here to add a title, and enter
Sales Report. Your screen should resemble the one in the following screenshot:
Power View design interface after adding the report title
The first object we will add to the Power View report is a bar chart comparing Sales Amount by Country. Follow these steps:
- Click on a blank space on the canvas.
- In the Field List, locate and expand the
Internet Salestable. - Click on the checkbox next to the field labeled
Sales Amountas shown in the following screenshot:
- Now locate and expand the
Geographytable in the field list, and click on the checkbox next to theCountry Region Namefield, as shown in the following screenshot:
Field list in Power View design interface
- At this point, the report body contains a table showing
Sales AmountbyCountry Region Nameand should resemble the following screenshot:
Report body with table showing Sales Amount by Country Region Name
- In the visualizations group of the ribbon, click on the Bar button to convert the table into a horizontal bar chart.
- Now increase the size of the bar chart object by clicking on the bottom-right corner of the bar chart and dragging it out towards the bottom-right corner of the screen, as in the following screenshot:
Bar chart visualization of Sales Amount by Country Region Name
The next object that we will add to the Power View report is a pie chart comparing Sales Amount by Customer Occupation by following these steps:
- Click on a blank space on the canvas.
- In the Field List, locate and expand the
Internet Salestable. - Click on the checkbox next to the
Sales Amountfield. - Move the object to the upper-right corner of the canvas in to the right of the bar chart.
- Now locate and expand the
Customertable in the field list and click on the checkbox next toOccupation. - With the new object still in focus, click on the drop-down button in the visualizations group of the ribbon (shown in the following screenshot), and select the Pie button.
- Just as with the bar chart in the previous section, click on and hold the bottom-right corner of the pie chart object and drag it out towards the bottom-right corner of the screen to increase the size. Your report canvas should now resemble the following screenshot:
Bar chart and pie chart side by side
The next object we will add to the Power View report is a column chart comparing Sales Amount by Customer Commute Distance. Follow these steps:
- Click on a blank space on the canvas.
- In the Field List, locate and expand the
Internet Salestable. - Click the checkbox next to the
Sales Amountfield. - Move the object to the lower-left corner of the canvas—just below the bar chart.
- Now locate and expand the
Customertable in the Field List, and click on the checkbox next toCommute Distance. - With the new object still in focus, click on the Column button in the visualizations group of the ribbon.
- Just as with the bar chart and pie chart from the previous sections, click on, and hold the bottom-right corner of the column chart object and drag it out towards the bottom-right corner of the screen to increase the size. Your report canvas should now resemble the following screenshot:
Column chart added to bottom-left corner of report body
Now that we have a few visualizations placed on the screen, let's add a slicer object to improve user experience by providing the ability to filter the data by Calendar Year. Follow these steps:
- Click on a blank space on the canvas.
- In the Field List, expand the
Datetable and click on the checkbox next toCalendar Year. - Move the table of year values to the bottom-right corner of the screen.
- Up in the ribbon, click on the Slicer button to convert the table of year values into a slicer object as shown in the following screenshot:
Slicer button in the ribbon
- Click on and hold the bottom-right corner of the slicer object and drag it out towards the bottom-right corner of the screen to increase the size.
- Now again from the ribbon, click on the button shown in the following screenshot to increase the size of the slicer values. Your report canvas should now resemble the following screenshot:
The Calendar Year slicer added to bottom-right corner of the report body
After spending some time testing out user experience by clicking around the report, it is time to deploy the report to SharePoint so that other users in your department can use it. Since we created this report via SharePoint, this process is really easy. Follow these steps:
- Click on the File button in the upper-left corner of the ribbon and choose the Save As option.
- In the Save As dialog box, navigate to the document library in which you wish to save the report, give the file a name, and click on the Save button. In the following screenshot, the report has been named
Sales Reportand will be saved in a document library calledPowerView. This document library happens to be a special type of document library known as a PowerPivot Gallery. As you will see in the last screenshot of the chapter, a PowerPivot Gallery uses Silverlight to provide thumbnail previews of each view in a Power View report. More information on the PowerPivot Gallery document library is available at the following address: http://msdn.microsoft.com/en-us/library/ee637430.aspx
The Save As dialogue box to save a Power View report
Now other users in the sales department can access the Power View report by navigating to the document library where the report was saved.
The following screenshot shows what the rest of the users see when they navigate to the SharePoint document library where the report was saved. To run the report, the user can simply click on the thumbnail of the report.
Power View report in (PowerPivot Gallery) document library