Last month filter - Created date - No default filter available?

[DavidUOP]

Hello,

We use custom bursting and send a report of all approved expenses to our Faculty Finance Managers at the beginning of every month via scheduled reports, using the 'Last Month (Based on Sent for Payment Date)' filter available in the Common Report Header Filters area.

We've just been asked for an alt version of the report that shows the non-approved items based on the original report, so items that don't have an entry in the sent for payment date field and have an approval status of everything except for 'Approved' and 'Approved and in Accounting Review'.  

To do this we are using the 'Created Date' field (Report Dates and Statuses), but this field doesn't have a filter like the SFPD field does, so currently we would have to use a value range and then manually change it.

Is there a custom expression or another way of having the created date just filter on the last month?

[KevinD]

@DavidUOP give this expression a try. You will need to add the expression to the expression editor. Before trying it, make a copy of the report you are trying to change to ensure the original parameters are still in place.

[Created Date] between _add_months(current_date,-1) and current_date

Let me know if this works or not. If it doesn't, try removing the "and current_date" portion of the expression. 

[DavidUOP · Accepted solution]

Hi Kevin,  that worked but showed last month and this month, and removing the 'and current date' caused a fair few errors on validation.

Coincidently I'd raised it with support last night and they came back to me with their own variation of the code which worked, and it isn't too dissimilar to yours:

'[Created Date] between _first_of_month(_add_months(current_date,-1)) and _last_of_month(_add_months(current_date,-1))'

[KevinD]

@DavidUOP Well, I got you 90% of the way there. LOL. Glad support was able to give you the filter.