Getting Count in X++ using Select statement

by alirazazaidi on July 27, 2014

Hi, You can get Count X++ select statement as

 

/// <summary>
/// Get resource poool count.
/// </summary>
/// <returns>
/// resource pool count.
/// </returns>
protected int getResourcePoolCount()
{
select count(Rank) from resourceTable
where resourceTable.UserSession == _userSession
&& resourceTable.ResourceSet == ProjResourceSet::Pool;

return resourceTable.Rank;
}

{ 0 comments }

If analysis services are not deployed on machine, then deployed it and select analysis services from sql server installation wizard.

Insdy

 

After installation, Please verify that sql server analysis services are running in services.

 

 

Analysis services services

 

Run the Dynamics Ax 2012 R3 setup to install the Analysis services component.

 

Setup Dynamics Ax

 

 

Validate the prerequisite and if they failed install the prerequisites

 

successful Vaildation

 

During installation wizard select the service name.

 

7-20-2014 11-10-07 PM

 

 

 

Select the AOS server name and database. if default then set to Dynamics Ax

 

 

 

 

 

7-20-2014 11-10-29 PM

 

 

 

 

Setup complete

 

 

Now open the dynamics ax 2012 Client and form file menu ==> Tools ==> Business Intelligence ==> sql

 

Wizard

 

 

select next

wizard1

 

Select the Deploy option

Deploy

 

deployOptions

Deployment process

status

Deployment process took 1 hour on my machine.

Deployment

Deployment are successfully install on my machine.

image21

{ 0 comments }

During search I found link codeplex for X++ extension, freely available Extensions at CodePlex by  José Antonio Estevan Estevan. This extension helped me to identify

  • Starting and ending of brackets.

-highlightbacket

  • Collapse and un-collapse sections wrapped with {- and }-brackets.

Collpase

  • Highlight the all occurrence of keyword in method similar to  NotePad++

HighlightKeyWords Installation of this extension is also very easy.

  • Download Zip file from following link from codeplex. http://ax2012editorext.codeplex.com/
  • Extract zip file.
  • Close all Dynamics Ax client instances on development machine.
  • Copy all files in extracted folder.
  • Paste all in following location.

X:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\EditorComponents Instal     Inspiration: http://yetanotherdynamicsaxblog.blogspot.com/2013/03/free-editor-extensions-for-ax2012.html

{ 0 comments }

Purpose: Purpose of this post is help to Consume AIF Document service in BizTalk and call its method.

Assumption: that target audience has deep knowledge of BizTalk, mapping, schema and orchestration.

 

First of all you have to create service group that is gateway to your document service. For this post I have to consume VendVendGroupService. I create a new service group  in AOT and drop VendVendGroupService to that node as follow

 

ServiceGroup

 

Right click and deploy the service group.

 

Deploy Service Group

 

 

Wait for generate Increment CIL and Infobox shows deployment of All possible Service group

VendorServiceGroupInfolog

 

Now to System Administration module of dynamics Ax. Click on System Administrationè Setup è Services and Integration frameworkè inbound port.

 

InBound Port

 

 

Now create a visual studio project for BizTalk. There are three sub solution, One for Schema, Map and third for orchestration.

 

In Schema solution. Add the generate Items and then select consume wcf Service

ConsumingAiFservice

 

Now add copy wsdl to generate Schemas.

Select metadata Exchange (MEX) endpoint

 

MetaData

 

 

Click next and generate schema

 

Schema Generation

 

Wsdl

 

 

Schema is generated.

 

VendorGroupSchema

 

Go to Schema project and delete the Vendor Group Service Orchestration.

 

 

Required Services

 

Important Schema which will used in mapping.

 

 

EntityKeyList Schema has following fields which are used to set values in Request response methods.

 

Entity List Schema

 

This key value pair used for search , Field represent table field Name and value is search field.

 

VendorGroupSchemaDetail

 

VendorGroup schema is replicate of VendorGroup table, here we use SenderId is used legal entity where operation will perform.

 

If we see the Service Schema, here all request and response methods are generated.

 

ServiceSchema

 

 

One Schema I create to take input to Interface, and decide which operation is performed I called it for InputSchema.

 

CustomSchema

 

This Schema will use for input to Interface. We will discuss it later when we start mapping one by one.

 

 

Bindings:

As compare to other wcf service, when we consume wcf service, empty binding files are generated, so we have create binding operations manually.

These operations are based on AIF service Object not AIF service Group, where I wasted hours on it.

Please consider the following screenshot for Service operations used in binding files.

ServiceOperations

 

The operations in BtsAction will be generated as follow.

Namespace +/+ExternalName+”/”+method name. All Action are similar. For example

 

http://WIN-IKPOSIU2SGD:8101/DynamicsAx/Services/VendorGroupService/read.

 

Settings for BizTalk Send Receive port is as follow.

 

NetTCPBindings

 

 

Pipeline settings:

Send Pipeline should be XML Transmit.

Receive Pipeline should be XML Receive.

Click on Configure Button and set as following.

 

WCF Settings.

 

Remember the Operation Name in SOAP action header mast be identical to Operation Name in logical port in Orchestration.

1234

 

All action mapping is as follow.

 

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<Operation Name="ReadOP" Action="http://schemas.microsoft.com/dynamics/2008/01/services/VendGroupService/read" />

<Operation Name="CreateOP" Action="http://schemas.microsoft.com/dynamics/2008/01/services/VendGroupService/create" />

<Operation Name="UpdateOP" Action="http://schemas.microsoft.com/dynamics/2008/01/services/VendGroupService/update" />

<Operation Name="DeleteOP" Action="http://schemas.microsoft.com/dynamics/2008/01/services/VendGroupService/delete" />

<Operation Name="FindOP" Action="http://schemas.microsoft.com/dynamics/2008/01/services/VendGroupService/find" />

</BtsActionMapping>

Mappings:

I attached  screenshots for mapping used for calling AIF methods.

 

Read Method:

 

Read method is two-step process, first step map the input to Entity list, and then Entity list  to VendGroupReadRequest

 

 

Input_To_EntityList

 

Entitylist_to_VendGroupRequestRead

 

 

Create Method:

In the case of Create Mapping is as follow.

 

InputSchema_to_VendorGroup

 

VendorGroup_to_CreatVendorGroupRequest

 

Delete Method:

 

EntityList_To_DeleteRequest

 

 

 

Update Method:

Update is little tricky, There is field in Update Request Schema, _DocumentHASH, This Field is hidden and every row has its unique value, without that row is did not updated. According to Microsoft Documentation, First you get /find required record using read or find method and then use response data to Update it. For saving time and for the sake of this tutorial, I just pic the_docuemntHash key form previously read response method and hardcode it in map. This is not generic way, but this solve the temporary and my wants to run the Update AIF document method.

 

FindMessage

 

 

Find method:

Find method is works wonder, if you want to search more than one or all records you can call Find method, where is field with Operation, where you can mentions, value Equal, not equal, greater then equal etc. I want to get all vendor group from Dynamics In response, so I sent its value “not equal”. Response message return all values.

 

FindMessage

 

The code of this tutorial is attached, you can download it from here.


{ 0 comments }

Drill drown reports in Dynamics Ax 2012 R3 are very Easy. For this purpose I have to create a new AOT/Static Query AOT. This query is join between CustTable and SalesTable. I want to create Inner Join so Only those Customer came who have Sales Orders. Consider following Steps to create A new report
Create a new Query with Name “DyWorldCustSales”
Drop or add Data Source with CustTable
6-21-2014 9-02-30 PM

 

Right click on Fields and add following fields from Customer table.

CustomerFileds

 

 

Expand Data Source inside Custtable, Add or create DataSource with SalesTable and fields

 

SalesOrder

 

Right Click on  SalesTables Data Source and set its join properties as follow

DataSetProperties

 

Expand Relationship node of SalesTable_1  Add Following relationship

Relations

 

Set it properties as follow

RelationshipDetail

 

New create a new report In existing or New DataModel Report in Visual Studio project

NewReport Project

 

Add New report with DyCustSalesOrderList

Add Dataset with Name DSCustomer and Point to Query which we create in above steps

 

PointToQuery QueryDataSet

Now drag and drop the dataset on designer to create a designer

And Update rename it to  DyCustSalesOrderList

 

Set DataTable’s Propeties visible to set false

DataTable

Add a List and set its name to CustList

Create two groups there one for CustomerGroup and second for AccountNum

Groups

 

Now drop the fields from dataset which  you want to display on report

Fields

 

Right click on CustList and set Data Navigation Style to DrillDown from properties window

DrillDownProperties

Now right click add report to aot and then deploy to Report Server

 

When you run the report  from meu Item you will find following

CustomerGroupClick

 

Click on Customer group this will open it

reportCustomerLevel

 

Click on Customer account it will drill down report on Detail level

Details

 

{ 0 comments }

Exception has been thrown by the target of invocation in dynamics Ax.
I got this error on my VM. On closely examination, I found that In AOT Data dictionary is not synchronize with Sql Server . I perform following steps to resolve this error
Exception Has been thrown by the traget of an invocation

• Right click on data Dictionary and synchronize the data Dictionary.
• Right click on AOT and compile it, it step take longer time. In the case of any error in any class, fix the issue and compiled.
• Generate full CIL.

{ 0 comments }

In Dynamics AX 2012 Reports can be called from Class, and it is import part of MVC pattern implementation for reports
The report created from following link is called from controller class
http://tech.alirazazaidi.com/exploring-drill-through-reports-in-dynamics-ax-2012-r3/
Create a new class and extends it with “SRSReportRunController” class.
Add New method, and update it with following code

public static void main(Args args)
{
DyWorldDrillThroughReportController _Con= new DyWorldDrillThroughReportController();
    _Con.parmReportName(ssrsReportStr(DynamicCustomerList,DesignCustomerList));
    _Con.parmArgs(args);
    _Con.startOperation();
    
}


When you run the class report will be open.

ReportController

 

Very interestingly if you did not want to show report dialog, and just run the report form default parameter value you have to add following statement in above code

_con.parmShowDialog(false);

{ 0 comments }

Creating basic drill through reports in Dynamics Ax 2012 R3 is really simple. Consider following scenario where we have to show the list of customer and their sales Orders in particular legal Entity.  In this report when User click on customer account, a new report open which contains list of all possible sales Order.

For this Purpose I created two Static/AOT queries. First for Customer, and second for Sales Order.

 

Customer Query is as follow.

  • Create a new AOT query with Name DyWorldCustomerList.
  • In DataSource add CustTable table.
  • In fields dynamic to true.

CustListTable

 

Similarly Create another Query “DyWorldSalesTable” for SalesTable. With Name DyWorldSalesTable

 

SalesTable

Now Open a Visual studio and create a new Dynamics Ax Report Project

 

Report Project

Add a new report with name

  • DynamicCustomerList

 

  • Add New dataset with Name
  • DynamicCustomerList set its properties as follow

CustomerDataSet

  • Select Following fields
    •   AccountNum
    •    CustGroup
    •    Party

 

  • Drag this DSCustomerList data set on Report Designer to create an AutoDesign.
  • Change the AutoDesign1 properties as follow

o   Name to DesignCustomerList

o   LayoutStyle to ReportLayoutStyleTemplate

 ReportDesinger2

 

Now create another report inside same Dynamics Ax Model Project, this report will call from customer Report

SalesOrderReport

Create and DataSet with DSSaleOrder with query  “DyWorldSalesTable”

SalesOrderDataSet

Drag this dataset on Report Designer and update newly Designer as follow.

SalesOrderDesignerProperties

 

 

Now open the parameter Section of report and add new parameter as “CustId” of string type

CustIdParameter

Now expand report designer and expand Table and add filter with following Properties

NewParameter

Set following Properties of filter

o   From  Expression select “=Fields!CustAccount.Value”

o   Name= Customer

o   Operator =Equals

o   Value “=Parameters!CustId.Value”

Properties

 

Save the report.

 

Now Open the first report “DynamicCustomerList”. And expend the reports design, “DSCustomerListTable” and then Data and select AccountNum.

Right click on AccountNum and select “Report Drill Through Action”

AddDrillThrough

 

Now Double click and Select the report designer which will open against the click on field

ActionProperties

Select SalesOrderReport and its Desinger form dialog

ReportSelectionSalesOrderList

It will create, number of parameters, all belongs to  DynamicSalesOrderList Report as follow

DrillThroughParameters

 

Delete all parameters except AX_CompanyName and CustId. If any other parameter remains, you definitely got following error when you call Sales Order report form drill through option.

“The Microsoft Dynamic AX parameter ID should be a RECID. The exception is System.FormatException: Input string was not in a correct format.”

AfterDeleteParameters

 

Please Select following Values for these Properties AX_CompanyName

Select its value “=Parameters!AX_CompanyName.Value

CompanyNameValue

Please select the following value for CustId as “=Field!AccountNum.Value”

SaleIdValue

Now saves the reports and right click on Report Project to Add to AOT and then deploy on Report Server

 

AOT and Deploy

Now Go to AOT, and refresh the Select the Visual Studio Model Project and SSRS reports into Dynamics Ax project so it will easy to edit or locate easily

Menu2

Now create a Menu Item of Display type and set its following Properties

Menu

Now run the report as follow

 

CusttomerList

As click on first Customer “CNMF-000001” To its salesOrderList , The second report successfully open as follow

SaleOrderList

{ 0 comments }

Today while deploying Dynamics AX 2012 R3 SSRS reports on window 7 I got following when I run Report deployment command on publish-AXReport -ReportName *.

“A call to the Microsoft Dynamics AX SRSFramework Service service failed. An existing connection was forcibly closed by the remote host.“.

Errors

I perform following steps to solve this problem.
1. Stop reporting services,
2. Stop AOS.
3. Goto: C:\Users\\AppData\Local\

UAC files
4. Take the backup of all AUC files,
5. Remove them from there
6. Start reporting services
7. Start AOS.
8. Again run publish-AXReport -ReportName *. In powershell.

Reports are successfully deploy on my machine.

References:

http://dynamics-ax.blogspot.no/2013/03/ax-2012-fatal-axrdce-exception-error.html

https://community.dynamics.com/ax/f/33/t/115327.aspx

{ 0 comments }

Dynamics Ax 2012 Development Overview session

by alirazazaidi on May 31, 2014

PowerPoint and pictures taken during Session at Lahore MIC on the topic of MS Dynamics Ax 2012

 

 

This slideshow requires JavaScript.

{ 0 comments }