Development | ErpCoder

PowerBI finance report grouped by main accounts and cost center

29. May 2018 Leave a comment

A customer recently asked to create a PowerBI report in order to group and compare ledger postings. The report had to meet the following requirements:

Compare two time periods
Group postings by account and type (e.g. Assets)
Postings on a certain cost center need to be shown separately
Grouping of accounts and cost centers are defined by a key user in Excel
e.g. 1001 – 1104 and 5001 – 5002 are “Assets”
Cost center groups are defined by using wildcard style
e.g. 601500 – 61500 and Costcenter ?6?? is Development/Compliance

Report Data from Dynamics AX

First, we loaded the report data from Dynamics AX into PowerBI. The data contained the LedgerJournalTrans joined with the LedgerAccountView. The Dimenions were renamed to Department, CostCenter and CostUnit.

select
T.TRANSDATE, T.AMOUNTCURCREDIT, T.AMOUNTCURDEBIT, T.ACCOUNTNUM, V.ACCOUNTNAME, DIMENSION as DEPARTMENT, DIMENSION2_ as COSTCENTER, DIMENSION3_ as COSTUNIT
from LedgerJournalTrans as T
join LEDGERACCOUNTVIEW as V
on T.ACCOUNTNUM = V.ACCOUNTNUM
where ACCOUNTTYPE <= 2

Report Definition Data

Next we defined and loaded the report definition from Excel. We used to sheets, one for the definition of the two time frames and one for the group definition

Like initially described the Accounts sheet defines the grouping of postings regarding their account number. For example in line 2 and 3 postings on accounts 1101 .. 1104 and 5001 .. 5002 shall be grouped as “Assets”. Postings on accounts 601500 .. 606300 with a cost center where the 2nd character is 6 shall be grouped separately as “Development/Compliance” regardless if they are also part of the group “Expenses”.

Both Excel worksheets were loaded into PowerBI. The Accounts was modifed to replace an empty value in the Costcenter column with the text “%”. This was done to use the Costcenter value in a SQL statement with a Like clause (see section “Calling function from the account definition”).

Query Parameter

We added 3 parameters to the PowerBI called FromAccountNum, ToAccountNum and CostCenter. The default values are the smallest account number for the FromAccountNum, the largest account number for the ToAccountNum and the text % for the CostCenter.

Next we changed the query of LedgerJournalTrans and added the parameter to the query. This can be done used “Edit query” on the data set and opening “Advanced Editor”

The new query text in the advanced editor looked like this

let
Source = Sql.Database(“localhost”, “DynamicsAx”, [Query=”select T.TRANSDATE, T.AMOUNTCURCREDIT,T.AMOUNTCURDEBIT, T.ACCOUNTNUM,V.ACCOUNTNAME,DIMENSION as DEPARTMENT,DIMENSION2_ as COSTCENTER,DIMENSION3_ as COSTUNIT #(lf)from LedgerJournalTrans as T#(lf)join LEDGERACCOUNTVIEW as V on T.ACCOUNTNUM = V.ACCOUNTNUM #(lf)where ACCOUNTTYPE <= 2 AND T.ACCOUNTNUM >= “&FromAccountNum&” AND T.ACCOUNTNUM <= “&ToAccountNum&” AND T.DIMENSION2_ like ‘”&CostCenter&”‘ #(lf)order by T.ACCOUNTNUM,T.TRANSDATE#(lf)”, CreateNavigationProperties=false])
in
Source

With the parameter in the query and the default values set to the parameters the dataset did not change. Next we added a new function to the LedgerJournalTrans. This can be done from the context menu of the query “Create Function”. PowerBI inspects the query statement and creates function parameter for each parameter in the query. In this case FromAccountNum, ToAccountNum and CostCenter.

Calling function from the Account Definitions

In PowerBI a function call can be used like a row wise join. A function can be added by using the used defined function button in the query editor. So we added the function call to the Accounts dataset, i.e. each account definition row fetches all postings from the LedgerJournalTrans with the corresponding accounts and costcenter.

The query parameter are mapped to the fields in the Accounts table.

PowerBI will popup a warning that function calls can have a security impact. In the actual version (Mai 2018) PowerBI was quite annoying with security warnings and required a restart (close&open) to stop asking again and again. Finally, expand the Accounts table and the function call results by clicking on the Arrow Right-Left Button next to “GetPostings”. Per default PowerBI adds the fields from the function call result with the function name prefix e.g. GetPostings.Transdate, GetPostings.AmountCurCredit, etc.

As you can see below the Account definition 1101 – 1104 was expanded with all resulting rows from the LedgerJournalTrans that have an account between 1101 and 1104 and any Costcenter (%)

Calculate Period Amounts

To get the amount values for each of the two periods, defined in the Daterange Excel sheet, we added 4 additional columns to the Accounts. A debit and credit column for period 1 and period 2.

The code looks like this

CreditDate1 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate1]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate1]);Accounts[GetPostings.AMOUNTCURCREDIT];0);0)

DebitDate1 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate1]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate1]);Accounts[GetPostings.AMOUNTCURDEBIT];0);0)

CreditDate2 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate2]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate2]);Accounts[GetPostings.AMOUNTCURCREDIT];0);0)

DebitDate2 = IF(Accounts[GetPostings.TRANSDATE]>=FIRSTDATE(Daterange[FromDate2]);IF(Accounts[GetPostings.TRANSDATE]<=FIRSTDATE(Daterange[ToDate2]);Accounts[GetPostings.AMOUNTCURDEBIT];0);0)

Display results in matrix

Finally we used a matrix to display the values from the Accounts dataset and grouped it by Name and Accounts.

Security considerations

Injecting range values in a query is not the best way to do this. A better way would be to refactor the LedgerJournalTrans query into a stored procedure and provide the FromAccountNum, ToAccountNum and Costcenter as parameter to the SP.

Filed under Dynamics AX, PowerBI Tagged with PowerBI; Finance; Dynamics AX

Print Customer and Item specific labels in Dynamics 365 FO

24. April 2018 Leave a comment

This is an example how to print customer and item specific labels in Dynamics 365 for Finance and Operations. The labels shall be printed directly within the sales order form at the sales line grid.

Configuration Table

Create a new table containing the following five fields. The table will hold the configuration which report and design to use for which combination of customer and item

Field	Purpose
CustAccount	Reference to the Customer
ItemId	Reference to an Item
ReportName	Name of the SSRS report to use
ReportDesign	Name of the report design
IsDefault	NoYes

Create Report Classes

Create a new class called ItemLabelContract, which will be the data contract class for the report. It contains the number of labels to be printed, the SalesId and the LineNum to reference the calling SalesLine.

[DataContractAttribute]
class ItemLabelContract
{
NumberOf numberOf;
SalesId salesId;
LineNum lineNum;

    [DataMemberAttribute]
public NumberOf parmNumberOf(NumberOf _numberOfLabels = numberOf)
{
numberOf = _numberOfLabels;
return numberOf;
}

    [DataMemberAttribute]
public SalesId parmSalesId(SalesId _salesId = SalesId)
{
SalesId = _salesId;
return SalesId;
}

    [DataMemberAttribute]
public LineNum parmLinNum(LineNum _lineNum = LineNum)
{
LineNum = _lineNum;
return LineNum;
}

}

Create a new controller class called ItemLabelContract. This class will be called from the menu item in the sales order form. It takes the SalesLine as parameter and decides which report and design to use.

class ItemLabelController extends SrsReportRunController
{
public static void main(Args _args)
{
ItemLabelSetup setup;
SalesLine salesLine = _args.record();

ItemLabelController ctrl = new ERPItemLabelController();
ctrl.parmArgs(_args);

        select firstonly setup where
setup.CustAccount == SalesLine.salesTable().CustAccount &&
setup.ItemId == SalesLine.ItemId;

if(setup.RecId == 0)
{
select firstonly setup where
setup.CustAccount == SalesLine.salesTable().CustAccount &&
setup.ItemId == “”;
}

        if(setup.RecId == 0)
{
select firstonly setup where setup.ItemId == SalesLine.ItemId &&
setup.CustAccount == “”;
}

        if(setup.RecId == 0)
{
select firstonly setup where setup.IsDefault == NoYes::Yes;
}

if(setup.RecId == 0)
{
error(“No report selected”);
}
else
{
str reportName = strFmt(“%1.%2″,
setup.ReportName,
setup.ReportDesign);

            ctrl.parmReportName(reportName);
ctrl.startOperation();
}

    }

    protected void prePromptModifyContract()
{
ItemLabelContract contract =
this.parmReportContract().parmRdpContract() as ItemLabelContract;

SalesLine salesLine = this.parmArgs().record();

        contract.parmSalesId(SalesLine.SalesId);
contract.parmLinNum(SalesLine.LineNum);
contract.parmNumberOf(SalesLine.SalesQty);

        super();
}

}

Create a temporary (InMemory) table called ItemLabelTmp for the report data set including the following five columns:

Field	Purpose
ItemId	Item Id
ItemName	Name
ItemBarcode	Barcode value
BarcodeString	Encoded barcode string
VendName	Name of the vendor, here “Contoso”

Create the report data provide class. Overwrite the prePromptModify method and populate the contract with the number of labels to print taken from the SalesQty, the SalesId and LineNum.

[SRSReportParameterAttribute(classStr(ItemLabelContract))]
class ItemLabelDP extends SrsReportDataProviderBase
{
ItemLabelTmp        itemLabelTmp;

    [SrsReportDataSetAttribute(‘ItemLabelTmp’)]
public ItemLabelTmp getItemLabelTmp()
{
select * from itemLabelTmp;
return itemLabelTmp;
}

    public void processReport()
{
ItemLabelContract contract = this.parmDataContract()
as ItemLabelContract;
SalesLine salesLine;

        select firstonly SalesLine where
SalesLine.SalesId == contract.parmSalesId() &&
SalesLine.LineNum == contract.parmLinNum();

        BarCodeString bcstring = “”;
InventItemBarcode itemBarcode = InventItemBarcode::findItemId(
SalesLine.ItemId,false,false);

if(ItemBarCode.RecId > 0)
{
Barcode barcode = Barcode::construct(BarcodeSetup::find(
itemBarcode.barcodeSetupId).barcodeType);
barcode.string(true,ItemBarCode.itemBarCode,
BarcodeContentType::Item);
Barcode.encode();
bcstring = Barcode.barcodeStr();
}

        for(int i = 0; i < contract.parmNumberOf(); i++)
{
itemLabelTmp.clear();
itemLabelTmp.ItemId = SalesLine.ItemId;
itemLabelTmp.ItemName = SalesLine.itemName();
itemLabelTmp.ItemBarCode = SalesLine.BarCode;
itemLabelTmp.VendName = “Contoso”;
itemLabelTmp.BarCodeString = bcstring;
itemLabelTmp.insert();
}

    }

}

Create a report with multiple designs

Create a new report and add the report data provider class as source. Create at least two designs. In this example I’ve created two designs, a small and and large label.

Create Menu Items and Forms

Create a new form using a the Simple List pattern to manipulate the configuration table form. Create a new display menu item for the form and add it e.g. to the accounts receivable module.

Create a form extension for the SalesTable form. Create an output menu item form the ItemLabelController class and add it to SalesTable extension e.g. Main > TabPageDetails > DetailsTab > LineView > LineViewTab > LineViewLines > LinesActionPaneStrip > LineOverviewActionTab . Make sure to set the SalesLine as Datasource for the Menu Item.

Test the labels

Create new sales orders and test the different configurations and labels. Here is a Youtube example.

Filed under Development, Dynamics 365 Finance and Operations Tagged with Dynamics 365 FO; Reporting

Setup multiple developer VMs for Version Control with Team Services in Dynamics 365 for Finance and Operations

10. April 2018 Leave a comment

Here is a walkthrough how to connect two Dynamics 365 Finance and Operations developer VMs with VSTS.

Configure Azure AD and setup Visual Studio Team Services

Before you start, you have to add your developers to Azure Active Directory. If you have Azure AD Connect make sure the accounts have been synced to the Cloud. In my case I added two additional users to my Azure AD.

Next logon to your Azure Portal using your Organization Account. Create a new service and search for “Team Services”. You should find Visual Studio Team Services (preview).

Create a new instance of VSTS. The basic version of VSTS for 5 users is free. Make sure to use Team Foundation Server as Version Control system. You may choose any Methodology you like, but II ‘d recommend to go for CMMI compatible one.

After the deployment succeeded, logon to your Visual Studio Team Services account, using the URL https://<ACCOUNTNAME>.visualstudio.com . There you see a new project. Open the project and add your developer users by clicking the (+) icon on the upper left “Members” pane. If everything is configured correctly, you should be able to add your AD users. In my example developer one and developer two.

Configure Developer VMs

If you are using the VHD Images from Microsoft, the first thing you should do is to rename the computer. If you don’t rename the VMs you will get errors when mapping the source control on multiple machines. In my case I rename the VMs to “devbox1” and “devbox2”. No domain is needed. Details here.

Configure first Developer Box

After the VM reboots, open Visual Studio 2015 in Admin mode. Depending on your licensing you may need to provide additional credentials to use your subscription. Don’t get confused if this may be your Microsoft ID (aka. Live ID) while you need your Organization Account to access VSTS. Zwinkerndes Smiley From the menu bar select > Team > Manage Connections. Provide the URL for your VSTS account.

After you have connected to VSTS select the project to work on. Next, from the Team Explorer open the Source Control explorer. Map the root folder to a folder on your developer VM.

Afterwards use the source control explorer to create two new folders. One for Visual Studio Projects and one for metadata. This is where the D365 source code artifacts will be stored. Check in you pending changes. This will sync the folders to VSTS.

Now, in the Source Control Explorer open the dropdown Workspace and edit your workspace. Map the metadata folder to C:\AOSService\PackagesLocalDirectory.

From the menu bar > Dynamics 365 > Model Management > Create a new model. Give it a name and complete the wizard. This will ask you to create a new Dynamics X++ project.

In the solution explorer, right click on the project and add to source control.

Add a new element to the project, for example add a new String Extended Datatype called DMOCustomerName. In the solution explorer, right click the project and select build. After a few seconds you should see the console output “Build completed”. Check in all your pending changes.

Next, from the Team Explorer open the Source Control Explorer. You should see the model structure in the tree view. Right click on the metadata folder and select “Add items to folder”. Navigate to your model folder and there to the Descriptor folder. Add the Model Descriptor XML file. Unfortunately you have to do this manually, otherwise the second developer can sync the Folders and Extended Datatypes etc. but will not see the model in the AOT.

You can also inspect your code in VSTS

Configure second Developer Box

Make sure that the second VM is properly renamed. Open Visual Studio in Admin mode and connect to VSTS. Logon with the account of the second developer. Select the Dynamics 365 project and again in the Source Control Explorer map the metadata folder to C:\AOSService\ PackagesLocalDirectory. Checkout the latest version of the metadata folder.

This will create the model folder in the packages directory.

In Visual Studio open the Dynamics 365 Application Explorer. If the AOT is in classic mode, right click and switch to model view. Scroll down and you we will see the synchronized model and the its software artifacts.

Filed under Admin, Development, Dynamics 365 Finance and Operations, Methodology

Integrate SharePoint Online with Dynamics AX Legacy Application

5. February 2018 Leave a comment

SharePoint Online is a wide spread solution for collaboration. Actual versions of Dynamics AX / 365 provide great integration capabilities and for all other purpose there exists the SharePoint Online Client API. However, legacy applications may not support the required libraries or .NET framework versions to use the SharePoint Online Client API. One way to overcome this issue is to utilize the REST API and communicate via HTTP. Here you can find a step by step guide how to provide an external application access to SharePoint Online via an App: http://www.ktskumar.com/2017/01/access-sharepoint-online-using-postman/

Register an App in SharePoint Online

The first thing you need to do is register an App in SharePoint Online. Open the registration site in a browser: https://YOUR_SITE.sharePoint.com/_layouts/15/appregnew.aspx . In my case I’m using a sub site, therefore my URL would look like this: https://YOUR_SITE.sharepoint.com/sites/development/_layouts/15/appregnew.aspx .Use the form to generate a client ID and a secret code. Provide any title you like. Set the domain to localhost and redirection URL to https://localhost

Set Permissions for the App

Open the following URL in a Browser: https://YOUR_SITE.sharepoint.com/_layouts/15/AppInv.aspx . Use the Client ID to lookup the created App. In the free text form add the required permissions e.g. access a list. You can find the syntax here: https://www.sumitagrawal.io/sharepoint-add-in-permission-xml-cheatsheet/ If you need access to more than one list, you have to repeat this step. In my case I only need access to one list. The XML Permission Code looks like this:

<AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web/list" Right="Read" /> </AppPermissionRequests>

Click the Create button, this will forward you to a form where you can choose which list can be accessed by the app.

SharePoint Online Tenant ID

To find the Tenant ID open the following URL in your Browser https://YOUR_SITE.sharepoint.com/_layouts/15/appprincipals.aspx Since I’m using a sub site my URL looks like this https://YOUR_SITE.sharepoint.com/sites/development/_layouts/15/appprincipals.aspx . There you can find the Tenant ID

Create a Token

To interact with SharePoint Online you need to generate a token.This is done by sending an HTTP POST requiest with client ID, secret, and desired resource to SharePoint Online. Here is the Code in X++ written in Ax 2009.

str clientID = “<YOUR_CLIENT_APP_ID>”;

str secret = “<YOUR_SECRET>”;

str tenant = “<YOUR_TENANT>”;

str ctx = “00000003-0000-0ff1-ce00-000000000000”; //i.e. SharePoint

str sp = “<YOUR_SITE>.sharepoint.com”;

str listName = “<NAME_OF_YOUR_LIST>”; // e.g. Customers

str listUrl = “https://YOUR_SITE.sharepoint.com/_api/web/lists/GetByTitle(‘”+listName+”‘)/items; “

str url = “https://accounts.accesscontrol.windows.net/”

+tenant+”/tokens/OAuth/2″;

System.Net.WebClient cl = new System.Net.WebClient();

System.Net.WebHeaderCollection headers = new System.Net.WebHeaderCollection();

System.Collections.Specialized.NameValueCollection body = new System.Collections.Specialized.NameValueCollection();

System.Byte[] response;

System.Text.Encoding enc = System.Text.Encoding::get_UTF8();

System.String responseText;

str xppResponse;

int indexOf;

int start;

int totalLength;

int length;

str token;

;

headers.Add(“Content-Type”,”application/x-www-form-urlencoded”);

body.Add(“grant_type”,”client_credentials”);

body.Add(“client_id”,clientID+”@”+tenant);

body.Add(“client_secret”,secret);

body.Add(“resource”,ctx+”/”+sp+”@”+tenant);

cl.set_Headers(headers);

response = cl.UploadValues(url,”POST”,body);

responseText = enc.GetString(response);

// cut token out of response text

indexOf = responseText.IndexOf(“access_token”);

start = indexOf + 15;

totalLength = responseText.get_Length();

length = totalLength – start – 2;

xppResponse = responseText;

token = responseText.Substring(start,length);

Query List Elements

The token has a valid timestamp measured in Unix Ticks. So it might be good idea to store the token in a parameter table and only request a new one if it is or will expire soon. However, in this example I go on and query the Customer List.

// .. Code from above

cl = new System.Net.WebClient();

headers = new System.Net.WebHeaderCollection();

headers.Add(“Accept”,”application/atom+xml”);

headers.Add(“Authorization”,”Bearer “+token);

cl.set_Headers(headers);

body.Clear();

url = listUrl;

responseText = cl.DownloadString(listUrl);

xppResponse = responseText;

info(xppResponse);

In this case the response type is an XML. You may parse the XML and only view e.g. the Title Tag or any other fields your are interested in. Here is the Infolog of the complete XML response from the SharePoint Online List.

Filed under Development, Dynamics AX, Office Tagged with SharePoint; Office 365; Dynamics AX; REST

Walkthrough: Configure XML/File Data Export via AIF in AX 2012

31. January 2018 Leave a comment

This is an example how to export the LedgerJournalTable + LedgerJournalTrans records as XML via AIF

Create a Query

Create a new query and name it AxdLedgerJournal.
Place the LedgerJournalTable as main Datasource
Set the Fields Property Dynamic to Yes
Add the LedgerJournalTrans as child data source
Set the Fields property Dynamic of the LedgerJournalTrans data source to Yes
Set the Relation property of the LedgerJournalTrans data source to Yes
Set the Fetch Mode property of the LedgerJournalTrans data source to 1:n
Set the Join Mode property of the LedgerJournalTrans data source to Outer Join

Use the AIF Document Wizard to generate the service classes

In the menu bar go to Tools > Application Integration Framework > Create document service
Provide the AxdLedgerJournal query as source query for the document service
Select the options to generate the Read and Find methods
Select the option to generate the AxBC classes
Let the wizard generate the software artifacts for you

Fine tune the generated code

Go to the private projects and open the newly generated project
Compile the project
If the cache() methods don’t compile delete them
Review the open tasks and provide meaningful labels

Configure the Batch Jobs

In AX go to System Administration > Inquires > Batch Jobs > Batch Jobs
Create a new Batch Job and go to “View Task”
Add four task and choose the following classes
AifGatewayReceiveService
AifGatewaySendService
AifInboundProcessingService
AifOutboundProcessingService
Close the Task window and set the Recurrence of the Batch Job to run e.g. all 5 minutes

Configure the File System Adapter

In AX go to System Administration > Setup > Services and Application Integration Famework > Outbound Ports
Create a new Outbound Port
Select the FileSystemAdapter as Adapter
Provide a target location where to place the files e.g. a share with write permissions for the AOS account
Open the Service Operation Dialog and add the LedgerJournalService.read method
Activate the Port

Add a send method

On the LedgerJournalTable add the following method

server void sendElectronically(XMLDocPurpose _xmlDocPurpose,
                               AifSendMode _aifSendMode = AifSendMode::Async)
{
    AxdSendContext      axdSendContext = AxdSendContext::construct();
    AifEntityKey        aifEntityKey   = AifEntityKey::construct();
    Map                 keyData;
    AifConstraint       aifConstraint = new AifConstraint() ;
    AifConstraintList   aifConstraintList = new AifConstraintList();
    ;
    keyData = SysDictTable::getKeyData(this);

    aifEntityKey.parmTableId(this.TableId);
    aifEntityKey.parmRecId(this.RecId);
    aifEntityKey.parmKeyDataMap(keyData);

    axdSendContext.parmXMLDocPurpose(_xmlDocPurpose);
    axdSendContext.parmSecurity(false);

    aifConstraint.parmType(AifConstraintType::NoConstraint);
    aifConstraint.parmId(this.JournalNum);
    aifConstraintList.addConstraint(aifConstraint);

    AifSendService::submitDefault(
        classNum(LedgerJournalService),
        aifEntityKey,
        aifConstraintList,
        _aifSendMode,
        axdSendContext.pack());
}

Test the export

Create a job and use the following code to test the export

static void JobTestAifExport(Args _args)
{
    LedgerJournalTable journal;
    ;
    journal = ledgerJournalTable::find("00001");
    journal.sendElectronically(XMLDocPurpose::Original);
}

In AX got to System Administration > Periodic > Services and Application Integration > Queue Manager. You will find a record for the LedgerJournal export. The Batch Job will first create an XML Document and in a second step perform the export to the file system.

Filed under AIF, Development, Dynamics AX Tagged with AIF; Filesystem; XML Export

Setup und Configure Office 365 Project Web App

27. December 2017 Leave a comment

Here you can find my video on how to setup Project Web App, link with Project Desktop client and how to setup a SharePoint subwebsite with a synchronized task list

Filed under Admin, Office Tagged with Office 365; Project Web App

Last Day Of Month and Year as View Computed Column in AX 2012

3. October 2017 1 Comment

View Computed Columns in Dynamics Ax 2012 support the use of TSQL functions to calculate additional view columns. Here is an example how to calculate the Last Day of Month and Last Day of Year based on the TransDate at the CustTransView. First, Extend the SysComputedColumnDatePart Base Enum and add two additional values Month and Year. Extend the getDateDiffFormatSelector() method at the SysComputedColumn class.

private static server str
getDateDiffFormatSelector(SysComputedColumnDatePart _dateDiffFormat)
{
    switch(_dateDiffFormat)
    {
        case SysComputedColumnDatePart::Day :
            return ‘d’;
        case SysComputedColumnDatePart::Second :
            return ‘s’;
        case SysComputedColumnDatePart::Month:
            return ‘m’;
        case SysComputedColumnDatePart::Year :
            return ‘yy’;
    }

    return ‘d’;
}

Last Day Of Month

Add a new static method to the view and call it lastDayOfMonth.

private static server str lastDayOfMonth()
{
    DictView    ctView = new DictView(tableNum(‘CustTransView’));
    str sTransDate = ctView.computedColumnString(‘CustTrans’,’TransDate’);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(‘-1’,
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff(
    ‘0’,sTransDate,SysComputedColumnDatePart::Month)+ ‘+1′,’0’,
    SysComputedColumnDatePart::Month),
    SysComputedColumnDatePart::Day),
    ‘DateTime’);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayofMonth() method as ViewMethod

Last Day of Year

Add a new static method to the view and call it lastDayOfYear

private static server str lastDayOfYear()
{
    DictView    ctView = new DictView(tableNum(‘CustTransView’));
    str sTransDate = ctView.computedColumnString(‘CustTrans’,’TransDate’);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff
    (‘0’,sTransDate,SysComputedColumnDatePart::Year) + ‘+1’,
    ‘-1’,
    SysComputedColumnDatePart::Year),
    ‘DateTime’);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayOfYear() method as ViewMethod

The View

Dynamics AX 2012 translates both fields in TSQL Code. The view looks like this

SELECT
ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION, 1010 AS RECID,
CAST(CAST(DATEADD(yy, DATEDIFF(yy, 0, TRANSDATE) + 1, – 1) AS DateTime)
AS DATETIME) AS LASTDAYOFYEAR,
CAST(CAST(DATEADD(d, – 1, DATEADD(m, DATEDIFF(m, 0, TRANSDATE) + 1, 0)) AS DateTime) AS DATETIME) AS LASTDAYOFMONTH
FROM
dbo.CUSTTRANS AS T1
GROUP BY ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION

The result in Dynamics AX looks like this