SQL11UPD06-TSCRIPT-08
This wiki is a transcript of a previously recorded video.
Related content assets:
Welcome to this demonstration, Exploring Self-Service Reporting with SQL Server 2012 Power View. I am Peter Myers. I am a SQL Server MVP and a Mentor with SolidQ.
The starting point for this demonstration is that I have a PowerPivot workbook. So this is a tabular model that can be used as a data source for Power View. And so I am going to navigate to my PowerPivot Gallery and on the Documents ribbon, I go ahead and upload the PowerPivot workbook. This workbook represents sales analysis information. And so now that has uploaded to the gallery, this is a launch point for the Power View authoring experience. So note here on the right-hand side, I can click to create the Power View report. That then opens up the Power View designer based on the tabular model embedded into the PowerPivot workbook.
A familiar interface is what we see. The ribbon, first a Home ribbon and very familiar with those who are already working with Office. The canvas then allows me to go ahead – first of all, add a title for the report. So I will name this Sales Analysis. Here on the ribbon I can open up the filters area. And in order to define a filter that will be used to filter all data in this first view named Sales Analysis, I use the Field List located here on the right side. So I expand the Date table to reveal that there are three fields. And it’s my intention to filter by a particular quarter. So I drag the Quarter and I drop it into the filter area. And here in the filter area, I then select the filter value for calendar year 2004 Q1. To save space, I collapse that filter area. And to introduce the first report then, it couldn’t be simpler than a single click of the Salesperson table. When I click it, because of the model has a default field set defined, those default fields, including the image of each of my salespeople, appears on the report as a table. So what I am going to do is change the visualization across to the Card. So now we see that, each salesperson, we have this card. I can resize it so now I can see four of those cards. And I can also convert this to tiles. So here on the Design ribbon, when I tile it, we now see that the images for each of the salespeople appear across the top and that we have the card embedded inside.
Now clicking inside I can create a new report based on the Product table. So here in the Field List I select the Category, Subcategory. I am interested in the sales analysis for this salesperson. So under Reseller Sales, I select the Sales measure. Note, down here is where the table layout is designed. So basically, this table, tiling by photo, includes category, subcategory, and the sum of sales. I do have the ability to modify the aggregate function. So I am more interested in the average sales that were achieved. And then, finally, bring in the profitability. Then what I can do is resize this as necessary. So note the way, with convenience now, I can select another salesperson. We see the card here showing details like phone number and then I have the details broken down by category, subcategory, average sales and the profitability achieved. All for calendar year 2004 Q1. Remember that I have filtered the entire view.
Now to introduce a new report on the canvas, and, inside this view, I just click into a blank area. Then I can start to find in new reports simply by selecting fields in the Field List. So here in the Product table I select Category, and then on the Reseller table I am interested in a breakdown by Business Type and then I would like to see the Sales. The default behavior when you create a report like this is to introduce a table, as we see here. But with this selected, I can also switch across and say I would like to see this as something different. I would like to see this as a column visualization. And so now, when I resize this, and I am going to switch across to full screen so it’s easier for you to see there in demonstration, what we then see is the ability for us to understand what the sales were by category in business type. When I hover over one of these segments of the bars we get a tool tip that shows us details. And what’s even cooler is that when I click this, you will see the selection then of the warehouse bike sales filters the corresponding reports on the same view. So now, for David Campbell, I get to see the breakdown of the category, subcategories, average sales and profitability. Let’s take a look at the value added resellers. And again it updates. What was going on for Garrett the salesperson? Then we can see the value added bike sales for Garrett. To clear that filter, I just click in a blank area of that chart and it resets it and no longer applies that filter. To return to the design mode I press Escape.
Then what I am going to do is introduce another report in the bottom right corner of the canvas. So I click in this area and that allows me to define a new report by using the Field List. This time I select from the Salesperson table the Salesperson, I would like to know what sales they achieved, profitability and the number of orders. And again by default we get to see that we have a table report. But on the design ribbon, I change this across to Scatter. Let me go ahead and resize. Now on the bottom right corner, we get to see the layout for this report. What I am going to do is introduce also a Play Axis so we can animate this Scatter Chart across time. So a convenient way to do this is either drag and drop, or, I come to my Month field, click this down arrow, and select Add as the Play Axis. To view this I can hover over the top right corner and I can pop it out and now we get to see this report. We can hover over the bubbles and we get a tool tip describing statistics about what they represent. And I think the coolest feature (in my opinion, anyway) is that we can go ahead and animate. So here we are animating across the months in that quarter to see what the sales were that were achieved. And note that when I click on one of the bubbles, you get to see the paths that it followed. You can click over the different bubbles across time to see statistics. For example, here, for Shu Ito, in March 2004, this is what she achieved. Here is what she achieved also in March 2004, but I guess a different date in March. Then I can go ahead and pop it back in. Then I just click in a blank area to reset that filter because it is filtering across the rest of the report. I think what is really cool about this reporting tool is that with minimal clicks – and that was very much the design principle – with as few clicks as possible there is the ability to create this presentation-ready, highly interactive, rich report.
Let me take it one step further, because a report consists of views. By default it is a single view, think of these like PowerPoint presentations with multiple slides. Here we have a presentation with one slide. On the Home ribbon I go and introduce a new view. And so now we have two views inside this report. I click the title and I press the Delete key to remove it. And what I am going to do is introduce a very simple report here that is based on the category and the sales achieved for the category. Now note that views don’t inherit the filters from the previous view. The Filter Area is actually empty. The filters that I applied in the first view do not carry across to the second. So what we are seeing here are all sales from all categories. Then what I do is convert this to a column and I can resize this to fill the entire view. I love this feature as well. And then I come up to the Quarter and then I go ahead and add the Quarter to the vertical multiples. On the Layout ribbon, I then specify the grid and I lay this out to be four by three. So now what we are seeing are the sales by quarter, by category. Now let’s just make this clearer to see. So I go ahead and put this in full screen mode and here we see it. For each of the quarters, broken down by categories, that we see down here what the sales were that we achieved.
The next part of the demonstration then is to go ahead and save the report. So on the File tab we have the ability to save, to print, even export to PowerPoint. And believe me, I don’t have time in this demonstration, but when you export to PowerPoint, it includes that interactivity providing that you have connectivity to the report itself. All right, so I am going to go ahead and save it. We’ll save it back to the PowerPivot Gallery. Remember, that is where I launched this authoring experience from. And I’ll name this Sales Analysis. When I click Back in the browser, it takes me back to the PowerPivot Gallery, and we get to see not just the workbook that I uploaded but the Power View reports also appear here with their thumbnail previews. So users who have actually been requesting the report can preview and if they want the report, they simply click it and that just opens up the Power View experience in the browser. Now providing they have permission to edit the report, they can switch across and edit. Otherwise, what they can do is put it into full screen mode which is truly a presentation mode that provides maximum use of the real-estate on the screen. Great for presentations and meetings. So there we see the report. I am going to switch across to full screen mode. I’ll just point out to you, on the bottom left corner we have the ability to navigate between the different views, and in the bottom right corner we have the ability also to navigate between them as pages. So take me to the first, take me to the second. And note here when I switch back to the first, the ability for the user not just to interact. For example, show me what Jillian sold and let’s take a look at that from the point of view of the components sold at warehouse level. And let me pop out this chart. But I am also interested when I pop it back in to know what was it like for a different calendar quarter? And so I can come to the filters area and I can configure something different and so I am now interested in the previous quarter and there we go.
That pretty much concludes the presentation. It showed how to upload a PowerPivot workbook. A Tabular BI Semantic Model is a requirement for Power View. A PowerPivot workbook was used in this presentation. How to launch Power View, and then how to, simply, with a minimal number of clicks, create presentation-ready, highly interactive report, and to save it. Thanks very much for watching.
Return to SQL Server 2012 Developer Training Kit BOM (en-US)
Richard Mueller edited Revision 1. Comment: Removed (en-US) from title, added tag
peter_myers edited Original. Comment: Edited by the presenter.