Search code examples
datereporting-servicesfinancialfiscal

Financial Year in SSRS from date

I'm trying to get SSRS to show the financial year an issue date was placed

As an example

A date showing 01/04/2018. The financial year column would return 2018 A date showing 31/03/2018. The financial year column would return 2017

SELECT

IIF(Month(rTranslog.IssueDate)<=3, Year(rTranslog.IssueDate)-1, 
Year(rTranslog.IssueDate)) AS [FY}

,rTranslog.IssueDate
,rTranslog.Site
,rTranslog.NSVCode
,rProduct.LabelDescription
,rTranslog.Ward
,SUM(rTranslog.Qty) AS Quantity
,SUM(rTranslog.Cost)/100 AS CostGBP

FROM

rTranslog
INNER JOIN rProduct
ON rTranslog.NSVCode = rProduct.NSVCode

GROUP BY
rTranslog.IssueDate
,rTranslog.Site
,rTranslog.NSVCode
,rProduct.LabelDescription
,rTranslog.Ward

Any ideas?

Solution

  • You can use an expression in SSRS like the one below to calculate the financial year

    = Iif( Month(Fields!calendardate.Value)<=3, Year(Fields!calendardate.Value)-1, Year(Fields!calendardate.Value))

    For SQL:

    CASE WHEN Month(rTranslog.IssueDate)<=3 THEN YEAR(rTranslog.IssueDate)-1 ELSE YEAR(rTranslog.IssueDate) END