Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Crystal Reports XI Array Help? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
ronc96@hotmail.
Newbie
Newbie


Joined: 29 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote ronc96@hotmail. Replybullet Topic: Crystal Reports XI Array Help?
     Posted: 29 Mar 2012 at 7:30am

Guys,

I am completely stressing out over this, so any help/advice/criticism would be GREATLY appreciated!!

I will try to lay out the basics of what I am trying to accomplish, in the case that I am overcomplicating things; however, I think I am pretty much on target for where to go, just clueless as to how to get there (which is really just as bad as not even knowing where you are going, right)??

I am trying to do the following (I work for a Healthcare Organization, so I will be reference Patients and such, if that matters in the grand scheme of things):


I am trying to pull a Listing of All Patients that have had at least 2 Office Visits/Walk-Ins (can be 2 of one or 1 of each, as long as it is 2 or more, total) within a Specified Date Range (I have Parameters/Formulas to acquire these and pass them to a Subreport, one for a Start Date and one for an End Date).


**This seems relatively simple to do; however, it needs to be Grouped by Provider.

 

What I have done is this:

In the Main Report, I have placed the Person.PID as the Group Header 1, and Document.ID as Group Header 2…then, I have created the Formula "Document Count Total" of the Documents (the Selection Criteria pulls only the specified document types, so this makes for a good count)…

***In the Main Report, the Person and Document tables are linked by PID


I have passed the Person.PID, Start Date, End Date, and Document Count Total to the Subreport, and am only pulling (Selection Criteria in the Subreport) records where "Document Count Total" >= 2, which gives me the correct information (gives me only people whom have had at least 2 OV/WIn combinations).


In the Subreport, I have placed the USR.Pvid as the Group Header 1, and Person.PID as Group Header 2...

***In the Subreport, the Person and Document tables are linked by PID, and USR table (for Provider information) is Linked to the Document Table, so that the Responsible User for the Document, not the Person/Patient, can be retrieved.

 

The problem is this:

If I put the Subreport in the Main Report’s Report Footer, I only see information for the last Person.PID from the Subreport, which makes sense because all previous records have been overwritten…

I need to figure out a way (I’m guessing an array) to store the Person.PIDs (from the Main Report), so that they can be passed to and searched upon in the Subreport; this way, I could run the Subreport only once (Main Report Report Footer), but still get all of my Patient PIDs from the Main Report.

**In addition, if an Array is the way to go, I would need to store far more than 1,000 values/elements, so I was wondering if there is another option in creating Multiple Arrays?

Any help would be greatly appreciated!!!!!!  Smile

Ron


Edited by ronc96@hotmail. - 29 Mar 2012 at 8:30am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Mar 2012 at 9:04am
so you have a table of visits with dates and associated doctors.
you need to run this with a date param to only show patients that had 2 or more visits during the dates entered.
you need to group this by doctor.
do you show the patient under each doctor if the patient was seen by more than 1 doctor?
IP IP Logged
ronc96@hotmail.
Newbie
Newbie


Joined: 29 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote ronc96@hotmail. Replybullet Posted: 29 Mar 2012 at 9:40am
Yes...if the Dates specified are 01/01/2012-03/01/2012, and you were seen by Dr. A on 01/22/2012 and Dr. B on 02/22/2012, you would show under each doctor.
 
All in all, I wanted to use the Main Report to sort by Person.PID (GH1) and Document.ID (GH2), simply so that I could do a "Document Count" of the Document.IDs (the Selection Criteria, in the Main Report, only allows Office Visits and Walk-Ins).
 
I have basically suppressed everything, in the Main Report, so that I could simply make the Subreport my Main Report (by putting the Subreport in the Main Report's Report Footer).
 
Again, I did all of this so that I could pass the Document Count Formula, from the Main Report, onto the Subreport, which would then allow me to only show patients where the "Document Count Total >= 2".
 
But, to answer your original question: yes, if a patient were seen by multiple doctors, given that the visit was in the specified Date Range, that patient would show up under the Grouping for Multiple Physicians.
 
Thanks,
 
Ron


Edited by ronc96@hotmail. - 29 Mar 2012 at 9:48am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Mar 2012 at 10:14am
i would approach this differently.
If you can write a stored proc for your source I would do that.
or use a crystal command with parameters in it.
you can use an inner query to limit your outer query data set to only include rows with 2 or more visits.
then you have a single source with your full alreadyfiltered data set and you can sort and group anyway you want.
Will be more efficient than any sub report proces as well.
IP IP Logged
ronc96@hotmail.
Newbie
Newbie


Joined: 29 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote ronc96@hotmail. Replybullet Posted: 29 Mar 2012 at 10:21am
DBlank,
 
As much as I hate to ask this, because I feel like I am asking you to do my job for me, how would you go about doing this?
 
If I don't need to fool around with the Subreport, I will gladly take it out of the equation.
 
I was hoping that there was a way for me to only show rows with 2 or more visits, but I wasn't sure how, besides using the count from the Main Report to the Subreport.
 
When you say use a Crystal Command with parameters (I am familiar with setting up Parameters) and/or using an inner query to limit my outer query data set, what exactly do you mean?
 
Again, I am familiar with Parameters and Inner/Outer Joins, but I may be missing something here.
 
Thanks,
 
Ron
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Mar 2012 at 10:22am
do you know where to create a command as a source?
IP IP Logged
ronc96@hotmail.
Newbie
Newbie


Joined: 29 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote ronc96@hotmail. Replybullet Posted: 29 Mar 2012 at 10:26am
I didn't, but I do now...are you telling me that I can simply create a Command, report off of that, and that will be all that there is to it?
 
I made it this difficult, for no reason???


Edited by ronc96@hotmail. - 29 Mar 2012 at 10:28am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Mar 2012 at 10:30am
yep.
in the command you can create parameters that are filled in at run time.
 
here is an example of a command with an inner query that is inner joined to the main query which will limit the data set to patients with only more than 1 visit in the date range
The inner query and outer query use the same 2 params. 
 
SELECT     table1.patientid, table1.visitdate, table1.visitid, table1.doctor
FROM         table1 INNER JOIN
                          (SELECT     patientid, COUNT(DISTINCT visitid) AS Visits
                            FROM          table1 AS INCIDENTS_1
                            WHERE      (visitdate BETWEEN @startdate AND @enddate)
                            GROUP BY patientid
                            HAVING      (COUNT(DISTINCT visitid) > 1)) AS Visits ON table1.patientid = Vistis.patientid
WHERE     (table1.visitdate BETWEEN @startdate AND @enddate)
 
IP IP Logged
ronc96@hotmail.
Newbie
Newbie


Joined: 29 Mar 2012
Location: United States
Online Status: Offline
Posts: 8
Quote ronc96@hotmail. Replybullet Posted: 29 Mar 2012 at 10:34am
DBlank,
 
Thank you so much for your help here!
 
I can't believe I was making this so much more difficult, than it had to be!
 
I can't express my gratitude enough!
 
Ron
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Mar 2012 at 10:36am
No problem. I am sure someone better versed in SQL could give you a more effieicent command example but I hope this at least leads you to a solution. Good luck.
IP IP Logged
Page  of 2 Next >>
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.