Line chart with 2 different total formulas using 2 different dates

[Davidabc]

Hi
I am using CR2008 and having problems with "on change of". I have a formula that total's the number of patient visits from the past 10 weeks using the field patient.contact_date (YYYMMMDDD) from a patient table. I have another formula that totals the Physicians available appointment slots for the past 10 weeks using Physician.Avaialble_slot_date_time (YYYMMMDDD HH:MM:SS) from a Physician_availability table. These are in the show values option in chart expert. I want to have 2 lines on the chart for each week of the past 10 weeks. One showing patient visits and one showing Physicians available appointment slots. I think my problem is with the "on change of" How can I do a "on change of" using one date (patient.contact_date and one date and time Physician.Available_slot_date_time. This is not a command object report. Any help would be much appreciated.

 

[lbass]

You need to use a field that contains all possible dates, and so one way of doing this is to merge the dates from both tables into the same field in a command. You can do this in a subreport, if you already have other report elements you don't want to redo. You would use the command as the sole datasource in the subreport, and set it up like this;

select patient.contact_date as alldates, patient.contact date, null as drslot
from patient
where patient.contact_date >= {?Start} and
patient.contact_date < {?End}+1
union all
select to_date(physician.available_slot_date_time,"YYYYMMDD"), null, to_date(physician.available_slot_date_time,"YYYYMMDD")
from physician
where physician.available_slot_date_time >= {?Start} and
physician.available_slot_date_time < {?End}+1


...where you would create the date parameters within the command (on the right)

I don't know your database re: syntax/punctuation and date conversion, so you wil probably need to adapt this.

Then you would use {command.alldates} as your on change of field (on change of week), and then add count of {command.contact_date} and count of {command.drslot} as your "show value" fields.

-LB

 

[Davidabc]

Hi lbass
Thank you for the reply. I added a subreport and it worked fine.
Adding Subreport definitely increases execution time.