Building query AND using query object in X++
Create and add datasource with range in X++ // Code using X++ to build the query Query query; QueryRun queryRun; QueryBuildDataSource qbds; ProjTable ProjTable; ; query = new Query(); // Add a datasource to the query qbds = query.addDataSource(tableNum(ProjTable)); // Add a range to the newly added datasource. qbds.addRange(fieldNum(ProjTable, ProjId)).value(“00403_1036..00412_1036”); queryRun = new QueryRun(query); while(queryRun.next()) { projTable = queryRun.get(tableNum(ProjTable)); info(projTable.ProjId + “, ” + ProjTable.Name); } Use query object to retrieve AOT query // Code using a query string static void UseAOTQuery(Args _args) { Query query; QueryRun queryRun; QueryBuildDataSource qbds; QueryBuildRange qbr; ProjTable projTable; query = new query(queryStr(ProjTable)); queryRun = new QueryRun(query); while (queryRun.next()) { projTable= queryRun.get(tableNum(ProjTable)); info (strFmt(“%1 – %2”, ProjTable.ProjId, ProjTable.Name)); } }
Conditional IIF Sum in SSRS report at group level
I got a requirement where I want to show sum amount based on differnent conditions. Simple Sum of amount can be shown with following expression; =Sum(Fields!Amount.Value) I tried following expression to show conditional sum on a group level; It did not work 🙁 =Sum(IIF(Fields!name.value = “Standard”, Fields!Amount.value, 0)) I tried following expression after getting idea from this blog and it worked =Sum(VAL(IIF(Fields!name.value = “Standard”, Fields!Amount.value, 0)))
Use ReportItems in SSRS report
Sommetime we require to use values from textboxes in SSRS for some calculations or data hiding and some other manipulations. Textbox or any other object on report design is an item of the report and can be accessed through ReportItems!TextBox.value This can be used as on textbox expression =ReportItems!Amount.Value – ReportItems!Amount1.Value
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*”)’));