Filter for previous month

[davidanthonyb]

Hello,

I am having trouble figuring out how to filter my reports to only show the previous month.

For instance, today is February 22nd. If I run my report it should only show records from Jan 1 - Jan 31. If I run the same report on March 22nd, it should only show Feb 1 - Feb 28, etc.

I know in the databand I can filter between two dates, but is there an easier way? Something like: Today.PreviousMonth?


Thanks,
David


EDIT:
I've created the following three variables:
startOfThisMonth = DateSerial(Today.Year, Today.Month, 1);
firstDay = startOfThisMonth.AddMonths(-1);
lastDay = startOfThisMonth.AddDays(-1);

I then use the Databand filtering to filter the DateTime field using the expression editor for 'between' firstDay and lastDay

When I select preview I receive: "Error - Error #1085"

[Alex K.]

Hello,

Please send us a simple report with test data which reproduces the issue for analysis.

Thank you.

[davidanthonyb]

Hello,

Please send us a simple report with test data which reproduces the issue for analysis.

Thank you.

Thanks for your time, find the attached report.

David

[davidanthonyb]

Hello,

Please send us a simple report with test data which reproduces the issue for analysis.

Thank you.

I don't necessarily have to use the method seen in that report. If there is a better way of filtering to only show records from the previous month then I am definitely open to that.


Thanks again for your time,
David

[HighAley]

Hello, David.

Please, try to change the Field Is to Expression in the Filter and write your own expression for the filtering.

Thank you.

[davidanthonyb]

Hello, David.

Please, try to change the Field Is to Expression in the Filter and write your own expression for the filtering.

Thank you.

Thanks for the reply. So I have removed the above variables and in the databand filter, I am filtering between:

DateSerial(Today.Year, Today.Month, 1).AddMonths(-1)
and
DateSerial(Today.Year, Today.Month, 1).AddDays(-1)


And I am still receiving the same error message. Any ideas?

[HighAley]

Hello.

There should be a C# expression.
It should be like next:

Code: Select all

Table1.InventoryHistoryQuantityChange > DateSerial(Today.Year, Today.Month, 1).AddMonths(-1) && Table1.InventoryHistoryQuantityChange < DateSerial(Today.Year, Today.Month, 1).AddDays(-1)

Thank you.

[davidanthonyb]

Hello.

There should be a C# expression.
It should be like next:

Code: Select all

Table1.InventoryHistoryQuantityChange > DateSerial(Today.Year, Today.Month, 1).AddMonths(-1) && Table1.InventoryHistoryQuantityChange < DateSerial(Today.Year, Today.Month, 1).AddDays(-1)

Thank you.

Hi HighAley,
Thanks again for your response. So I have tried this and I am still receiving 'Error #1085".

Also, I've tried applying that expression to filter the Table1.InventoryHistoryQuantityChange field, but I believe we should be filtering the Table1.InventoryHistoryHistoryDateTime field no? Either way, I tried applying the filtering to both fields, and receive the same error either way

Any ideas?

[davidanthonyb]

Hello.

There should be a C# expression.
It should be like next:

Code: Select all

Table1.InventoryHistoryQuantityChange > DateSerial(Today.Year, Today.Month, 1).AddMonths(-1) && Table1.InventoryHistoryQuantityChange < DateSerial(Today.Year, Today.Month, 1).AddDays(-1)

Thank you.

Hi HighAley,
Thanks again for your response. So I have tried this and I am still receiving 'Error #1085".

Also, I've tried applying that expression to filter the Table1.InventoryHistoryQuantityChange field, but I believe we should be filtering the Table1.InventoryHistoryHistoryDateTime field no? Either way, I tried applying the filtering to both fields, and receive the same error either way

Any ideas?

I've also tried applying the filter in one single expression using the && operator as suggested and this too results in the same error :/

[Alex K.]

Hello,

Please check the modified report in the attachment.

Thank you.