Tutorial: Create a Sample Report in Power View

مقالة
04/08/2013

Important

Please visit the most up-to-date Power View documentation on office.microsoft.com. Power View is now a feature of Microsoft Excel 2013, and is part of the Microsoft SQL Server 2012 Reporting Services add-in for Microsoft SharePoint Server 2010 and 2013 Enterprise Editions.

In this tutorial, you create a Power View report that demonstrates what Power View can do, using a small sample model created in PowerPivot for Excel. You create tables and convert them into a number of different visualizations. You create tiles and add visualizations to them. You create multiple pages, or views, in your report. You create a bubble chart and add a play axis to it, so you can see how the values change over time. You create small multiples – a set of small charts, one per series, with the same axes. Then you experiment with different ways to filter the data in the visualizations. In the end, you export your report to PowerPoint.

A report similar to the one that you can make by following the steps in this tutorial

Power View report you create in the tutorial

Prerequisites

To do this tutorial, you need to:

Have a computer that meets the System requirements for Power View.
Download and install Power View samples, including the image files, from the Download Center. For more information, see Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012 in SQL Server 2012 Samples.
Install PowerPivot for Excel.

Note

HelloWorld_Picnic is a tiny sample data model that demonstrates how to set data model properties to best present that data in Power View reports. In the scenario, the data is used to plan and track food and drink for picnics. For more information, see Power View Samples for SQL Server 2012.

Before you start the tutorial

Before you can start the tutorial, save the HelloWorldPicnicPowerViewRTM.xlsx file to a:

SharePoint document library. If you save it to a SharePoint document library, create a shared data source that points to the HelloWorldPicnicPowerViewRTM.xlsx file that you saved to the SharePoint document library.
PowerPivot Gallery. If you save it to a PowerPivot Gallery, it doesn’t need the shared data source. You can open Power View directly from the gallery.

Save the images to the associated SharePoint document library.

To create a shared data source (RSDS) file to point to the PowerPivot (XLSX) file

In Shared Documents, create a HelloWorldPicnicSQL2012 folder, and save the HelloWorldPicnicPowerViewRTM.xlsx file there.
In the HelloWorldPicnicSQL2012 folder, click the Documents tab, click the arrow next to New Document, and then click Report Data Source.

If there is no Report Data Source option on the New Document tab, then someone with adequate permissions on the SharePoint site needs to add that content type to the site. For more information, see Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).
In the Name box, type HelloWorldPicnicRSDS.

Note the RSDS file extension.
In the Data Source Type box, click Microsoft BI Semantic Model for Power View.
The connection string for an XLSX file is the full URL to the file, including the file name. For example:

Data Source=’http://<myserver>/Shared%20Documents/HelloWorldPicnicSQL2012/HelloWorldPicnicPowerViewRTM.xlsx’
For Credentials, click Windows authentication (integrated) or SharePoint user.
Click Test Connection.

You should see a message that the connection test was successful.
Click OK.

To save the images

Create an Images folder in the HelloWorldPicnicSQL2012 folder. For example:

http://<myserver>/Shared Documents/HelloWorldPicnicSQL2012/Images
In the Images folder, save the images that you downloaded from the Download Center. For more information, see the Prerequisites section at the beginning of this tutorial.

To update the links to the images

Open HelloWorldPicnicPowerViewRTM.xlsx in Excel.
On the PowerPivot tab, click PowerPivot window.

If you don’t see a PowerPivot tab, you may need to install PowerPivot for Excel.
In the Items worksheet, find the URL in the Photo, Drawing, Category Drawing, and Category Photo columns.

For example, the URL in the Photo column is: https://localhost/Shared Documents/HelloWorldPicnicSQL2012/HelloWorldPicnicImages/rs_crescent_picnic_image_.
In each column, update localhost in the URL with the name of your server.

Start the tutorial

If you saved HelloWorldPicnicPowerViewRTM.xlsx in a SharePoint Server 2010 document library, click the arrow next to the HelloWorldPicnicRSDS.rsds shared data source, and then click Create Power View Report.

If you saved HelloWorldPicnicPowerViewRTM.xlsx to the PowerPivot Gallery, then click the Create Power View Report icon for HelloWorldPicnic .

Power View opens with the blank view in the center, the ribbon across the top, and field list on the right. In the fields section of the field list are three tables. To begin with, the tables are collapsed, showing just their names:
- Dates
- Items
- Quantities
These tables make up the data model on which the Power View report is based.
To expand the tables, click the arrows next to the table names.

Now you can see the fields in each table. These may include calculated columns (marked with a Sigma ∑ symbol), measures (marked with a small calculator symbol), and row label fields (marked with a gray and white icon).

For more information about calculated fields and measures, see Measure and Non-Measure Fields in Power View. For information about the Power View design environment in general, see Power View Design Experience.

Set the title

To change the title text and formatting

Click in the default title box and type a title: Picnic items.
Select the text and click the Italic button, and then make the text Segoe (Bold).
Drag the sizing handle on the right side of the title text box to make it half the width and in the top-left corner of the view.

Bar and line charts

To create bar and line charts

Click the blank view.
In the fields section of the field list, in the Items table, drag the Category field to the blank view.

You now have a one-column table in the view.
In the fields section of the field list, in the Quantities table, drag Sum of Qty Served to the one-column Category table.

The table now has two columns with a total.
In the Visualizations gallery, click the Bar chart.
Hover over the edge of the chart until the double-headed sizing arrows appear over one of the sizing handles. Drag the sizing handle until you can see all the bars of the chart.
Hover over the edge of the chart until the hand icon appears. The hand icon means you can move the chart. Drag the chart to the lower-left corner of the report for now.

Bar chart with sizing handles

Copy and paste a visualization

To copy and paste

With the bar chart selected, on the Home menu click Copy.

You may see a message asking if you want to allow this Web application to access your clipboard. If you do, click Yes.
Click off the bar chart on the blank canvas, and then click Paste.

A second bar chart is pasted on the view.
Make sure the copy of the chart is selected. In the layout section of the field list in the Axis box, click the arrow next to Category and then click Remove Field.
In the fields section of the field list in the Items table, click the arrow next to the Distributors field and click Add as Axis.
In the Visualizations gallery, click the Column chart.

Add a second series to a chart

In the fields section of the field list in the Quantities table, click the dropdown arrow next to the Sum of Qty Consumed field and click Add to Values.
The chart is now a stacked column chart, with the Qty Served and Qty Consumed values stacked on top of each other.
Click the dropdown arrow in the Visualizations gallery and click the Clustered Column chart.

Now the Qty Served and Qty Consumed values are side by side.
Hover over the edge of the chart until the hand appears, and then drag the chart to the lower-right corner of the report.
Hover over the upper-right corner of the chart until the double-headed sizing arrows appear. Drag the sizing handle until the chart is half as tall as the view, and half as wide.

Sort the values in a chart

When you hover over the Distributors column chart, you see sort by Distributors asc. Click the arrow next to Distributors and click Qty Served.

Now the bars are sorted from smallest to largest quantity served.
Click asc. It changes to desc, and the bars are sorted from largest to smallest quantity served.

Move the chart legend

With the Distributors chart selected, click the Layout tab in the Chart Tools contextual tab, click Legend, and then click Show Legend at Bottom.

Arrange items in the view

Drag the small Serve bar chart so that it is on top and in the upper-right corner of the large Distributors column chart.

By default, Category, the first chart you created, will be ‘behind’ or ‘under’ the second chart, Distributors.
With the Category chart still selected, click Bring Forward.

If you no longer have the Category chart selected and it’s now completely covered by the Distributors chart, just select the Distributors chart and click Send Backward.

The Distributors chart with the Categories chart inset on it, with fruits highlighted.

Bar chart inset on column chart

Highlighting and slicers

You can filter and highlight data in your report in a few different ways. All of them allow you to select one or multiple values.

To highlight one chart with another chart

In the Category bar chart, click the fruits bar.

Notice that the values in the Distributors column chart are highlighted to show the amount of fruit from each distributor. Columns for distributors with no fruit are completely grayed.

Tip

To clear the selection, click in the Category chart, but not on any bar.

To create a slicer

Click the blank part of the view, so none of the visualizations are selected.
In the Items table in the fields section of the field list, select Category Drawing.
On the Design tab, click Slicer.

The slicer contains four filtering buttons. Each button features the image of one of the four categories.

Note

If you don’t see the images of the categories, the images may be missing. See the section about saving images in Before you start the tutorial.
Drag this slicer to the upper-left corner of the view, under the title.
Resize it so you can see all four categories.

To create a second slicer

Click the blank part of the view, so none of the visualizations are selected.
In the Items table in the fields section of the field list, select Drawing.
On the Design tab, click Slicer.

This slicer contains filtering buttons, one for each item, with an image for each item.
Drag it to the right of the Category Drawing slicer and make it as tall as the view allows.
Click one of the categories in the Category Drawing slicer.

Note the behavior is different from highlighting in the chart.
- It filters everything in all the visualizations on the report, rather than just highlighting. For example, the Categories bar chart now just has one bar -- the category that you’ve selected in the slicer. The Distributors column chart just has the columns for the distributors in that category.
- It filters the items in the other slicer. If you select beverages in the Category Drawing slicer, then the Drawing slicer will have only beverages.
To show all values again, click the Clear filter icon in the upper right corner of the slicer.

For more information, see Filtering, Highlighting, and Slicers in Power View.

Cards

To convert a table to a card

Click the blank part of the view to start a new visualization, and then in the fields section of the field list, click the Items table name itself.
A table is created automatically with the fields Name, Category, Color, and Drawing.

These are the default fields in this table, as defined in the model on which this report is built.
With the table selected, in the fields section of the field list, in the Items table, click the Distributors field.

It is added to the table.
With the table still selected, click the arrow to expand the Visualizations gallery, and then click Card.
Drag the scroll bar on the right of the card to scroll through the rest of the cards.

Note the name and image are prominently displayed in each card. This is the result of properties set in the model.

Tiles

To create a tile container

With the cards selected, drag the Drawing field from the fields in the card listed in the Fields box in the layout section of the field list to the Tile by box in the layout section.

This creates a series of tiles in a container, one tile for each value in the Drawing field. Notice that the card in the tile no longer has a scroll bar. It is filtered to the value in the Drawing field.

Take a moment to click the different fruits in the tab strip along the top of the tile container. See how the data in the card changes.
Drag the sizing handle on the right of the card to make the card taller than it is wide.
Hover over the edge of the tile container until the hand appears, and then drag the container to the upper-right corner of the report, next to the title.

Tip

You drag a tile container by clicking the edge – not by clicking in the tab strip.

For tips on selecting, moving, and resizing visualizations, see “Working in the view” in Power View FAQ, Troubleshooting, Tips, and Tricks.

To add to the tile container

Select the tile container. Drag the right handle of tile container to make it three times as wide as the card.
Click in the tile container, but not the card.
In the fields section of the field list, in the Dates table, select the Month Name field.

This starts a new table in the tile container.
In the fields section of the field list, in the Items table, select the Name field.
In the fields section of the field list, in the Quantities table, select the Sum of Qty Served field.
In the Visualizations gallery, click the Line chart.

The line chart has a legend, but you do not need it because the chart contains just one line for the food item on the tile.
With the line chart selected, on the Layout tab in the Chart Tools contextual tab, click Legend, and then click None.

Make the chart wider to show the names of all the months.

You can resize the card and line chart inside the tile container, independent of the container, but you may need to make the container bigger first.

To experiment with tiles

Click the different foods in the tab strip along the top of the tile container.

Notice that the card and line chart in the tiles change to show values for the selected food.

But also notice that none of the other visualizations in the view change. They are not controlled by the tile container.
Click the tile container, and on the Design tab in Tile Visualizations, click Cover Flow.

You may need to resize the tile container to fit the card and chart.
Again, click the food items in the cover flow, or drag the slider, to show values for different food items.

Tile container with cover flow navigation

Card in tile with cover flow navigation

Compare highlighting and slicer filtering

Now that you have several different visualizations in one view, you can see how filtering and highlighting differ among them.

Select a bar in the Category bar chart. Notice that it:
- Highlights related parts of the other chart.
- Filters the items in the tile.
- Has no effect on the slicers.
Clear that selection by clicking in the bar chart but not on a bar.
Select a category in the Category Drawing slicer. Notice that it:
- Filters both charts, removing the unrelated bars and columns.
- Filters the items in the tile.
- Filters the values in the Drawing slicer.

Add another view

Your report can have more than one view. You can add a duplicate of an existing view or add a new blank view. All the views in a report are based on the same model.

To add a new blank view

On the Home tab, click New View.
Select the title text box and press Delete.

Bubble charts

Scatter charts display two measures and bubble charts display three.

To create a bubble chart

Click the new blank view to start a new visualization.
In the fields section of the field list, in the Items table, select the Category field.
In the fields section of the field list, in the Quantities table, select the Qty Served YTD (year-to-date) and Sum of Qty Served measures.

Notice that the numbers in each column are the same, because the quantity consumed year-to-date has no date to reference.
Click the Scatter chart button.
Drag one of the sizing handles to make the scatter chart larger.
In the fields section of the field list, in the Quantities table, drag the Sum of Qty Consumed measure to the Size box in the Chart Fields layout section.

This is a snapshot of the totals. The values on the X and Y axes are the same.

If you add a date component, you can see how the values change over time.
In the fields section of the field list, in the Dates table, drag Month Name to the Play Axis box in the Chart Fields layout section.
In the bubble chart, click the play arrow next to Month Name.

The bubbles move, grow, and shrink as their values change over the months. Notice that the months display as watermarks in the upper-right corner, and the bubbles move to the right as the months pass and the year-to-date quantity increases.

Note

If the bubble chart is very small, you may not see the watermark.
Click one of the bubbles. This does two things:
- It draws a line tracing the path of that bubble over time.
- If there are other visualizations on the view, it highlights the value of the bubble in the other charts on the report and filters the tiles to that value.
To see the bubbles even better, you can ‘pop out’ the chart: hover in the upper-right corner of the chart and click the popout icon.

Popout button

Now the chart appears to take up the full view, but only temporarily. The rest of the report is still the same underneath, and when you click the popout icon again, the chart returns to its spot in the report.
With one bubble selected and the chart popped out, click the play arrow again.
Watch as the bubble traces its path.

Bubble chart with play axis and data labels, tracing the path of beverages
To return the bubble chart to its original size, click the popout icon again.
Drag the bubble chart to the upper-left corner of the view.

Add category group colors to a scatter or bubble chart

Sometimes grouping the bubbles in a scatter or bubble chart by category makes the information clearer.

To add category group colors to a bubble chart

Drag the Name field from the fields section of the field list to the Details box in the layout section of the field list, replacing the Category field there.

Now the bubble chart has one bubble for every item. It makes a busy chart.
Drag the Category field from the fields section of the field list to the Color box in the layout section of the field list.

Now the bubbles are colored depending on the category to which they belong.
Click a category name in the legend.

Notice that it highlights the items in that category in the bubble chart.

Small multiples

With tiles and slicers, you can see a series of charts filtered to a specific value. In this tutorial, the tiles filter the data to show the value for one of the foods. The slicers filter the report for one or more categories of food.

With small multiples, you can see data in a series of small charts side by side.

To create small multiples

Click the blank part of the view next to the bubble chart to start a new visualization.
In the fields section of the field list, in the Dates table, click the Month Name field.
In the fields section of the field list, in the Quantities table, click the Sum of Qty Served field.
In the Visualizations gallery, click the Line chart.

This line chart shows quantities for all foods by month.
Drag the left and lower sizing handles to make the chart bigger.
In the fields section of the field list, in the Items table, click the Name field and drag it to the Vertical Multiples box in the layout section of the field list.

By default, it creates a visualization that displays nine multiples at a time. They are a little crowded.
With the small multiples chart selected, on the Layout tab in the Chart Tools contextual tab, click Grid.
Drag to select three multiples across and two multiples tall.

Now you see six multiples at a time, so you can see the individual multiples better.
To see them even better, pop out the chart: hover in the upper right corner of the chart and click the popout icon.

Small multiples popped out to fill the view

The small multiples now fill the entire design space. Again, this has no effect on the other visualizations in the view.
To return it to its original size, click the popout icon again.
Drag the small multiple container to the lower-right corner of the view.

View and visualization filters

Like slicers, view filters filter the whole view, but not other views in a report. One advantage of view filters is that they don’t take up any space on the view. Another is that advanced filters provide much more flexibility: For example, you can search for parts of values, rather than just a whole value.

Visualization filters are similar to view filters but they filter individual visualizations on a view, rather than the whole view.

To create a view filter

On the Home tab, click Filters Area.
In the fields section of the field list, in the Dates table, click Date and drag it to the filters area.

In Basic Filter Mode, you can select individual dates.
Click the Advanced Filter Mode button to the right of Date.
Click the arrow next to is on or after, and then click is on or before.
Click the calendar and click August 31, 2011.
Click apply filter.

This removes dates after August 2011.

To create a visualization filter

Hover over the vertical multiples and then click the filter icon in the upper-right corner of the visualization.

Notice that the fields in the visualization are already in the chart filters area.
In the filters area, click Sum of Qty Served.
Drag the right end of the slider so the highest value is 400.

Notice that the Values axis for the multiples has now changed. The highest number is now 400.

In the bubble chart, the Y value, also Qty Served, is unchanged – the maximum is still 600.

View your report

You can preview your report, and you can view it in presentation mode. In both modes, the report is still interactive. When you click bars or columns in the charts, it filters and highlights the rest of the report. However, the field list and ribbons are absent, so you cannot add fields and create visualizations, or move or resize existing visualizations. Also, your interactions will not be saved with the report.

To preview your report

On the Home tab, click Reading Mode.

Notice that the filters area is still visible.
Click the icons in the lower-left corner or the numbers in the lower-right to move from one view to the other.
Try some of the interactions you’ve tried earlier in the tutorial:
- Click bars, columns, or bubbles in the charts.
- Click different foods in the tile container.
- Click an item in a slicer.
From here, you can return to design mode or go to full-screen mode with the buttons in the upper-left corner. Click Edit Report.

To present your report

On the Home tab, click Full Screen.

Notice that the filters area is still visible.
Try some of the interactions you tried earlier in reading mode.
Click ESC to leave full-screen mode.

Save your report

To save your report

On the File menu, click Save or Save As.

The Save As dialog box contains the location of the model on which your report is built. You can change that location, saving the report to a subfolder, for example, but you must save the report to the same SharePoint server as the model.
Give the report a unique name.

Tip

We recommend a name that identifies the model on which the report is built.
Keep the Include an image of each view in its current state as a preview for other users (in PowerPivot Gallery and other applications) check box checked. For more information about these preview images, see Choose Whether to Save an Image of Each View with the Report.

Export your report to PowerPoint

You can export your report to PowerPoint. It has the same interactivity that it has in reading and full-screen modes.

To export to PowerPoint

On the File menu, click Export to PowerPoint.
Browse to where you would like to save the new PowerPoint file. You can save it anywhere, as long as it can connect to the report stored on the SharePoint server.
Open the PowerPoint presentation.

Each Power View view has been added to a separate slide. In PowerPoint design mode, each is static representation of the view.

Note

If you didn’t choose to save a preview image when you saved the report, then you don’t see a static representation of the view in PowerPoint design mode.
Make any changes you want in the PowerPoint presentation.

Note

You can resize Power View views in PowerPoint, but you can’t edit them.
In PowerPoint, click Slide Show.
In the lower-right corner of the slide, click click to interact.

Now you can interact with the view in the same way that you can in reading and full-screen modes.

For more information, see Creating, Saving, Exporting, and Printing Power View Reports.

Next Steps

Now that you’ve completed this tutorial, you are ready to try using your own data in Power View. To learn more about building data models for use with Power View, try Tutorial: Optimize a Sample PowerPivot Model for Power View Reports.

مشاركة عبر