cancel
Showing results for 
Search instead for 
Did you mean: 

How to create report which includes records of current month and last 7 days of previous month.

Former Member
0 Kudos

Hi Experts,

I need to create a report which includes records of current month and last 7 days of previous month.

I will get records of current month by this formula :- month({PROBSUMMARYM1.OPEN_TIME})=month(currentdate)

Please tell me how to add the records of last 7 days of previous Month for the same report.

Thanks in Advance.

Accepted Solutions (1)

Accepted Solutions (1)

former_member205840
Active Contributor
0 Kudos

Hi Ajay,

If you have more than a year data in your database then your formula will return wrong results. ie. If your data consist of 2012,2013,2014 data then below formula will return all 8th month data irrespective of year. So, you need to check year also here


month({PROBSUMMARYM1.OPEN_TIME})=month(currentdate)  and

Year({PROBSUMMARYM1.OPEN_TIME})=Year(currentdate)

Now add Abhilash second statement in OR so, your formula should look like :

(month({PROBSUMMARYM1.OPEN_TIME})=month(currentdate)  and

Year({PROBSUMMARYM1.OPEN_TIME})=Year(currentdate))

OR

Date({PROBSUMMARYM1.OPEN_TIME}) IN [DateAdd('d',-7,Maximum(LastFullMonth)), Maximum(LastFullMonth)



-Sastry

abhilash_kumar
Active Contributor
0 Kudos

Good eye, Sastry!

More options:

Date({PROBSUMMARYM1.OPEN_TIME}) IN [Minimum(MonthToDate) TO Date(year(currentdate),month(currentdate)+1,1)-1]

OR

Date({PROBSUMMARYM1.OPEN_TIME}) IN [DateAdd('d',-7,Maximum(LastFullMonth)), Maximum(LastFullMonth)

-Abhilash

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Ajay,

Use this code:

month({PROBSUMMARYM1.OPEN_TIME})=month(currentdate)

OR

Date({PROBSUMMARYM1.OPEN_TIME}) IN [DateAdd('d',-7,Maximum(LastFullMonth)), Maximum(LastFullMonth)

P.S: Do note that this formula won't push the filter to the database. If you want the filter to be pushed back to the database, please use a SQL Expression (if you're reporting against a Table/View) or use db functions in the where clause (if you're using a Command Object or Stored Proc)

-Abhilash

Former Member
0 Kudos

Thanks

Abhilash & Sastry for help.

Formula is working for me