Need to calc percent in GF using Grand Totals
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=893
Printed Date: 03 May 2024 at 2:23am
Topic: Need to calc percent in GF using Grand Totals
Posted By: luk1128
Subject: Need to calc percent in GF using Grand Totals
Date Posted: 20 Jun 2007 at 11:31am
|
Hello! I'm sure there is a simple solution to this, but I can't seem to find it! Here is my dilema: I have a report that pulls in quality audit data for medical claims data entry for trending of errors, etc. The report is designed by supressing the details and having a group for the Error Types which is a field returned from the database. The report then calculates the number of errors for each error type and displays that using a running total. Now we need to display the percentage of errors of the the total number of records and the percentage of errors of the total number of records with errors. I can do this just fine in the Report Footer by using the an overall running total, BUT I cannot seem to successfully pull these totals into the calc in the Error Type Group Footer section so I can display the percentages for each Error Type. It only calculates for the number of records for that group, whereas I need to show as a percentage of ALL records and ALL errors. Does anyone have any ideas on how I can get that number into my calculation?? Any help is MUCH appreciated! |
Replies:
Posted By: BrianBischof
Date Posted: 20 Jun 2007 at 2:26pm
Create a formula that does the percent calculation. In the Formula Workshop, you can easily find the report field which does the group summary and the grand total. Double click on each one to put in the the code editor box. Then put the '/' between them to do the division. Here is a sample I did to do a percent of sales by customer based on the total sales for the whole company.Sum ({Orders.Order Amount}, {Customer.Customer ID})/Sum ({Orders.Order Amount}) * 100.00------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: luk1128
Date Posted: 21 Jun 2007 at 11:43am
|
Thank you!
This seemed to work just fine on getting a percentage of the total number of errors. Luckily we have a formula that calcs the number of errors and we just plugged that in with the Sum() function and it worked.
However, I'm still having an issue trying to get a Sum() of the number of DCNs. The DCN number is a field in our database that determines what document this is. There are instances where the DCN number is duplicated as a new record if there are more than one error on that DCN.
To get a distinct number of DCNs to be reported in the Report Footer section, it is easy to just run a Distinct Count running total to get this number. But I cannot get a Sum() of this because it is a running total.
If I try to create a formula to count the distinct DCNs, I run into the same problem. I even tried to create a formula that compared the current DCN to the previous DCN and then run a Sum() on that, but the Previous() function won't allow a Sum. So I'm lost as to how to get the number of errors to display as a percentage of the total distinct DCNs in the report.
Does anyone have any ideas!?
Thanks again!
|
Posted By: BrianBischof
Date Posted: 21 Jun 2007 at 1:43pm
|
Well, you're on the right track and what you've done is what I would have recommended anyway. The problem you are encountering is trying to do a running total and using the Previous() function. First off, are you using the Running Total Expert or creating a manual running total with a formula? I would think that you shouldn't have any problems doing a manual running total. Either way, another idea is to not use the Previous() function and instead replace it with a shared variable the tracks the current DCN. For each record, update it to the new DCN. But prior to updating it, check the current field with this variable and that is the same as comparing it to the previous value (did that make sense?) In other words, put the DCN in a variable so you can see what the previous value of it was in your testing.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: luk1128
Date Posted: 21 Jun 2007 at 2:42pm
| I'm a little confused on how to write this... Can you provide a quick example? |
Posted By: BrianBischof
Date Posted: 21 Jun 2007 at 3:48pm
|
You should read my free online chapters. I have examples and explanations there. http://www.crystalreportsbook.com/Crystal_Reports_XI.asp?Page=3_7 - Running Totals Tutorial Near the middle of the chapter I discuss using formulas instead of the running total field. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: luk1128
Date Posted: 21 Jun 2007 at 10:07pm
|
Here is what I have for my first formula whiich is named 'TotalDCNAll':
Global StringVar DCNNumber;
Global NumberVar DCNCount; If DCNNumber <> {tblMainResults.DCN} then
DCNNumber = {tblMainResults.DCN};
DCNCount = DCNCount + 1; DCNCount I put the above formula field into the Details section which is suppressed.
Then my other formula, named 'TotalDCNSum', is this:
Sum( mailto:%7b@TotalDCNAll - {@TotalDCNAll })
As soon as I try to put the second formula into the Error Type group footer, I get an error saying that the mailto:%7b@TotalDCNAll - {@TotalDCNAll } field cannot be summarized...
Am I doing something wrong??
Thanks for your help!
|
Posted By: BrianBischof
Date Posted: 21 Jun 2007 at 10:19pm
|
Yes, you almost have it. You can't summarize the formula because it doesn't hold a value. It's the DCNCount variable inside the formula that you want to display. The formula is just the means for doing the calculations. The TotalDCNAll formula has the right idea, but i'm confused. It looks like you are using Crystal Syntax, but you are using "=" instead of ":=". That implies you are using Basic syntax. I'm surprised you haven't gotten an error message in the formula about this. I'm going to rewrite it for you and see if that helps out.
Then, the formula TotalDCNSum just needs to print the variable that holds the count. Global NumberVar DCNCount; ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: luk1128
Date Posted: 22 Jun 2007 at 5:33am
|
I think I'm close. I rewrote my formulas as you described above and I got the following results.
When I put the TotalDCNAll formula into the details section, it seems to be incrementing, but it started off at the number of 9,134 and then incremented by one for each DCN. I also got a 0 in the TotalDCNSum formula regardless of whether I place it in the Error Type group footer or in the details section, so I don't think that is correct.
To try and fix the count starting at 9,134 I added a new line of code to the count formula:
Global StringVar DCNNumber;
Global NumberVar DCNCount; If DCNNumber <> {tblMainResults.DCN} then
( DCNCount := 0; DCNNumber := {tblMainResults.DCN}; DCNCount := DCNCount + 1; ); DCNCount; Then to try and get the sum of the count, I changed the TotalDCNSum formula back to:
Sum( mailto:%7b@TotalDCNAll - {@TotalDCNAll })
This gave me a total number of 18,881 which is correct, EXCEPT for not counting the duplicate DCNs, so I don't think the TotalDCNAll formula is catching the dups. The total should equal 17,184.
Any more suggestions??
I appreciate all your help! I wouldn't have gotten this far without it!
Thanks,
Dan
|
Posted By: BrianBischof
Date Posted: 22 Jun 2007 at 10:41am
|
Well, I'm a little confused (I'm at work and don't have time to TOTALLY study every question b/c there are so many on the forum today....) Anyway, the best way to reset the variable is to put a reset formula in the group header that sets TotalDCNAll to zero. That way it starts at zero for each group. I don't understand how you can use that Sum() calculation in the formula because you can't sum on another formula. That strikes me as odd that this works. Also, the formula SHOULD be correct for not counting dups because you If Then statement is perfect. So, all around I'm not sure what to tell you. What I would do at this point is to unhide the detail section so that you can see the value of DCNCount for every record in the report. This will let you look at it line by line and see exactly which records cause it to change value and which ones don't. Oh! I just realized something. Put WHILEPRINTINGRECORDS in the beginning of the formula. I bet that's the problem! It's doing the calculations while its READING the records. That means that the data isn't grouped yet and that would explain why it starts at such a large number when it begins printing. Yes, try this and let me know if that fixes it. Oh yea, I would still try getting rid of Sum({@TotalDCNAll}) in that formula. It just doesn't sound right to sum a formula and I'm surprised it doesn't give an error (I guess I have to research this one in the future to see what is happening). ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: luk1128
Date Posted: 22 Jun 2007 at 12:04pm
WhilePrintingRecords worked! Thank you!!
However, after all of that, I noticed a fundamental flaw in this whole thing. The DCNs are not in order because it is grouped bt Error Type, so if one DCN has 2 different error types, they are not going to be listed together anyway, so comparing the DCN to the previous isn't going to work...
It's so frustrating because I can see what the distinct count of the total is, but I cant use it!
Thanks for all your help.
I think I'm going to have to tell the business team that I cannot provide the percentage of DCNs.
 |
Posted By: BrianBischof
Date Posted: 22 Jun 2007 at 12:58pm
|
Well, I'm glad it worked, but I'm not glad that it still doesn't work (as if that makes any sense). Here is how I would do it, but you're going to have to write some code. I would create two global arrays. As you read each DCN in, I would loop through the array to see if it already exists or not. If it does, then increment the counter in the second array using the same index number. If it doesn't exist, then add it to the end of the first array and then start the counter in the second array with a '1' as the initial value. I don't know if you know enough about CR programming to pull this off or not (or how much time you can allocate to the report), but this is the way I would approach it if you really need this report to work. HTH. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |