locked
Set financial Year Value dynamically in SSRS Report RRS feed

  • Question

  • Hi All,

    I have a requirement to display the count of project data by their status (On Hold,  In Progress, Pre-concept and Closed) for the current FY and Last FY based on column 'Financial Year' and 'Project Status'. 

    Financial Year column has a value in the following format (FY2011-12, FY2012-13, FY2013-14,FY2014-15, FY2015-16,FY2019-20 ,FY2020-21,FY2021-22)

     

    I need to set the financial year value dynamically in above format for the last and current year (FY2013-14, FY2014-15) based on today's date and  Financial Year is from July to Jun. For example,

     

    • When today's  date is 28-06-2015 then it should set the current FY as FY2014-15 and Last Financial Year as FY2013-14
    • When today's  date is 28-06-2021 then it should set current FY as FY2020-21 and Last Financial Year as FY2019-20

     

    Has anyone come across this requirement? I really appreciate your help.

     

    Thank you.


    Badal Ratra MCTS


    Sunday, June 28, 2015 12:29 AM

Answers

  • In T-SQL, you'd be looking at code like this:

    DECLARE @Date date = getdate()
    --SET @Date = '2015-07-01'
    
    SELECT @Date, CASE WHEN MONTH(@date) < 7 
    		THEN CONCAT('FY', YEAR(@Date) - 1, '-', YEAR(@Date))
    		ELSE CONCAT('FY', YEAR(@Date), '-', YEAR(@Date) + 1)
    		END

    For SSRS, you'd want to use an IIF with MONTH() and YEAR() in the save fashion.

    I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)

    Sunday, June 28, 2015 1:14 PM

All replies