Friday, February 22, 2013

Query Object Data usage in Power Pivot - NAV 2013

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
Now, open New Excel sheet-> Power Pivot Tab->click on Power Pivot window to open the window in which we can upload our query data

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

No comments:

Post a Comment