Count records in Query Vs Cound Loops in Query
Sometime we do need to know the number of records fetching through X++ query and this can be achieved by SysQuery::CountRecords(QueryRun) function. This function works similar to this X++ logic; ProjTable projTable; select count(RecId) from projTable;info(strFmt(“%1 total records”, projTable.RecId)); // total tecords 1130 Lets see with X++ query; static void CountProjTableRecords(Args _args) { Query query = new Query(); QueryRun queryRun; QueryBuildDataSource qbd; qbd = query.addDataSource(tablenum(ProjTable)); queryRun = new QueryRun(query); info(strfmt(“Total Records in Query %1”, SysQuery::countTotal(queryRun))); // total tecords 1130 } However, this SysQuery::CountRecords(QueryRun) function count only the number of records of the first datasource. Let’s assume if we need to count number of records of a query with more than one datasource, we need to use SysQuery::CountLoops(QueryRun) function. static void CountProjTableRecords(Args _args) { Query query = new Query(); QueryRun queryRun; QueryBuildDataSource qbds, qbds1; qbds = query.addDataSource(tablenum(ProjTable)); qbds1 = query.addDataSource(tablenum(ProjTable)).addDataSource(tablenum(ProjForecastTable)); queryRun = new QueryRun(query); info(strfmt(“Total Records in Query %1”, SysQuery::countLoops(queryRun))); }
Show last day of the month from date in SSRS report
Following expression can be used to show last day of the monthin SSRS report. Format(DateSerial(Year(Parameters!Dataset1_AsPerDate.Value), Month(Parameters!Dataset1_AsPerDate.Value),“1”).AddMonths(1).AddDays(-1),“dd/MM/yyyy”)Input: Parameters!Dataset1_AsPerDate.Value = “02/01/2015”Outout: 31/01/2015
Show AX full company in SSRS
Following expression can be used to show full AX company name from legal entities in SSRS report. =Microsoft.Dynamics.Framework.Reports.DataMethodUtility.GetFullCompanyNameForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value)
Show Print Date & Time and Page Number in SSRS report – Custom formatted
Following expression can be used to show print date and time in SSRS report. =Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value,Parameters!AX_UserContext.Value, System.DateTime.UtcNow, “d”, Parameters!AX_RenderingCulture.Value) & ” at “ & Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, “t”, Parameters!AX_RenderingCulture.Value) OutPut: 01/01/2015 at 8:14 AM Following expression can be used to show page number in SSRS report. =System.String.Format(Labels!@SYS182565, Globals!PageNumber & space(2) & Labels!@sys26401 & space(2) & Globals!TotalPages) OutPut: Page 1 of 2
Format a date in SSRS report
After adding a Date parameter in contract class of SSRS report it starts showing in report’s parameters node with Data type DateTime. There is no option to make it either short date or format a date at parameter level. You can use following expression in report design where this parameter has been used in design, mostly in textbox. =format(Parameters!Dataset1_AsPerDate.Value,“dd/MM/yyyy”) Happy Daxur!ng
The data source is not embedded within a (parent) data source.
This error comes when you try to add new datasource to the top of the query. QueryBuildDataSource qbds; qbds = query.addDataSource(tableNum(newTable)); // This is wrong You should add new table to the Main datasource instead.query.dataSourceTable(tableNum(newTable)).addDataSource(..) // This is correct
AOT and X++ queries and ranges in AX 2012
Let’s say we have a query in AOT QueriesProjTable And now we want to add more datasources into this query’s parent datasource (ProjTable) and some ranges etc. Let’s assume we have a class (SRS report data provider class or a dialog class) which is using above query and during processing in this class we want to add more datasources and so on. I created a SRS report data provider class for this example; Class declaration [ SRSReportQueryAttribute (querystr(MarginAnalysisReportV2Sw)), SRSReportParameterAttribute(classstr(MarginAnalysisReportContractV2Sw)) ] public class MarginAnalysisReportDPV2Sw extends SRSReportDataProviderBase { Query query; TempTable tempTable; RecordInsertList recordInsertListTmpTable; ProjTable projTable; TransDate AsOfDate; } ProcessReport [SysEntryPointAttribute] public void processReport() { // Query variable declaration QueryRun queryRun; // Contract class declaration MarginAnalysisReportContractV2Sw dataContract; // Contract class parameters ProjDateCriteriaSw dateType; // Views declaration ProjTableDateViewSw projTableDateViewSw; // This will retrieve the above ProjTable query // Select * from ProjTable query = this.parmQuery(); dataContract = this.parmDataContract(); dateType = dataContract.parmDateType(); asOfDate = dataContract.parmAsOfDate(); this.addParameterRanges(); queryRun = new QueryRun(query); while(queryRun.next()) { projTable = queryRun.get(tablenum(ProjTable)); this.insertTmpTable(ProjTable); } } addParameterRanges() Method private void addParameterRanges() { QueryBuildDataSource qbds1; QueryBuildRange qbr1; // add ProjTableDate table into query’s parent datasource projtable qbds1= query.dataSourceTable(tableNum(projTable)).addDataSource(tableNum(ProjTableDate)); // defining relation based on ProjId qbds1.addLink(fieldNum(ProjTable, ProjId), fieldNum(ProjTableDate, ProjId)); // defining join mode qbds1.joinMode(JoinMode::ExistsJoin); // adding range on ActualEndDate field of ProjTableDate table // setting value <= asOfDate which is considered as per date qbds1.addRange(fieldNum(ProjTableDate,ActualEndDate)).value(queryRange(dateNull(), asOfDate)); } Resultant X++ Query SELECT * FROM ProjTable(ProjTable) EXISTS JOIN * FROM ProjTableDateViewSw(ProjTableDateViewSw_1) WHERE ProjTable.ProjId = ProjTableDateViewSw.ProjId AND ((ActualEndDate<={ts ‘2015-05-14 00:00:00.000’})) Adding OR and AND in query QueryBuildRange qbr; qbr = query.dataSourceTable(tableNum(Table)).addRange(fieldNum(Table, TransDate)); qbr.value(strFmt(‘((%1 != %2) || (%3 == %4))’, fieldStr(Table, ModelId), queryvalue(“Latest”), fieldStr(Table, TransDate), asOfDate )); qbr.value(strFmt(‘((%1 != %2) && (%3 == %4))’, fieldStr(Table, ModelId), queryvalue(“Latest”), fieldStr(Table, TransDate), asOfDate )); Using enum value in query as a range qbr.value(strFmt(‘(Status == %1)’, any2int(ProjStatus::active))); Check modifiedDate value of the table qbr.value(strFmt(‘(ModifiedDate > ProjTable.ModifiedDate)’)); Using wildcards in query You can use LIKE keyword for wildcards in ranges qbr.value(strFmt(‘(ProjName LIKE “*Builder*”)’));
Use enum values and unbounded table fields in insert_recordset
insert_recordset is one the greatest enhancement in AX 2012 which allows us to insert records from one to another table in single client-server trip. This is an X++ SQL statement and is similar to update_recordset which i explained in my this post. Sometime we may require to insert values into destination table’s column which do not exists in source table’s column. To elaborate it more let’s have an example and then I will explain you how it works; static void testJob(Args _args) { ProjTable projTable, projTableInsert; ProjStatus enumValue = ProjStatus::Created; str 30 strValue = “test”; update_recordSet projTable Setting Name = “test” where projTable.projId == “Foobar”; insert_recordset projTableInsert (Name, ProjId) select strValue, ProjId from projTable where projTable.projId == “Foobar”; // You can set to an enum value, providing that update_recordSet projTable Setting status = ProjStatus::Created where projTable.projId == “Foobar”; insert_recordset projTableInsert (Status, ProjId) //select ProjStatus::Created, ProjId NOT ALLOWED select enumValue, ProjId from projTable where projTable.projId == “Foobar”; } I have two variables StrValue of string type and enumValue of enum (ProjStatus) type. You can set enum value directly into update_recordset but this is NOT ALLOWED in insert_recordset. For this reason I declared a variable enumValue and used later in insert_recordset statement. another complex example for insert_recordset; // I created this method to insert records into customized table (JobProfitDetailsSw) from different tables. private void insertPurchaseOrderTypeProjCostTrans(ProjId _projId) { JobProfitDetailsSW jobProfitDetailsSW; ProjCostTrans projCostTrans; VendInvoiceJour vendInvoiceJour; str 20 jobProfitSourceSw = enum2str(JobProfitSourceSw::BCIInvoice); str 20 jobProfitTypeSw = enum2str(JobProfitTypeSw::Cost); insert_recordset jobProfitDetailsSW (Amount, Description, JobProfitSourceStr, JobProfitTypeStr, ProjectDate, ProjId, CategoryId) select sum(TotalCostAmountCur), Txt, jobProfitSourceSw, jobProfitTypeSw, AsOfDate, _projId, CategoryId from projCostTrans group by Txt, projId, CategoryId where projCostTrans.TransDate <= AsOfDate && projCostTrans.TransactionOrigin == ProjOrigin::PurchaseOrder && projCostTrans.ProjId == _projId join vendInvoiceJour group by Purchid where vendInvoiceJour.LedgerVoucher == projCostTrans.VoucherJournal; }
All about Update_recordset
AX 2012 introduces many feature in regards to the performance enhancement and provides ways to access (insert, delete, update) database with less overhead (less database calls). If we talk about updating record(s) in database, few points come into our mind. One is looping through all records and update them in database (will create total number of records calls to database, and will degrade performance). How about to make only one call to database and update all records in this one call. AX 2012 provides way to use X++ SQL statements to enhance performance. This option is update_recordset which enables you to update multiple rows in a single trip to the server. SQL server statement UPDATE CUSTTABLE SET BLOCKED = 0 WHERE CUSTTABLE.ACCOUNTNUM = ”; X++ SQL Server static void update_recordSetJob(Args _args) { CustTable custTable; // Table buffer declaration update_recordSet custTable setting Blocked = 0 where custTable.AccountNum == “”; } We can also use join statements in update_recordset update_recordSet custTable setting Blocked = 0 Join custTrans where custTable.AccountNum == custTrans.AccountNum && […some other criteria];
Cache display methods in AX 2012
Display methods must be written at the table level However, developers often write display or edit methods to perform some calculations and then bind them on form’s controls to allow user to display or edit values in those bounded controls. Display/Edit methods create excessive client-server round trips and impact on product performance. And if there are display methods added on the form it will make 5 client-server round trips, one for each method. Options 1: CacheAddMethod This issue can be addressed by using the CacheAddMethod on the FormDataSource. This method enables the form to calculate all of the display methods in single round trip to the server rather making individual calls. Let’s assume you have added edit method on ProjTable form to show some calcuated dates. Override init() method under ProjTable datasource and write following code; public void init() { super(); ProjTable_ds.cacheAddMethod(tableMethodStr(ProjTable, projectCompletedDate), false); } Options 2: SysClientCacheDataMethodAttribute AX2012 also allows to use SysClientCacheDataMethodAttribute attribute in the display method declaration. Then, forms should cache such methods automatically.Attribute’s constructor accept one optional parameter (_updateOnWrite), which corresponds to the second parameter of cacheAddMethod().Example: [SysClientCacheDataMethodAttribute(true)] display DirPartyType type() {…} [SysClientCacheDataMethodAttribute] public display EcoResProductTitle title() { return inventTable.product().title(); } Options 3: CacheDataMethod peoperty on Form’s control AX 2012 introduces a new feature called the Declarative Display caching. This allows you to enable caching by setting the form control property CacheDataMethod with either of three values; Yes, No and Auto A lot more on this topic is here https://msdn.microsoft.com/en-us/library/aa596691.aspx Happy Daxure!ing