ERP Software Logo1

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

The Resource Group

Month, Quarter, and Year Selection for SmartLists in Microsoft Dynamics GP


Email | Print

It’s no surprise that many reports are prepared on a quarterly basis. Within Microsoft Dynamics GP’s SmartList tool, this is usually performed by running the selected SmartList and using the “Search” options to narrow down on a certain date field with a range. Lately, I have been working on a lot of customized SQL Reporting Service reports where users did not want a date range selection, but instead wanted a specific “Quarter Number” and “Year” selection. I wanted to start utilizing this same functionality in my custom SmartList Builder objects and here is what can be done for easy “Quarter”, “Year”, and “Month” selections.

 

In your SmartList Builder object, create a calculated field “Quarter” and use the calculation formula:

 

CASE

                WHEN MONTH({RM Open File:Document Date})  IN('1', '2', '3') THEN '1'

                WHEN MONTH({RM Open File:Document Date})  IN('4', '5', '6') THEN '2'

                WHEN MONTH({RM Open File:Document Date})  IN('7', '8', '9') THEN '3'

                WHEN MONTH({RM Open File:Document Date})  IN('10', '11', '12') THEN '4'

END

 

Next, create another calculate field “Year” and use the calculation formula:

 

YEAR({RM Open File:Document Date})

 

If monthly reporting is desired instead of - or in addition to - quarterly reporting, the calculation below can be used to create a “Month” field:

 

MONTH({RM Open File:Document Date} )

 

I happen to be working with transaction in the RM Open File (RM20101), but remember to change this field for the actual date field you will want to analyze or use as a filter.

 

Now in SmartList, we can easily filter by our custom fields “Quarter” and “Year”:

 ERPSWB - photo - april 29

*Note that the above formula’s work for a calendar year scenario.  Adjustments will need to be made for fiscal periods and other period scenarios outside of standard calendar.

 

Learn more helpful tips by contacting a Microsoft Dynamics GP partner like The Resource Group (www.resgroup.com/training).

One Response to “Month, Quarter, and Year Selection for SmartLists in Microsoft Dynamics GP”

  1. My VP of sales is wanting me to run a report so that he can see the total amount his customers purchased last year 1st quarter as opposed to this year 1st quarter. I can not figure out a way to do anything other than Total Sales YTD, Total Sales LTD, and Total Sales LYR.