Solved

CRYSTAL REPORTS Calculate a unique id using acct#, DOS and ChargeTotal

Posted on 2009-05-15
29
617 Views
Last Modified: 2012-06-27
I am trying to get stats for our physicians for the month.  These are clinic visits where the patient ID doesn't change from one visit to the next.  So, I created a unique ID to count patient visits using their account ID and the month, day and year of service.  That way it is different for every month, day and year.  However, I also only want patient's whose total charges for the service date are > 0.  Otherwise it will count dates where charges were entered and backed out.
So currently my unique id is this:                (patient id plus month plus day plus year)
I want it to also say "where "sum of price" > 0.  When I use the word "where" or "when" it gives me an error.  Anyone know what the crystal syntax would be for this?
0
Comment
Question by:Becky Edwards
  • 14
  • 10
  • 4
  • +1
29 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 20 total points
ID: 24396912
I assume you are grouping on the uniqueid and calculating a sum of charges.
Use a Group Selection rule to select just the groups whose toyal is not zero.

Report>Selection formulas>group
Select the Group total by ouble clicking it in the report fields and use..
{grouptotalhere}<>0
0
 

Author Comment

by:Becky Edwards
ID: 24399692
I can get it to just "display" the ones who totals <> 0, but the count in the physician's group, that counts the unique id's, is still counting the ones that total zero, even though they are not displayed.
I am grouping on service date, but have the unique id placed in that group and the formula counts unique id's.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 160 total points
ID: 24400329
Are you counting with formulas or summary functions?

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 320 total points
ID: 24400840
Using a group selection formula, as Peter suggested, is probably the best way to handle this in CR.  However, a group selection formula just hides the other groups.  CR still reads all of the records and will still include them all in any results from the CR summary functions.  If you're using the summary functions, you'll need to use formulas or running totals so that you can exclude the "0" groups.

 James
0
 

Author Comment

by:Becky Edwards
ID: 24412409
James:  Exactly.  I need to use a formula to count the patients, because excluding the patients with zero charges doesn't eliminate them from my count.  I need a formula that excludes them.  Any ideas?  So far my formula to "count" the patients is this:  
 (patient id plus month plus day plus year)
 
 I want it to also say "where "sum of price" > 0.  When I use the word "where" or "when" it gives me an error.  Do you know what the crystal syntax would be for this?

0
 

Author Comment

by:Becky Edwards
ID: 24412432
mimcc:     Are you counting with formulas or summary functions?
    I  am not sure I would know how to count with a summary function.  I created a unique id and put it in the Group Section where the patient resides.  It is the service date Group.
So in every service date group, there is a unique id that combines the service date and the patient id.  That is what I am counting, to get every patient visit.
 
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 160 total points
ID: 24412943
How are you counting?
Did you right click the field and click INSERT --> SUMMARY?
If so that is a summary and you have little or no control over how it counts.

You could try this id
In the report header
Add a formula
WhilePrintingRecords;
Global NumberVar PatientCount := 0;
''

In the group header
Add a formula
WhilePrintingRecords;
Global NumberVar PatientCount;
If Sum({PriceField}),{GroupField}) > 0 then
    PatientCount := PatientCount + 1;
''

In the report footer
WhilePrintingRecords;
Global NumberVar PatientCount;
PatientCount

mlmcc
''
0
 

Author Comment

by:Becky Edwards
ID: 24415984
Mimcc:
Yes, I did right-click the field and then did Insert-Summary.  What other way is there to do it?
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 160 total points
ID: 24416201
That is a summary and it wil use all records regardless of whether you have have a condition as in this case or not.

Try using the method I outlined.

mlmcc
0
 

Author Comment

by:Becky Edwards
ID: 24416983
I have questions:  Your suggestion with my question is below...
You could try this id
In the report header
Add a formula  
 [where? in the section expert, on the formula button for suppress? If yes, I tried that and got an error of "The formula result must be a boolean".  I am not sure if I was supposed to change the word PatientCount to something, but If I am what would it be?  I am sorry to be so dense.]

WhilePrintingRecords;
Global NumberVar PatientCount := 0;
''

In the group header
Add a formula
WhilePrintingRecords;
+Global NumberVar PatientCount;
If Sum({PriceField}),{GroupField}) > 0 then
    PatientCount := PatientCount + 1;
''

In the report footer
WhilePrintingRecords;
Global NumberVar PatientCount;
PatientCount


0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 320 total points
ID: 24417296
No, not a suppression formula (or anywhere else in the Section Expert).  In the Field Explorer, right-click on Formula Fields and select New to create a new formula.  Call it whatever you like.  Type in the lines that mlmcc posted for that first formula.  Then save the formula and drag and drop it into the report header section.  Do the same thing for the other two formulas (create a new formula, enter the formula, save it and put it in the appropriate section).

 PatientCount is the name of the variable that the formulas use to hold the count.  You don't need to change that name, but you can if you like.  Just make sure it's the same in all three formulas.

 {PriceField} and {GroupField}, OTOH, would be replaced by your fields.  Sum ({PriceField}),{GroupField}) > 0 is your "sum of price" > 0 test.  It should be the same test that you used in the group selection formula.

 James
0
 

Author Comment

by:Becky Edwards
ID: 24421749
Ok I learned something.  However, it looks just like a running total to me.  I have created a pdf of the report so you can see what is happening.  The first doctor has a sum of 3 patients, which is correct, only because there were no days with zero dollars.  Your formula computes to a 1, which just counts the patient once.  That is not what I want.  I want to count every patient every day UNLESS there are zero dollars.
The next doctor shows 254 patients.  However, it is counting those patient visits where there were zero charges.  Don't want that.  Your number (to the right) shows a zero where there are zero dollars, but is only counting the patients once, which is why it shows 5 instead of 254, I guess.
What am I doing wrong?
0
 

Author Comment

by:Becky Edwards
ID: 24421753
Sorry I forgot to add document.
Crystal-Reports---Clinic-Visits-.pdf
0
 
LVL 34

Accepted Solution

by:
James0628 earned 320 total points
ID: 24427993
The formulas and placement (eg. report footer) that mlmcc posted were meant to produce a total count for the whole report of the patients with a group total (daily total?) that's > 0.  I get the impression that you want a count for each doctor.  Correct?

 I take it that the lines that start with "HEMATOLOGY VISIT   Doctor" are a group header for a "doctor" group, and that's where you want to see the counts?

 The "detail" lines on the sample report pages you posted (eg. the line that starts with "01/02/09   426243   2505835   Patient").  Are those in the detail section or a group header or footer?


 Based on your sample report pages, I'm guessing that you put the middle formula (the one that increments the count) in what I'm thinking of as the "doctor header" lines (the lines that start with "HEMATOLOGY VISIT   Doctor") _and_ the "detail" lines (like the line that starts with "01/02/09   426243   2505835   Patient").  That's wrong, but I can't be sure how to fix it until I get the answers to the above questions.


 Responding to a couple of your comments:

 Yes, the formulas that mlmcc posted are more or less a running total.  As I said, they were meant to produce a total for the whole report.  Exactly what results you get will depend on where you place the incrementing formula.

 > ... which is why it shows 5 instead of 254, I guess ...

 Actually, that's part of the "running total" thing.  I think that was just the 5th time that the incrementing formula was evaluated, so the count was up to 5 at that point.


 It might simplify things if you u/l'ed a copy of your report.  If you're going to do that, note that EE only accepts certain file extensions and RPT is not one of them.  Just change the extension to an accepted type (eg. TXT) and add a note to the file saying that the extension needs to be changed back to RPT.

 James
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Becky Edwards
ID: 24433057
OK I kind of get what you are saying.  If I can figure out what groups to put the formula in - it should count just the ones I want.  
In the meantime, I would send this if I could figure out how.  Crystal won't let me save it in any other format, and the only other option is to export it, but then it isn't a Crystal report anymore.
Any ideas?
0
 

Author Comment

by:Becky Edwards
ID: 24433344
OK - Here is a pdf with the correct running totals.  I figured out where to place them, thanks James and mimcc for your assistance.
Now if I could just figure out how to get them into the cross-tab at the bottom that would be great.  I have attached a pdf of the results using running totals, and the results using a unique id.  You will see that Doctor #2 in the cross-tab has patients overstated by 14, due to the zero dollar days.  
So yes, the running total is more accurate, but if I want to do this for an entire 12 months and reflect it in columns, don't I have to use a crosstab?

Crystal-Reports---Clinic-Visits-.pdf
0
 

Author Closing Comment

by:Becky Edwards
ID: 31581974
Thank you so much for your assistance!!!!!  You are a wonderful resource.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24434077
You could build it manually using formulas.  It is a bit of a pain to do it that way.

The formulas you are using won't work with a cross tab since it uses all the data.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 24437721
> Crystal won't let me save it in any other format, and the
 > only other option is to export it, but then it isn't a Crystal
 > report anymore.

 FYI:
 You don't save the report in a different format.  You just take the existing report file (which ends in RPT) and change the extension on the file name (rename the file) from RPT to TXT (for example) and u/l the file under the new name.  For example, if your report was named Crystal-Reports---Clinic-Visits-.rpt, you could change the name to Crystal-Reports---Clinic-Visits-.txt and then u/l the file under that name.  After you u/l'ed the file, you would change the .txt in your file name back to .rpt .  Or, if you want to be extra careful, you could make a copy of the RPT file and change the extension on the copy to TXT and u/l that copy (rather than changing the name of the original file).  If you have Windows set up to not show the extension on known file types, which I think is the default, then you probably don't see the .rpt at the end of the filename, which might be part of the problem.

 Another way to change the extension is to have the report open in CR, go to File > "Save as" and where you put in the file name, manually type .txt at the end.  I just tried that here and it worked, creating a copy of the report with .txt at the end of the name instead of .rpt, but it might depend on your Windows settings.  If that works, then u/l the copy you saved with .txt at the end of the name.


 As for the cross-tab:

 Where is the middle formula (the one that adds 1 to PatientCount) located now?  Is it in a detail section or a group header/footer?  If it's in a detail section, then I can think of something that might work.  If it's in a group header/footer, then I don't know.

 James
0
 

Author Comment

by:Becky Edwards
ID: 24474766
oK - the running totals work but they count the doctors also.  What I need is a count of patients by doctor, and a count of patients by total clinic.  Attached is the report with a .txt format.  Please rename it to .rpt for it to become a crystal report.
Thanks!

Copy-of-Clinic-Visits-by-Month-g.txt
0
 
LVL 34

Expert Comment

by:James0628
ID: 24489092
> ... but they count the doctors also.

 If I'm following what you're saying, the total "counts the doctors" because the PatientCountGroup formula simply adds 1 to the count every time it's evaluated.  You included the formula in the doctor group header, so it adds 1 for each doctor.

 If you want to see a count for each doctor that's based on that formula, would it be OK to put the count in a group footer for the doctor, rather than the group header?  I don't think that you're going to be able to use CR's built-in summary functions to get a count based on that "sum > 0" test.  I don't think you can use Sum () on a formula that uses Sum ().  That means that you won't have the count for a doctor until the report has "printed" all of the records for that doctor.  Putting the count in the group footer would be the easiest way to handle that.


 I also have a general question about your counts.  When you say that you want a "count of patients", do you want a count of the patients that had a visit on any date that totalled over 0, or a count of the dates that patients had a total over 0?  For example, if the same patient visited on 4 days and the total for 3 of those days was over 0, does that count as 1 patient, or 3 days?  The formula that you have now is counting each date separately, so if the same patient has 3 dates that total over 0, that formula adds 3 to the count.

 James

 PS:
 I noticed that in the report you posted, you had "Doctor" in GH2, and not the actual group name.  If that was an attempt to conceal confidential information, it's only partially successful.  If I drag the group field onto the report, I can see the names.  It's included in the saved data in the report, since the report groups on that field.  You weren't displaying it, but it's there.

 If you weren't actually trying to hide that info, then I guess there's no problem.  I just wanted to mention it.
0
 

Author Comment

by:Becky Edwards
ID: 24498088

RE:  If you want to see a count for each doctor that's based on that formula, would it be OK to put the count in a group footer for the doctor, rather than the group header?
 Response:  I tried putting the count in the group footer for the doctor, rather than the header.  Then it just counts the doctor in the footer rather than the header.  I dont want it to count the doctor at all, just the patients.
 
RE:  I don't think that you're going to be able to use CR's built-in summary functions to get a count based on that "sum > 0" test.  I don't think you can use Sum () on a formula that uses Sum ().  That means that you won't have the count for a doctor until the report has "printed" all of the records for that doctor.  Putting the count in the group footer would be the easiest way to handle that.  
Response: I really dont understand what you were trying to say here.  I dont think putting the count in the group footer works, because I tried it and it counts the doctor and keeps on going to the end of the report.
 
RE:  I also have a general question about your counts.  When you say that you want a "count of patients", do you want a count of the patients that had a visit on any date that totalled over 0, or a count of the dates that patients had a total over 0?  For example, if the same patient visited on 4 days and the total for 3 of those days was over 0, does that count as 1 patient, or 3 days?  The formula that you have now is counting each date separately, so if the same patient has 3 dates that total over 0, that formula adds 3 to the count.
 
Response: Your last sentence, that is exactly what I want.  I need a count of the dates that a patient had a total over 0.  I need each date counted separately, for each patient.  So one patient came in one day, charges >0, = 1.  One patient came in 3 days, all three days > 0 each day, so = 3.  Total is 4 visits.
 
Thanks for the PS.  The confidential info is the pts name, thats all.  
Why isn't there a way to write a formula that says "Count {patientid+service date} where {sum of price} > 0? "  {PatientID+servicedate} is a formula that creates a unique id for that patient on that day.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24502073
> I tried putting the count in the group footer for the doctor,
 > rather than the header.  Then it just counts the doctor in
 > the footer rather than the header.

 Sorry for the confusion.  I didn't intend to put that same formula in the footer.  I planned to do something else.  But before I got into it, I wanted to see if it was OK to have the count in the group footer.  I don't think you're going to be able to get the count for the doctor in the group header (unless you want to use a subreport).


 > I really don't understand what you were trying to say here.
 > I don't think putting the count in the group footer works,
 > because I tried it and it counts the doctor and keeps on
 > going to the end of the report.

 Sorry (again :-).  When I talked about putting the count in the footer, I wasn't referring to your PatientCountGroup formula, but just "the count" (however it's produced).  I was just saying that the count for the doctor would probably have to be shown in the footer, rather than the header.

 
 > I need a count of the dates that a patient had a total over 0.

 OK.  Good.  That's what your current formula is counting.  It just suddenly occurred to me that you might want to just count each patient once.


 > Why isn't there a way to write a formula that says
 > "Count {patientid+service date} where {sum of price} > 0? "

 I think the basic problem with that is that summaries (Sum, Count, etc.) are done in a certain pass through the report data and in this case you're trying to do two different summaries at the same time - Sum of price for one group and then a count for another group, based on the results of the sum.  The concept of what you're trying to do may seem simple enough to you, but I just don't think it fits the way CR does things very well.

 Anyway ...

 I've got some changes for you to try.  They seem to work here.  The basic idea is to use two different variables, one for the doctor count and one for the report (clinic) count.  And you'll use a separate formula to output the doctor count.  You were using PatientCountGroup to output the doctor count, but it also adds 1 to the count.  So, we'll use a different formula that just outputs the count, without changing it.

 Change the PatientCount0 formula to the following:

WhilePrintingRecords;
Global NumberVar PatientCountDoctor := 0;
Global NumberVar PatientCountTotal := 0;

""


 PatientCountDoctor and PatientCountTotal replace the old PatientCount variable.  The "" at the end is simply so that the formula doesn't produce any output on the report.  Without that, you get a 0 on the report, unless you suppress the field/section.

 Create a new formula named PatientCountDoctor0.  It will be used to reset the doctor count for each new doctor.  Put this formula in the doctor group header (GH2).

WhilePrintingRecords;
Global NumberVar PatientCountDoctor;
if not InRepeatedGroupHeader then
    PatientCountDoctor := 0;
""


 The "not InRepeatedGroupHeader" is because you have the group header repeated on each page.  Without that check, the formula would reset the doctor count at the top of each new page.

 Change PatientCountGroup to the following:

WhilePrintingRecords;
Global NumberVar PatientCountDoctor;
Global NumberVar PatientCountTotal;
If Sum({BIMWDCP.DCEPRC}, {@SvcDate}) > 0 then
(
    PatientCountTotal := PatientCountTotal + 1;
    PatientCountDoctor := PatientCountDoctor + 1;
);


 That now increments both the doctor count and the new total (clinic) count.  It increments the doctor count last, so if you let the formula show on the report, you'll see the doctor count, same as before.  If you don't want to see the count, you can add "" at the end (like the earlier formulas).

 The PatientCountGroup formula should be _only_ in GH5 (the @SvcDate group header), and nowhere else.


 FWIW, I renamed the PatientCountFooter formula to PatientCountTotal, just because Footer could mean group or report footer (and the new name matches the name of the new PatientCountTotal variable).  You can keep the old formula name if you like.  But you need to change the formula to use the new variable name:

WhilePrintingRecords;
Global NumberVar PatientCountTotal;
PatientCountTotal


 Create a new formula named PatientCountDoctor and put it in the doctor footer (GF2):

WhilePrintingRecords;
Global NumberVar PatientCountDoctor;
PatientCountDoctor


 I think that should do it.  To recap:

 PatientCount0

 In the report header.
 Declares two variables, PatientCountDoctor and PatientCountTotal, and sets them to 0.


 PatientCountDoctor0

 In the doctor group header (GH2).
 Resets the doctor count, PatientCountDoctor, for each new doctor.


 PatientCountGroup

 In the @SvcDate group header (GH5), and _nowhere_ else.
 Increments both the doctor and total (clinic) counts.


 PatientCountFooter (formerly PatientCountTotal)

 In the report footer.
 Outputs the total (clinic) count.


 PatientCountDoctor

 In the doctor footer (GF2).
 Outputs the doctor count.



 Do you want the doctor counts in the cross-tab too?  That's trickier, but I've come up with something that seems to work.  It's a bit off the wall and I make no guarantees, but it does seem to work with the data saved in the report you posted.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24502157
OK, when describing the changes to the PatientCountGroup formula, I said:

 > That now increments both the doctor count and the new total (clinic) count.
 > It increments the doctor count last, so if you let the formula show on the
 > report, you'll see the doctor count, same as before.  If you don't want to
 > see the count, you can add "" at the end (like the earlier formulas).

 In case this wasn't clear, what I meant by "you'll see the doctor count", was the incrementing count (1, 2, 3, 4, etc.) that you've been getting from this formula every time it adds 1, _not_ the final count for each doctor.  The new PatientCountDoctor formula will output the final count for each doctor in the doctor group footer (GF2).  PatientCountGroup, if you let it show, will show the count as it's being accumulated.  I just wanted to make it clear that I wasn't talking about the final count for the doctor there.

 James
0
 

Author Comment

by:Becky Edwards
ID: 24529675
James:  I can get everything just right except the doctor count.  I am missing something here.  Could you look at this one and see?  You have to rename it to .rpt to get it to work.
Clinic-Visits-by-Month-TEST.txt
0
 
LVL 34

Expert Comment

by:James0628
ID: 24532554
You missed one formula.

 Create a new formula named PatientCountDoctor and put it in the doctor footer (GF2):

WhilePrintingRecords;
Global NumberVar PatientCountDoctor;
PatientCountDoctor



 You have PatientCountDoctor0 in the doctor group header (GH2), and that's correct, but all that does is reset the count to 0 for each new doctor.  The PatientCountDoctor formula above is the one that shows the count for each doctor, and it has to go in the footer (GF2).

 James
0
 

Author Comment

by:Becky Edwards
ID: 24538198
OMG It works.  Thank you Thank you Thank you.  
Now I need a crosstab with these doctors in it.  Shall I ask the question or do we continue with this discussion?
0
 
LVL 34

Expert Comment

by:James0628
ID: 24542719
I kind of hate to leave all this discussion behind, but you should probably start a new question, partly because a cross-tab wasn't specifically part of your original question, but also largely just because this question has already been closed for a week.  You can use the "ask a related question" link right above where you type in your comment and it will start a new question, with a link pointing to this one for reference.

 Whether you start a new question or we continue here, post exactly what you want in the cross-tab.  In one of the reports you posted earlier, you had what I think was a count of each patient visit.  Do you want that count _and_ the "> 0" count, or just the "> 0" count, or something else?  FWIW, I think I've come up with a way to get the "> 0" count in a cross-tab.  It's kind of off the wall, but it seems to work.  So, hopefully, the cross-tab part won't be too difficult.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24542726
Oh, and you're welcome.  :-)

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now