Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: After refresh subreports contain different data Post Reply Post New Topic
Author Message
CrystalGump
Newbie
Newbie


Joined: 14 Apr 2014
Online Status: Offline
Posts: 3
Quote CrystalGump Replybullet Topic: After refresh subreports contain different data
    Posted: 14 Apr 2014 at 6:41am
I was given a report by a client that they are having difficulty getting to work.  It is written in Crystal XI.  The report is a patient summary report for a health facility.  The report uses different subreports to pull things like the patients providers and the patients diagnosis. 
 
When you first run the entire report you get the correct data.  If you then do a refresh and you do not change any of the data being used the subreports return the first row of information.  The only way to get this to show the correct data again is to completely close out of Crystal Reports and relaunch it.  Closing the actual report does not work. 
 
A couple of other observations I have made is that you can open the report and then open the subreports and run those all day long.  You will always get the correct data.  Once you run the main report you are unable to either rerun the main report or the subreports and get the correct data. 
 
I have had myself and two other report writers look at this and we are stumped at this point.  Anyone have any suggestion on what settings might cause this type of behavior?  Thanks in advance.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Apr 2014 at 7:03am

What are your data source types?

Are these using any overridden sources that you are over looking?
Are the sources using any parameters?
what are the subreports joined to the main report on?
are you saving data with the report?
are you using runtime parameters in both the main and subreports?
 
 
IP IP Logged
CrystalGump
Newbie
Newbie


Joined: 14 Apr 2014
Online Status: Offline
Posts: 3
Quote CrystalGump Replybullet Posted: 15 Apr 2014 at 2:58am
The data source is a MSSQL database.
 
 
The source gets passed a patient ID and a evaluation ID from the main report.
 
The subreport for the providers is linked to the main report by the patient ID and does not require any more paramaters. The diagnosis subreport is linked on both the patient ID and evaluation ID and doesn't require any additional parameters.  Both subreports have the issue I described happening.
 
We are not saving any data with the report and I guess I am not sure what you mean by using overridden sources?


Edited by CrystalGump - 15 Apr 2014 at 3:53am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2014 at 4:18am
what is the select formula for each of the subreports?
IP IP Logged
CrystalGump
Newbie
Newbie


Joined: 14 Apr 2014
Online Status: Offline
Posts: 3
Quote CrystalGump Replybullet Posted: 15 Apr 2014 at 4:56am
Here is the code for the provider(we have tried removing the distinct):
Select DISTINCT
prc.PAT_ID
, p.PERSON_ID
, pt.PERSON_TYPE_DESC
, rt.description
,cpt.description
, p.LAST_NAME
, p.FIRST_NAME
, p.SUFFIX
, p.PHONE_NUMBER
, p.FAX_NUMBER
from pat_ref_care prc
join PERSON p on prc.CARE_ID = p.PERSON_ID
left join PERSON_ASSIGNED_TYPES pat on p.PERSON_ID = pat.PERSON_ID
left join PERSON_TYPES pt on pat.PERSON_ID = pt.PERSON_TYPE_ID
left join REFERRING_TYPES rt on prc.REFERRING_TYPE = rt.referring_type
left join care_providers cp on prc.care_id = cp.care_id
left join CARE_PROVIDER_TYPES cpt on cp.CARE_PROVIDER_TYPE=cpt.care_provider_type

where prc.pat_id =   {?PAT_ID}
 
Here is the code for the diagnosis:
 
Select pe.PAT_EVAL_ID
, pe.EVAL_DATE
, d.*
, d1.inactive_date
,d1.DIAG_COMMENT
from pat_evaluation pe
join EVAL_DIAGS ed on pe.PAT_ID = ed.PAT_ID and  pe.PAT_EVAL_ID = ed.EVALUATION_ID
join  diag_view d on ed.pat_id = d.pat_id and ed.diag_id = d.DIAG_ID
 and ed.diag_date = d.diag_date and d.inactive = 0
join DIAGNOSIS d1 on d.pat_id = d1.pat_id and d.diag_id = d1.diag_id  and d.diag_date = d1.diag_date
where ed.pat_id = {?PAT_ID}
and pe.PAT_EVAL_ID =  {?PAT_EVAL_ID}
 
There is nothing out of the ordinary on these.  One of my coworkers suggest we look at the version this was written in.  Because we originally wrote it in Crystal XI and our client might have been editing it in a newer version.  From what I have been told this can cause some weird things to happen with reports.


Edited by CrystalGump - 15 Apr 2014 at 4:57am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2014 at 5:13am
so you main report does not retun any data?
You are using two parameters at run time that are not filtering the main report
?PAT_ID and ?PAT_EVAL_ID
You are using each of these parameters as the field to join on from the main report to the sub reports? 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.