Problem with Year to Date and Month To Date Totals

[harley1d]

I am having a problem with my year to date and month to date totals. What I am trying to do is calculate the totals for the YTD and MTD. I have set up a date range parameter as well allowing the user to select a beginning date and an ending date.

The problem I have is in my record critera if I use the date range parameter In (BegDate) to (EndDate) the report will only return the data within the date range (as it should), however, The YTD Total is not correct.

When I use the use {@First of the year} to {EndDate} in the record critera it returns the entire year (as it should) but the Monthly totas calculate the yearly totals.

What date range needs to be in my record Critera for it to calculate the yearly total but display only the month set in my date range? Example: a user wants the data to display for the month of April as well as the total for the month of April but also need the YTD total.

Here is a sample of the code I am using. I am using Crystal Reports vs. 10.

Formula for Fist of the Month
cdate(year({?EndDate}), month({?EndDate}),1)

Formula for First of the Year
if year({?EndDate}) = 2005 then date(2005,01,01)
else
date(year({?EndDate}),1,1)

Formula for Month Start Date
date(year({@First of the Month}),month({@First of the Month}),1)

Formula for Count YTD
if {HSP_ACCOUNT.ADM_DATE_TIME} in {@First of the Year} to {?EndDate}
then DistinctCount ({PATIENT.PAT_MRN_ID})

Formula for MTD Total
if {HSP_ACCOUNT.ADM_DATE_TIME} in {@Month Start Date} to {?EndDate}
then DistinctCount ({PATIENT.PAT_MRN_ID})

 

[synapsevampire]

Try posting technical information such as you Crystal version and the database/connectivity used.

You have a few ways to approach this, but you should first understand that you're asking for 2 different things and expecting to get both

One solution might be to use a SQL Expression to return the totals for the YTD, and then return only the MTD data for display purposes:

select Count(distinct PATIENT.PAT_MRN_ID) from PATIENT

This is dependent upon the database used.

Another solution which is more Crystal-centric would be to return all of the YTD data and then suppress the data in the details that isn't in MTD.

Another method is to place a subreport in the beginning to do the YTD, and then pass the values in shared variables to the main report.

-k

 

[kskid]

I have a similar situation that calls for YTD total, the MTD of a user specified date. However in my situation, I did not feel the need to specify a start date since the report is only specifying MTD which will always start on the first of the month.

To get YTD, I used the following in my record selection

{table.date} in dateserial(year({?startdate}),1,1) to {?enddate}

When I click on Database->Show SQL, I get all records from the first of the year to the user specified end date

To display the summary for the various parts of the year, I created the following formulas

For the YTD, I just insert a group and summarize the field I want.

For the MTD of the ?enddate,

//@MTD - Month to date of ?enddate 
if {table.field} in dateserial(year(?enddate)),month({?enddate},1) to {?enddate}

I summarize this formula to get my total MTD

-lw