Solved

# Crystal Reports 10: percentage

Posted on 2008-10-21
Medium Priority
799 Views
Last Modified: 2012-05-05
My Crystal Reports 10 report gives the totals of a field based on what is in the field
Selection:
Count of field where x
Count of field where y
Count of field where z
It is easy then to get a total count of those records included in the field.
Count or RunningCount.
In addition there are data elements that may be described as a or b or c.  These data fields are not included in the RunningCount.
However, I would like to get a Count of all the data fields, not just the x, y and z fields.
I want to show : ( (RunningCount [x,y,z] fields) / (All fields [where all x, y, z and a, b, c, for example]) as a percentage.

Is there a way to count all fields, not just those selected? to use as the denominator for the % calculation

NOTE: No points will be rewarded for answers that simply refer me to a book.  I am looking for a solution, not a reference.
0
Question by:eedavidson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 27
• 16
• 11
57 Comments

LVL 101

Expert Comment

ID: 22773407
I am not sure what you are getting at.  Are the a,b,cx included in the record set?

How are you getting the counts now?

When you say Count of field where x what does that mean?

Do you have a formula like

If {Field} = 'x' then
1
else
0

and you are summing it up?

mlmcc
0

Author Comment

ID: 22777656
i will try to ask the question better:
select all persons where x, y or z condition is true, during @start and @stop dates.  = shows all persons who meet the x selection criteria.
but the entire person population is not included in this summary. -- now i would like to get a percentage of all persons who are included in the selected group / over all persons in the entire data set.

% = (count of persons who meet x,y,or z) /c ount of all persons
between the parameters Start and Stop [use input]

0

LVL 101

Expert Comment

ID: 22779414
Since you are excluding everyone who doesn't meet x,y,z then you won't have the records to get the count.

mlmcc
0

Author Comment

ID: 22779693
mlmcm
true. BUT that does no answer my question.  You have restated the initial problem:  -- that is my question, in essence.  to repeat, more simply -  In the same report, can i get total of selected records (yes), as well as a total of all records queried? [those that met the criteria, AND those that dd not].  would this be possible using a subreport perhaps?
0

LVL 101

Expert Comment

ID: 22780432
Only if you use the report to do the filtering or add a subreport to get the total number of records or people.

mlmcc
0

Author Comment

ID: 22780514
yes, that is my question.  once again you have restated the problem.  My question is what steps do i need to take to get the desired results?  (Count of selected records)/(Count of all records).  what are the steps?  An answer to this question: requires a more specific solution.  (The obvious has already been said [repeatedly, by me, then by you].  I am looking for a Solution.
Can this be done?  How?
0

LVL 101

Expert Comment

ID: 22780646
Sorry.

You can add a subreport

In the main report add a formula to the report header
WhilePrintingRecords;
Shared NumberVar TotalRecordCount := 0;
''

Add formulas
Name - CountXs
If {Field} = 'x' then
1
else
0

Similarly for Y and Z
Create a subreport using the same SQL but without the where clause
Add a formula to the report header
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount := Count({SomeField});
''

Put the subreport into the report header after you insert the other formula.

When you need to do a %
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
Sum({CountXs}) / TotalRecordCount

Similarly for Y & Z

mlmcc
0

Author Comment

ID: 22781291
how do i get around the error
Division by zero
when creating the %?
0

Author Comment

ID: 22781765
hi mlmcc
the Divide by zero, was ok, once i thought about it.  i've a question though about the Subreport and the main report.  Do i need to set Links from one to the other? in order for the formulae to work?
0

LVL 101

Expert Comment

ID: 22781990
Only if you need to restrict the records in the subreport.

mlmcc
0

Author Comment

ID: 22782106
thank you for your patience.  to recap:
the subreport only needs the initialize formula in the header [note - remove the where statement]
the main report needs the formula in the header and then the subreport in the header
Count formula is written for each x,y,z
%formula is entered in report where desired as written [ i got the Divide by Zero error]
so i changed the % to be
If TotalRecordCount = 0 then 0 else
Sum({CountXs}) / TotalRecordCount

but now i get a 0 when i enter the %formulas in the report.

countX's formula written with no errors
%formulas written with no errors

do i need any @increment formula or @display formula?

0

Author Comment

ID: 22782107
mlmcc
more work.  more points.  to be fair
0

LVL 101

Expert Comment

ID: 22782227
I assume you have the declaration and WhilePrintingrecords; in the formula

Can you upload the report.
Make a copy and change the extension to TXT

mlmcc
0

Author Comment

ID: 22782307
i wish.  but the information is proprietary.  i'll try to be clear on my steps.  you've been great.
when you say the declaration? do you mean the
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount := Count({SomeField});
in the subreport header?  (it is)

and the
WhilePrintingRecords;
Shared NumberVar TotalRecordCount := 0;
in the main report header?  (it is)

do i need other statements/formula in the report headers?
then the subreport is entered in the main report header.

ideas?

percentages return 0.  [if i use TotalRecord Count = 0 then 0 else]
or Divide by Zero error
0

LVL 101

Expert Comment

ID: 22782643
You also need them in the formula that does the division

WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
If TotalRecordCount = 0 then 0 else
Sum({CountXs}) / TotalRecordCount

mlmcc
0

Author Comment

ID: 22787539
that is in the %formula just as its written.
i have a question regarding the shared variable TotalRecordCount-
In the subreport it is set to 0 in the report header.  that number becomes the actual total record count by the end of the subreport.  say 5000.
When the initialize formula in the header of the actual report, sets the totalrecordcount to 0.
(with the subreport in the same section.)
and when does the Report TotalRecordCount (zero at beginning of report) become the subreports 5000?
0

LVL 101

Expert Comment

ID: 22790100
It should at the end of the subreport.

Try this

Right click the report header in the left margin
Click INSERT SECTION BELOW
Move the subreport to the RHb

mlmcc
0

Author Comment

ID: 22790419
tmlmcc: that didn't work properly.  all records displayed [500] on the main report, instead of just the counts. i will look closer at the report and doublecheck the original select sql.
i will be back at this task in 3 days.  and will work again then.  i apologize for the interrupt in finding this solution.
0

LVL 101

Expert Comment

ID: 22790913
No problem  Just post when you get back.

If you want to upload the report ( no data needed) I can look at the formulas and how the subreport is linked to see if I see anything

mlmcc
0

Author Comment

ID: 22791337
unsure of how i can "de-identify" the data elements, in the underlying sql.  even with no data saved.
but thanks.
0

LVL 101

Expert Comment

ID: 22792081
No problem if even the data elements are sensitive or proprietary.

mlmcc
0

Author Comment

ID: 22814685
recap:
(1)main rpt headerB--
WhilePrintingRecords;
Shared NumberVar TotalRecordCount := 0;
then
(2)count formulas-- [same for A,B,C] - Place these count formulas in report footer
If {TITLE} = 'A'
then
1
else
0

(3)next: in subreport header--
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount := Count({SomeField}

(4)then the %'s
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
If TotalRecordCount = 0 then 0 else
Sum({@A}) / TotalRecordCount [for A,B,C] -- in the report footer
did i miss anything?

The subreport returns ALL records, not just those that are A,B,C.  Success there.
The main report returns 0 for TotalRecordCount, wherever i put it.  The same for the Counts and %s

ideas?
0

LVL 35

Expert Comment

ID: 22818305
Are the formula in the main report that sets TotalRecordCount to 0 and the subreport in different sections?

Make sure that you have = and := in the right places (:= when setting TotalRecordCount and = when testing to see if it's 0).

Can you have the subreport output TotalRecordCount at the end, to confirm that it is set at the end of the subreport?

James
0

Author Comment

ID: 22824662
i'm having no luck, and think it must be something small that i'm not doing correctly.  i'll recap the steps;
(1) A subreport is written with the following, and it does return the total number of all records
---
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount := Count({PAT_ENC_HSP.ED_EPISODE_ID});
---
(2) in the headerA of the main report
WhilePrintingRecords;
Shared NumberVar TotalRecordCount = 0
(3) in the headerB - Insert the subreport
(4) count formulas-- [same for A,B,C] - Place these count formulas in report footer [problem ALL = 0]
If {TITLE} = 'A'
then
1
else
0
(5) percent formulas.  placed in report footer (problem all return 0)

If TotalRecordCount = 0 then 0
else
Sum({@CountElopedPatients) / TotalRecordCount
any ideas
0

LVL 35

Expert Comment

ID: 22846930
Sorry I didn't get back to this sooner.  I've just been busy the last couple of days.

You said that you put the count formulas in the report footer.  Was it the formulas, or sums of the formulas?  Technically, I don't think the count formulas have to be on the report at all, but if they were, it would be in a detail section.  All that you'll get from them in the report footer is the result (1 or 0) from the last record read.

In the percentage formula you posted, is {@CountElopedPatients} one of your count formulas?

If the percentage formulas are returning 0, it could be because the total that should come from the subreport is 0, or the sum of the count formula is 0, or both.  Have you tried showing each of those (variable from the subreport and the sum of the count formulas) by itself in the main report footer, to see which ones are 0?

It's probably just a typo, but in your initialization formula in step 2, you have = instead of := .  I actually doubt it would change anything, one way or the other (in that I generally don't bother to initialize variables like that), but you might as well make sure the formula is correct.

You could try removing that initialization formula from the report, at least temporarily, to eliminate the possibility that it might be resetting the variable after the subreport runs.  As I said, I usually don't initialize variables before setting them in a subreport.  It's not necessarily a bad idea, but in my experience, it's not necessary.

James
0

Author Comment

ID: 22879368
thanks.  i'm going throught the code to determine what is the case.  not yet certain
0

Author Comment

ID: 22882498
do either of you feel like going through this again?  i cannot find the missing piece.
0

LVL 35

Expert Comment

ID: 22882875
Could you answer the questions in my last message?

James
0

Author Comment

ID: 22888606
In subreport header
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount := Count({PAT_ENC_HSP.ED_EPISODE_ID});

(Count the field episode_id)

In subreport footer (same formula)
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount := Count({PAT_ENC_HSP.ED_EPISODE_ID});

In main report header
WhilePrintingRecords;
Shared NumberVar TotalRecordCount = 0;

Formula in main report to figure the % of each A,B or C / The total of All [the number in the subreport.  In example A is AMA.   Placed in main report Footer B

WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
If TotalRecordCount = 0 then 0 else
Sum({@CountAMAPatients}) / TotalRecordCount

Count of each A,B or C In example A is AMA  in report footer B

If {ZC_ED_DISPOSITION.TITLE} = 'AMA/Against Medical Advice'
then
1
else
0

all report 0

this report takes an incredible amount of time to execute.  an unacceptable amount.  and it doesn't work.  ideas?
0

LVL 101

Expert Comment

ID: 22889915
CAn you try this

Change the final formula to
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
If TotalRecordCount = 0 then -10 else
Sum({@CountAMAPatients}) / TotalRecordCount

mlmcc

0

Author Comment

ID: 22890668
That put into the Report Footer returns a value of -10.
0

LVL 101

Expert Comment

ID: 22890769
Is the subreport or the section it is in SUPPRESSED?

mlmcc
0

Author Comment

ID: 22890875
yes
0

LVL 101

Expert Comment

ID: 22891314
If it is suppressed it won't run

mlmcc
0

Author Comment

ID: 22891667
with the report Not suppressed, the correct TotalRecordCount appears in the footer.
now my %'s are all returning 0

WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
If TotalRecordCount = 0 then -10 else
Sum({@CountAMAPatients}) / TotalRecordCount

i'll try to troubleshoot again tomorrow.  i'm missing something basic
0

LVL 35

Expert Comment

ID: 22891738
Good catch mlmcc.

He's right.  If the subreport is suppressed, or the section that the supreport is in is suppressed, the subreport won't run at all, so the variable is not set.

I'm guessing that the count formulas in the subreport produce output (the count).  Does the subreport produce any other output?

Basically, what you need to do is change the subreport so that it doesn't produce any output, then in the subreport format in the main report, on the "Subreport" tab, check the "suppress blank subreport" option.  Then, in the section expert for the section that the subreport is in, check the "suppress blank section" option instead of the "suppress" option.  That way the subreport is executed, then if it does not produce any output, it's suppressed, and if that section of the main report is then blank, it's suppressed.  Of course if there is anything else in the same section as the subreport, it will need to be suppressed, or blank, in order for that section of the main report to be suppressed.

There are various ways that you could keep the subreport from producing any output.  The simplest is probably just to suppress every section of the subreport.  The formulas in the sections will still be evaluated.

Other odds and ends:

The subreport is in a report header section (not page header, but report header).  Correct?  If it's in another section, it could be executed more than once, which would slow the report down.

The formula you posted that initializes the count in the report header shows:

Shared NumberVar TotalRecordCount = 0;

Like I said before, I don't know that you really need to initialize the variable, but if that formula is going to do any good, that should be :=, instead of =.

I doubt that it actually has any effect on the report, but you don't need a formula to set TotalRecordCount in both the header and footer of the subreport.  I'd take the formula out of the header and leave it in the footer, just because that seems more logical to me.

As for the performance of the report, that may just be something that you're stuck with.  Your main report is reading one set of data and the subreport is reading everything to get the total count.  If you're looking at a lot of records, it's going to take time.  I'd like to see if we can get the subreport working.  If we can, but it's just taking too long, we can look for other solutions.

James
0

LVL 35

Expert Comment

ID: 22891809
You hadn't posted that last message when I started writing my last message.

Looking at the counts in the main report:

Looking at one of your previous messages, I'm not sure if you're saying that you put the count formulas (if x = y then 1 else 0) in the report footer, or a Sum () of the count formulas.

The count formula in the report footer would just give you a 1 or 0, depending on what was in the last record read by the report.  A Sum () of the count formula should give you a total of that count.  If you're getting 0, I think there must be something wrong with your count formula.

Forgive the obvious, but are you sure that you're looking for the right values?

Could the field that you're checking (eg. ZC_ED_DISPOSITION.TITLE) ever be null?  Nulls can cause problems in some situations.  It actually doesn't seem like they'd be a problem for a simple test like this, but you can always add a check for them, just to make sure.  Something like:

If not IsNull ({ZC_ED_DISPOSITION.TITLE}) and
{ZC_ED_DISPOSITION.TITLE} = 'AMA/Against Medical Advice'
then
1
else
0

Or, if you don't need to see any null values in your report, you could go to File > "Report Options" and check the "Convert Database NULL Values to Default" option.  If you've got several count formulas, using that option would be a lot simpler than changing each formula.

James
0

LVL 35

Expert Comment

ID: 22938484
I'm glad I could help.

Just out of curiosity, what was it that finally got the % formulas working?

James
0

Author Comment

ID: 22945276
hello james  -- thanks for asking.
in truth, i cannot get those formulas to work.  i gave some time to it today.
i wanted to give you guys the credit for your work.
i've got a beautiful report with the totalcount showing in the main footer, but no luck with the percentages.  it is part of the delivery requirements of this particular set of data.  i went through our entire 'conversations'  today, without success.  i think i need to begin again with counts or sums of the A,B and C groups.  then create a percentage perhaps using the {@countA}%{@totalcount] or {@sumA}%{@totalcount}.  it is my next step.  i must get the Sums working first.
0

LVL 35

Expert Comment

ID: 22949922
Hmm.  A formula like "if {field} = 'value' then 1 else 0" is pretty basic stuff.  You should be able to simply put that in the detail section, right click on it and select Summary and get a total of it.  If that works, then Sum () on that formula should work.

If that's giving you 0, I've got to wonder if you're checking for the right value(s).  For example, there could be embedded spaces somewhere in the field or maybe the case of some of the letters is wrong.  I'd try making one of the count formulas less specific and see what you get.  For example:

If UpperCase ({ZC_ED_DISPOSITION.TITLE}) like "*A*" then
1
else
0

That should give you a 1 for every record where TITLE has an "a" (upper or lower case) in it somewhere.  Put that in the detail section and do a summary on it and see what you get.  If the summary is still 0, there's a basic problem somewhere (assuming that some records should have an "a" in that field).
Along the same lines, you could put that field on the report somewhere (if it's not already) and check the values.

James
0

Author Comment

ID: 22964062
hi james.  i'll need to wait now until monday am to check.  in the meantime, i did show "if {field} = 'value' then 1 else 0"  in the details field, and it correctly showed a 1 for each match.  meaning, i wrote the three different formula, for A,B,C and put the variables in the detail line, and it correctly showed 1 or 0 for the appropriate rows.  make sense?  in other words, it worked.  Now i'll right click for the Sum.  it can't fail!  I'll be able then to get the percentages of the Sum/WholeCount.  i may open a related question, in order to give you the points.  i'm back at my desk in 3 days.
l
0

LVL 35

Expert Comment

ID: 22965354
So you got the 1's and 0's.  Sounds good.

As for the points, if you want to change how they're allocated or something, you can ask to have this question re-opened.  If you were actually talking about opening another question just to award additional points, that's not necessary as far as I'm concerned (and might be against EE's rules).

James
0

LVL 35

Expert Comment

ID: 22973716
I thought that might be the case (but was too lazy to go check the rules :-).

James
0

Author Comment

ID: 22990470
i'm perplexed.  will re-open.
need to check the how-to's
thanks
0

Author Comment

ID: 22998990
This report runs with flexible date parameters.  I think i need the subreport to recognize these date parameters, or else the number it returns for TotalRecordCount will be huge [no begin or end date].  Is this a correct assumption?
0

LVL 101

Expert Comment

ID: 22999556
Yes, you will have to pass the dates to the subreport.

You can link the subreport on the dates then change the selection formula of the subreport to reflect the fact it is a range.

mlmcc
0

Author Comment

ID: 23000674
home stretch
---Main Report counts A, B, C correctly ex.
If {ZC_ED_DISPOSITION.TITLE} = 'AMA/Refused Treatment'
or
{ZC_ED_DISPOSITION.TITLE} = 'Left Against Medical Advice'
then
1
else
0
---Main Report Sums each count, shown in the Main Report Footer B ex. Sum ({@CountAMAPatients})
---Main Report Footer C has % formula
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
If TotalRecordCount = 0 then 0
else
Sum({@CountAMAPatients}) / TotalRecordCount
;
---SubReport Header
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount := Count({PAT_ENC_HSP.ED_EPISODE_ID})
;
---Subreport Details include
Identity_ID & Contact_Date
-the identity_ID is the field that is counted for the TotalRecordCount, Contact_Date was added to link to MainReport Contact_Date in order to pass start date and end date
---i linked the MainReport and the SubReport on the ContactDate

---In SubReport Report Footer A is the simple
Count of Pat_Enc_Hsp.Episode_ID
---In Subreport Footer B is TotalRecordCount
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;
TotalRecordCount = Count({PAT_ENC_HSP.ED_EPISODE_ID});

Main Report Header C contains SubReport

well the MainReport when run/refreshed/new parameters
returns 0 for TotalRecordCount
--------
i think that's the total of what we did so far.  does it make sense to you?  any ideas?

0

LVL 101

Expert Comment

ID: 23001283
You do have a formula like below in Report header A
WhilePrintingRecords;
Shared NumberVar TotalRecordCount;

mlmcc
0

LVL 35

Accepted Solution

James0628 earned 1000 total points
ID: 23002698
A few things that have been covered before, but FWIW ...

In the formula in subreport footer B, you show = instead of :=.  If it's using =, it's not setting the variable, although that's not necessarily a problem, as long as the variable is set somewhere in the subreport (like in the subreport header).

I think the formula to set TotalRecordCount in the subreport header will work.  Personally, I'd set it in the subreport footer, but I don't know that it really matters.  But you don't need to set it in both places.  I'd get rid of one formula or the other, just to avoid potential confusion.

You're not suppressing the subreport, are you?

James
0

LVL 101

Assisted Solution

mlmcc earned 1000 total points
ID: 23005366
Good catch James.

mlmcc
0

Author Comment

ID: 23009303
All is well.  Totals of a,b,c are working.  Sum of totals are working.  a shared variable is being passed from the subreport to the main report.
its the variable that is incorrect.
the Main Report has flexible date parameters: 'start date' and 'end date'
i linked the MainReport and the SubReport on the field "Contact_Date".

i am getting ALL records, not just those in the range.

I am going to do this:  suppress the Contact_Date in the SubReport details [forgot to do this]
and check the box in the SubReport Links dialog box to "Select data in subreport based on field"

we'll see.  Let me know if you see anything else.
0

Author Closing Comment

ID: 31508519
Both Experts gave excellent advice toward the solution of my problem.  I wish there were more points to give!
0

Author Comment

ID: 23009583
i printed this earlier, but then closed the question without the comment
SUCCESS
Factors:
I made certain that the variables were accurate (:= vs =) and well placed
I linked the MainReport and the SubReport on Contact_Date - (the flexible date parameter field)
I check the box in the Subreport Links dialogue box to "Select Data on Subreport based on field"
I used the simple "%" to calculate percentages at the end of the report, i.e. ({@SumAs})% ({@TotalRecordCount})

Each step/recommended in the 40+ threads in this question led me to the Solution.  Working with a genius and a guru, is the best.  The points are split 250/250.  The "solution" marked are the last of our correspondence, not necessarily the actual, entire, or 1/2 solution.  i wish there were more points to give!

i'm not including this in the knowledgebase.  It does not easily help other's knowledge!  For me - great!
0

LVL 35

Expert Comment

ID: 23012288
I'm glad you finally got it working.

James
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
###### Suggested Courses
Course of the Month14 days, 2 hours left to enroll

#### 801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.