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