| Author |
Message |
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
  
Topic: Totally Stumped with Subreport Linking
Posted: 29 Dec 2009 at 7:57am |
|
I've searched the forums here, yet haven't found anything that fits my issue. Could be that I don't know what I'm really looking for.
My issue is this: I have a medical database containing a table for charges/billing. Each charge has a patient's chart number, date of service, and loads of other data. I need to compare the current year (2009) patient population to the patient population 3 years before (2006-2008). I'm looking for a list of patients who were previously seen but haven't been back for a year. Vice versa. A subreport sounds like it should handle this.
For the life of me I cannot figure this out. This is my first try at a subreport. What I've done to get my list is run a report to get patient chart numbers for 2006 thru 2008. I then export that into an excel file. I create a new report, link the excel file in the database expert, then in the select expert I choose my time frame for 2009 and only want chart numbers that are not in the excel file.
I
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Dec 2009 at 8:13am |
I do not think excel files support outer joins which is what you would need.
You can create the report with a param of service date>1-1-2006.
Group on PCN
Create a formula to flag the records you want as
if {table.servicedate} > date(2009,1,1) then 1 else 0
Insert a Summary using this formula field as a sum on the PCN group level
e.g. SUM(formula, PCN)
In the select expert use the group selection to find your records
SUM(formula, PCN)=0 Edited by DBlank - 29 Dec 2009 at 8:35am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Dec 2009 at 8:33am |
Forget to mention to suppress the details section to avoid displaying all the excess service date.
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 29 Dec 2009 at 9:02am |
|
Should have included "Total Newb" in the thread somewhere. Sorry but your reply confused me more. I need a little explanation on how the subreport linking works. Let me rephrase what you said and see if I'm understanding anything...
In my main/parent report I'll use the Select Expert - table.servicedate is between 1-1-2006 and 12-31-2009 (so all four years instead of the first three)
In main/parent report - Insert Group: table.chartno (don't see why you're doing this)
In main/parent report - New formula treated like a boolean variable, if service date is during 2009 return a 1, else return a 0.
In main/parent report - Insert Summary: Choose field = my new formula, Calculate this summary = Sum, Summary Location = Group #1.
In main/parent report - Go back into the select expert and add Group #1 is equal to 0.
In main/parent report - Suppress details line (since I don't have any fields in there).
So a subreport is not needed at all?
My focus here is to initially grab all the data I need at once instead of splitting it up into two parts (2006-2008 and 2009). I am flagging the data I want returned through a formula (2009). I am grouping on the chart number since there are multiple entries for each patient, then using the group summary (sum of formula) to return either a 0 (meaning no charges entered during 2009 but charged entered in 2006-2008) or 1+ (meaning I'll get the number of charges entered in 2009). Then back in the select expert I only want records where the group summary is 0 (2008-2009).
I am way off base, aren't I? I don't see how this will do what I want it to do.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Dec 2009 at 9:07am |
You are right on target.
You wanted a list of patients that had been seen between 06-08 but not in 09, correct? This will give that to you.
Were you looking for something else too that I am not addressing?
Edited by DBlank - 29 Dec 2009 at 9:07am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Dec 2009 at 9:20am |
They main thing is that you are grouping on the patient ID then Summing the formula at that group level then using the select expert to exclude the grouped data if the SUM>0.
Example...
patient #1 with 10 visits all before 2009 therefore the SUM=0 and the patient is kept in the report.
Patient #2 with 9 visits all before 2009 and 1 visit after 2009 so the SUM=1 and the patient is removed.
Patient #3 has 10 visits all after 2009 so the sum = 10 and the patent is excluded.
The key is your are excluding the entire group, not just rows.
Make sense?
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 29 Dec 2009 at 10:20am |
|
Totally makes sense now. Thank you for the explanation.
I really appreciate the response. Wasn't expecting anything for a few days really. You are awesome to say the least. :)
|
| IP Logged |
|
geraldh
Newbie
Joined: 29 Dec 2009
Online Status: Offline
Posts: 10
|
Posted: 29 Dec 2009 at 11:03am |
|
Brain is fried trying to understand this. I didn't make myself clear. Let me try again.
Actually I am trying to find new patients seen for the first time in 2009. A patient is considered new if they were not seen within the last 3 years. Patients may have been seen prior to 2006, however their status changes to new since they've been out so long. I have tried to modified the method you described above, but I'm just lost now.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Dec 2009 at 11:06am |
change your flag formula to < 2009...
if {table.servicedate} < date(2009,1,1) then 1 else 0
|
| IP Logged |
|
|
|
