Author |
Message |
dbeethekidd121
Newbie
Joined: 09 Oct 2013
Online Status: Offline
Posts: 3
|
Topic: Previous Months Data Posted: 22 Oct 2013 at 4:10am |
Hi Everyone
I'm trying to integrate a monthly Crystal Report with another systems workflow to send the report on the 8th of every month for the previous month of that year
The workflow I can do but can someone help me with the formula to make sure that on the 8th of November it will show all the data from the 1st to the 31st of October
Much appreciated
Dbee
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Oct 2013 at 5:02am |
local datetimevar star := date(year(today), 1, month(today)); //start of this month
local datetimevar dne := star; //never know if end is a reserved word
star := dateadd("m",-1,star); //start of prior month
dne := dateadd("d",-1, dne); //end of last month
this will get the date of prior month.
HTH
|
IP Logged |
|
dbeethekidd121
Newbie
Joined: 09 Oct 2013
Online Status: Offline
Posts: 3
|
Posted: 22 Oct 2013 at 5:13am |
Hi Lockwelle
Much appreciation of the quick response.
I did forget to mention that I need it to pull on data raised during that previous month from one of the tables in my database
The field is TA_DATE which is a date field of when the record was created within that month, so what I need is for it to look back at the previous month at all records that TA_DATE falls in that previous month
Ideally, if this could be done in the select expert that would be superb. If not, any solution with the same result is fine
Hope that makes sense
Thanks again
Dbee
Edited by dbeethekidd121 - 22 Oct 2013 at 5:23am
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Oct 2013 at 5:35am |
in all honesty, I have never use the select expert.
in Report/Selection Formulas/Record you could use the date selection part and:
{table.TA_DATE} in start to end
this returns a true or false, and if false the data is not included in the report. It might work in the Selection Expert, but again, I have never used it.
I usually use a stored procedure to get my data
HTH
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Oct 2013 at 6:18am |
select would be something like
datepart("d",currentdate)=8 and {table.TA_DATE} in lastfullmonth
|
IP Logged |
|
dbeethekidd121
Newbie
Joined: 09 Oct 2013
Online Status: Offline
Posts: 3
|
Posted: 22 Oct 2013 at 7:11am |
Thank both
Quick question for DBlank
If I drop the ("d", currentdate)=8 will the remainder of the formula still provide me with the desired data from the previous month
My workflow will automatically send this report out on the 8th and doesn't need to be part of the Crystal Report itself.
Thanks
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Oct 2013 at 8:36am |
correct
the first part would be unecessary
|
IP Logged |
|
|