RS First Dynamics NAV Blog

...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

OData & Powerpivot: Viewing Page Data in Excel Using PowerPivot (OData)

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


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)

Two web services

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)

Creating a relationship between two data sources 

8) Building a pivot table

PivotTable from the PivotTable menu

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.

Adding fields to the PivotTable

Final pivot field list

Final pivot table 

Final pivot table after modification

rif. MSDN

Walkthrough: Viewing Page Data in Excel Using PowerPivot (OData)

Walkthrough: Combining Data from Microsoft Dynamics NAV Queries and Pages with Data from Azure DataMarket (OData)

Categoria: Dynamics NAV 2013
martedì, 08 lug 2014 Ore. 15.25

Messaggi collegati

  • Views Home Page: 399.620
  • Views Posts: 819.854
  • Views Gallerie: 0
  • n° Posts: 345
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0 | Home Page Blogs
ASP.NET 2.0 Windows 2003