dynamics-community-at: NAV2013: using OData with SSRS (Reporting Services)

[Blog bot]

 

Hi all, probably you are familiar with NAV 2013 and that it is supporting OData services to provide data from NAV to other applications for example for business intelligence.

Reporting Services does not support Odata directly, but with a small workaround it is possible to create server based reporting services reports for NAV by using odata and get all the server based features which are not available by using RDLC (end user access who are not NAV user, subscriptions, additional controls, running in browser, etc.)

(This post will not describe how you are using visual studio to create great reports. If you need assistance for this and Microsoft Dynamics NAV take a look on this book or training video 257-ebook-nav-2009-inside.aspx - Videotraining Microsoft Dynamics NAV 2009- Berichte mit Reporting Services)

This post will describe how you can use a OData feed from NAV with reporting services.

First of all you have to be familiar with the requirements when using Odata in Microsoft Dynamics NAV 2013

Our goal is: Create a report based on Value Entries from NAV using Odata and (“server based”) reporting services

Step 1 create a Odata service in NAV 2013

1. Open the NAV 2013 windows client
2. Navigate to right-corner and use the global search to find the web service page
3. 
4. 3. Create a new web service entry with page 5802 value entries
5. 

Step2 Check that odata service is running

1. Open the NAV 2013 Administration console
2. Check Odata services is enabled and service is running
3. 
4. Open a web browser and navigate to http://localhostortnumber/ServerInstance/OData (sample: http://localhost:7048/DynamicsNAV70/OData)
5. Check the listed Odata services and look for your created entry “ValuesEntries”
6. 
7. Check that you are able to access it by enter the url into a new browser tab
8. http://localhost:7048/DynamicsNAV70/OData/ValueEntries

If you are able to access it, all prerequisites exists. Lets move forward by using it for reporting.

Step 3 create a report using SSRS  (Reporting Services)

1. Open Visual Studio and create a new report project
2. Add a new data source by using the solution explorer
3. 
4. Because SSRS does not support OData directly use the XML connection type and the url for the connection string (Note: Do not forget to set windows authentication)
5. 
6. Skip the “shared Dataset” directory and go directly to the folder “reports” (Of course you can work with shared datasets)
7. 
8. Select the Datasource and click Next
9. Before moving on designing the query, lets remember that Odata is not supported by SSRS. Because of this we have to use a short xml query to structure the odata feed into typically “fields”
10. Enter this query into the query string textbox
    1. <Query>
    2. <ElementPath IgnoreNamespaces="true">
    3. feed{}/entry{}/content{}/properties   
    4. </ElementPath>
    5. </Query>
11. 11. Check the query before moving on by clicking on Query Builder and then run the result button
12. 
13. 
14. You should now see structured “NAV” fields in you query and can move forward to create your report like any other reports in SSRS
15. 
16. …
17. ..
18. .
19. Finalized NAV report based on NAV OData
20. 

NAV 2013 Rocks …!

PS If you want to show someone the capability of Odata and NAV, and you have no SSRS or Excel Power Pivot use Microsoft Outlook RSS Feed feature instead. What is more easier than a notification using Odata directly in Outlook



Далее