Monday, January 4, 2016

Consuming a Query in Excel PowerView in Microsoft Dynamics NAV 2013 R2

First things first, lets clear what we are going to discuss here today. We are going to configure Excel's PowerView feature to visually demonstrate the Navision tables data. Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting in Excel. This is a feature of Microsoft Excel 2013.

Prerequisites - Microsoft Excel 2013, Microsoft Silverlight.

PowerView feature in Excel Microsoft Dynamics Nav 2013 R2

Now, to demonstrate the feature we are going to visually represent the Profit by Country by using the data from Customer table. 

We are going to demonstrate it in just 3 very easy steps as follows:

Step 1 : Create a Query and Publish it as an OData WebService

  • Open the Development Environment and Create a New Query Object.
Object Designer Query in Microsoft Dynamics NAV 2013 R2
  • Define a Data Item. (i.e. you can use Customer table)
Create a Data Item in Query Object Dynamics Nav 2013
  • Open the Field Menu and Select the Field you want to include in the Query.
Field Menu in Query object Microsoft Nav 2013 R2

  • Save the Query.
  • Run the Query to verify the correctness of the Query.
  • Now from the RTC search for 'Web Services' and open it, a page opens.
    • Click on New to create a new Web Service.
    • Select 'Query' as Object Type, enter the Object ID, type in the Service Name and then Click on Publish to Publish the Web Service.
Publish Query Web Services in Microsoft Dynamics NAV 2013 R2

  • Copy the OData URL from the Web Services Page and paste it on Internet Explorer to check whether the Web Service is working or not.

Step 2 : Create an Excel Workbook and Insert a Powersheet into it.

  • Go to Insert and Click on PowerView to insert a PowerView Sheet.
  • When you Create a PowerView sheet for the first time, Excel asks you to enable the PowerView add-in. Click on Enable.

Step 3 : Establish a Data Connection from Excel directly to the new OData URL and visualize the data in the PowerView.

  • In the Excel, Click on PowerPivot and then Click on Manage.
  • Inside the PowerPivot, Click on From Data Service and then From OData Feed.
Power Pivot from OData Data Feed in Microsoft Excel 2013

  • In the Pop-up window, Paste the Copied OData URL and then Click Next then Click on Finish.
Power Pivot from OData Data Feed in Microsoft Excel 2013

  • Now close the PowerPivot and Refresh the PowerView Sheet. The CustomerSales is shown as Power View fields in the Right Menu.
  • Drag the Country Region Code field on the main page. As we want to see it visually so we select the Map option in the Design Menu on Top Bar.

Now the Fun part 
There is a lot to interact in the PowerView sheet that you have created. Play with the fields in the Customer Sales. 
  • Drag the Profit (LCY) to show the data in the Map with the Profit by the Countries.
  • You can also add colour to the Name of the Customer by dragging the Name field to the Colour box at the bottom.
  • You can add any field as a filter by dragging the field to the filter area. For example, you can filter the data by the Salesperson Code by selecting the particular Salesperson from the Filters menu. 
Powerview visual geographical representation microsoft excel 2013

I hope you are caught up with the whole process. Write your comments below if you are facing any problem with the configuration. Watch the whole process in the video below to understand the concept more clearly about Excel Powerview and its use in Microsoft Dynamics NAV 2013 R2 and also Subscribe to our Youtube channel.


No comments:

Post a Comment