Microsoft CRM Reporting by Fiscal Year

Many companies track their sales by quarter. When you want to report on historical sales or on future pipeline, it can be helpful to view this information by quarter; however, Microsoft CRM does not record the quarter that the sale happened. CRM does include Fiscal year settings, but this is only really used for the CRM quota functionality.

So what do you do if you have a fiscal year that doesn't correspond to calendar year? For example, if your fiscal year runs June – May?

Here is a SQL query that will do the job:

select name, accountidname, estimatedclosedate,case

when datepart(m,estimatedclosedate) >=6 then (datepart(year,estimatedclosedate))+1

Else

datepart(year,estimatedclosedate)

end as 'Fiscal Year',case

when datepart(m,estimatedclosedate) between 6 and 8 then 'Q1'

when datepart(m,estimatedclosedate) between 9 and 11 then 'Q2'

when datepart(m,estimatedclosedate) =12 then 'Q3'

when datepart(m,estimatedclosedate) between 1 and 2 then 'Q3'

when datepart(m,estimatedclosedate) between 3 and 5 then 'Q4'

Else

null

end as 'Fiscal Quarter'

from FilteredOpportunity

This query is a simple pipeline type query that returns the Opportunity Title, Account Name, Fiscal Year, and Fiscal Quarter. If the month is June-December, it puts the opportunity in the next fiscal year.

Comments

[Michael Dodd]

Joel,

Great post. I've worked with similar SQL code myself, and once, when writting a report, I noticed all of the wonderfull SQL Functions that come OOTB for grouping your SQL by DatePart parameters. So I did a little more digging and came up with my own SQL Functions to allow you to group by Quarter:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--Return The Quarter of a given date
CREATE Function dbo.Fn_BPT_SelectQuarter (
@Date DateTime
)
Returns Int
As
BEGIN
Declare @Result Int
IF DatePart(M, @Date) Between 1 AND 3
Set @Result = 1
IF DatePart(M, @Date) Between 4 AND 6
Set @Result = 2
IF DatePart(M, @Date) Between 7 AND 9
Set @Result = 3
IF DatePart(M, @Date) Between 10 AND 12
Set @Result = 4
Return @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Other functions can play on this:

--Return The Quarter of a given date
CREATE Function dbo.Fn_BPT_SelectQuarter (
@Date DateTime
)
Returns Int
As
BEGIN
Declare @Result Int
IF DatePart(M, @Date) Between 1 AND 3
Set @Result = 1
IF DatePart(M, @Date) Between 4 AND 6
Set @Result = 2
IF DatePart(M, @Date) Between 7 AND 9
Set @Result = 3
IF DatePart(M, @Date) Between 10 AND 12
Set @Result = 4
Return @Result
END

AND also:

--Return The Next Fiscal Quarter of a given date
ALTER Function dbo.Fn_BPT_NextQuarter (
@Date DateTime
)
Returns Int
As
BEGIN
Declare @Result Int
IF DatePart(M, @Date) Between 1 AND 3
Set @Result = 2
IF DatePart(M, @Date) Between 4 AND 6
Set @Result = 3
IF DatePart(M, @Date) Between 7 AND 9
Set @Result = 4
IF DatePart(M, @Date) Between 10 AND 12
Set @Result = 1
Return @Result
END