Home > MS CRM 4.0 > Microsoft CRM Reporting by Fiscal Year

Microsoft CRM Reporting by Fiscal Year


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.

Advertisements
Categories: MS CRM 4.0
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: