Danjo's two cents NAV, Powershell, Android, C#, Lync, Win8, …


Dynamics Techincal Airlift [Germany] A look at the SQL-Side of Querys

During my airlift presentation I have shown a SQL-Statement which was captured using sql profiler during runtime of a query-object.

This query brings the same results as TOP10-Customer-Report, but in shorter time.

Here is what the statement behind the query looks like.

SELECT TOP (10) ISNULL("Customer"."No_",@0) AS "No",ISNULL("Customer"."Name",@1) AS "Name",ISNULL(SUM("SUB$Customer$Sales (LCY)"."Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)"),@3) AS "Sum_Sales_LCY",ISNULL(SUM("SUB$Customer$Balance (LCY)"."Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)"),@5) AS "Sum_Balance_LCY" FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Customer" AS "Customer" WITH(READUNCOMMITTED)

OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Sales (LCY)$Cust_ Ledger Entry"."Sales (LCY)"),@2) AS "Sales (LCY)$Cust_ Ledger Entry$SUM$Sales (LCY)" FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Cust_ Ledger Entry" AS "Sales (LCY)$Cust_ Ledger Entry" WITH(READUNCOMMITTED)  WHERE ("Sales (LCY)$Cust_ Ledger Entry"."Customer No_"="Customer"."No_")) AS "SUB$Customer$Sales (LCY)"

OUTER APPLY (SELECT TOP (1) ISNULL(SUM("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."Amount (LCY)"),@4) AS "Balance (LCY)$Detailed Cust_ Ledg_ Entry$SUM$Amount (LCY)" FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Detailed Cust_ Ledg_ Entry" AS "Balance (LCY)$Detailed Cust_ Ledg_ Entry" WITH(READUNCOMMITTED)  WHERE ("Balance (LCY)$Detailed Cust_ Ledg_ Entry"."Customer No_"="Customer"."No_")) AS "SUB$Customer$Balance (LCY)"


This example shows how the query-object increases performance by the usage of TOP, nested statements and SUM.


NAV2013 – How to access OData

Did you already try out the new possibilities of OData coming with Microsoft Dynamics NAV 2013?

Here is a quick summary of what you can do when accessing OData webservice.

All examples can be done with default installation of Cronus and publishing Page 22 as a webservice with the name Customer.


Show all OData-feeds:


Show Metadata:


Show all customers:


Show customer with the primary key 10000 (using key):


Show all customers with Location Code BLUE (using field-filter):

http://localhost:7048/DynamicsNAV70/odata/Customer?$filter=Location_Code eq 'BLUE'

Show Name, Location Code and Credit Limit (LCY) from customers (selecting a couple of fields only):

http://localhost:7048/DynamicsNAV70/odata/Customer?$select=Name, Location_Code, Credit_Limit_LCY

Show Name, Location Cod and Credit Limit (LCY) for customers with Location Code BLUE (selection and filter):

http://localhost:7048/DynamicsNAV70/odata/Customer?$filter=Location_Code eq 'BLUE' &$select=Name, Location_Code, Credit_Limit_LCY


NAV2013: How to convert a NAV2009 Database

Last week Rene Gayer posted how to technically convert a NAV 2009 database to NAV 2013.

Since this is a topic I already worked on, I asked him about a german translation for his post. He decided that his post is interessting for all NAV Developers and wrote it only in english.

For english people: http://www.dynamics-community.at/post/2012/09/06/NAV2013-How-to-convert-a-NAV-2009-Database-to-NAV-2013.aspx

I will post a german version of this how-to in my blog.

Tagged as: No Comments

The Microsoft Dynamics NAV 2013 Beta Virtual Machine Demos are now available.

Did you already see that Microsoft published a Virtual Machine for NAV2013?
It can be downloaded from the partner-source.
Just follow this link: https://mbs.microsoft.com/partnersource/deployment/methodology/vpc/MSDYN_NAV2013BetaVMDemos.htm

Tagged as: No Comments

NAV 2013 Benchmarking results

Yesterday Vjekoslav Babić published a new blog post where he shows the results of his benchmark-tests with Microsoft Dynamics NAV 2009 and 2013 Beta.

It's pretty amazing how much faster the current beta-version already runs even compared to the native databases.

But you should have a look for yourself:


Tagged as: No Comments
%d bloggers like this: