Monday, September 18, 2017

How to call Stored Procedure in MS Dynamics NAV?

How to Connect with SQl in NAV 2009?

This is for NAV 2009 using Automation Object For Above 2009 you can change these variables to Dotnet. The Link is provided below :- 

Direct Link For Reference in NAV 2013 and Above.

Name DataType Subtype Length
ADOConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection 
LADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command 
LADOParameter Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter 
lvarActiveConnection Variant  
SQLString Text  1024
ADORecordSet Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset 

Name ConstValue
pcodServerName Server NAME
pcodDatabaseName DBNAME
pcodPassword PASSWORD

Step 1 :- Create the Connection

ltxtConnectionString := 'Driver={SQL Server};'
                        + 'Server='+pcodServerName+';' 
                        + 'Database='+pcodDatabaseName+';' 
                        + 'Uid='+pcodUserID+';' 
                        + 'Pwd='+pcodPassword+';'; 

NameCompany := 'DEV Company';


Step 2. :- Calling the SQL Connection.


ADOConnection.ConnectionString := GetLiveConnectionString;


Step 3. :- Write the SQL Querry

SQLString := 'select CAST(sum(SIL.Amount) as float) as InvoiceAmount, '
            + '((SUM(SIL.Amount)-SUM(SIL.[Unit Cost (LCY)]*SIL.Quantity))/SUM(SIL.Amount)*100) as GrossProfit '
            + 'from dbo.['+NameCompany+'$Sales Invoice Line] as SIL '
            + 'join dbo.['+NameCompany+'$Sales Invoice Header] as SIH '
            + 'on SIL.[Document No_] = SIH.[No_] '
            + 'where (SIH.[Posting Date] between '+ ConvertedStartDate + ' and ' + ConvertedEndDate + ');';

Step 4. :- Execute the SQL Querry

ADORecordSet := ADOConnection.Execute(SQLString);

Step 5 :- Manuplate if you want the record set if you want the SQL Record Set.


Step 6 :- Close All Connection and Clear the Variable.


Tuesday, August 15, 2017

More than 30 Points you must think before designing any RDLC Reports in MS Dynamics NAV 2017


This post is related with RDLC Report Defination Language in C/AL so before Designing you must

know the reason why and whom and How you will create Reports in MS Dynamics NAV 2017.

So my readers will be able to resolve many problems and concepts which anyone can use in their

daily development process.

25. We can pick Year , Month and Date Difference From Posting Date in NAV.


= Year(Fields!PostingDate.Value) Further you can also use this as grouping in RDLC.

24. We can use Format in TextBox & also Use HTML in TextBox in NAV

You can use HTML Format in Reports using Textbox If you need help in it you can comment below.

23. Use Of Exit Funtion in Source Expression

You may use  Exit Function also in Data Source of DataItem of RDLC Reporting

22. PrintOnlyIfDetail

Most of you might be knowing it can be used to save unwanted dataset in Report so can be used to improve the timing issue.

21. Use Matrix Report

20. Use conversion Function in RDLC Reporting to convert Format of Expression

19. What is the use of Dataset (Fields!Expression,"Dataset Result")?

It Defines the scope of Field Expression to complete the Result.

18. Use of Function in RDLC 

Check where Code.BlankZero Defination is Given in Report in Report Properties.

17. We can also use Date Function in RDLC Reports

16. We can use year for Grouping in RDLC Reports

15. INLine IF (IIF) and Switch Case can be used in expression of Row and textboxes

14. We can change Expression of Text Box on Behalf of conditions

13. Filling row with color even odd as per requirement.

You can use it in visibility expression

=Iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White")

You can change the Color of BackGround on Behalf of grouping in MS Dynamics NAV.

12. We can insert Row No. in Table using inbuilt function ROWNUBER in RDLC

11. We can repeat header in RDLC Report on every page using static property

10. We can use top n property present in Filters of tablix property to calculate top N customer Item Vendor Report.

9. You can put images in MS Dynamics NAV Report and according to conditions.

8. You can Dynamically break a Tablix and remove last Line from tablix easily using RowNumber Inbuilt Function in RDLC.


So these are the points which are possible in MS Dynamics NAV if any any of the above point you need practical session you can comment me or directly message me from my blog I will update you with my blog.

Thanks to all my readers.

Happy Independence Day 2017 to all Indians.

Understand Grouping and applying Toggles in MS Dynamics NAV RDLC Layout


Before going in detail you should check the details about controlling the Columns and Rows in NAV

RDLC Layout.

Today I got a requirement from my client to show all Items By Location Report in MS Dynamics 

NAV. The requirement was simple using Item and ILE Tables but he wants to see the total in one line 

and if he needs to show details then only details must appear.

This requirement can be completed by using SSRS or RDLC Toggle Functionality present in MS Dynamics NAV.

So lets Get Started with seeing the Final result in Demo DB Cronus.

You can click on + and see the details so it is dynamic report for your client.

How to achieve it?

This Hide Details is a boolean field and Integer has Data Item Table view property
Number = 1 as we do earlier.

Now I had created the Layout as follows

Location Code is Grouped as Parent Group here. And selecting the Details Row I had Set the expression as Follows:

NOT Last(Fields!HideDetails.Value, "DataSet_Result")

Monday, August 14, 2017

Top 10 Points While Setting Up New Company in MS Dynamics NAV

1. Define No. Series

You have to decide which No. Series You are going to use and record for future discussion with 

client. These No. Series are defined under :-

1. Sales & Receivables Setup.
2. Purchase & Payables Setup.

2. Decide about Payment Tolerance?

This implies whether an invoice is settled at less amount than actual means 1059.00 $ can be settled at 1050.00 $ so ask from your client.

3. What are the major setups needed to start the transaction?

2. Sales & Receivables SETUP
3. Purchase & Payables Setup.
4. Inventory Setup.
5. VAT and TAX SETUP as per your localisation.

4. Document Exchange Service & XBRL 

If data exchange is required from MS Dynamics NAV Financial Management PEPPOL Format
Extensible Business Reporting Layout required by system.

5. Types Of Costing Method

For inventory decision FIFO LIFO Average Specific or Standard.
FIFO :- Inventory Valuation will be based on cost last received 
LIFO:-  Inventory Valuation will be based on cost First received
AVERAGE:- Inventory Valuation will be based on average of all.
SPECIFIC:- Inventory Valuation will be based on serial No. of Items and Inv. Val. will be based on exact units in stock.
STANDARD :- Inventory Valuation will be based on FIFO but the only difference is that the incoming cost which are based on an estimated cost that you set up on an ITEM. It is mainly used with Manufacturing.

6. How will you close the company?

Will there be any subsidiary coming in the company decide the designing of COA accordingly.

7. Decide about the Posting Groups?

Specific , General and Tax Posting Group present in MS Dynamics NAV.

1. Customer Posting Group 
2. Vendor Posting Group
3. Inventory Posting Group
4. Bank Account Posting Group
5. Job Posting Group
6. Fixed Asset Posting Group
7. Gen. Business Posting group
8. Gen. Product Posting Group
9. VAT Posting Setup / Tax Setup.

8. How will you do Payment Reconciliation ?

Will there be any data exchange framework requirement for details you may check Table series 1200.

So these are few major points which a Functional consultant must be aware before delivering the project to client.

Sunday, August 13, 2017

How to use Update Propagation Property in NAV


This post is bridge between a blog which I liked the most to describe the functionality of MS

Dynamics NAV from 2015 version.

What is Updatepropagation property in MS Dynamics NAV?

To reduce the customization in the page of MS Dynamics NAV we can use this property , you will 

find this property on Sub Page as shown below in screen.

With the use of this property you dont need to use Currpage.Update on various triggers.

Where we can use this property?

This property is majorly used while calculating Lines Column in document on Header for eg.

Sales Header .

TO view about this in practical you can go to this Link.