Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Pulling specific data problem Post Reply Post New Topic
Page  of 2 Next >>
Author Message
JDart
Newbie
Newbie
Avatar

Joined: 06 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
Quote JDart Replybullet Topic: Pulling specific data problem
     Posted: 07 Dec 2011 at 4:46am
Greetings!
 
Although I have been using Crystal Reports for several years now, I have not had any formal training and currently using an embedded version of it (XI).  I am struggling with terminology when trying to figure out how to search for an answer to my issue.
 
So, here I go to attempt to describe my problem.  Boss wants to know how many patients have one specific diagnosis versus those that have more than one diagnosis.  I know I need to develop a comparison formula to weed out the "more than one" but I am struggling with it.
 
I am using one database with seven (7) linked tables.  Here is an example, though I'm pretty sure the explanation will be confusing.  Using a time frame of 11/28 to 11/30, I have 13 admitted patients.  Of the 13 patients, 4 have a primary (order 1) diagnosis of anxiety, 8 have other diagnosis, and 1 a primary (order 1) diagnosis of anxiety, plus two others.  Boss wants a count of all patients with a primary (order 1) diagnosis of anxiety only. When I run the report, I should have a result of 4, but the result is 5.
 
I am able to filter out the additional diagnoses, but I don't know how to filter out the primary (order 1) anxiety + other (secondary, tertiary, etc) diagnosis.
 
Using filters I can get "Primary", "Order 1", "Anxiety" but it still pulls five (5).  I've tried using count, if/then/else, grouping, record orders, summaries, and exclusions to no avail.  I'm terrified of attempting a subreport since i've never done one and I'm still not sure what i'm looking for.
 
I am not sure what information is needed to help with a resolution to this issue, though I will be checking this post for help.  Let me know what I need to provide.
 
Thank you in advance.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Dec 2011 at 4:58am

you have one row per diagnosis per client?

or one row per client with multiple columns to show the various diagnosis in each column?
IP IP Logged
JDart
Newbie
Newbie
Avatar

Joined: 06 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
Quote JDart Replybullet Posted: 07 Dec 2011 at 5:02am
one row per diagnosis per client
 
therefore
 
Client MRN 19786 shows two rows, one for Primary (order 1) dx1 and one for Secondary (order 2) dx2
 
Filtering on Primary, Order 1, or Dx doesn't resolve the issue.
 
I am grouped on MRN for distinctcount purposes.


Edited by JDart - 07 Dec 2011 at 5:06am
IP IP Logged
JDart
Newbie
Newbie
Avatar

Joined: 06 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
Quote JDart Replybullet Posted: 07 Dec 2011 at 5:20am
Looking at some other posts, I am trying to add the needed information. Below is the preview
 
Client.med_rec_no      Client_DX_Code.axis_qualifier    DX_CODE.DX_code_desc
19786                        Primary                                           Anxiety
19786                        Secondary                                       Paranoia
 
19951                        Primary                                           Anxiety
 
20145                        Primary                                           Ugly Duck Syndrom
 
16433                        Primary                                           Paranoia
16433                        Secondary                                       Goose Egg Disease
 
10123                        Primary                                           Anxiety
 
Boss only wants the Primary "Anxiety" records counted, nothing more than one diagnosis attached.  I think I read it as  different data set same field?
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 07 Dec 2011 at 5:46am
you could try to create a crosstab
put Client.med_rec_no into Row
DX_CODE.DX_code_desc into Summarized field(as distinct count)
IP IP Logged
JDart
Newbie
Newbie
Avatar

Joined: 06 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
Quote JDart Replybullet Posted: 07 Dec 2011 at 7:25am
Thank you kostya;
 
But, with the Distinctcount, it evaluated each "type" of entry and counted them, not the number of times the entry was listed.
 
Though, I like the idea and will try different combinations.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Dec 2011 at 7:36am
group on Client.med_rec_no .
insert a count of Client.med_rec_no at the group level
for counting set a running total (you can use a shared varible too)
name=whatever
field to summarize=Client.med_rec_no 
type=distinctcount
evaluate=use a formula
Count(Client.med_rec_no,Client.med_rec_no)>1 and       Client_DX_Code.axis_qualifier='primary' and DX_CODE.DX_code_desc='Anxiety)
reset=never
place in report footer
 
 
 
IP IP Logged
JDart
Newbie
Newbie
Avatar

Joined: 06 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
Quote JDart Replybullet Posted: 07 Dec 2011 at 7:56am
DBlank
 
Okay, here's my checklist
 
Group on Client.med_rec_no - good
Insert a count of Client.med_rec_no at Group Level - Good
Set a Running total - uh, How?
 
I recall seeing it, but now I can't find it.  I created the Formula and it runs as 'true' but I know that's useless unless it's evaluating something.
 
Sorry for my density issues.
 
BTW, thank you!
 
IP IP Logged
JDart
Newbie
Newbie
Avatar

Joined: 06 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
Quote JDart Replybullet Posted: 07 Dec 2011 at 7:57am
Scratch previous post, found it on my 'Main Report' options.  Will give it a go and report results.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Dec 2011 at 8:02am
in the 'Field Explorer' (same place you create a formula field)
Running Total is the 3rd option from bottom (if tree is collapsed)
right click on it
select New
enter it as I described above.
 
as you saw when you created the formula as a formula Field it evaluates as True or False for each row of data.
This formual is used to tell the Running Total (RT) to include(TRUE) or Exclude(False) that row for the distinctCount of the clientid.
 
If you place the formula field you created (that returns true and false) and place it on the your detail section you should see it as FALSE on all client id rows that you did not want to count (your 5 records instead of 4)
 
NOTE: RTs DO NOT WORK IN HEADERS. -never display them there. they will display data but it is not the full RT value that is expected.
 
NOTE: Many users prefer to use Shared Variable fomrulas instead of RTs.


Edited by DBlank - 07 Dec 2011 at 8:07am
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.