Beginning Date in REport

I want the user to pick the report month and year  when he first runs the report.  Then I will base my quantity and charge formula's on that month and year he chose.  I want one set of quantity and charges to be from the beginning of the month to the end of the month.  and the other set to be from the beginning of the calendar year, to the end of the month he chose.  I am going to need to create these formulas in three different grouping levels.

I created 2 parameters, report month and report year.  

In the record selects  I need to put a formula something like the one below.  I don't think it is quite right, but I don't know where.  CAn someone review this one for me?

 {.POST_DATE} in DateSerial ({?Report Year}, {?Report Month},1)
    to DateSerial ({?Report Year}, {?Report Month}+1,1 - 1)

Then I need quantity and charge formulas to using the report month and year functions also.  I have something but don't think it is correct.

if {DETAIL_TYPE} in [1,10] //charge types
and {POST_DATE} in DateSerial({?Report Year}, {?Report Month},1)
    to DateSerial({?Report Year}, {?Report Month}+1, 1 - 1)
    and month({POST_DATE}) = {?Report Month} //do I need this too?
 then {AMOUNT} else 0  //charge amount

Quantity Formula:
if {DETAIL_TYPE} in [1,10] //charge type
and {POST_DATE} in DateSerial ({?Report Year}, {?Report Month},1)
    to DateSerial ({?Report Year}, {?Report Month}+1,1 - 1)
then 1  //{PROCEDURE_QUANTITY}
else 0
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Since your selection formula filters out all records except the ones for the month you want you can simply use

if {DETAIL_TYPE} in [1,10]  then
    {AMOUNT}
else
    0

Similrly for the other one - if you are trying to count the records or all quntities are 1

if {DETAIL_TYPE} in [1,10]  then
    1
else
    0

If you have a quantity field

if {DETAIL_TYPE} in [1,10]  then
    {Quantity}
else
    0

mlmcc
0
James0628Commented:
mlmcc sort of has a point, and sort of doesn't.  :-)  He's correct in that if the record selection is already filtering the records, you don't have to do it in the other formulas.  But he seems to have forgotten/missed that you also want year to date totals.

 If you were looking for just the month (instead of year to date), your record selection formula seems OK.  I wasn't sure about using 0 for the ending day, but it seems to work (produces the last day of the previous month).  FWIW, I'd use 1 for the day in DateSerial and then subtract 1 from the result, just because it seems easier to follow to me:

 {.POST_DATE} in DateSerial ({?Report Year}, {?Report Month},1)
    to (DateSerial ({?Report Year}, {?Report Month}+1,1) - 1)


 However, since you also want year to date totals, the record selection formula needs to include the whole year.  Just change the starting month to 1 (Jan).

 {.POST_DATE} in DateSerial ({?Report Year}, 1,1)
    to DateSerial ({?Report Year}, {?Report Month}+1,1 - 1)


 Then you'd need to use formulas like the ones that you posted to get the selected month totals, so that they only include that month and not the whole year.  The tests in those formulas look OK.  No, you don't need to do the month({POST_DATE}) = {?Report Month} test too.  The IN test before that will make sure that POST_DATE is in the correct month.

 For the YTD totals, leave out the POST_DATE tests.  The record selection formula is handling that.

 Depending on what other information you have on the report, you may want to use suppression to "hide" the records from earlier in the year (before the selected month).

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
It worked - you guys are awesome!!!!!   I am now able to give them a line by line analysis of MTD YTD volumes that NOONE has built for this practice before.  I still have some cleanup to do on the report headers and parameter fields, but the numbers look great.  

Two final things.  I am asking the user to give me report month when I shouldn't really have to.  How can I build into the report that the report month is the month in the "End Date" parameter?

Also, is there guidance anywhere that would help me with these headers?  It seems like I can never get all of them to display correctly.  If I get the page headers right, then the group headers mess up, or for example,  if I get group three header correct, then then other group headers don't show.  

I am attaching the report, because Report Month is used in so many places I am not sure what consequences there will be by changing ANYTHING.  

I think I know just enough to be dangerous.
Generic-PB-Volume-and-Charges-MT.rpt
0
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Better yet - if this report could run automatically for the most recent month end, we wouldn't have to ask for an end date at all.  OR a report month if the reprot month can be based on the most recent month end.  
0
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
OK - I actually do have all the headers working correctly.  Please disregard that portion of my request.  

I am going to submit this report the way it is and see if it flies.  However, an enhancement would be to fix this report so they cannot change the month it is running for - that they just get the most recent month.  I can submit another question for this part of the request.  THank you!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.