This Tutorial is available to download from the downloads page, there are two versions one for the GB localised version of Dynamics NAV and the other for the worldwide version, in the download you will find the NAV objects and the tutorial in PDF format   

Scenario

We have a requirement in from the Finance Director at Cronus International Ltd, they have acquired two companies as part of their growth strategy, and they will be moving the accounts information across to their MBS Dynamics NAV® database and the two new companies and all the transactions, will then go through these companies.

As these companies use different Fiscal year and accounting period start dates, to Cronus International Ltd, our implementation consultant has set these up ready for them in the Accounting Periods table.

The Finance Director is familiar with the Accounts Schedules but wants a report made from the G/L Accounts table with these options:

1. Grouping totals by General Business Posting Group
2. Must be able to deal with the new Accounting Periods
3. Whole system year and the twelve periods printed onto a landscape view
4. Accounting year with up to thirteen periods printed onto a landscape view
5. Rounding Factors of None, 1, 1000 and 1000000
6. Budget values only
7. Amount values only
8. Amount and Budget all
9. Amount and Budget only where the budget has a value

Analysis

1. Grouping totals by General Business Posting Group, no real problems here the wizard will do this for us.
2. Must be able to deal with the new Accounting Periods, I have seen some code to deal with this on the Customer Statistics card form, I will look there.
3. Whole system year and the twelve periods printed onto a landscape view, This we can do from the data table
4. Whole fiscal year and the thirteen periods printed onto a landscape view, I think I can squeeze in the 16 columns, just a lot of work copying and pasting.
5. Rounding Factors of None, 1, 1000 and 1000000, the Accounts Schedules can do this I will look there for the solution.
6. Budget values only, option box on the report.
7. Amount values only, option box on the report.
8. Amount and Budget, option box report
9. Amount and Budget only where the budget has a value, flag on the report

So we need to look at three things, which fields to use from the G/L Accounts table, how to get and set the date filters and how to do the output rounding.

There are several flowfields on the G/L Accounts table lets look at some of these and how they work and choose which to use in out report.

We have three Amount fields and two Budget fields in the image, there are others but we will look at these, as this flowfield structure is repeated throughout the system, in the Inventory, Jobs and Resources modules, so this could be useful to you later on.

Balance: this value is affected by the applied filters except the date filter, so if I put in a date filter of 01/01/2006..01/31/2006, this field would give the sum total value of all the transactions for the G/L Account.

Balance at date: this value is affected by the applied filters and the maximum Date Filter value, so if I put in a Date Filter of 01/01/2006..01/31/2006, this field would give the sum total value of all transactions up to the 01/31/2006.

Net Change: this value is affected by the applied filters and the maximum Date Filter value, so if I put in a Date Filter of 01/01/2006..01/31/2006, this field would give the sum total value of the selected transactions from the 01/01/2006 up to and including the 01/31/2006.

So we need to use the Net Change and Budgeted Amount fields for our report, we will look at this in the next section.

How to get and set the accounting periods, I said earlier that there are standard functions to do this so let’s take a look at one of the pieces the code we need to use:

CurrentDate := WORKDATE;

DateFilterCalc.CreateAccountingPeriodFilter(CustDateFilter[1],CustDateName[1],CurrentDate,0); DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[2],CustDateName[2]

,CurrentDate,0);

We have 4 variables

1. CurrentDate, a date variable to hold the base date.
2. CustDateFilter an array of text fields
3. CustDateName an array of text fields
4. DateFilterCalc Codeunit DateFilterCalc

Set the current date to the workdate

CurrentDate := WORKDATE;

Get the FiscalYear for the current date

DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[2],

CustDateName[2],CurrentDate,0);

Get the accounting period for the current date

DateFilterCalc.CreateAccountingPeriodFilter(CustDateFilter[1],

CustDateName[1],CurrentDate,0);

This is enough for us to work with the accounting Periods

Now for the Standard Calendar we need a different structure

We have 2 more variables

1. PeriodFormMgt codeunit PeriodFormManagement
2. CalendarSource

Setting which calendar type to use, 0=Day, 1=Week, 2=Period, 3=Quarter, 4=Year and 5=Accounting Period

Get the date record for the Calendar Year

CalendarSource=4

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

Get the date record for the Calendar Month

CalendarSource=2

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

So we can use these date structures for our report, as the company changes accounting periods or the structure, our report will still work, as Cronus International Ltd, want both views we will just drop on a Use Accounting Period flag on our report.
All we have to look at now is the rounding, so let’s look at the code in the Account Schedule Management and the function FormatCellAsText

FormatCellAsText(VAR ColumnLayout2 : Record "Column Layout";Value : Decimal) : Text[30]

IF NormalFormatString = '' THEN BEGIN

   IF NOT GLSetupRead THEN BEGIN

      GLSetup.GET;

      GLSetupRead := TRUE;

   END;

   NormalFormatString := Text014 + GLSetup."Amount Decimal Places" + Text015;

END;

WITH ColumnLayout2 DO

   IF Value <> 0 THEN

      CASE "Rounding Factor" OF

         "Rounding Factor"::None :

            ValueAsText := FORMAT(Value,0,NormalFormatString);

         "Rounding Factor"::"1" :

            ValueAsText := FORMAT(ROUND(Value,1));

         "Rounding Factor"::"1000" :

            ValueAsText := FORMAT(ROUND(Value/1000,0.1),0,Text016);

         "Rounding Factor"::"1000000" :

            ValueAsText := FORMAT(ROUND(Value/1000000,0.1),0,Text016);

      END;

EXIT(ValueAsText);

If we look at what happens here, a value gets passed in as a decimal, then based on which rounding factor is selected, passed back as a formatted string, we can see that most of what we need is in this function, we could copy this function and 3 text constants over to our report later, and just modify the code to suit our own requirement, but we will write our own function in this tutorial.

So we now know what we need to do, where we can find examples of how to do it, we can start by just using the Wizard to give us the fields and the structure of the report.

As this tutorial is aimed at all levels of developers, I am going to go through the Wizard steps one by step for anyone who is not familiar with using the Wizard.

Step 1 New report dialog choose the G/L account Table from the lookup list

Step 2 we only need four fields on our new dynamic report these include the two flowfields we looked at earlier, and we will be using in our report Net Change and Budgeted Amount

Step 3 Choose which key we want to run the report on

We want to group our report on the General Business Posting Group so we can total

We want totals for both our financial fields

Accept the default values

Save the report, we will save the report at each stage, this is a good habit to get into as there is nothing worse that loosing hours of work.

At this point we are not interested in the sections, we need to add some variables and code first, so to add our variables, view C/AL Globals and add the following

We will get to look at the variables later, but for now we need to add the array and Option values

Arrays:

DateFilter = 15

DateName = 15

ChangeAmtTxt = 15

BudgetAmtTxt = 15

ChangeAmt = 15

BudgetAmt = 15

To put in the array value, select the variable DateFilter then open the properties sheet for this variable Shift+F4, repeat this for the other 5 variables

Option Strings

RoundingFactor = None,1,1000,1000000

WhichValue = Amount,Budget,Both

To put in the option string value, select the variable RoundingFactor then open the properties sheet for this variable Shift+F4

Save your Report

Now we will add two functions, for this one you could copy and change the code, from Account Schedule Management but we will hand code it

Press F9, Select to the Functions tab

1. Type into the name field FormatCellAsText, now press the button Locals
2. Select the Parameters tab then add the Name = Value and DataType: Decimal.
3. Now select the variables tab and add Name = ValueAsText and DataType = Text with the Size = 30
4. Close the dialog box, and select TextConstants and add the following 3 lines
5. Text014
6. Text015 >
7. Text016
8. close the dialog box and save the report

Now we can add our Code

Press F9 to bring up the code window write the following

FormatCellAsText(Value : Decimal) : Text[30] Section

IF NormalFormatString = '' THEN BEGIN

   IF NOT GLSetupread THEN BEGIN

      GLSetup.GET;

      GLSetupread := TRUE;

   END;

   NormalFormatString := Text014 + GLSetup."Amount Decimal Places" + Text015;

END;

IF Value <> 0 THEN

   CASE RoundingFactor OF

      RoundingFactor::None :

         ValueAsText := FORMAT(Value,0,NormalFormatString);

      RoundingFactor::"1" :

         ValueAsText := FORMAT(ROUND(Value,1));

      RoundingFactor::"1000" :

         ValueAsText := FORMAT(ROUND(Value/1000,0.1),0,Text016);

      RoundingFactor::"1000000" :

         ValueAsText := FORMAT(ROUND(Value/1000000,0.1),0,Text016);

   END;

EXIT(ValueAsText);

Save the Report

So what have we got here?

FormatCellAsText(Value : Decimal) : Text[30] Section

Note: ValueAsText is a local variable and is empty each time

If the NormalFormatString string is empty I.E first call to this function then fill it

IF NormalFormatString = '' THEN BEGIN

   If we haven’t already got the G/L setup record then get it

   IF NOT GLSetupread THEN BEGIN

      GLSetup.GET;

      GLSetupread := TRUE;

   END;

   In the UK the resulting string will be

   NormalFormatString := Text014 + GLSetup."Amount Decimal Places"

   + Text015;

END;

If we have a financial value passed in, format it to a string and return the value

Text016 will Round to 1 decimal place E.G = 1200 with factor 1000 will return 1.2

IF Value <> 0 THEN

   CASE RoundingFactor OF

      RoundingFactor::None :

         ValueAsText := FORMAT(Value,0,NormalFormatString);

      RoundingFactor::"1" :

         ValueAsText := FORMAT(ROUND(Value,1));

      RoundingFactor::"1000" :

         ValueAsText := FORMAT(ROUND(Value/1000,0.1),0,Text016);

      RoundingFactor::"1000000" :

         ValueAsText := FORMAT(ROUND(Value/1000000,0.1),0,Text016);

   END;

Return the formatted string

EXIT(ValueAsText);

Press F9, Select to the Functions tab

1. Type into the name field BuildDateFilter, now press the button Locals
2. Select the Parameters tab then add the Name = Calendar, DataType: = Record and SubType = Date.
3. Select the tab return value then enter return type = text and length = 50
4. close the dialog box and save the report

Now we can add our Code

Press F9 to bring up the code window write the following

BuildDateFilter(Calendar : Record Date) : Text[50]

WITH Calendar DO BEGIN

   IF "Period End" = 0D THEN

      "Period End" := 31129999D;

   "Period End" := NORMALDATE("Period End");

   EXIT(FORMAT("Period Start") + '..' + FORMAT("Period End"));

END;

What does the function do, when you pass in the Calendar record?

BuildDateFilter(Calendar : Record Date) : Text[50]

WITH Calendar DO BEGIN

   If there is no end date set the date to 12/31/9999

   IF "Period End" = 0D THEN

      "Period End" := 31129999D;

   Set the Period end date from a closed date to a normal date

   "Period End" := NORMALDATE("Period End");

   Create a filter string IE: 01/01/2006..01/31/2006 and return the value

   EXIT(FORMAT("Period Start") + '..' + FORMAT("Period End"));

END; ends the with statement

Now we will add the code, to the report, select a blank data item line and press F9 to view the C/AL code

Now add the following code

IF "G/L Account".GETFILTER("Date Filter") <> '' THEN

   CurrDate:= "G/L Account".GETRANGEMAX("Date Filter")

ELSE

   CurrDate := WORKDATE;

GLFilters := "G/L Account".GETFILTERS;

IF UseAccPeriods THEN BEGIN

   DateFilterCalc.CreateFiscalYearFilter(DateFilter[14],DateName[14],CurrDate,0);

   EVALUATE(Calendar."Period Start",COPYSTR(DateFilter[14],1,8));

   EVALUATE(CurrDate,COPYSTR(DateFilter[14],11));

   CalendarSource := 5;

END ELSE BEGIN

   Calendar."Period Start" := CurrDate;

   CalendarSource := 4;

   PeriodFormMgt.FindDate('',Calendar,CalendarSource);

   DateFilter[14] := BuildDateFilter(Calendar);

   DateName[14] := Calendar."Period Name";

   CurrDate := NORMALDATE(Calendar."Period End");

   CalendarSource := 2;

END;

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

DateName[1] := Calendar."Period Name";

DateFilter[1] := BuildDateFilter(Calendar);

MaxColumnNo := 1;

FOR i := 2 TO 13 DO BEGIN

   IF PeriodFormMgt.NextDate(1,Calendar,CalendarSource) <> 0 THEN

      IF NORMALDATE(Calendar."Period End") <= CurrDate THEN BEGIN

         MaxColumnNo := MaxColumnNo + 1;

         DateName[i] := Calendar."Period Name";

         DateFilter[i] := BuildDateFilter(Calendar);

      END;

END;

What are we doing here?

Make sure we have a date to work with if a filter was input use that else use workdate

IF "G/L Account".GETFILTER("Date Filter") <> '' THEN

   CurrDate:= "G/L Account".GETRANGEMAX("Date Filter")

ELSE

   CurrDate := WORKDATE;

Copy all the filters the user may have entered

GLFilters := "G/L Account".GETFILTERS;

If the user elected to use accounting periods then we action this code

IF UseAccPeriods THEN BEGIN

   Use the function to return our last column filter and name

   DateFilterCalc.CreateFiscalYearFilter(DateFilter[14],DateName[14],CurrDate,0);

   We now have a date filter to cover the fiscal year IE 01/01/06..12/31/06

   Now we chop the date filter and cast the values into 2 date variables

   Period Start 1 to 8 = 01/01/06 and CurrDate from string position 11 = 12/31/06

   EVALUATE(Calendar."Period Start",COPYSTR(DateFilter[14],1,8));

   EVALUATE(CurrDate,COPYSTR(DateFilter[14],11));

   Set the calendar Source to Accounting Period

   CalendarSource := 5;

END ELSE BEGIN

   This code is run for the system year

   Set the Period start with the CurrDate

   Calendar."Period Start" := CurrDate;

   Set the Calendar Source to Year

   CalendarSource := 4;

   Find the relevant year

   PeriodFormMgt.FindDate('',Calendar,CalendarSource);

   Use our function to make our Date filter

   DateFilter[14] := BuildDateFilter(Calendar);

   Return the period name IE 2006

   DateName[14] := Calendar."Period Name";

   Set this to the last day of the year so we can use it later

   CurrDate := NORMALDATE(Calendar."Period End");

   Set our source to Month

   CalendarSource := 2;

END;

Now we have a calendar source of Month or Accounting period based on the Use Accounting Periods flag, find the first period within the Year

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

DateName[1] := Calendar."Period Name";

DateFilter[1] := BuildDateFilter(Calendar);

Now we loop and fill in our Date filter and date name for the other periods

MaxColumnNo := 1;

FOR i := 2 TO 13 DO BEGIN

   If a record is returned then action the code

   IF PeriodFormMgt.NextDate(1,Calendar,CalendarSource) <> 0 THEN

      Check to see if the period end date is in range

      IF NORMALDATE(Calendar."Period End") <= CurrDate THEN BEGIN

         MaxColumnNo := MaxColumnNo + 1;

         DateName[i] := Calendar."Period Name";

         DateFilter[i] := BuildDateFilter(Calendar);

      END;

END;

Next we will add our code to the “G/L Account” in dataitem view select the line and press F9 to view the C/al code

Now add this code

G/L Account - OnPreDataItem() Section

LastFieldNo := FIELDNO("Gen. Bus. Posting Group");

SETRANGE("Date Filter");

CurrReport.CREATETOTALS(ChangeAmt,BudgetAmt);

G/L Account - OnAfterGetRecord() Section

CLEAR(ChangeAmt);

CLEAR(BudgetAmt);

HasValue := FALSE;

FOR i := 1 TO MaxColumnNo DO BEGIN

   SETRANGE("Date Filter");

   SETFILTER("Date Filter",DateFilter[i]);

   CALCFIELDS("Net Change","Budgeted Amount");

   ChangeAmt[i]:= "Net Change";

   BudgetAmt[i]:= "Budgeted Amount";

   IF NOT HasValue AND NOT ShowOnlyIfBudget THEN BEGIN

      IF WhichValue IN[WhichValue::Amount,WhichValue::Both] THEN

         HasValue:= ChangeAmt[i] <> 0;

   END;

   IF NOT HasValue THEN BEGIN

      IF WhichValue IN[WhichValue::Budget,WhichValue::Both] THEN

          HasValue:= BudgetAmt[i] <> 0;

   END;

END;

IF NOT HasValue THEN

   CurrReport.SKIP;

SETRANGE("Date Filter");

SETFILTER("Date Filter",DateFilter[14]);

CALCFIELDS("Net Change","Budgeted Amount");

ChangeAmt[14]:= "Net Change";

BudgetAmt[14]:= "Budgeted Amount";

FOR i := 1 TO 14 DO BEGIN

   IF ChangeAmt[i] <> 0 THEN

      ChangeAmtTxt[i] := FormatCellAsText(ChangeAmt[i]);

   IF BudgetAmt[i] <> 0 THEN

      BudgetAmtTxt[i] := FormatCellAsText(BudgetAmt[i]);

END;

What’s it all doing?

G/L Account - OnPreDataItem() Section

Navision’s wizard wrote this line

LastFieldNo := FIELDNO("Gen. Bus. Posting Group");

Clear the date filter

SETRANGE("Date Filter");

Tell Navision we want group totals for our financial fields

CurrReport.CREATETOTALS(ChangeAmt,BudgetAmt);

G/L Account - OnAfterGetRecord() Section

Clear our variables

CLEAR(ChangeAmt);

CLEAR(BudgetAmt);

HasValue := FALSE;

Fill our Financial Array with the filtered values

FOR i := 1 TO MaxColumnNo DO BEGIN

   SETRANGE("Date Filter");

   SETFILTER("Date Filter",DateFilter[i]);

   CALCFIELDS("Net Change","Budgeted Amount");

   ChangeAmt[i]:= "Net Change";

   BudgetAmt[i]:= "Budgeted Amount";

   Now we want to set our flag based on our selections of Amount or Both

   IF NOT HasValue AND NOT ShowOnlyIfBudget THEN BEGIN

      IF WhichValue IN[WhichValue::Amount,WhichValue::Both] THEN

          HasValue:= ChangeAmt[i] <> 0;

   END;

   Now we want to set our flag based on our selections of Budget or Both

   IF NOT HasValue THEN BEGIN

      IF WhichValue IN[WhichValue::Budget,WhichValue::Both] THEN

         HasValue:= BudgetAmt[i] <> 0;

      END;

END;

If our array variable is empty move on to the next record 

IF NOT HasValue THEN

   CurrReport.SKIP;

Add our fiscal year values

SETRANGE("Date Filter");

SETFILTER("Date Filter",DateFilter[14]);

CALCFIELDS("Net Change","Budgeted Amount");

ChangeAmt[14]:= "Net Change";

BudgetAmt[14]:= "Budgeted Amount";

Convert our Values to Text

FOR i := 1 TO 14 DO BEGIN

   IF ChangeAmt[i] <> 0 THEN

      ChangeAmtTxt[i] := FormatCellAsText(ChangeAmt[i]);

   IF BudgetAmt[i] <> 0 THEN

      BudgetAmtTxt[i] := FormatCellAsText(BudgetAmt[i]);

END;

So now we can look at our wizard sections.

Delete and change the section width to 262 and delete the following sections

G/L Account.GroupHeader (3)

G/L Account.Groupheader (5)

G/L Account.GroupFooter(7)

Now we will set some properties for our text boxes

G/L Account, Body (4) Field “Net Change” SourceExpr = ChangeAmtTxt[1]

G/L Account, Body (4) Field “Net Change” Width = 1350

G/L Account, Body (4) Field “Budgeted Amount” SourceExpr = BudgetAmtTxt[1]

G/L Account, Body (4) Field “Budgeted Amount” Width = 1350

G/L Account, GroupFooter (5) Field “Net Change” SourceExpr = ChangeAmtTxt[1]

G/L Account, GroupFooter (5) Field “Net Change” Width = 1350

G/L Account, GroupFooter (5) Field “Net Change” FontBold = Yes

G/L Account, GroupFooter (5) Field “Budgeted Amount” SourceExpr = BudgetAmtTxt[1]

G/L Account, GroupFooter (5) Field “Budgeted Amount” Width = 1350

G/L Account, GroupFooter (5) Field “Budgeted Amount” FontBold = Yes

Next we need to add a field for the Dynamic Headings

View tool box and drop a text box into the G/L Account header 2 Section

Delete the old labels Net Change and Budgeted amount

Properties

SourceExpr = DateName[1]

Width = 1350

FontBold = Yes

VertAlign = Bottom

Now for the boring bit we have to copy each field and paste it back in 13 times, then go to properties and change the index value to match the Column

As in this example DateName[3] being the third column index

So we now have 14 column headings, save the report

Select the G/L Account, Body (5) section bar press F3 and you should get the a dialog box like this, make sure that the

The Options selected should be Body and After Current Section, press ok and repeat these steps, so we have 3 body sections on our report.

Now do the same to our first GroupFooter section adding a new GroupFooter, after current Section, we should now have 3 footer sections

Now do the same to our first Header section adding a new Header, after current Section, we should now have 2 Header sections

Select the body (5) section and BudgetAmtTxt[1] field, and drop it in the 1^st new Body (6) section, now do the same with the GroupFooter BudgetAmtTxt[1] field, move it into the second group footer (9) section.

Now for the boring bit we have to copy each field and paste it back in 13 times, then go to properties and change the index value to match the Columns, in all for sections.

Save after changing each row, if you click off the sections, you may loose work

So at the end you will have:

First Body (5) Section fields = ChangeAmtTxt[1] to ChangeAmtTxt[14]

Second Body (6) Section fields = BudgetAmtTxt[1] to BudgetAmtTxt[14]

First Group Footer (8) Section fields = ChangeAmtTxt[1] to ChangeAmtTxt[14]

Second Group Footer (9) Section fields = BudgetAmtTxt[1] to BudgetAmtTxt[14]

Rows 3 of the Body and 3 of the Group Footer need to be filled in now expand the row and select copy and and paste in the rows from above, so section body section 3 has a row for Amount and a row for budget.

To do this select body (5) then Ctrl+A this selects all the objects in that section, Ctrl+C to copy, select Body (7) and Ctrl+V to paste then in, do the same with the budget section, and place then in section 7 below the amount fields

Your report sections should now look like this, if not adjust them to match, we will be changing the labels next

Now add some text boxes to the header 2 section

As you can see the SourceExpr value for all the boxes are visible, and I will let you add them from viewing the image above, you do not put in the =< or >, so the first box you just type in ‘For the Fiscal year: ‘+DateFilter[14], save the report

Now we have to give our Customer some options View and Request form

Add the controls as in the image and set the sourceexpr as in the image.

The first check box SourceExp value is set to our flag ShowOnlyIfBudget.

The second check box SourceExp value is set to our flag UseAccPeriods.

Save the report nearly there just a bit more coding for you to do, this is to switch our sections on and off, look at the images for each section and add the code.

Start with Header (2) select the section and view code F9, show only on Page 1

Body 5 only show if which value Amount was selected on the request form

Body 6 only show if which value Budget was selected on the request form

Body 7 only show if which value Both was selected on the request form

Group Footer 8, shows if Amount was selected, in the group footer we need to load the group values into the ChangeAmtTxt variables, only if they have a value

Group Footer 9, shows only if the budget was selected, in the group footer we need to load the group values into the BudgetAmtTxt variables, only if they have a value

Group Footer 10, shows only if the both was selected, in the group footer we need to load the group values into the ChangeAmtTxt and the BudgetAmtTxt variables, only if they have a value

All you have to do now is run the report.

DOCUMENT COPYRIGHT

The authors that contributed to this Adeptris.com tutorial are delighted to share their work with others.
Please recognize the hard work and integrity of our contributing authors by giving them appropriate credit and citing references on any material that you share with others.
Permission is granted to reproduce this material in whole or in part for educational purposes only (not-for-profit beyond cost of reproduction) provided that the authors receive acknowledgement and this notice is included:

Any additions or changes to these materials must be pre-approved by the author.
NOTE: On most Adeptris.com documents Author Access information is placed at the bottom of the document