prior month in addition to current month

[ginkoba]

I'm trying to pull data based on a date parameter. So when the last date of the month is entered in the parameter field, it pulls all data for that month. In addition to this, I also want the report to pull data for the prior month as well. Can someone assist with this problem?

 

[fisheromacse]

I am not sure i understand what you want, so please bear with me...

1) Do you want to pull the data for the selected month and the prior month only when the selected parameter value is equal to the last day of the month?
OR
2) Do you want to pull data for the selected month and the prior month no matter what day of the month is provided by the selected parameter value?
OR
3) Do you want no data returned unless the selected parameter value is equal to the last day of the month?
OR
4) Am i totally missing it and none of the above are correct?



I think #2) would be the simplest and so that is what i am going to include here as a starting point.

Create two new formulas similar to below and use them in the selection values as start and end dates instead of parameters.
The first example below should display the first day of the month prior to the month selected in the parameter.
The second example below should display the last day of the month selected in the parameter.

//{@SampleFirstofPriorMonth}
dateserial(year({?YourParameter}),Month({?YourParameter})-1,1)

//{@SampleLastofParamMonth}
dateserial(year({?YourParameter}),Month({?YourParameter})+1,1)-1

 

[ginkoba]

Thanks for responding fisheromacse. Sorry for the confusion. let me rephrase this again:

This is the formula I have right now:
{JD_GL_Job_Profit_SP.DocDate} in dateadd('m',-1,{@EndDate}) to {@EndDate}. I'm trying to pull all dates of the prior month. So if the parameter entered is 5/31/13, I want the formula to go back one month for entire month. What is happening right now is that it goes back 1 month from the date entered on the parameter which is incorrect.

 

[pmax9999]

Your clarification isn't all that clear (and whenever you show formula code, you need to include the code for any other formulas referred to within that code). If a date other than the end of a month is entered as the parameter, do you want the "End" Date to be the end of that month or the actual date of the parameter?

Assuming the former, the code suggested by fisheromacse would provide a start date (beginning of month prior to the parameter date) and finish date (end of month represented by the parameter date), which you would then reference in your Record selection formula.

If you wanted to use fisheromacse's approach directly in the Record Selection rather than creating 2 formulas, it would look lke this:

{JD_GL_Job_Profit_SP.DocDate} in dateserial(year({?YourParameter}),Month({?YourParameter})-1,1) to dateserial(year({?YourParameter}),Month({?YourParameter})+1,1)-1

Cheers
Pete

 

[ginkoba]

Pete and all. Thanks for trying to help. This is what I have:

Parameter based on {JD_GL_Job_Profit_SP.DocDate} called {@EndDate}. Only the last date of the month will be entered here. I have a running total formula that looks like this:

{JD_GL_Job_Profit_SP.GroupMask} = 4 and
{JD_GL_Job_Profit_SP.DocDate} in {@StartDate-Month} to {@EndDate}.

{@StartDate-Month} looks like this to calculate the entire month when the last date of any month is entered: date(year({@EndDate}),month({@EndDate}),1)

This is what I want: In addition to the running total that calculates the full month based on the last date of any month entered in {@EndDate}, I also want to build a running total that calculates the last full month of any date that is entered in {@EndDate}.

{JD_GL_Job_Profit_SP.GroupMask} = 4 and
{JD_GL_Job_Profit_SP.DocDate} in last full month of whatever is entered in {@EndDate}. So far no luck. Whenever I use lastfullmonth in combination with this I get an error date time is required.

{JD_GL_Job_Profit_SP.DocDate} in LastFullMonth to {@EndDate ????(errors out)

Hopes this helps.