Skip to main content

Featured

An Overview of the Chart of Accounts in Microsoft Dynamics NAV

The chart of accounts is a listing of all accounts used in the general ledger of an organization. The chart is used by the accounting software to aggregate information into an entity's financial statements. The chart is usually sorted in order by account number, to ease the task of locating specific accounts. The accounts are usually numeric, but can also be alphabetic or alphanumeric.  Sample Chart of Accounts Accounts are usually listed in order of their appearance in the financial statements, starting with the balance sheet and continuing with the income statement. Thus, the chart of accounts begins with cash, proceeds through liabilities and shareholders' equity, and then continues with accounts for revenues and then expenses. Many organizations structure their chart of accounts so that expense information is separately compiled by department; thus, the sales department, engineering department, and accounting department all have the same set of expense accoun
2 comments
Read more

How to Design a bar chart in Microsoft Dynamics NAV 2017 - Microsoft Dynamics NAV Uganda and World Wide

Microsoft Dynamics NAV allows its users to represent NAV data in charts, graphically. Microsoft Dynamics NAV supports many chart types, which are line chart, bar chart and pie chart. There are many ways in which the user can build graphical charts to represent his/her chart; here in this blog, we will discuss bar charts.
Fig 1: Bar chart
Example: You have a list of Employees in the company. Each employee is assigned to a given contract. You have been given a task to produce a bar chart report that shows the employees per gender per given contract.

 NOTE: Ensure that you have Microsoft Visual Studio 2013 installed if you are using Microsoft Dynamics NAV 2017 before following the procedure below

Fig 2: Employee Data - Tabular form

Fig 3: Employee Data - Bar Chart in Microsoft Excel
Below are the steps you can follow to produce the bar chart report:

Go to the development environment, select the Report object and go to File – New.
In the first-row type in Data Source Integer and Name EmployeeContract.

Fig 4: Report Object


Press CTRL + G or go to view – C/AL Globals and fill in the following:
MaleEmp, FemaleEmp, AdmMale, AdmnFemale, ProdMale, ProdFemale, DevMale, DevFemale, Employee and ContractName. These are the variables to be used in the report.

Fig 5: Variables


On the same page, Click Functions and type in CalculateGenderNo

With this function selected, click Properties icon and change the Local Property value to – No.

Fig 6: Function Properties

Fig 7: Property - Local


Go back to the report dataset designer and add the rest of the rows as seen: for EmployeeContract.Number, MaleEmp, FemaleEmp, and ContractName.

Fig 8: Report Dataset


In the same page, press F9 or click View and select C/AL Code.

Under EmployeeContract – OnPreDataItem() type:

CalculateGenderNo;
Under EmployeeContract – OnAfterGetRecord() type:

IF Number = 1 THEN BEGIN
  //ADM
  MaleEmp   := AdmMale;
  FemaleEmp := AdmnFemale;
  ContractName := 'ADM';
END ELSE IF Number = 2 THEN BEGIN
  //PROD
  FemaleEmp := ProdFemale;
  MaleEmp   := ProdMale;
  ContractName := 'PROD';
END ELSE IF Number = 3 THEN BEGIN
  //DEV
  FemaleEmp := DevFemale;
  MaleEmp   := DevMale;
  ContractName := 'DEV';
END ELSE IF Number > 3 THEN BEGIN
  CurrReport.BREAK;
END;

Under CalculateGenderNo type:

//ADM Male
Employee.RESET;
Employee.SETRANGE(Employee.Gender, Employee.Gender::Male);
Employee.SETRANGE(Employee."Emplymt. Contract Code", 'ADM');
IF Employee.FINDFIRST THEN REPEAT
  AdmMale += 1;
UNTIL Employee.NEXT = 0;

//ADM Female
Employee.RESET;
Employee.SETRANGE(Employee.Gender, Employee.Gender::Female);
Employee.SETRANGE(Employee."Emplymt. Contract Code", 'ADM');
IF Employee.FINDFIRST THEN REPEAT
  AdmnFemale += 1;
UNTIL Employee.NEXT = 0;

//PROD Female
Employee.RESET;
Employee.SETRANGE(Employee.Gender, Employee.Gender::Female);
Employee.SETRANGE(Employee."Emplymt. Contract Code", 'PROD');
IF Employee.FINDFIRST THEN REPEAT
  ProdFemale += 1;
UNTIL Employee.NEXT = 0;

//PROD Male
Employee.RESET;
Employee.SETRANGE(Employee.Gender, Employee.Gender::Male);
Employee.SETRANGE(Employee."Emplymt. Contract Code", 'PROD');
IF Employee.FINDFIRST THEN REPEAT
  ProdMale += 1;
UNTIL Employee.NEXT = 0;

//DEV Female
Employee.RESET;
Employee.SETRANGE(Employee.Gender, Employee.Gender::Female);
Employee.SETRANGE(Employee."Emplymt. Contract Code", 'DEV');
IF Employee.FINDFIRST THEN REPEAT
  DevFemale += 1;
UNTIL Employee.NEXT = 0;

//DEV Male
Employee.RESET;
Employee.SETRANGE(Employee.Gender, Employee.Gender::Male);
Employee.SETRANGE(Employee."Emplymt. Contract Code", 'DEV');
IF Employee.FINDFIRST THEN REPEAT
  DevMale += 1;
UNTIL Employee.NEXT = 0;

Save the object with ID – 50002 and Name – Employee Per Gender/Contract.

Go to the Report Dataset designer. Go to View then select Layout or type ALT + V, and type Y. this will open Microsoft Visual Studio.

If you receive a Project Target Framework Not Installed warning as seen below, select the option - Change the target to .NET Framework 4.5 You can change back to “.NETFramework,Version=v4.5.2” at a later time and click OK. 

Fig 9: Project Target Framework Warning

Fig 10: Report Layout


From the ReportLayout, select Toolbox, then Chart.

Select the Columns option. This will open a bar chart as seen below.

Fig 11: Column


Fig 12: Bar Chart


Click inside the chart area, this will open a chart data window on the right as seen.

Fig 13: Chart Data


Under Values Group, click on the plus icon to add MaleEmp and FemaleEmp. Under Category Groups, click on the plus icon to add Number_EmployeeContract. These are some of the columns we added in the Report Dataset Designer earlier.

Fig 14: Chart Data - Items


Modify chart title to Employee Per Gender / Contract. Change the Axis title on the horizontal axis to Contract and the axis title on the vertical axis to Number of Employees.

Click inside the chart area, this will open a chart data window if its not open. Select the upper arrow point down on the MaleEmp Value and select Show Data Labels. This displays figures on the individuals bars in the bar chart.

Follow the same previous procedure for the FemaleEmp.

Fig 15: Data Labels


Select the upper arrow point down again on the MaleEmp Value and select Series Properties. Select the Legends option. Under Custom Legend Text, fill in Male. Follow this same step for the FemaleEmp Value and fill in Female in the Custom Legend Text field.    

Fig 16: Series Properties


Fig 17: Series Properties - Legend


Still within the Chart Data window, click on Number_EmployeeContract under Category Groups. Go to its properties on the right. Modify the Label Value to =Fields!ContractName.Value. This will display the name of the individual contracts on the bar chart on the horizontal axis.    

Fig 18: Label - Contract


You can also modify the color of the bars of the individual bar elements, that is male and female by selecting the MaleEmp or FemaleEmp. Then on the right under the properties window you can locate the Color property and select the color of your choice.

Fig 19: Color


After, you can close Microsoft Visual Studio. You will be prompted to save changes. Click Yes.

Fig 20: Save Report Layout

Go back to the Report Dataset designer and click on ContactName. This will open a prompt window asking you whether you want to load the changes. Click Yes. Save the object - Employee Per Gender/Contract.

Fig 21: Load Changes


You can then add this report to the Employee List page. You can refer to the tutorial for XMLPorts on how to add an object to a page - https://bryaneluis.blogspot.ug/2018/03/how-to-import-multiple-journal-entries.html. In this case we are adding a Report – 50002 – Employee Per Gender/Contract to the employee list page – 5201.

You should be able to run the report as seen.

Fig 22: Preview Report

Fig 23: Bar Chart - Microsoft Dynamics NAV


You can leave a comment or question in the comment section below. For more information, you can contact us at www.adroitltd.com – Microsoft Dynamics NAV Uganda.




Comments

Post a Comment