What is a Power Pivot?? What do we achieve with it in NAV?
In General, Power Pivot as we all know is a data exploration tool. With OData and Power Pivot, we gain access to a powerful set of tools and technologies for data exchange and analysis like
· Publishing a Microsoft Dynamics NAV Query as a web service.
· Verifying web service availability from a browser.
· Using the Power Pivot add-in for Excel 2010 to import the table data as a new worksheet.
· Creating a PivotTable from the worksheet, selecting relevant fields, and then organizing and formatting the data to highlight strategic data.
What’s now?The section below will take us through in “Viewing Query Object Data in Excel Using Power Pivot “
In short, create a new custom query in NAV 2013, and display the output it in terms of charts using Power Pivot Data
Query Object Creation
First, let's create a new query (new object released with NAV 2013) and use that query data as raw data for power pivot.
Open the developer environment of NAV 2013 and start creating a query. Create a query.
The sample I have taken below is for the Customer Balances using the Customer and Cust. Ledger Entry tables.In this I need the top 10 Customer Balance with location wise
After that we execute the query to get top ten customers. Now, we need to deploy this query as web page to do this we need to verify the deployed Query in browser
Power Pivot Data Creation in Excel
Following setup will illustrate how to upload· Go to Get External Data-> ”From Data Sources” following screen will open
Select “Other feeds” click next
· On the next screen we need to pass URI(uniform Resource Identifier) (i.e: The URL which we used to check the web page/Query deployed or not in Brower)
Click on Test Connection to see the connection succeeded message
Click Next to proceed
· Now system will show, what are all the pages or Queries are deployed within this service it will list down in one table like below
· Select how many tables you want and then click Finish
· After clicking on the Next button system will show the output data of the Query in separate sheets
Creating charts using Power Pivot Data:Now we are going to use the data generated above to create charts, and upload them in the share point so that we can view the data online
System will prompt you New Work sheet or Existing Worksheet. After choosing one of the option system will create a pivot table with fields selected in the Data
Here we can choose which field has to be taken for x-axis, y-axis and Legend Fields
That’s it, we have the Chart that displays Customer Balances location wise