CR 11, SQL database, current month and YTD

[azoe]

I have a report that is grouped:
By month, Doctor, department, and procedure code. In the detail is units and charge amount.

Sample:

October
Dr. Smith
Cardiology
1042
10 100.00
5 50.00

What they want is to see subtotals after each department and totals after each doctor. They want that for the current month and for year to date.

The part I need help with is figuring out how to do the current month and year to date parts. I need 3 dates. The beginning of our year is 1/8/05 (this year, different next year) and then the start and end of whatever they want the "current" month to be. Originally I made a crosstab that broke out the months by calendar date (as opposed to the dates they use - sample: for October their dates for the beginning and end of the month are 9-2-05 to 10-03-05) but they don't want to see every month.

So I don't know if the beginning date will need to be hardcoded somehow and then I ask, through parameters for the "current" month...

The date I will be using is the chargesClosingDate (it is stored as yyyymmdd in SQL). I haven't added parameters in Crystal before but if it is possible I could provide a list of date ranges to choose from to make it easier for the user:
September would be 09-02-05 to 10-03-05 and then October would be 10-04-05 to 11-01-05, etc.

The goal will be for the current month to look like the sample above and then the same thing but with Year to date totals.

Thank you -

 

[Madawc]

Do you mean calendar month or the last 30 days? These would be Month({your.date}) >= Month(currentdate) or else {your.date}) > currentdate-30

For years, it could be Year(currentdate) or Dateadd("yyyy", -1, currentdate)

For parameters, a parameter defined as a date will input dates.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[azoe]

calendar month or the last 30 days?" Unfortunately it is neither. Their month starts on the second business day and goes through the first business day of the next month.

So I don't know if it is possible, but if it was I could put in a list of date ranges that they could pick from like 10-4-05 to 11-1-05 (it would be nice if that range could be 1 parameter). Otherwise I guess I could put in 10-4-05 as 1 parameter (like a BeginningDate parameter) and 11-1-05 as another (like EndingDate parameter) and so on.

My real question is not knowing whether to use a record selection formula, or grouping, or a crosstab, or what? to give them the current month figures and then the year to date figures in two different columns or areas on the report.

I mean I don't know how to pull all the data and then separate out a portion of the data. My date field is a varchar(8).

Thanks

 

[lbass]

I think you could create a master formula like the following to be placed in the report header and manually updated each year:

datevar range jan := date(2005,01,08) to date(2005,02,02);
datevar range feb := date(2005,02,03) to date(2005,03,04);
datevar range mar := date(2005,03,05) to date(2005,04,05);
datevar yearstart := date(2005,01,08);
datevar range currentmo;

if {?Date} in jan then
currentmo := jan else
if {?Date} in feb then
currentmo := feb else
if {?Date} in mar then
currentmo := mar else //etc up through dec
currentmo := date(year(currentdate),month(currentdate),01) to currentdate;
1 //you need the 1 to prevent an error message

Then create two formulas:

//{@mtd}:
datevar range currentmo;
if {@Date} in currentmo then 1 //or "then {table.amt}"

//{@ytd}:
datevar range currentmo;
datevar yearstart;

if {@Date} in yearstart to maximum(currentmo) then 1
//or "then {table.amt}"

You can then right click on each of these and insert summaries (sums) as the various group levels.

Before you start the above though, you should remove the group on month, set up a parameter {?date} that is a date type parameter, and convert your stringdate to an actual date by using a formula {@Date}:

cdate(val(left({table.datestring},4)),val(mid({table.datestring},5,2)),val(right({table.datestring},2)))

You should probably use a record selection formula something like:

{@Date} in date(year({?date}),01,01) to dateserial(year({?date}),month({?date})+2,01)-1

This would bring in some dates outside of the accepted dates, but you could then suppress them in the section expert and then you wouldn't need to alter it from year to year. This assumes that the business year never starts in a previous calendar year. You would use a formula like the following in the conditional suppression formula area for the detail section:

datevar range currentmo;
datevar yearstart;

{@Date} < yearstart or
{@Date} > maximum(currentmo);

-LB

 

[azoe]

ok - thank you for that. I can't wait to try it because for now I've got them accepting a calendar month, so I put it in a crosstab and exported it to Excel so I can take out the months they don't want and email it to them. Problem is it takes 20 minutes just to open the file and then it is really difficult to format, I tried to delete 3 columns and started looking for another way after it still wasn't done in 30 minutes.
Anyway, thanks I will probably try it tomorrow.