Query Range Condition - Removing preceeding whitespace

Hi

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!
 

Caching display method

Hi

ON AX 2009 :

Add this line code in the FORM method init
Source : http://ax-dynamics.blogspot.it/2007/08/caching-of-display-methods.html

Public void init()

{

     super();

     this.cacheAddMethod(tablemethodstr(, ), [_updateOnWrite]);  

}

 

ON AX 2012 :

Add this line code in the DISPLAY method before the declaration

Source : http://msdn.microsoft.com/en-us/library/aa596691.aspx

 

[SysClientCacheDataMethodAttribute([_updateOnWrite])]

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!

 

Import Label files ( ALD ) script, using SysLabelFile class

Hi

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",
                                   "en-gb.ald",
                                   "it.ald",
                                   "fr.ald",
                                   "fr-be.ald",
                                   "de.ald",
                                   "es.ald",
                                   "zh-cn.ald" ];
   
    FilenameOpen    file;
    #WINAPI
    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);
                labelFile.fromFile(file);                                   
            }
            else               
                warning(strFmt("File %1 not found", file));        
        }
    }

That's it!

 

AX 2012 SSRS Improve Performance - PART I

Hi

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.

SELECT
e.timestart,
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],
e.*
FROM
[executionlog2] AS e
--WHERE e.[timestart] BETWEEN @basedate AND DATEADD(hh,25,@basedate)
ORDER BY 1 DESC;

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"

Hi

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.

http://msdn.microsoft.com/en-us/library/cc618616(v=ax.50).aspx

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

(dayRange(-50,0))

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!
 

Delete Huge Dynamics AX Log Tables

Hi

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
BEGIN    
 PRINT GETDATE()    
 SET ROWCOUNT 10000    

 BEGIN TRANSACTION    
 DELETE FROM Table where CREATEDDATETIME < '2014-01-01'
      )

 SET @rowcount = @@rowcount     
 COMMIT    
 PRINT GETDATE()    
 IF @rowcount = 0    
 BEGIN        
  SET @continue = 0    
 END
END

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!

List of AX Users security roles

Hi

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

User security role assignment report [AX 2012]

Enjoy!

 

Dynamics AX Open Source Software

Hi

Below some nice software that enrich more Dynamics AX.

Dynamics AX 2012 R2 - Kitting

Dynamics AX Admin

How to create a generic end-user transactional report writer for Microsoft Dynamics AX 2012

Tables tracer tool helps the Ax technical developers as well business developers in identifying the tables involved in a functional flow

Master data synchronization


Enjoy!




 

How to print any file in Dynamics AX

Hi

Below a interesting post about print some files from Dynamics AX.

How to print any file in Dynamics AX

Enjoy!

Moving between Microsoft Dynamics AX 2012 Environments

Hi

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 !

 

SQL Server Scripts

Hi

Below some interesting SQL Scripts :

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

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

Enjoy!







 

Printing Purchase Order to file by code

Hi

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
controller.parmExecutionMode(SysOperationExecutionMode::Synchronous);

// Suppress report dialog
controller.parmShowDialog(false);

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

// Change print settings as needed
settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::File);
settings.fileFormat(SRSReportFileFormat::PDF);
settings.fileName(@'\\UNCPATH\test.pdf');

// Execute the report
controller.startOperation();

Enjoy !
 

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

Printing reports from code in AX2012


 

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

Hi

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

Enjoy!