OData & Powerpivot: Viewing Page Data in Excel Using PowerPivot (OData)
Roberto Stefanetti
12,998
Like
Share
ReportGreat post about OData & Powerpivot tables on Excel on MSDN
"Use OData Web Services to obtain an AtomPub document" "The Open Data Protocol (OData) is a web protocol that is designed for querying tabular data and provides you with an alternative to SOAP-based web services. OData builds on web technologies such as HTTP, the Atom Publishing Protocol (AtomPub), and JavaScript Object Notation (JSON) to provide access to information from different applications, services, and stores. OData uses URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with resources. This commitment to core Web principles allows for OData to enable a new level of data integration and interoperability across a broad range of clients, servers, services, and tools. You can use OData web services to show Microsoft Dynamics NAV data, and you can update data in a Microsoft Dynamics NAV database using OData web services" rif. http://msdn.microsoft.com/en-us/library/hh166950(v=nav.71).aspx |
CheckList to use "OData" on Excel 2010 or 2013
1) Create a NAV Query
2) Publish NAV WEB Services (Query or Pages)
3) Verify Web Services (OData) availability
The browser should now show the web services that you have published, in the format of an AtomPub document.
ex: : http://localhost:7048/DynamicsNAV/OData ("Company name" if more than one)
4) Install or use Excel Powerpivot component (Excel 2010) or native (Excel 2013)
5) Import NAV Data into Excel
6) Add colums (if you need)
es: Addcolumn: quantity > = SalesOrderInfo[Quantity] ...=SalesOrderInfo[Quantity]*SalesOrderInfo[Amount]
7) Create relationship between data sources (Field, table)
By creating relationships between your data sources in Excel, you make it possible to display data from multiple sources in the same pivot table (ex:Sell- to Customer and customer)
8) Building a pivot table
PowerPivot automatically assigns these values to categories in the pivot table. Country_Region_Code and No are categorized as row labels, and QuantityByAmount as a sum.
rif. MSDN
Walkthrough: Viewing Page Data in Excel Using PowerPivot (OData)
http://msdn.microsoft.com/en-us/library/hh165316(v=nav.71).aspx
Walkthrough: Combining Data from Microsoft Dynamics NAV Queries and Pages with Data from Azure DataMarket (OData)
http://msdn.microsoft.com/en-us/library/hh165375(v=nav.71).aspx
This was originally posted here.
*This post is locked for comments