Format Date Data for Accounting-Style “Year_Month#” Columns in Your SSRS Reports

29
Jan

So, you have a requirement to create a report on planned or actual cost data by month, by year, and your report requester would prefer that the column headings be formatted accounting-style, such as 2014_01, 2014_02, 2014_03, etc. No Problem!

Place the following code into your SQL SELECT and GROUP BY statements, sum your cost field, and put the {Year_Month#] field into your SSRS report’s Column Group:

CONVERT(VARCHAR,DATEPART(YEAR, TableA.Date))+’_’+CONVERT(VARCHAR,RIGHT(‘0’ + RTRIM(MONTH(TableA.Date)),2)) AS [Year_Month#]

Be sure to replace “TableA.Date” with your particular field name, insert your planned or actual cost field below the column group, and you’re good to go.

Happy Reporting!

-Todd Meier