Modern business era requires involvement of lots of calculations with annual financial statements as well as the resources to outcome values over some predefined periods in year. This is where Fiscal year comes into picture and reporting based on fiscal year periods is one of the best ways to understand the business.
In this post an example used for entire explanation will be a report “Opportunities Created” in which we can list out all the opportunities based on there Created Date. Here we will try to drill down the report based on fiscal periods.
Can I generate such report in standard Sage CRM?
Yes, it is pretty much doable task. We just need to remember our fiscal periods i.e. the Start Date and End Date of each fiscal period in a year. Let’s consider in above example we are using “Created Date” for “Opportunity”. We can put this field in Report Search Criteria and enter the date range of our fiscal period to get the report based on that particular fiscal period of year. We will get all the opportunities created within the provided date range. Well, the main hitch in this functionality is that, though most of the business minds still run on fiscal periods; they don’t bother to remember the fiscal period date ranges.
How can I make it more simplified?
We have designed an out of box functionality in order to achieve this in CRM. The functionality involves below mentioned two modules.
1. Create New Fiscal Years :
i). You just need to enter the fiscal period, Start Date and End Date values for that period in an excel sheet and run an executable to create fiscal periods in CRM.
ii). Above action creates a lookup family in CRM for all the fiscal periods which can be then referenced in any selection field as well as the multiselect fields as per CRM functionality.
iii). Code for each caption values will be formed based on logic in date ranges.
2. Store fiscal period equivalent dates:
This involves following set of actions (consider the example mentioned above).
i). Create the field to store fiscal year equivalent value of date field. We will create a Selection field named “Created Date (FY)” under “Opportunity” and attach the above created Fiscal year lookup family with it to see the options as shown below.
ii). Logic can be written in Table level script or SQL trigger in order to update the value in this field based on actual “Created Date” field value. So if Created Date falls in the date range of fiscal period 06 of year 2009, then the Created Date (FY) will be set to FY09-06.
iii). Now we can place this fiscal period equivalent date on our Report Search Criteria and then we will be able to select single as well as multiple fiscal periods in order to generate the report.
Note: – For the CRM systems that are already being in action over the time, old data updation in order to update the fiscal year date values is also a necessary task. We have also designed an executable which does the same.