Date-range-dependent formula

[Kmccormic]

Crystal Ver. 2008
Thank you in advance for any help you can lend. I’m trying to get a parameter-dependent formula to work to count the number of entries in a month. Then I need to list year to date information on the same report. The parameter is type Date and allows ranged values. What I have so far doesn’t give me correct results. I’m starting to wonder if I’m going about this the wrong way. The problem formulas are at Group 2 level.

Report setup:
Two Group levels. Group 1 is Compliments/Suggestions/Praise. Group 2 is different subjects such as work groups or locations. At the detail level I have this correctly working formula, one fore each month, named @Jan, etc:
If not isnull ({@Display 1 Topic}) then If {@Month of OpenDate}= 3 then 1 else 0

This formula is for totals for the current date range of the report (i.e. current month), it will only count 1 entry for the month there’s an entry in
If date({TASKS.OPENDATE}) in minimum({?Date Range}) to maximum({?Date Range})
Then Count ({TASKS.WOID}, {@Display 1 Topic}) Else 0
I also tried this. I get zero when there’s no entry that month, but get the cumulative total for all group 1 levels, up to the end date of the parameter:
If date({TASKS.OPENDATE}) in minimum({?Date Range}) to maximum({?Date Range}) then
If {@MonthofDateRange} = 1 then sum({@Jan}) else
If {@MonthofDateRange} = 2 then sum({@Feb}) else
If {@MonthofDateRange} = 3 then sum({@Mar})+sum({@feb})

My YTD formula works if I report for March, if I report for February, although my @feb formula lists 1 in the detail, it lists 0.
If {TASKS.OPENDATE} <= {?Date Range} Then Count ({TASKS.WOID}, {@Display 1 Topic}) Else 0


Example of basic data, Jan - Mar:
Compliments:
Boston 0 0 3
New York 0 1 1
Virginia 0 3 0
Complaints
Boston 0 2 3
New York 0 0 1
Virginia 0 1 0

If I run the report for February I should get:
Compliments – total 4 YTD total 4
Boston – total 0 YTD 0
New York - total 1 YTD 1
Virginia - total 3 YTD 3
Complaints – total 3 YTD total 3
Boston -total 2 YTD 0
New York - total 0 YTD 0
Virginia - total 1 YTD 1


Regards,
Kara

 

[lbass]

What you need to understand is that your formulas are not limiting the content of the summary by the condition. They are just saying: if the last record meets the condition, then show me the summary for the group. If you want formula to return a summary that meets the condition, then you need to set them up like this:

if month({table.date}) = 1 //January
then 1

You say you already have these formulas in place and working. All you need to do then is to right click on them and insert summaries at the two different group levels and then suppress the detail section.

For YTD through Feb, you can use:

{@Jan} + {@Feb}//detail level

//FEB YTD:
sum({@Jan},{table.group2})+sum({@Feb},{table.group2})

//etc.

I'm assuming that you are limiting the overall date range in your selection formula.

-LB

 

[Kmccormic]

Thank you LBass:

I looked at the summaries. I was asked for a report which only displays the month of the date range along with the cumulative total for the year. I agree one which displays sum totals for every month and then a year to date summary total at the end would be much simpler. This is a public facing report, which means not going in every month to update which summary is displayed for each month. Since the report will usually be run for the month prior, (but not always), I need the YTD to only include up to the end date of the month the report is being run for, so if it's run on the 5th of March, it should not include information from March in the YTD total. It should also align, if there's only Boston and Virginia in the month specified, but in the year to date there's Boston, Virginia, New York, and Florida, both sides should list all of the groups so they line up properly with each other.


My record selection criteria
{@OpenDate Local time} in YearToDate and
{TASKS.REQUEST} = "Customer Feedback"


I can change my selectrion criteria to match the parameter date range, how could I list the year to date information then? Should I just tell them it can't be formatted that way and to deal with having each month listed out?

Thanks again for your help.
Regards,
Kara

 

[lbass]

I guess I'm not seeing what you want displayed. Is it just one month's summary and the YTD? I don't see how this relates to the date range parameter--why have one if you are only interested in the most recent full month and the YTD? You could more easily use a selected month end date.

Also, of more concern, is there a standard number of groups you are reporting on? Because if there is no data YTD, they won't show up. Or is that okay?

-LB

 

[Kmccormic]

Hi LBass:

It's fine if groups don't appear when they have no data.

Maybe I'm not understanding the selected month end date? Or do you mean switch the parameter to a "choose a month"? How could I get the data to load up from the start of the year to the end of that month?

Thank you,
Kara

 

[lbass]

If you use and end date parameter, you can set up the record selection like this:

{table.date} in date(year({?EndDate}),1,1) to
{?EndDate}-day({?Enddate})

If you want to show results for the most recent month only and YTD, then use:

//{@mostrecentmonth}:
if {table.date} in dateserial(year({?EndDate}),month({?EndDate})-1,1) to dateserial(year({?EndDate}),month({?EndDate}),1)-1 then 1

For YTD, you can just use counts of any non-null field at the group level, since the date range has already been defined in the selection formula.

-LB