Report Filters - Dates prior to Current Month

Question

I want a report that includes dates prior to the current month for a dashboard - So it needs to be automated (ie cant manually enter the date in)
I cant see how this can be achieved.
The best I can do is the Last # of Days (and put in a suitably large # value), but I get the current month up to the current day.
I was hoping grouping filters would allow you to "exclude" a group through "NOT", so I could exclude the current month, however filter groups seem to only support "AND" & "OR"

Am I missing something?
For example is there a value I could put into the date field like "now(month)" (similar to now() or Today commands used in excel/SQL) so I could use the "Before" filter?

Jason Hicks · Answer

Hi Jason, this might not be achievable in the existing Reports module without code customization. 
 
 If you don't want code customization, you can try using Advanced reports module where you can write your own SQL query and schedule it, but it's only a tabular report without charts. 
 
 With code customization, we can do something like this: 
 In File: include/generic/SugarWidgets/SugarWidgetFielddatetime.php 
 There is a function expandDate (around line 66): 
 protected function expandDate ( $date , $end = false ) 
 
 Here, we can write our own code with an ALLOW_LIST for terms like last_month or last_year , and write our implementation: 
 protected function expandDate ( $date , $end = false ) { // Customization to allow specific terms in the before field: if ( strtolower ( trim ( $date )) === 'last_month' ) { return $this -> now () -> get_day_by_index_this_month (0) ; } else if ( strtolower ( trim ( $date )) === 'last_year' ) { $startOfYear = $this -> now () ; $startOfYear -> setDate ( $startOfYear -> year , 1 , 1) -> setTime (0 , 0) ; return $startOfYear ; } 
 If you want to make this upgrade safe, move the entire file SugarWidgetFielddatetime to the custom folder. 
 
 Then, all you have to do is - in the UI, you can do something like:

If you want a more elegant solution, like adding "Before Last Month" in the dropdown itself, it requires non upgrade safe changes to modules/Reports/templates/templates_modules_def_js.php where you can add your own dropdown criteria, and implement a similar function in the SugarWidgetFieldDatetime as well. For example, search for tp_last_n_days in the codebase.