Grouped on DateTime field with subreport

[jenschmidt]

My main report and the subreport are both based on the same table. The part of the structure that's important for this report is the following:

Patient Provider Appt_Made_Date Appt_Date Appt_Len
Doe Bills 03/02/04 06/16/04 15
Jones Love 02/02/04 10/03/04 30
Smith Schmidt 10/03/04 12/01/08 10

I want to match up the Appt_Made_Date to the Appt_Date.

So, I have the main report displaying Appt_Len totals grouped by Appt_Made_Date week and I have one column for each weekday.
Mon Tues Wed Thur Fri
GH#1 02/01/04 150 100 200 300 50
GH#1 02/08/04 400 180 445 125 10

Formula for each weekday is -
if DayOfWeek({PAT_ENC.APPT_MADE_DATE})=2 then {PAT_ENC.APPT_LEN} else 0

The subreport is displaying Appt_Len totals grouped by Appt_Date week and again, I have one column for each weekday with the same type of formula.

I've then linked the from Appt_Made_Date in the main report to the Appt_Date in the subreport. But, when I try to display the subreport or pass over the totals, it only shows me a value from the subreport for Monday!!!

What I want to see:
Mon Tues Wed Thur Fri
GH#1 02/01/04 150 100 200 300 50
(SubReport) 100 90 150 275 60

GH#1 02/08/04 400 180 445 125 10
(SubReport) 375 120 360 100 5

What I am seeing:
Mon Tues Wed Thur Fri
GH#1 02/01/04 150 100 200 300 50
(SubReport) 100

GH#1 02/08/04 400 180 445 125 10
(SubReport) 375

What am I doing wrong? Is there a better way rather than subreport and variables?

Thanks!




jennifer.giemza@uwmf.wisc.edu

 

[tektipdjango]

The param passed to the subreport is evaluated when the sub is fired, that is on the first date of the week the monday.
To get the correct info you have to link the date of the groupHeader in main report to a parameter in the sub
Then in the sub select records if date is between param and param+6 days and you'll get the results

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[MrBillSC]

It seems that your main and sub reports are really grouped by week not day. Therefore link to subreport by week and just display the subreport.

Subreports run slow. You might try linking your table to itself, linking AppointMadeDate to AppointDate. This may allow you to eliminate your subreport.

MrBill