Date time custom formats

Sometime we have to deal with custom date formats, scenarios like you want to append any custom date format in the file name. In such case we can use DateTime.ParseExact() method through .NET Interop:

Ax example can be :

System.Globalization.CultureInfo culture
    = System.Globalization.CultureInfo::get_InvariantCulture();

utcDateTime dt = System.DateTime::ParseExact(
                            '10/17/2014 0:00',
                            'MM/dd/yyyy H:mm',
                            culture);

Parameters details about DateTime.ParseExact():

s String

A string that contains a date and time to convert.

format String

A format specifier that defines the required format of s. For more information, see the Remarks section.

provider IFormatProvider

An object that supplies culture-specific format information about s.

Returns

DateTime

An object that is equivalent to the date and time contained in s, as specified by format and provider.

AX 2012 R3 – INSERTING DATA IN TABLE DIRECTLY FROM QUERY

On Ax 2012 R3 one of the new cool features is the ability to insert data from a query directly into a table.

Here is a quick demo of how it works:

I have created a table called TESTQuery2Record. It contains 4 fields: CustGroupId, CustGroupName, PaymTermId and PaymTermDesc. It should be fairly obvious what to expect from these fields.

 static void Test_InsertRecordSet(Args _args)  
 {  
   TESTQuery2Record testTable;  
   Map fieldMapping;  
   Query query;  
   QueryBuildDataSource qbds_custGroup;  
   QueryBuildDataSource qbds_paymTerm;  
   QueryBuildFieldList fldList_custGroup;  
   QueryBuildFieldList fldList_paymTerm;  
   // Empty the target test table  
   // ---------------------------  
   delete_from testTable;  
   // Build the query  
   // ---------------  
   query = new Query();  
   qbds_custGroup = query.addDataSource(tableNum(CustGroup));  
   qbds_paymTerm = qbds_custGroup.addDataSource(tableNum(PaymTerm));  
   qbds_paymTerm.addLink(fieldNum(CustGroup, PaymTermId), fieldNum(PaymTerm, PaymTermId));  
   // Field lists are required  
   // ------------------------  
   fldList_custGroup = qbds_custGroup.fields();  
   fldList_custGroup.addField(fieldNum(CustGroup, CustGroup));  
   fldList_custGroup.addField(fieldNum(CustGroup, Name));  
   fldList_custGroup.dynamic(QueryFieldListDynamic::No);  
   fldList_paymTerm = qbds_paymTerm.fields();  
   fldList_paymTerm.addField(fieldNum(PaymTerm, PaymTermId));  
   fldList_paymTerm.addField(fieldNum(PaymTerm, Description));  
   fldList_paymTerm.dynamic(QueryFieldListDynamic::No);  
   // Specify the mapping between target and source  
   // ---------------------------------------------  
   fieldMapping = new Map(Types::String, Types::Container);  
   fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupId), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, CustGroup)]);  
   fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupName), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, Name)]);  
   fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermId), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, PaymTermId)]);  
   fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermDesc), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, Description)]);  
   // Let AX handle getting data from the query to the target table  
   // -------------------------------------------------------------  
   query::insert_recordset(testTable, fieldMapping, query);  
   // Done!  
   // -----  
 }  

The effect of this is x number of records queried and inserted in 1 round-trip to the SQL server and still based on a query.

Reviewing Advance Rule structure in SQL (Query)

This SQL query is worth to share and can be used if we need to review Advance rule structure outside Dynamics AX

 

 

 ;with CTE_tbl as  
 (  
 select DH.NAME as 'AdvanceRuleStructure', DH.DESCRIPTION  
 ,DHL.LEVEL_  
 ,DA.NAME  
 --,DCNC.RANGETO  
 , DCNC.RANGEFROM from DimensionHierarchyLevel DHL  
 inner join DimensionAttribute DA  
 on DA.RECID = DHL.DIMENSIONATTRIBUTE  
 inner join DIMENSIONHIERARCHY DH  
 on DH.RECID = DHL.DIMENSIONHIERARCHY  
 and DH.STRUCTURETYPE = 1  
 inner join DIMENSIONCONSTRAINTTREE DCT  
 on DCT.DIMENSIONHIERARCHY = DH.RECID  
 inner join DimensionConstraintNode DCN  
 on DCN.DIMENSIONCONSTRAINTTREE = DCT.RECID  
 and DHL.RECID = DCN.DIMENSIONHIERARCHYLEVEL  
 INNER JOIN DimensionConstraintNodeCriteria DCNC  
 on DCNC.DIMENSIONCONSTRAINTNODE = DCN.RECID  
 )  
 select  
   AdvanceRuleStructure, DESCRIPTION  
 , Max(case when LEVEL_=1 then RANGEFROM end )as BU  
 , Max(case when LEVEL_=2 then RANGEFROM end )as CC  
 , Max(case when LEVEL_=3 then RANGEFROM end )as Market  
 , Max(case when LEVEL_=4 then RANGEFROM end )as Region  
 FROM CTE_tbl  
 group By AdvanceRuleStructure,DESCRIPTION  

SQL Query view.

Dynamics Ax 365 SSRS: How to call new Report/Design for existing Print management report

Hi Dax guys,

I was working for a customer who was looking to implement custom designs on existing SSRS print management reports like purchase order confirmation report, for that requirement we created a new report with new design (Basically we copied the existing one and make changes to its design)

Now the problem was how to point print management framework to look for new report that we have designed.

Solution is :

Make a new extension class for example “hsPrintMgmtDelegatesHandler” for PrintMgmtDocType class on method getDefaultReportFormatDelegate and using that returns the custom report design name.

Complete code of extension class is given below.

 class hsPrintMgmtDelegatesHandler  
 {  
   /// <summary>  
   /// Delegate handler for the getDefaultReportFormatDelegate method of the <c>PrintMgmtDocType</c> class.  
   /// </summary>  
   /// <param name = "_docType"><c>PrintMgmtDocumentType</c> enumeration value.</param>  
   /// <param name = "_result">The <c>EventHandlerResult</c> object.</param>  
   [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getDefaultReportFormatDelegate))]  
   public static void getDefaultReportFormatDelegateHandler(PrintMgmtDocumentType _docType, EventHandlerResult _result)  
   {  
     PrintMgmtReportFormatName formatName = hsPrintMgmtDelegatesHandler::getDefaultReportFormat(_docType);  
     if (formatName)  
     {  
       _result.result(formatName);  
     }  
   }  
   /// <summary>  
   /// Gets the report format value.  
   /// </summary>  
   /// <param name = "_docType">The <c>PrintMgmtDocumentType</c> enumeration value.</param>  
   /// <returns>The report format value.</returns>  
   private static PrintMgmtReportFormatName getDefaultReportFormat(PrintMgmtDocumentType _docType)  
   {  
     switch (_docType)  
     {  
       case PrintMgmtDocumentType::PurchaseOrderRequisition:  
         return ssrsReportStr(PurchPurchaseOrderCopy, Report);  
       case PrintMgmtDocumentType::PurchaseOrderConfirmationRequest:  
         return ssrsReportStr(PurchPurchaseOrderCopy, Report);  
     }  
     return '';  
   }  
 }