Introduction

Most of us might have faced this challenge of displaying data in terms of fiscal year than calendar year. Now here is a simple solution to show the data by fiscal year using SSRS 2005.

Sample table setup

Let’s begin by creating a sample table and inserting few rows to it. Although the table UserRegistration does not have all the required field but is good enough to display some meaningful data by fiscal year.

--Create UserRegistration table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserRegistration](
      [UserId] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [varchar](50) NOT NULL,
      [Middlename] [varchar](1) NULL,
      [LastName] [varchar](50) NULL,
      [Email] [varchar](50) NULL,
      [UPassword] [varchar](10) NULL,
      [Registrationdate] [datetime] NULL,
      [Status] [char](1) NULL,
 CONSTRAINT [PK_UserRegistration] PRIMARY KEY CLUSTERED
(
      [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--Populate Userregistration table
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','10/01/2010','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'11/20/2010','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','12/01/2010','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'01/02/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','02/01/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'03/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','04/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'05/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Mike',NULL,'Wallace','mike@wallace.com',NULL,'06/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Tim',NULL,'Robinson','tim@rob.com',NULL,'07/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Jim',NULL,'Hays','jim@hays.com',NULL,'08/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Ed',NULL,'Fish','ed@fish.com',NULL,'09/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','10/01/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'11/20/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','12/01/2011','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'01/02/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','02/01/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'03/20/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Sam',NULL,'Thomas','sam@sam.com','','04/20/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Robert',NULL,'Chen','rob@chen.com',NULL,'05/20/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Mike',NULL,'Wallace','mike@wallace.com',NULL,'05/21/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Tim',NULL,'Robinson','tim@rob.com',NULL,'05/24/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Jim',NULL,'Hays','jim@hays.com',NULL,'05/26/2012','A')
INSERT INTO [userregistration] ([FirstName],[Middlename],[LastName],[Email],[UPassword],[Registrationdate],[Status])VALUES('Ed',NULL,'Fish','ed@fish.com',NULL,'05/27/2011','A')
GO

The fun part

Now let’s work on the query to fetch the data from the table. My requirement is to find the user count per month. Let’s go step by step and first create a query to pull the data by calendar year. This could be similar to any of your existing query which displays data in calendar year and you want it to show the data in fiscal year.

select COUNT(Userid) as [User Count], DateName(Month,Registrationdate) as mnth, YEAR(Registrationdate) as Yr
    from UserRegistration
       group by DateName(MONTH,Registrationdate), YEAR(Registrationdate), Month(Registrationdate)
       order by Month(Registrationdate), YEAR(Registrationdate)

The above query will provide the user count per month in multiple calendar year sorted by calendar month. Now our task is to display the same data in fiscal month which usually starts from October and ends in September of next year. To achieve this I added two more fields to the above query which can give the fiscal year and fiscal month as seen below.

select COUNT(Userid) as [User Count], DateName(Month,Registrationdate) as mnth, YEAR(Registrationdate) as Yr,
    Case when MONTH(Registrationdate) = '10' then 'FY ' + cast((YEAR(Registrationdate) + 1) AS varchar(4))
     when MONTH(Registrationdate) = '11' then 'FY ' + cast((YEAR(Registrationdate) + 1) AS varchar(4))
     when MONTH(Registrationdate) = '12' then 'FY ' + cast((YEAR(Registrationdate) + 1) AS varchar(4))
     when MONTH(Registrationdate) = '01' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '02' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '03' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '04' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '05' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '06' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '07' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '08' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
     when MONTH(Registrationdate) = '09' then 'FY ' + cast(YEAR(Registrationdate) AS varchar(4))
    end as FY,
    Case when MONTH(Registrationdate) = '10' then cast ('1'  AS int)
             when MONTH(Registrationdate) = '11' then cast ('2'  AS int)
             when MONTH(Registrationdate) = '12' then  cast ('3'  AS int)
             when MONTH(Registrationdate) = '01' then cast ('4'  AS int)
             when MONTH(Registrationdate) = '02' then cast ('5'  AS int)
             when MONTH(Registrationdate) = '03' then  cast ('6'  AS int)
             when MONTH(Registrationdate) = '04' then cast ('7'  AS int)
             when MONTH(Registrationdate) = '05' then  cast ('8'  AS int)
             when MONTH(Registrationdate) = '06' then  cast ('9'  AS int)
             when MONTH(Registrationdate) = '07' then  cast ('10'  AS int)
             when MONTH(Registrationdate) = '08' then  cast ('11'  AS int)
             when MONTH(Registrationdate) = '09' then  cast ('12'  AS int)
    end as FYMonth
        from UserRegistration
        group by DateName(MONTH,Registrationdate), YEAR(Registrationdate), Month(Registrationdate)
        order by FYMonth, YEAR(Registrationdate)

In the above query I started with the Oct month and added a year to the existing date to set it as next fiscal year in the FY column. And for fetching the fiscal month I just used the case statement to set the fiscal month.

Ready for report

Although I am not covering the full steps to create a report but will focus on the key points to display the report with FY information. While creating the report use the above query as the query based on which the report will be generated. You can use the report wizard to create the report. Once done then you can either remove the fields and just add chart from the toolbox to the designer. For my purpose I am using a line chart as shown below in Fig 1.

Fig 1 - Chart selection

On the Data tab I selected the dataset and on the values box I chose the value as Sum(Fields!User_Count.Value) as shown in the screenshot below in Fig 2.

Fig 2 - Value setting

Now on the Category groups choose the Fields!mnth.value to display the month name as shown below in Fig 3.

Fig 3 - Category group setting

On the series group choose Fields!FY.value to display the data in fiscal year as shown in Fig 4.

Fig 4 - Series group setting

Now you are all set and should be seeing the report as it shows below with user count for each month under each fiscal year.

Hope this will help you in displaying the chart based on fiscal year.