Thursday, January 1, 2015

SSRS report expressions - compiled and ongoing

How to get full AX company in SSRS report

=Microsoft.Dynamics.Framework.Reports.DataMethodUtility.GetFullCompanyNameForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value)

How to show alternate row colour in SSRS report

Set following expression to row’s background property
=IIf(RowNumber(Nothing) Mod 2 = 0, "LightGrey", "WhiteSmoke")

How to format a date
=format(Parameters!Dataset1_AsPerDate.Value,"dd/MM/yyyy")

How to show date and time on 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
                  Page 1 of 2

How to show page number on report

=System.String.Format(Labels!@SYS182565, Globals!PageNumber & space(2) & Labels!@sys26401 & space(2) & Globals!TotalPages)


How to get last date of the month

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

How to get Month’s name and Year from date


cstr(MonthName(month(Parameters!Dataset1_AsPerDate.Value))) + " - " + cstr(Year(Parameters!Dataset1_AsPerDate.Value))