Crystal Reports Enterprise Record Filter Formula Month to Date

[Former Member]

I'm fairly new to Crystal Reports for Enterprise and searched this question but didn't see anyone answering it. When I build a Crystal Report and want to filter by Current Month each time how do I write the code in the Formula Record Filter? I have a current set of data with labor utilization. I'd like to only see this month's numbers per employee each time I run it without using a static filter or prompt.

Thanks in advance for your help.

[former_member292966 · Accepted solution]

Hi Robert,

If your date field is an actual date type, you can use the MonthToDate function like: 

MonthToDate ({table.DateField});

Avoid a formula like: 

Month ({table.DateField}) = Month (CurrentDate);

If you have records from previous years, you'll need to filter out the year as well. 

Good luck,

Brian

[Former Member · Accepted solution]

What if I don't have a DateField? Here's a screenshot of what I have right now. I need to be able to filter these. Thanks.

[DellSC · Accepted solution]

There has to be a date field in one of the tables in your report - even if it's not showing on the report - in order to filter for month to date.

-Dell

[Former Member · Accepted solution]

Does it have to be in the report itself or just available in the data source options on the left?

[DellSC · Accepted solution]

Just available in the options in the Field Explorer.

-Dell

[former_member292966 · Accepted solution]

Ah, so your formula will probably have to look like: 

{Table.Calendar month} = Month (CurrentDate) And

{Table.Calendar year} = Year (CurrentDate);

Brian

[Former Member · Accepted solution]

When I try and format it like Brian mentioned above I get this overload error.

[Former Member · Accepted solution]

So I've gotten a little further. Apparently since the Month Values are coming through as Abbreviations the code for the filter needs that to work. So I have to use "NOV" instead of 11 for the month.

Is there a built in function to easily convert the abbreviation to number or do I just need to hard code all of these abbreviations?

[abhilash_kumar · Accepted solution]

Hi Robert,

There isn't a built-in function unfortunately.

You'll need to hard code them for now.

-Abhilash