Tag: OData

Authorization, Base64 Convert, Business Central, InStream, Integration, JSON, JsonObject, JsonToken, OData, Post, Postman, SOAP, Tenant Media, Tip & Tricks, URL, Web Access Key, Web Services

Integrating with Business Central – using Web Service

Ashwini Tripathi

This is fourth post in this series, if you wish to see first post, you can access here.

In second post I discussed about, how to create Azure Functions, you can access here.

In previous post I discussed about, how to Integrate Azure Functions in Business Central, you can access here.

In this post we will discuss how to make available QR Image using API by exposing as Web Service.

Let’s cut the theoretical part and coming directly to the solution.

This post is very short and as an answer to query from one of the blog readers, who regularly gives feedback and suggest topic he wish from me. Sometime it is not possible to write post for every query, but I try to pick some which I find interesting. Converting any topic into post is quite time taking, solution can be of 10 mins but presenting same in form of blog takes 2-3 hours, sometimes even more.

Some time appropriate environment is not available where I can put the solution and take screenshots to present as blog.

Since this topic is part of current series, I am writing posts on, so I was able to easily pick this topic.

Requirement is to add capability to get QR Image we generated in our previous posts using Web Service/ API/ OData/ JSON Base64Text.

To get this working we will first create a Codeunit with appropriate methods and expose it as Web Service.

Step – 1.

Create Codeunit & Add below method.

Step – 2

Publish above Codeunit as Web Service.

Step – 3

Copy SOAP URL

[SOAP URL]

https://api.businesscentral.dynamics.com/v2.0/1e4fc386-962e-4a20-8364-d5aeb65e2485/Test/WS/CRONUS%20USA%2C%20Inc./Codeunit/QRCodeGenerator

Convert to ODataV4 URL by making necessary modification as below:

[ODataV4 URL]

https://api.businesscentral.dynamics.com/v2.0/1e4fc386-962e-4a20-8364-d5aeb65e2485/Test/ODataV4/QRCodeGenerator

Similarly, your $metadata URL will be:

https://api.businesscentral.dynamics.com/v2.0/1e4fc386-962e-4a20-8364-d5aeb65e2485/Test/ODataV4/$metadata

Run the metadata URL in browser:

Search for your function created above, you should get definition as below:

<Action Name=”QRCodeGenerator_GetQRSetImageAsJSON”>

<Parameter Name=”qRSetId” Type=”Edm.Int32″/>

<ReturnType Type=”Edm.String”/>

</Action>

Let’s test the above URL for function using Postman to see the result:

[POST] – https://api.businesscentral.dynamics.com/v2.0/1e4fc386-962e-4a20-8364-d5aeb65e2485/Test/ODataV4/QRCodeGenerator_GetQRSetImageAsJSON?Company=CRONUS USA, Inc.

Authorization:  Basic Auth

Username: ASHWINI.TRIPATHI

Password: xjAtAtTaFhgXkgR4M5WoHapotvJNmqJ5//XBYK2v5s4= (Web Service Access Key)

JSON Parameter to Function: (Body raw:JSON)

{

    “qRSetId” : “6”

}

Result Body:

{

    “@odata.context”: “https://api.businesscentral.dynamics.com/v2.0/1e4fc386-962e-4a20-8364-d5aeb65e2485/Test/ODataV4/$metadata#Edm.String&#8221;,

    “value”: “iVBORw0KGgoAAAANSUhEUgAAApQAAAKUAQMAAACAATp/AAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAGUExURQAAAP///6XZn90AAAAJcEhZcwAADsMAAA7DAcdvqGQAAAHtSURBVHja7dpbasMwEAXQ2YH3v0vvwKVBsZ44DZ4UWs58BEnMHH1eYjuO9NqZTCaTyWQymUwmk8lkMplMJpPJZDKZ/8KMsbb27Ls3St+2bmYymUwmM8Ws58N2b+FiXjQzmUwmk3nTLMN1qkPq6LqZyWQymczPmUdTwxVMJpPJZP6G+di0/7lqH5PJZDKZnzOnK7ZzW2tbNzOZTCaTmWIOtbXw9DM3M5lMJpOZYK6rPvSL9huJywkmk8lkMu+Yz0d9tR7HQ7INl9UWJpPJZDITzDIwv39arVbNTCaTyWRmmS0XbZ5Nq64uM47JZDKZzDfMdmrv4W47ZByTyWQymalm7S2rre0tq+eNxRw4JpPJZDITzDhjq8uu9p5udSLBZDKZTGae+UyxKd4GqWbc3MxkMplMZpZ5EXSdVPuYTCaTyUw12/kh1Dq4rVcZx2QymUzmG+aqinmcU12etdceTCaTyWSmmDHWkHaDNJ8xmUwmk5li1vM6GjFfu60nmEwmk8nMMuvUFGV7zPWjjGMymUwm87ZZt/Pqcc/wXJDJZDKZzE+apSXOV1TRf9HHZDKZTGaiub4i+ijb+48iDiaTyWQyM82h2rZYRFmsYpDJZDKZzHtmVjGZTCaTyWQymUwmk8lkMplMJpPJZDL/rHnsXwW1n/zFqtyZAAAAAElFTkSuQmCC”

}

Copy the Value and use any base64 – image converter tool, search on Google

You will get your QR Image:

Don’t try any URL or Passwords in this post as same is modified, and in images covered to hide actual url & passwords.

Hope you enjoyed learning from this post.

That’s all for this post, but visit again to learn from upcoming posts in this series, will post soon.

See you again in next post, till then keep exploring, learning and sharing with others.

AL, API, Business Central, Development Tips, Extension Package, How To, Information, JSON, Modern Development Tool, OData, Tip & Tricks, V2, Visual Studio Code, What's New

API – Business Central Part-2

Ashwini Tripathi

In our previous post we saw basics of API in Navision. Let’s explore further.

If you missed the earlier post you can find here API – Business Central Part-1

Continuing from where we left in previous post.

Someone asked me why we require API when we have web service in place and can achieve same OData either query or filter in same fashion.

So what I am going to explain below will answer to that query.

The API will generate a REST service which returns OData.  The API is not the same as the OData web services that we discussed in our earlier post.

There we created an OData web service based on a card page.  If there were fields that need to be displayed on a card in the client application but you do not want those fields to expose in the OData web service, you will have to create a second card page to solve this problem.  In this case, we create a separate page for our API and only for the API.  This page cannot be requested in the client application.  It’s also much better concept to separate them from the regular pages.

Also we can apply templates for default value of field, which we will discuss later in below post.

Let’s start with creating our own API.

Each resource is uniquely identified through an ID. As discussed in our earlier post. So let’s start with this, I will start with my earlier created Table LoadoutPoint and add one field ID.

api009

Any new entry in my table will have a unique ID for Loadout Point, so I have added code in OnInsert trigger of the table.

api010

To create an API, you should create a page of type API, instead of a card page or list page.

Use tpage, Page for type API snippet for page structure. You get all the bare minimum properties to be added for API Page.

api011

Then you have to define which fields you would like to include.

api012

Some important rules to be followed for API Pages:

  • Fields should be named in the APIs supported format, Any Captions cannot have spaces and special characters. Only AlphaNumeric values permitted.
  • When you insert an entity through API endpoint, Business Central don’t run OnInsert trigger on the table.
  • And we have assigned the ID for the new record there. So Add Insert(true) for OnInsert Trigger.
  • Add business logic to Modify trigger. As external user can change values through API, even the value of the primary key field.
  • Add Delete(true) for On Delete trigger. The reason same as above.

So let’s add these 3 trigger in our page too.

api013

Ok so now we have modified Table and Created new API page, now it’s time to publish our app/extension.

Use command palette to publish your app.

Now it’s time to test, let’s access our API page from client and do setup for same.

Search for API Setup Page in the client.

api014

You can define and assign your Template from Template Code field, check with available same Templates how to do it.

api015

Also the conditions when this Template should apply as discussed in earlier post also.

Now let’s access the API from outside the Navision/ Business Central.

I will use Postman to test this.

To get the list of 44 standard APIs.

https://KSD-DESKTOP:7748/BC130/api/beta/

api016

To get the list of custom APIs.

https://ksd-desktop:7748/BC130/api/ksdconsultancy/app1/v1.0/

Hope you remember when we created API page we assigned few Properties like

APIPublisher = ksdconsultancy, APIGroup = app1, APIVersion = v1.0.

Now we will use those values to access my custom APIs.

See the url those are included after /api/

api017

All information is available in JSON format and further can be confirmed that there are 1000 records. Thus, the number of records integrated here depends on the Max Page Size parameter setup in Navision Server.

What else you can do with APIs:

  • Get to fetch or List
  • Post to insert records
  • Patch to modify records
  • Delete to Delete records
  • And so on.
  • You can extend existing API Pages too, I have yet not tried.

That we may discuss in some other post. Not to complicate this topic more for now I conclude this post here.

Will come up with more details in my upcoming posts, till then keep exploring, learning and take good care of yourself.

 

AtomPub, Business Central, EDMX, How To, Information, JSON, OData, REST, RPC, Smtp, SOAP, Tip & Tricks, Web Client, Web Services, WSDL, XML

Web Services – Business Central Part- 2

Ashwini Tripathi

Today in this post we will continue from where we left our previous post, if you missed or wish to have a look you can find her in this link.

Web Services – Business Central Part- 1

We saw in our previous post, you expose an OData service that can be accessed from a URL by using a web browser or any other HTTP client.

You need the URL of the OData page or query web service

“https://server:webserviceport/serverinstance/oData/web service”

If you use the attribute ?$format=atom you will get an AtomPub document

https://KSD-DESKTOP:7748/BC130/OData/Customers?$format=atom

If you use the attribute ?$format=json you will get an JSON document

https://KSD-DESKTOP:7748/BC130/OData/Customers?$format=json

You can obtain service metadata documents for either page or query web services.

https://ksd-desktop:7748/BC130/OData/$metadata

SOAP Web Services

If your client requests another transport protocol than HTTP or HTTPS, then you can use SOAP as it also supports, for example, SMTP.

Microsoft is heavily investing in REST and OData, and certainly with the online apps like Power BI, PowerApps, and others.  So at this point SOAP isn’t the primary choice for web services anymore.

We can find the available SOAP web services at the same page where we define the OData web services.

If we go to the search box and then search for web services, This page is used to enable access to OData and SOAP web services.

ws-001

We could enable OData web services only with the object types of query and pages, and for SOAP web services, we can only enable it for pages and codeunits and not for queries.

For example soap url for my Customers Service will be as below:

https://KSD-DESKTOP:7747/BC130/WS/CRONUS%20International%20Ltd./Page/Customers

You can find some post related to consuming web service and working with web service for older versions but it can be used with business central too.

Exposing & Consuming the Web service from & inside Navision – Part-1

Exposing & Consuming the Web service from & inside Navision – Part-2

Exposing & Consuming the Web service from & inside Navision – Part-3

Exposing & Consuming the Web service from & inside Navision – Part-4

When you are exposing your object for web service, ensure that you use GUIAllowed function to supress the UI. Encapsulate the code that could cause in error, not your entire code.

Also the exceptions must be handled in order to facilitate that your web service don’t end the execution unexpectedly.

You must check with the GuiAllowed function when you are using one of the following dialog functions:

Close, Confirm, Error, Open, StrMenu, Update

The Message function is the only dialog function that doesn’t cause an exception.

Other keywords that you shouldn’t use are:

Page.Run, Page.RunModal, Activate, Report.Run, Report.RunModal, Hyperlink, File.Upload, File.Download

Hope you got some insight on Web Services.

I will come up with more features in other posts, till then keep exploring and learning, and take care of yourself.

API, AtomPub, Business Central, Development Tips, Dynamics 365, EDMX, How To, Information, JSON, OData, REST, RPC, SOAP, Tip & Tricks, Web Services, WSDL, XML

Web Services – Business Central Part- 1

Ashwini Tripathi

You can use web services in Microsoft Dynamics 365 Business Central to expose data to the outside world.

You can use web services to get data from Microsoft Dynamics 365 Business Central and use it in other applications.

Any application, programing language or program that can work with XML and/or JSON for example: – Power BI, Microsoft PowerApps, a custom .NET program, etc. can connect to Microsoft Dynamics 365 Business Central fetch the available data.

A web service can also be used to create new or update existing data in the application.

Both XML (Extensible Markup Language) and JSON (JavaScript Object Notation) are open-standard file formats that are readable by humans. They are used to send information between clients over web services.

In this post we will discuss some terminology and differences between SOAP and OData. We will also learn on how to configure these services in Microsoft Dynamics 365 Business Central.

SOAP relies on HTTP(S), SMTP, FTP for message negotiation and transmission.

The most common type of messaging pattern in SOAP is the Remote Procedure Call (RPC), where one network node (the client) sends a request message to another node (the server), and the server sends a response message to the client. It uses SOAP envelope, which defines the message structure and how to process it.

A SOAP service exposes a WSDL (Web Services Description Language) file that describes how the service can be called, what parameters it expects and what data structure it returns. This file is an XML based document, and is targeted to be read by machines, not by humans.

OData (Open Data Protocol) is an open protocol that is designed to use and query RESTful APIs. An API (Application Programming Interface) is a set of routines and protocols that is used to communicate between different software components.

OData is built upon REST services and can be used to query REST services. By providing extra query parameters to a URL, you can for example limit or filter the result set.

SOAP exposes a WSDL document, OData exposes an EDMX document, which contains metadata for all the published web services.

You can see all the available OData web services by going to the search box and search for web services.  This page is used to enable access to OData and SOAP web services.

ws-001

OData web services can only be used with object types pages and queries, not with codeunits.  There are two versions of OData you can use, version 3 and version 4.  Version 4 is an enhanced version, and important to know is that version 4 returns JSON where version 3 returns AtomPub documents which are XML documents.

From this window you can add new or manage existing Web Services.

It is simple 2 Step task Add new object give service name and enable Publish checkbox. You will get your OData and/or SOAP URL.

For example: – object type page can be used in OData and in SOAP web services.

Let us explore the Web-Service Urls. For test purpose, I have created a Service with the name Customers for Page Customer Card.

https://ksd-Desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers

When we open, for example, our Customers web service, we can have a look at the URL.

First we have our server name and the port it’s using.  “https://ksd-Desktop:7748”

Then we have our instance name, which in our case is BC130.

Then our OData version, so version 4.  “ODataV4”

And then our company name, “Cronus International Ltd”.

And then finally our web service name, which is, in our case, “Customers”.

Let’s try this url, when we execute this url in our browser we get json file listing customers similar to below:

WS-003.jpg

Here etag is the unique identifier of each records, it will be required when you update records, will discuss later.

If I remove my service name then:

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)

{“@odata.context”:”https://ksd-desktop:7748/BC130/ODataV4/$metadata#Company/$entity&#8221;,”Name”:”CRONUS International Ltd.”,”Evaluation_Company”:false,”Display_Name”:”CRONUS International Ltd.”,”Id”:”ab76c7b4-3c72-4805-86f7-7d91a10612ce”,”Business_Profile_Id”:””}

If I remove my Company Name then:

https://ksd-desktop:7748/BC130/ODataV4/

WS-004.jpg

If I open the metadata url

https://ksd-desktop:7748/BC130/ODataV4/$metadata

You will get EDMX file which shows metadata about the information in the OData web service.

ws-002

You can see some properties with our data types.  For example, if we scroll down, if we go to Customers, we have our entity type Customers, we can see some properties which our data types and max lengths.  We can also see what properties are primary key.  So this is number and so on.  So all useful information when you’re creating applications that use these OData web services.

Filtering the OData

You can set filter expression to the url few examples as below:

We will use the filter option top for limiting the result to top 5 records of customer.

After our service name, we type question mark, then the dollar sign, and then the word top equals 5.

For example :

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers?$top=5

Now the query will result in only first 5 records for customer.

If we want to see next 5 records then we can append &$skip=5 on above url

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers?$top=5&$skip=5

If you want to see specific customer you can modify url as:

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers(‘10000’)

Please refer to EDMX file to find correct name of the fields if wants to filter on specific fields.

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers(‘10000’)?$select=Name

You will get the result as below for above url query

“@odata.context”:”https://ksd-desktop:7748/BC130/ODataV4/$metadata#Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers/$entity”,”@odata.etag”:”W/\”JzQ0O044eFNmcWdXV0NpYlhTZDhUdTlrdytZUmdQbWRkL2U4TzZ4UEsxNGtod0

E9MTswMDsn\””,”Name”:”The Cannon Group PLC”}

You can add other fields too by appending the url with ‘,’

Customers(‘10000’)?$select=Name,City,Address……………

As told earlier please refer to EDMX file for correct Fields Name or Property it is case sensitive.

You can remove the Primary Key from the url to get list of selected fields for all customers.

Customers?$select=Name,City,Address……………

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers?$orderby=City&$select=Name,City,Address

You can use orderby to sort the data from other than Primary key, by default it is sorted by Primary Key.

You can use desc for descending sort.

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers?$orderby=City desc&$select=Name,City,Address

You can use filter to filter specific field values as:

https://ksd-desktop:7748/BC130/ODataV4/Company(‘CRONUS%20International%20Ltd.&#8217;)/Customers?$filter=City eq ‘Zutphen’ or City eq ‘Atlanta’&$select=Name,City,Address

you will get output as:

WS-005.jpg

You can use expressions like [filter=Balance_LCY gt 0] or [filter=Location_Filter eq ‘BLUE’] etc.

I stop here for today. We have seen how the Web Services OData can be used to query records.

We will discuss about other stuffs in our next post in more details.

Till then keep exploring and learning, see you in next post. Take Care of yourself.

 

Development Tips, Excel, Office Integration, Report, Server

Viewing Page Data in Excel Using PowerPivot (OData)

Ashwini Tripathi

Here I will discuss how you can use OData to expose a Microsoft Dynamics NAV 2015 page as a web service and then analyse the page data using Microsoft PowerPivot for Excel 2013.

With OData and PowerPivot, you gain access to a powerful set of tools and technologies for data exchange and analysis.

This walkthrough illustrates the following tasks:

  • Publishing a Microsoft Dynamics NAV page as a web service.
  • Verifying web service availability from a browser.
  • Using the PowerPivot add-in for Excel to import the table data as a new worksheet.
  • This procedure also includes optional instructions about how to use a web service access key.
  • Creating a PivotTable from the worksheet, selecting relevant fields, and then organizing and formatting the data to highlight strategic data.

Optional:

If you want to use a web service access key to authenticate access to the web service, Microsoft Dynamics NAV must meet the following requirements:

The Microsoft Dynamics NAV Server is configured to authenticate users by using the NavUserPassword credential type.

There is a Microsoft Dynamics NAV user account that has a web service access key.

You can find more details in my earlier post here

Publishing a Page as a Web Service

You can publish a web service by using the Microsoft Dynamics NAV Web client or the Microsoft Dynamics NAV Windows client.

To register and publish a page as a web service

  • Open the RoleTailored client and connect to the CRONUS International Ltd. company.
  • In the Search box, enter Web Services, and then choose the related link.
  • In the Web Services page, choose New.
  • In the Object Type column, select Page. In the Object ID column, enter 21, and in the Service Name column, enter Customer.

This exposes the Customer Card page as an OData web service.

  • Select the check box in the Published column.

Choose the OK button to close the New – Web Services page.

PowerPivot-1
Verifying the Web Service’s Availability

Security Note

After publishing a web service, verify that the port that web service applications will use to connect to your web service is open. The default port for OData web services is 7048. You can configure this value by using the Microsoft Dynamics NAV Server Administration Tool.

To verify availability of a Microsoft Dynamics NAV web service

Start Windows Internet Explorer.

In the Address field, enter a URI using the following format: http://Server : WebServicePort/ServerInstance/OData/

Server is the name of the computer that is running Microsoft Dynamics NAV Server.

WebServicePort is the port that OData is running on. The default port is 7048.

ServiceInstance is the name of the Microsoft Dynamics NAV Server instance for your solution. The default name is DynamicsNAV80.

For example, if the Microsoft Dynamics NAV Server is running on the computer that you are working on, you can use: http://localhost:7048/DynamicsNAV80/OData/

In my case: – http://indel-axt5283n1.tecturacorp.net:8048/DynamicsNAV80/OData/

The browser should now show the web service that you have published, as shown in the following illustration.

PowerPivot-2
Note

If the browser cannot find the web service, it may indicate that the specified Microsoft Dynamics NAV Server instance is not running.

Make Sure Enable OData Services is checked.
PowerPivot-3
Importing Microsoft Dynamics NAV Data into Excel

In the following procedures, you use PowerPivot to import Microsoft Dynamics NAV data into Excel. If you will be using a web service access key for authentication, only perform the second procedure; otherwise, only perform the first procedure.

To import Microsoft Dynamics NAV data into Excel

Start Microsoft Excel.

In Excel, on the PowerPivot tab, choose Manage.

PowerPivot-4
This opens the PowerPivot for Excel window.
PowerPivot-5
In PowerPivot, on the Home tab, choose Get External Data, choose From Data Service, and then choose From OData Data Feed.

The Table Import Wizard opens.

PowerPivot-6
If your Microsoft Dynamics NAV implementation requires that you use a web service access key, you must specify the NavUserPassword credentials as described in the following steps:

In the Advanced dialog box, in the Security section, set the Integrated Security field to Basic. If your OData is configured to use SSL, then set the field to SSPL.

In the Password field, type the web service access key.

In the UserID field, type the user name for the Microsoft Dynamics NAV user account. For this walkthrough, use NavTest.

In the Source section, in the Service Document URL field, type the URL for the OData web service that you verified in the previous procedure, for example, http://localhost:7048/DynamicsNAV80/OData/.

In my case: – http://indel-axt5283n1.tecturacorp.net:8048/DynamicsNAV80/OData/

Choose the OK button to return to the Table Import Wizard.

In the Connect to a Data Feed page, in the Data Feed Url field, enter the OData URI that you verified in the previous procedure.
PowerPivot-7

Choose the Next button.

Important: The URI must end with a slash (/) as shown in the example.

Verify that Customer appears in the Source Table column.

Select the check box next to the Customer web service, and then choose Finish.
PowerPivot-8
After you see the Success message, choose the Close button.
PowerPivot-9

The data from the Customer OData web service displays, and you can use the data to build pivot-based views in the Excel workbook.

Creating a PivotTable Containing Key Microsoft Dynamics NAV Data

In this procedure, you use the Excel workbook with data from the Customer web service to create a PivotTable from the worksheet. You select relevant fields and then organize and format the data to highlight strategic data. Building a pivot table is a way to select and arrange data so as to highlight and focus on key elements.

To create a PivotTable

In Excel, select the cell where you want the PivotTable located.

In the ribbon, choose the Insert tab, and then in the Tables group, choose PivotTable.

In the Create PivotTable dialog box, select Use an external data source, and then choose the Choose Connection button.

In the Existing Connections dialog box, on the Connections tab, under Connections in this Workbook, choose the data feed for your OData web service, and then choose the Open button.
PowerPivot-10

Choose the OK button to add the PivotTable to the Excel worksheet.

The PowerPivot Field pane on the right side includes a list of fields from the Customer web service that where imported from PowerPivot.

In the PowerPivot Field List pane, choose Location_Code.

Tip

To quickly find a field in the field list, type part or all of the field name in the Search text box that is above the list of fields, and then press Enter to highlight the first field that contains the text. You can then choose the right arrow to proceed to the next field, and so on.

Select the Balance_LCY field.

Select the Name field.

You can now see the data in the body of the worksheet, as shown in the following illustration.

The PivotTable shows customers by location and individual customer balances, and also adds the balances by location. To make the information more readable, you can update the headings on the PivotTable.
PowerPivot-11

Select the cell that has the heading Sum of Balance_LCY, and then, in the formula field, type Balance.

Select the cell that has the heading Row Labels, and then in the formula field, type Customers by location.

Select the empty cell that is below the Customers by location cell, and then, in the formula field, type Location not specified.

The above illustration shows how the worksheet looks after you make these changes.

Next Steps

Now that you have created your PivotTable, you can continue to enhance the data to make it more useful and readable. You can:

Add a column to the data that shows average balance by region.

Enhance data presentation with a graph.

Post the data in a Microsoft SharePoint environment with live data from Microsoft Dynamics NAV 2015.

Development Tips

Configuring Web Services to Use SSL (SOAP and OData)

Ashwini Tripathi

Secure Sockets Layer (SSL) is a cryptographic protocol that helps provide security and data integrity for data communications over a network.

By encrypting your Microsoft Dynamics NAV web services using SSL, you make your data and the network more secure and more reliable.

Microsoft Dynamics NAV Web Services and SSL

Microsoft Dynamics NAV supports SSL authentication for SOAP and OData web services.

The server authenticates itself to the client, but the client does not authenticate itself to the server.

When the web service client connects to the Microsoft Dynamics NAV Server instance, the server replies by sending its digital certificate to the client. This certificate contains the server’s public encryption key and the name of the authority that granted the certificate. The client verifies the certificate using the authority’s public key.

We have covered above top related to Creating and Implementing Certificates for Microsoft Dynamics NAV Server. If not seen please follow below links for better understanding:

How to create a root CA and a private key file by using the makecert.exe utility

How to create certificate revocation list for the root certification authority

How to create and install a test certificate for the Microsoft Dynamics NAV Server computer

How to grant access to the certificate’s private key to the service account for the Microsoft Dynamics NAV Server

Implementing Security Certificates.

Configuring Microsoft Dynamics NAV Web Services to Use SSL

The first step is to prepare Microsoft Dynamics NAV to use SSL. This involves configuring the relevant Microsoft Dynamics NAV Server instance to specify SSL.

The following procedure uses Microsoft Dynamics NAV Server Administration tool to configure the Microsoft Dynamics NAV Server instance.

  • Choose the Start button, and in the Search programs and files box, type Microsoft Dynamics NAV Administration, and then choose the related link.
  • In Microsoft Dynamics NAV Server Administration tool, in the left pane, under Console Root, expand the node for the Microsoft Dynamics NAV Server computer. This is typically named Microsoft Dynamics NAV (Local), which is the local computer.

The Microsoft Dynamics NAV Server instances on the computer appear in the left pane and center panes.

In the left pane, choose the relevant instance to display settings for that instance in the center pane.

UseSSL-1

  • At the bottom of the center pane, choose Edit.
  • To configure SOAP web services to use SSL, expand the SOAP Web Services tab, and then select the Enable SSL

Make a note of the port that is used by SOAP web services. You will need the port number later on in this walkthrough.

UseSSL-2

  • To configure OData web services to use SSL, expand the OData Web Services tab, and then select the Enable SSL

Make a note of the port that is used by OData web services. You will need the port number later on in this walkthrough
UseSSL-3

  • Choose Save at the bottom of the center pane.
  • In the right pane, under Actions, choose Login Account.
  • Make a note of the service account information. You will need this information later on in this walkthrough.

UseSSL-4

Configuring the Access Control List and the Web Services Ports for SSL

An access control list (ACL) is part of the Windows security infrastructure and features. The ACL controls who can access resources on a computer.

Note

These procedures use the netsh tool (netsh.exe) for configuring the HTTP server. The netsh tool is supplied with Windows 7 and Windows Server 2008.

By default the netsh tool is located in the c:\Windows\System32 folder.

To configure the ACL

  1. On the computer running Microsoft Dynamics NAV Server, open a command prompt as an administrator as follows:
    1. From the Start menu, choose All Programs, and then choose Accessories.
    2. Right-click Command Prompt, and then choose Run as administrator.
  1. At the command prompt, type the following command to change to the Windows\System32 directory.

cd\Windows\System32

  1. To view the ACL for the web services ports to determine if any entries are already using the relevant host name and port, type the following command:

netsh http show urlacl

  1. Each entry is listed by the Reserved URL field, which has the format http://hostname:port. hostname is the name of the computer running the service and port is the port number the service runs on. A ‘+’ (plus sign) in the Reserved URL field represents localhost, which indicates the computer that you are working on.
  2. By default, SOAP and OData web services in Microsoft Dynamics NAV use ports 7047 and 7048, respectively, and connect to a Microsoft Dynamics NAV Server instance named DynamicsNAV80. The default reserved URL entries for these services are: http://+:7047/DynamicsNAV80/ and http://+:7048/DynamicsNAV80/.
  3. You must delete any entries that use the same port as the SOAP or OData web services.

To delete an entry, type the following command:

netsh http delete urlacl url=http://hostname:Port/NAVserver/

Substitute Port with the port number of the SOAP or OData web service and NAVserver with the name of the Microsoft Dynamics NAV Server instance.

For example, to delete the default entries for SOAP and OData, use the following two commands:

netsh http delete urlacl url=http://+:7047/DynamicsNAV80/

netsh http delete urlacl url=http://+:7048/DynamicsNAV80/

  1. To register the ports for the SOAP and OData web service with https, type the following command for each service:

netsh http add urlacl url=https://hostname:port/navserver user=DOMAIN\user

  1. Substitute the following options with the proper values:
Option Description
hostname The name of the computer running Microsoft Dynamics NAV Server. Use + for localhost.
port The port that is used by the web service.
navserver The name of the Microsoft Dynamics NAV Server instance to use with the web service. The default is DynamicsNAV80.
DOMAIN\username The domain and user name of the service account for Microsoft Dynamics NAV Server. If the service account for Microsoft Dynamics NAV Server is Network Service, then use “NT AUTHORITY\NETWORK SERVICE”.
  1. For example, if the service account for Microsoft Dynamics NAV Server has the domain ABC and the user name xyz, and then the command for the SOAP web service is as follows:

netsh http add urlacl url=https://+:7047/DynamicsNAV80 user=”NT AUTHORITY\NETWORK SERVICE”

  1. If the service account for Microsoft Dynamics NAV Server is Network Service, then the command is as follows:

netsh http add urlacl url=https://+:7047/DynamicsNAV80 user=”NT AUTHORITY\NETWORK SERVICE”

  1. To verify that your port has been registered, repeat step 3.

To configure the port to use the SSL certificate

  1. At the command prompt, type the following command to view the current port configurations:

netsh http show sslcert

  1. All port bindings to SSL certificates are listed. Each entry is indicated by the IP:port field, where IP is the IP address that uses the port (0.0.0.0 indicates all IP addresses) and port is the port number.
  2. If SSL is already configured on the address and port that you want to use for SOAP or OData web services, then use the following command to delete the entry:

netsh http delete ssl ipport=ipaddress:port

  1. Substitute ipaddress and port with the IP address and port number of the entry that you want to delete.
  2. For example, to delete the entry on the default SOAP port, use the following command:

netsh http delete ssl ipport=0.0.0.0:7047

  1. To bind an SSL certificate to the SOAP or OData web service port, use the following command:

netsh http add sslcert ipport=ipaddress:port certhash=thumbprint appid={id}

  1. Substitute the following options with the proper values:
Option Description
ipaddress The IP address that can use the web service. Use 0.0.0.0 to include all IP addresses.
port The port that is used by the web service.
thumbprint The certificate thumbprint that you obtained in previous post.
id A 32-digit hexadecimal number that identifies the Microsoft Dynamics NAV application. The id must be in the format {NNNNNNNN-NNNN-NNNN-NNNN-NNNNNNNNNNNN). If you do not know the appid, then use any value, such as {00112233-4455-6677-8899-AABBCCDDEEFF}.
  1. For example, the following command binds a certificate that has the thumbprint c0d0f27095b03d4317e219841024328cef248779 to port 7047 for all IP addresses:

netsh http add sslcert ipport=0.0.0.0:7047 certhash=c0d0f27095b03d4317e219841024328cef248779 appid={00112233-4455-6677-8899-AABBCCDDEEFF}

Imp Note: – Please replace the port and thumbprint values with your valid values.

Restart the Microsoft Dynamics NAV Server Instance

You can use the Microsoft Dynamics NAV Server Administration Tool to restart the Microsoft Dynamics NAV Server instance.

To restart Microsoft Dynamics NAV Server

  1. In Microsoft Dynamics NAV Server Administration tool, in the left pane, under Console Root, choose the Microsoft Dynamics NAV Server computer.
  2. In the center pane, choose the Microsoft Dynamics NAV Server instance that you have configured, and then in the right pane under Actions, choose Restart.
  3. After the instance restarts, close Microsoft Dynamics NAV Server Administration tool.

Verifying the Configuration

You should now be able to use web services that are encrypted with SSL. To verify this, type the following URL in the address bar for your browser.

https://localhost:7047/DynamicsNAV80/WS/services

The page lists any web services that have been published.