Tuesday, July 24, 2018

SSRS tips


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





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




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)






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")





How to get Month’s name and Year from date



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


No comments:

Post a Comment