Filter Help

[jon]

I'm trying to write 3 reports.

The first will be scheduled to run daily, pulling yesterday's numbers.

The second will be scheduled to run every Monday, pulling last week's numbers.

The third will run on the 1st of each month, pulling the previous month's numbers.

I can't figure out how to write the filters. I have calendar date, calendar day, calendar week and calendar month available. I just don't know how to set it up to pull the previous day/week/month when setting it up to run on a schedule.

???

I guess this is what we get for cramming Report Studio into a one day class... :)

[NaviGator]

I think this logic should be inside the report. The Schedule has nothing to do with this.

you need to create a daily, weekly, monthly reports using the right calculations to aggregate the data the way you want.

After that in the Schedule, you will schedule each report to run whenever you want.

Hope this could help.

[jon]

I understand that part, it's how to create the right calculations that I'm struggling with.  :)

Thanks

[Nuffsaid]

Hi,

You need to set up appropriate filtering to get your result set, then schedule accordingly.

There's many variations you can use. Here's a couple examples to get your creative juices flowing  ;D

FILTERS;

Yesterday

[CalendarDate]=_add_days(current_date,-1)

Schedule today to get yesterday.

Last Week

[CalendarDate] between_add_days(current_date,-7) and _add_days(current_date,-1)

Schedule on the first day of the week to get last week.

Last Month

[CalendarDate] between _first_of_month(_add_days(current_date,-28)) and  _last_of_month(_add_days(current_date,-28))

Schedule on any day of the month (before the 29th) to get last month.



Nuffsaid

[jon]

Thank you! That's exactly what I needed!  :D

[jon]

Ok, one more question, just because I'm not liking the monthly report.

I have available Shipment Calendar Month. Is there a way to filter on it to get last month's data? I tried several things, but none of them work.

My only problem with [CalendarDate] between _first_of_month(_add_days(current_date,-28)) and  _last_of_month(_add_days(current_date,-28)), unless I'm misunderstanding it, is that it's not necessarily pulling the whole month.

What I would like to do is run the report on the first and get the previous month, regardless of how many days there were in it.

Thanks!

[ydeliwala]

You should use _add_months function instead of the _add_days function for the month logic.

[DateColumn] between _first_of_month(_add_months(current_date,-1)) and  _last_of_month(_add_months(current_date,-1))

[jon]

I'll give that a try on Monday. Thanks!

[Nuffsaid]

Hey again,

As I mentioned there's many variations that will work. Yo!'s is one of them....

_add_days(current_date,-28) simply drops you in the previous month, then, from _first_of_month to _last_of_month captures all days within that month.

You can probably do something with your "Shipment Calendar Month" field but that would all depend on the field's structure. Is it just the month, or is it month/year?

I'll leave this as an exercise for the user....   ;)

Nuffsaid.

[jon]

It's just month. I've had to play with all of them to get them to work...I guess we're set up a little different than most :-)

[jon]

You should use _add_months function instead of the _add_days function for the month logic.

[DateColumn] between _first_of_month(_add_months(current_date,-1)) and  _last_of_month(_add_months(current_date,-1))

Yo!, you made it too easy for me! I copied & pasted your filter above in place of the filter I was using, validated it, it came back with no errors & the report popped right up!

Nuffsaid & Yo! thank you so much for your help! I tried getting help internally and didn't even get a response, so I couldn't have done any of this without your help!