Data Model, Development Tips, Excel, How To, Power View, PowerPivot

Set field defaults in PowerPivot

When you set a default field set for a table, you can simply click that table in Power View, and the default set of fields is automatically added to a new report.

Open the Excel Workbook we created in our earlier post. Create a Power View report

In today’s post, we will learn how to set defaults for our workbook that will save our time when creating reports.

Steps to Create the Default Field Set for a table

  • Select the Medals table in Data View Mode from PowerPivot Manager.
  • From the Advanced tab, select Reporting Properties > Default Field Set. A window appears that lets you specify default fields for tables created using client tools such as Power View.
  • Select Sport, Event, EditionID, Athlete, and Medal in the left pane, and click Add -> to make them the default fields.

ExcelData-31

To see how this works, switch to the Power View sheet in Excel.

  • Click anywhere on the blank report canvas, to make sure you don’t have an existing visualization selected. Your Power View sheet currently only has one visualization, which is the map you created earlier in previous post. Create a Power View report
  • In the Power View Fields list, click the Medals table name. Power View creates a table and automatically adds the five default fields from the Medals table, in the order you specified, as shown in the following screen. Make sure you don’t click on the triangle beside Medals, if you do so the table simply expands, rather than adding a new table with default fields.

ExcelData-32

I will come up with more details on this topic in my upcoming posts. Till then Stay Tuned, Keep Learning and Practicing.