?
Solved

Grouping on datetime fields restricts records

Posted on 2006-03-31
13
Medium Priority
?
267 Views
Last Modified: 2006-11-18
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?
0
Comment
Question by:LBarrett
  • 6
  • 5
  • 2
13 Comments
 
LVL 16

Expert Comment

by:wykabryan
ID: 16344964
What is the column lay out look like?

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?
0
 

Author Comment

by:LBarrett
ID: 16345107
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?
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16345152
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.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:LBarrett
ID: 16345195
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.
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16345231
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.
0
 

Author Comment

by:LBarrett
ID: 16345248
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}
0
 

Author Comment

by:LBarrett
ID: 16345341
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?
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 16345417
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",{subreport time}),00)

then modify the link between the main and sub to be based around the formula time.
0
 

Author Comment

by:LBarrett
ID: 16345570
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.
0
 
LVL 16

Assisted Solution

by:wykabryan
wykabryan earned 375 total points
ID: 16345645
Well, there might be a way around that as far as displaying it and ordering it.  I believe this exist in 8.5

You could link on patienid
Then in the subreport do the following:
group by document number.  Then go to the options tab.

Check the first box, "Customize Group Name Field"
   leave it at the first option and change it to the datetime field.



Running out of ideas...hehehe..
0
 

Author Comment

by:LBarrett
ID: 16345689
Will give this a try and post results.  Will let you know.  Thanks for the help so far.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 375 total points
ID: 16348278
Do you have SUPPRESS IF DUPLICATE set for the detail section or fields in the subreport?

Since the appointment times are the same you only get one group header  for the two records.

You could display the data in the DETAIL Section and allow drill down

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16351712
Glad i could help

mlmcc
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses
Course of the Month9 days, 8 hours left to enroll

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question