Crystal Reports 10: percentage

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

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]

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

mlmcc

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?

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

mlmcc

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?

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

how do i get around the error
Division by zero
when creating the %?

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?

Only if you need to restrict the records in the subreport.

mlmcc

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?

mlmcc
more work.  more points.  to be fair

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

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

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

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?

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

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.

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

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

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

mlmcc

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?

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

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

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

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

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

Could you answer the questions in my last message?

 James

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?

CAn you try this

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

mlmcc

That put into the Report Footer returns a value of -10.

Is the subreport or the section it is in SUPPRESSED?

mlmcc

yes

If it is suppressed it won't run

mlmcc

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

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

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

I'm glad I could help.

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

 James

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.

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

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

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

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

 James

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

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?

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

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?

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

mlmcc

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.

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

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!

I'm glad you finally got it working.

 James