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
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
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