≡ Menu

Time change, Things change but experience always counts. Today I decide to write a post to build very simple custom Report Data provider based report. It is similar report I  developed back in May or April 2012.

My current development environment is Dynamics 365 for operations Update 2 vm on my personal Laptop vm.

Prerequisite of this post  is understanding the AOT in Visual studio and basic programming language like C#.

 

In very simple basic level RDP report three objects are required.

Data contract,  Data Contract class  ties with extended data types. This class contain properties with getter setters. At run time these properties act as report parameters.

Data Provider Class. This class act as container for report logic.  Here we get Report Query or parameters of Data contract class and perform queries on required tables. This class is extended with framework class.

Temperory table

Temperory table is used as bridge for data between report designer (DataSet) and Report logic. We populate temporary table with required logic in data provider class.

 

Report Requirement:

 

Client wants a report where End user select Customer and Report shows, Customer Name, Sale order Number, Item Id, Item group. Sales order Quantity, expected delivery date of Sale order and Unit Sales price. The report must be print in A4 paper size.

Mapping:

 

So our first step is to create mapping. This mapping contain following things.

  • Expected field Name
  • Description / explaination / expected values
  • Orignal table field, which we read and map to temporary field.
  • Extended data type. You can get it from original table field. This is optional, but good in the case of create links and length of fields in table.

 

So build following table with our requirement and generates mapping.

Report Column Description Dynamics 365 for operation Mapping Extended data Type /  Base Enum
Order Number Sales Order Number SalesLine.SalesId SalesIdBase
Item Number Item Number SalesLine.ItemId ItemIdSmall
Item group Item Group InventItemGroupItem.ItemGroupId ItemGroupId
Sales Price Sales Price SalesLine.UnitPrice SalesPrice
Open Qty Order Line Quantity SalesLine.QtyOrdered InventQty
CustAccount Cust Account Salesline.CustAccount CustAccount
CustName CustName CustTable.Name() Name
Date Requested Ship date requested SalesLine.ShippingDateRequested SalesShippingDateRequested
Order Status Sales Order Line Status SalesLine.SalesStatus SalesStatus

 

 

Now create temp table in Visual studio

 

 

 

 

Now double click on table in solution explorer and open it designer mode in center of screen.

Update its Table Type property to InMemory.

Now create each field and update its extended data type as we created in previous step.

 

 

Now next step is report parameters. For this purpose we use Data contract class. In current report we required only one parameter base on Extended Data Type CustAccount. At simplest our data contract class will be look like as follow.

 

[

DataContractAttribute

]

public class AlSaleOrderDC

{

CustAccount custAccount;

 

[

DataMemberAttribute('CustomerAccount')

]

public CustAccount ParmCustAccount(CustAccount _custAccount=custAccount)

{

custAccount = _custAccount;

return custAccount;

 

}

 

}

 

Now we required to write some logic which you above mention class as its data contract and populate the custom temp table we build in pervious step.

[

SRSReportParameterAttribute(classStr(AlSaleOrderDC))

]

public class AlSaleOrderPD extends SRSReportDataProviderBase

{

AlSaleOrderDC dc;

ALSalesOrderTemp salesOrderTemp;

 

 

 

 

[

SRSReportDataSetAttribute('tempSalesOrderDS')

]

public ALSalesOrderTemp gettempSalesOrderDS()

{

select * from salesOrderTemp;

return salesOrderTemp;

}

 

/// </summary>

public void processReport()

{

CustAccount _CustAccount;

SalesLine _SalesLine;

dc =this.parmDataContract();

 

_CustAccount = dc.ParmCustAccount();

 

while select * from _SalesLine where _SalesLine.CustAccount == _CustAccount

{

salesOrderTemp.SalesId = _SalesLine.SalesId;

salesOrderTemp.CustAccount = _SalesLine.CustAccount;

salesOrderTemp.CustName = CustTable::find(   _SalesLine.CustAccount).name();

salesOrderTemp.SalesPrice = _SalesLine.PriceUnit;

salesOrderTemp.ItemId = _SalesLine.ItemId;

salesOrderTemp.ItemGroupId = InventItemGroupItem::findByItemIdLegalEntity( _SalesLine.ItemId).ItemGroupId;

salesOrderTemp.OpenQty = _SalesLine.QtyOrdered;

salesOrderTemp.DateRequested = _SalesLine.ShippingDateRequested;

salesOrderTemp.SalesStatus = _SalesLine.SalesStatus;

salesOrderTemp.insert();

 

}

 

 

}

 

}

At this step build the solution, never forget to check the project Sychronized database on build set to true.

Now Add new report in project

Double click on Report in solution and open in designer screen. And right click on datasets and create new Data Set.

From new data set update the following properties. Data Source Type to Report Data provider. Change Name to DSSales Order

Click on Query and from dialog select the data provider class we create some above screen.

 

After Selecting Class, click on next button. From next dialog select field for usage in report.

 

Now right click on designer node of report and click on precision design.

In precision design, we can create report designer by our need.

 

New designer will be added, rename it and double click to open it. from left pane, toolbar to drag to table to add in designer.

 

From Report Data drop dataset fields on table columns. Add new column in table.

When all fields are added in report. Right click outside the report body and click on report property.

 

And select report print layout. Our target is A4 page. so set it from report properties.

 

Now right click on report body and check its with. Its width must be under 6 because at run time 1 inch on both side must be skip.

After that right click on report in solution explorer and click on deploy

After the add new display menu Item update the its following properties

 

Here there is one advancement, In 2012 there is very difficult to debug report. But in Dynamics 365 for operation is its very simple. Just put break point and set report as starting object and run the project.

 

 

Now new browser window you find similar form.

Now Customer appear because by default it opens in “DAT” legal entity. Now update the url as follow.

https://usnconeboxax1aos.cloud.onebox.dynamics.com/?mi=AlSaleOrderMenu&prt=initial&debug=vs%2CconfirmExit&activityid=041d0eaf-7d60-0000-162d-1d04607dd201&cmp=usmf

 

select parameter from customer. click on ok

And my break open hit

So it is very here to debug report.

 

 

So our report logic fine, but one step is pending Which is report should be run from Client with particular module.

So I decide to add this report in sales and marketing module and under sale order report . For this purpose we have to create a new menu extension.

 

New menu extension added in solution explorer.

Expand menu extension in designer and drag display menu in required menu

From property update “Display In Content Area” to true to create show crumb bar.

Now save and compile again the project.

And open client url in Browser

Login and goes in Sales module For identification purpose, I set the label of display menu as “Ali Sale Order”.

Click on it find report parameter dialog

 

So our report is running successfully.

{ 0 comments }

Yesterday I face a problem; some Ledger journal records are stuck into Workflow. In Error message, described about issues in  customization. In the after last deployment code full cil was not generated. So first step is full Cil generation.

For resubmit these pending records, I used the code snippet delivered on Microsoft Forum by Dick wenning. It help me to resolve ledgers records.

https://community.dynamics.com/ax/f/33/t/77311


WorkflowTrackingStatusTable WorkflowTrackingStatusTable;

while select WorkflowTrackingStatusTable where WorkflowTrackingStatusTable.TrackingStatus == WorkflowTrackingStatus::Faulted &&

WorkflowTrackingStatusTable.WorkflowType == WorkflowTrackingStatusWorkflowType::Workflow

{

Workflow::resumeWorkflow(WorkflowTrackingStatusTable.CorrelationId);

}

 

I updated the code snippet based on reci id instead of all faulted records.

 


WorkflowTrackingStatusTable WorkflowTrackingStatusTable;

 

select * from WorkflowTrackingStatusTable where WorkflowTrackingStatusTable.RecId ==5637166344;

    if (WorkflowTrackingStatusTable!=null)

{

Workflow::resumeWorkflow(WorkflowTrackingStatusTable.CorrelationId);

 

}

 

 

 

 

You can get recid from workflow history screen.

But one record remains unrecoverable. For this I explored tables  as follow.

If we explore workflow tables, you find that main player tables are

SysWorkflowTable

WorkflowTrackingStatusTable

WorkflowTrackingTable

 

In workflowTrackingStatusTable ContexttableId feild contains the table id of ledgerjournaltable and contextrecid contains the records recid of ledgerjoural row on workflow is applied.

SysWorkflowTable correlation id is key relation between Sysworkflow.

 

 

In current scenario, when all records are recovered, but one record is not recoverable. The follow these steps, but you can use them but your own risk.

 

  • Get recid from workflowTrackstatusTable from workflow history. Or Use contextrecid of LedgerJournalTable.
  • Sql Query on workflowtrackingSTatusTable and get correlationId.
  • Query on SysworkflowTable workflowcorrelationid and get the reference sysworkflow table and get reference id
  • Query on Select WorkflowTrackingTable workflowTrackingTable.WORKFLOWTRACKINGSTATUSTABLE Which is based on recid of workflowTrackStatusTable.
  • Delete the records based on reference and reset the ledgerJournaltable status to draft.

 

For your reference I used following Select query.

select from WorkflowTrackingStatusTable where CONTEXTRECID =5637239972

select * from WorkflowTrackingTable where WorkflowTrackingTable.WORKFLOWTRACKINGSTATUSTABLE =5637166344

select * from SysWorkflowTable where workflowcorrelationid =’D4AF23DF-9CC1-40F9-B886-DAB1CD6B35DE’

 

update LEDGERJOURNALTABLE set WORKFLOWAPPROVALSTATUS=1 where lEDGERJOURNALTABLE.JOURNALNUM =’xyze’

 

{ 0 comments }

During project build process following operations are performing. All these operations are one by one after each other.

  1. Metadata and X++ code validation.
  2. Best practice checks.
  3. Report RDL Generation
  4. Compilation and creating .Net assembly.
  5. Label and resource file generation.
  6. Database Synchronization.

 

{ 0 comments }

Following are some recommendation.

  • Using Camel casing.  Variable name must be in camel Case for example table buffer for CustTable must be similar custTable.
  • Avoid keyword. Avoid system keywords.
  • Company name of  Prefix with company name or project,  For example if my company name with Al then all custom table, Custom form, Extended data Type, enum must be start with al suppose.  AlFileInstallmentSchedule, AlCustTable. table.
  • Maintaining consistency, throughout the customization prefix used are consistence, so elements are easily locate.
{ 0 comments }

Recently I completed a customization.  Basic requirement is workflow type have to decide on Certain enum based value at run time.

For dumpy example, I have two customized type for Sales Order. External Sales and Internal Sales.

And Workflow based on same query or table will be different for external sales and internal sales.

For this you have to left empty Workflow in Design Property of form.

 

You have to over write. LoadWorkflowConfiguration

 

For more reference Kindly explore the ledgerjournaltable form.

{ 0 comments }

Recently I upgrade the Dynamics Ax 2012 R3 RTM environment to CU12.

You can find official Microsoft guide for CU12 form following link.

https://mbs.microsoft.com/Files/public/CS/AX2012R3/CumulativeUpdate12InstallationGuideforMicrosoftDynamicsAX2012R3.pdf

Following are my notes during upgrade process.

First step to download patch KB3199741.

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;3199741

This link requires  login to partner source. You have to add email address and then Microsoft send you email where you can download patch.

 

Second step is to download cu12 silpstream.  For this you have to login LCS .

 

If you don’t have any cloud environment No issue Microsoft still let you download Cu12 slipstream.

create a new project if there is no project available in LCS.

I create a project with Name test.

Click on it and let the project open and navigate the screen and then click on Update.

From next screen download Cumulative Update 12 sliptream

Download depends on speed of internet. Size of file is round 931 MB.

 

I copied both patch and slipsteam on required folder and extract both in one folder. First I extract the patch and then extract the slipsteam.

 

 

Similarly

Now I run the AxUpdate

 

Next

Accept and Continue.

 

 

 

But I face the problem that Visual Studio 2013 Dev Tools are not enable. For it solution I found following link which help me.

https://blogs.msdn.microsoft.com/axsupport/2014/11/24/visual-studio-tools-for-microsoft-dynamics-ax-2012-r3-cu8-now-requires-microsoft-visual-studio-2013/

In short, I stop the Installation. And do the following steps.

Run the Dynamics Ax 2012 setup and Uninstall the Visual studio 2012 tools.

Install Visual studio 2013 VS2013_U4 on machine.

Download and install Report Viewer 2012 runtime component. You can found it from following link.

https://www.microsoft.com/en-pk/download/details.aspx?id=35747

 

After all these step I again run the Cu12 setup and Visual 2013 tools are enabled.

Check this box and click on next.

 

After that I restart the AOS service. And open the AX client and select option from update check list and select first option.

Let the complete the database synchronize, Compile and Full CIL.

My environment is successfully Upgrade to CU12. Now next step is code merge or code upgrade That is not part of this post.

If you want new installation  direct to CU12. Then copy content of Dynamics Ax 2012 R3 into hard disk and then extract slipsteam to update folder. And run the Default setup. Hopes this will work.

 

 

 

 

 

 

 

 

{ 0 comments }

On running report, following error message appear in info box.

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://tempuri.org/:queryBuilderArgs. The InnerException message was ‘Element ‘http://tempuri.org/:queryBuilderArgs’ contains data from a type that maps to the name ‘http://schemas.datacontract.org/2004/07/XppClasses:SrsReportProviderQueryBuilderArgs’. The deserializer has no knowledge of any type that maps to this name. Consider using a DataContractResolver if you are using DataContractSerializer or add the type corresponding to ‘SrsReportProviderQueryBuilderArgs’ to the list of known types – for example, by using the KnownTypeAttribute attribute or by adding it to the list of known types passed to the serializer.’. Please see InnerException for more details.

2016-12-06_9-58-01

 

Solution:

  • Stop AOS service.
  • Stop Sql Server Service.
  • Start SQL server Service.
  • Restart SQL Server reporting services.

 

 

{ 0 comments }

After Installation and configuration, When I connect the Entity store and establish DNS error, I got following error.

error-detail

 

error

 

I found that Data Import and export framework did not installed on machine.

solution

 

 

I log this error for my future reference I got its solution from Deepak Agrwal Sahib, He blog this issue on following link

https://community.dynamics.com/ax/b/axaptavsme/archive/2015/02/14/dixf-error-assembly-containing-type-microsoft-dynamics-ax-framework-tools-dmf-serviceproxy-dmfentityproxy-is-not-referenced

{ 0 comments }

I observed that Many developers are using Power BI with Dynamics Ax 2012 R3 by using Direct SQL server connection on Dynamics Ax database. Which is not good approach? This will expose Dynamics Ax 2012 database outside Dynamics Ax 2012 security. Especially when you expose or deploy Power BI on public URL.

With entity Store, Microsoft provide near to real time database access to Power BI.

In this approach, you can extract data to external database by Designing entities and data extraction from Dynamics ax tables and populate that staging entities and then to database. And this process can schedule into Dynamics Ax.

2016-12-03_4-55-24

This approach is faster and more secure in the sense

  • Only required data available for Query, Unnecessary fields and table already removed.
  • No extra overhead on OLTP Dynamics Ax 2012 Database.
  • You can create more indexes on target database to faster fetch. As compare to indexes on Ax tables which creates performance overhead on Dynamics Ax.
  • Instead of Creating complex data mart, cubes and complex queries. We can create simple table structure and build dashboard on them.
  • Certainly external database can share and access on Internet or reporting is much more secure instead expose Dynamics Ax 2012 database for reporting.

 

For reference you can explore following links.

https://blogs.msdn.microsoft.com/intel/archives/185

http://download.microsoft.com/download/2/4/2/242608B8-7F6A-4A59-8AAF-545EBE5166BA/Entity%20store%20Microsoft%20Dynamics%20AX%202012%20R3.pdf

 

 

 

Microsoft release the Entity Store for Dynamics Ax 2012 R3 back in May 2016.

But somehow I decide to explore it. So today I decide to Install Entity store on my local VM. And My local vm had Dynamics Ax 2012 R3 RTM. No other Dynamics Ax 2012 Update Installed on machine. Entity stored is already part of Cu 11 for Dynamics Ax 2012 R3.

 

So First step is download and install KB. You can find It from following link

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;3147499

 

I have to enter my email address and Microsoft send download link for patch.

I recommend to explore the following link for Installation of any update for Dynamics.

https://technet.microsoft.com/en-us/library/hh335183.aspx

 

For this article I just extract the self-extract file.

2016-11-24_20-09-04

Extraction result in folder something like

2016-11-24_20-12-002

This file is again extracted exe. On its extraction it creates two folders

2016-11-24_20-29-005

And again these two folder contains two self-extracted exe files..

On extracting these exes, one update for Model store and second for Kernel update.

Model Store Update:

So First I decide to install update for Model store.

Before that I stopped the AOS service. Otherwise update process takes unnecessary time.

 

So I Click on update Ax exe in Application folder.

2016-11-24_20-14-06

Click on Next

2016-11-24_20-14-031

Accept and Continue

2016-11-24_20-14-50

 

Select you model store and Click on next.

2016-11-24_20-16-07

On this steps you find conflict then first resolve them. In my clean installation there will be no conflict. Press next.

2016-11-24_20-16-30

Next

2016-11-24_20-16-046

Click on Install.

2016-11-24_20-16-59

And finally Update is successful.

2016-11-24_21-28-034

 

Kernel Update.

Now I decide to install what ever exe at Kernel folder.

So I go the extracted folder and run the update ax exe there

2016-11-24_21-29-16

Next

2016-11-24_21-29-49

Accept and Continue
2016-11-24_21-33-13

Next

2016-11-24_21-35-02

Click on Install

2016-11-24_21-35-24

Finally it also installed successfully.

 

 

Post Installation update challenges:

After installation when I start AOS service and open Ax Client. I found check list, with different options for example database synchronization, compilation and full cil Generation.

In my case, this check list did not perform anything.

2016-11-24_21-51-51

So closed the Ax client and in Windows command prompt, I run following command. Remember, run Windows command prompt as run as administrator.

 

AXUTIL set /noinstallmode

Now open Dynamics Ax client and perform following steps.

  • Synchronize the database.
  • Full compile and resolve any error.
  • Generate Full CIL.

 

Interestingly I got errors. And errors are related to Payroll some symmentic dll. Each line shows error 99999.

I used very strange trick. Just add new empty line and add empty comment //. Save it and compile and as result error resolved. I resolved all these error 99999 by same way.

More details I follow my own link.

http://tech.alirazazaidi.com/errors-in-payroll-classes-symmetry-dynamics-ax-2012-r3/

 

 

 

 

After that, I found Entity store menus in Import export framework.

2016-11-26_1-52-35

 

DNS and Entity store connection.

Now open Windows control panel, Administrator tools.

2016-12-03_5-43-01

 

Select ODBC Driver 11 or it depends on installation.

connection

 

 

Enter Name of User dsn.

2016-12-04_9-25-26

Set default database.

2016-12-04_9-26-36

 

 

Now test the connection.

2016-12-04_9-27-24

 

2016-12-04_9-27-43

 

 

 

Now go back to Dynamics Ax client and click on entity store. Select on User DNS. And set User DSN in last step. And click on test.

dns

 

ddd

 

2016-12-04_10-23-44

.

 

 

{ 0 comments }

After upgrade process, Dynamics Ax checklist dialog did not let me do anything. 2016-11-24_21-51-51

 

So closed the Ax client and in Windows command prompt, I run following command. Remember, run Windows command prompt as run as administrator.

 

AXUTIL set /noinstallmode

Now open Dynamics Ax client and perform following steps.

  • Synchronize the database.
  • Full compile and resolve any error.
  • Generate Full CIL.

 

{ 0 comments }