Friday, February 28, 2014

Query Range Condition - Removing preceeding whitespace


If you have a query and do you want filter a value where there is a initial whitespace, like " XXXX", you have to use QueryValue(..) method.

So, QueryValue(" XXXX");

If you don't use this method, the initial whitespace is being removed and the query range doesn't work.

That's it!

Wednesday, February 26, 2014

Caching display method


ON AX 2009 :

Add this line code in the FORM method init
Source :

Public void init()



), [_updateOnWrite]);  



ON AX 2012 :

Add this line code in the DISPLAY method before the declaration


Display type DisplayMethodname(Parameter)

You can also use FormDataSource.cacheAddMethod(..) that support also Edit Method.

More info on :

Optimizing display methods in AX 2012
Display/Edit method caching

That's it!


Wednesday, February 19, 2014

Import Label files ( ALD ) script, using SysLabelFile class


If you have to import many Label files in different languages into AX 2012, you can use the script below.
If the label file doesn’t exists, it will be created at the current level.
This operation will overwrite any existing label different from file.

    str             path = @"C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\Application\Appl\Standard\";
    container       files = ["axXXX","axYYY"
    container       extensions = [ "en-us.ald",
                                   "zh-cn.ald" ];
    FilenameOpen    file;
    int             i;
    int             j;
    SysLabelFile    labelFile;
    for(i=1; i<=conLen(files); i++)
        for(j=1; j<=conLen(extensions); j++)
            file = path + conPeek(files, i) + conPeek(extensions, j);           
            if( WinAPI::fileExists(file)) // check file exists
                // In AX 2012 there are 2 files for chinese, so we copy(overwrite) the old file in the user desktop with the new name                
                if( conPeek(extensions, j) == "zh-cn.ald")
                    WinAPI::copyFile(file, WinAPI::getFolderPath(#CSIDL_DESKTOP) + @"\" + conPeek(files, i) + "zh-hans.ald", true);                   
                    file = WinAPI::getFolderPath(#CSIDL_DESKTOP) + @"\" + conPeek(files, i) + "zh-hans.ald";
                // create from file
                labelFile = SysLabelFile::newFilename(file);
                warning(strFmt("File %1 not found", file));        

That's it!


Sunday, February 16, 2014

AX 2012 SSRS Improve Performance - PART I


By using the execution log tables in the ReportServer database, an administrator can determine the run times of reports.

The following query provides the total time, data retrieval time, processing time, rendering time, Report Definition Customization Extension (RDCE) snapshot generation time, and more for every time that a report has run.

DATEDIFF(ms, e.[timestart], e.[timeend]) AS [TotalTime],
e.[timedataretrieval] AS [DataRetrievalTime],
e.[timeprocessing] AS [ProcessingTime],
e.[timerendering] AS [RenderingTime],
COALESCE ([AdditionalInfo].value('(/AdditionalInfo/RdceSnapshotGenerationTime//node())[1]', 'int'), 0) AS [RDCESnapshotGenTime],
[executionlog2] AS e
--WHERE e.[timestart] BETWEEN @basedate AND DATEADD(hh,25,@basedate)

The column of interest would be TimeDataRetrieval, TimeProcessing, TimeRendering.
Here is some site where you can analyse your results:

ExecutionLog2 View - Analyzing and Optimizing Reports

Report Server Execution Log and the ExecutionLog3 View

SQL 2008 R2 RTM! Time to look at some new Execution Log Reports

That's it!


AOT Query - Set range using "variables"


If you want to set variable ranges on AOT query, you can use SysQueryRangeUtil class. You can use existing methods there or create your own.

For example, if I have an AOT query with PurchLine as datasource. Then I can add a Range to createdDateTime field with value:


I will only see PurchLine from the past 50 days relative to the session date.

When using SysQueryRangeUtil methods, there should be outer brackets enclosing the method itself.

That's it!

Saturday, February 15, 2014

Delete Huge Dynamics AX Log Tables


In the last days, I analyzed a Customer Database that grew up very fast.

The top tables was :
1- AIFResponse
2- AifMessageLog
3- SalesParmLine
4- BatchJobHistory
5- InventSumLogTTS

For the first two tables, related the AIF Interface log, I have build a script to run on SQL Server side.

Use DatabaseName

DECLARE @continue INT
DECLARE @rowcount INT  
SET @continue = 1
WHILE @continue = 1
 SET ROWCOUNT 10000    

 DELETE FROM Table where CREATEDDATETIME < '2014-01-01'

 SET @rowcount = @@rowcount     
 IF @rowcount = 0    
  SET @continue = 0    

For the SalesParmLine table that stores temporary sales order line entries when a sales order is posted, I run the clean up job on Menu "XXX/Sales and marketing/Area page/Periodic/Sales update History cleanup"

For the BatchJobHistory table, I have open the Batch Job History form and delete all ended Job

About InventSumLogTTS table, the delete function is trickered by the setup of the dynamic masterplan, see classes\ReqCalcScheduleItem\InsertItemSched
So to correct the MRP, you need to go to Master Planning/Setup/Master planning parameters Set the current dynamic master plan to the same as the static plan.

Finally, you must perform the Shrink Database.
So, first of all I have set the Recovery Database to Simple, execute the shrink process and change again the Recovery Database to Full.
I have also Stop the AOS before the Shrink process.

Update 1:

Take a look to the link below, in order to Delete huge volume of data on Big Tables.

Concurrency Week: How to Delete Just Some Rows from a Really Big Table

That's it!

Friday, February 14, 2014

List of AX Users security roles


Below a nice post for have a lists of the AX users and their security roles.

User security role assignment report [AX 2012]



Sunday, February 9, 2014

Moving between Microsoft Dynamics AX 2012 Environments


It is common practice to restore a Microsoft Dynamics AX database from one environment into another environment for testing and development purposes.

More informations on the link below

Moving between Microsoft Dynamics AX 2012 Environments

Enjoy !


Friday, February 7, 2014

SQL Server Scripts


Below some interesting SQL Scripts :

1- sp_who data plus a connection summary and lead blocker details

2- This script will help you to get the culprit along with IP address and User name which helps us to determine the Blocking root cause
Figure out the Blocking issue

3- In this script, we can search a Field and Table in all databases on the server, returning the Field value
Search Table And Field In All Database And Return Field Value

4- This script finds the size of all indexes in a database along with the table and the filegroup on which the index resides
Find the size of all Indexes in a database

5- I wrote this query that helps us find executed queries with most number of execution counts. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text
Find Top 5 executed queries ordered by execution count

6- Top 5 expensive Queries from a Write IO perspective 
Find Top 5 expensive Queries from a Write IO perspective

7- The below written query helps us find the most expensive queries from a read IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text
Find Top 5 expensive Queries from a Read IO perspective

8- Script to be executed every 60 minutes as a job to determine long running jobs
Long Running Jobs Monitor



Tuesday, February 4, 2014

Printing Purchase Order to file by code


Below the code for generate a file ( PDF, Xlsx, etc. ) from a Purchase Order Report.

SrsReportRunController controller = new SrsReportRunController();
PurchPurchaseOrderContract rdpContract = new PurchPurchaseOrderContract();
SRSPrintDestinationSettings settings;

// Define report and report design to use
controller.parmReportName(ssrsReportStr(PurchPurchaseOrder, Report));

// Use execution mode appropriate to your situation

// Suppress report dialog

// Explicitly provide all required parameters
rdpContract.parmRecordId( VendPurchOrderJour::findRecId(5637146077).RecId );

// Change print settings as needed
settings = controller.parmReportContract().parmPrintSettings();

// Execute the report

Enjoy !

How to print a report from X++ ( pdf, xlsx, Email, etc. )

Monday, February 3, 2014

Retrieve the format for numeric and date value according to the Culture


For retrieve the format for numeric and date value according to the Culture you can use the class RetailENInfo methods formatDatetimeData or formatNumericData.

e.g. :

    TransDate       dateLocal;
    dateLocal = 22\08\2014;

    info ( queryValue( RetailENInfo::formatDatetimeData( dateLocal, "EN-US") ) ) ;

    Result : 8/22/2014