LBarrett
asked on
Grouping on datetime fields restricts records
Using Crystal reports 8.5 with sql database odbc.
Have a report which must be grouped by an appointments datetime field. When they entered the data they mistakenly entered two appts. at the same date and time. Once generated they can not go back and change the entry. The report is only showing one of the appts. not both. Have group set to group on datetime field by seconds but they are exactly the same. Both are 3/27/2006 10:30:00 am. If I group by patients ID number all correct records show but when grouping on datetime I loose one record. Have tried several things, no answer. Can someone help?
Have a report which must be grouped by an appointments datetime field. When they entered the data they mistakenly entered two appts. at the same date and time. Once generated they can not go back and change the entry. The report is only showing one of the appts. not both. Have group set to group on datetime field by seconds but they are exactly the same. Both are 3/27/2006 10:30:00 am. If I group by patients ID number all correct records show but when grouping on datetime I loose one record. Have tried several things, no answer. Can someone help?
ASKER
No columns after the header just a subreport. They wanted to be able to see the times on the drilldown, expected to see 10:30 am and then drilldown further. If not this way then how?
So on the main report they want to see 10:30, but then when they drill down into 10:30 they want to see both(if they exist, even in error) 10:30 appointments.
ASKER
Yes, that is one way for them to tell that the time is wrong. Sounds weird but since they can't change an appointment that has already been entered this is an error check for them.
Ok.. This should work with out a subreport.
Group on Datetime -
Then in the detail section provide the detail. Right click on the Section Name, and choose hide (not suppress).
The way in the report it will appear as one line but one clicking of one of the datetimes it will display the details behind the scenes.
Hope this helps.
Group on Datetime -
Then in the detail section provide the detail. Right click on the Section Name, and choose hide (not suppress).
The way in the report it will appear as one line but one clicking of one of the datetimes it will display the details behind the scenes.
Hope this helps.
ASKER
BTW datetime is a parameter for the main and all subreports as is Employee ID. One employee can have many appts on the same day and can even see the same client twice on the same day. Part of selection formula for main is:
{DocVstCtn.DatIn} in {?DateIn} to dateadd("h",23,{?DateIn}) and
{PatCht.AutPrsSys} = {?Personnel ID}
{DocVstCtn.DatIn} in {?DateIn} to dateadd("h",23,{?DateIn}) and
{PatCht.AutPrsSys} = {?Personnel ID}
ASKER
My fault it not being clear about the design of this report. There is no detail on the main. There are only page headers a & b with 3 groups below. Group Header 1a, 1b, 1c. All are grouped by same datetime parameter field and linked to main with a document number. All other sections on main are suppresed except for page footer a & b which are conditionally supressed based on page number. Does this help?
What information is contained in GH 1a, 1b, 1c? What is the link look like between the main and the sub for appointments?
I am wondering if in the subreport if you reformated the time to get rid of the seconds if that would help. A formula like in the main and subreport:
Time (datepart("h",{subreport time}),datepart("n",{subre
then modify the link between the main and sub to be based around the formula time.
I am wondering if in the subreport if you reformated the time to get rid of the seconds if that would help. A formula like in the main and subreport:
Time (datepart("h",{subreport time}),datepart("n",{subre
then modify the link between the main and sub to be based around the formula time.
ASKER
Main Report is patient info:
Name
address
appt date, employee Id etc.
--------------------------
Group 1a -1st sub - medical visit assessment info
--------------------------
Group 1b - 2nd sub - mental health questions and answers
--------------------------
Group 1c - 3rd sub - clinical notes info and advisements
--------------------------
all subs are linked to main with a unique document number assigned by the system software to the patient.
I did try to change the grouping to hour and lost even more patients. Those with appts. at say 10:15 am were gone. As I said before if I group by document number all visits show but not in datetime order.
Name
address
appt date, employee Id etc.
--------------------------
Group 1a -1st sub - medical visit assessment info
--------------------------
Group 1b - 2nd sub - mental health questions and answers
--------------------------
Group 1c - 3rd sub - clinical notes info and advisements
--------------------------
all subs are linked to main with a unique document number assigned by the system software to the patient.
I did try to change the grouping to hour and lost even more patients. Those with appts. at say 10:15 am were gone. As I said before if I group by document number all visits show but not in datetime order.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Will give this a try and post results. Will let you know. Thanks for the help so far.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad i could help
mlmcc
mlmcc
Group Header 1 - Datetime
Name|Datetime |Description
The next question is why have the grouping at that level, since you want to see both records even if it was entered in error?