Counting a formula 2

[bnsSteve]

Hello,

I'm wondering if there's a way to count a formula. For ex: I made a formula saying return "1" if (field1/field2) >0.2.

So the formula worked but now I would like to count all the '1' in each group.

Thanks!

 

[kray4660]

Not sure what you are trying to accomplish, but yes, I have 'counted' formulas a lot. It works as long as there is not a summary in the formula (summary of a summary, not allowed in CR).

 

[lbass]

Your formula should not result in a string, but a number:

if (field1/field2) >0.2 then 1

Right click on the formula and insert a sum on it, NOT a count, at the group level.

-LB

 

[FireGeek21]

Create 3 formulas as follows and place in the mentioned sections:

{@Reset} - put in group header section and suppress
WhilePrintingRecords;
NumberVar x := 0;

{@Detail} - put in detail section and suppress
WhilePrintingRecords;
NumberVar x := 0;

IF (field1/field2) > 0.2 THEN x := x + 1;

{@End_Result} - put in footer section and show
WhilePrintingRecords;
NumberVar x;

"Total Count: " & ToText(x, "#", 0)

Hope this helps!





FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[kray4660]

I must be getting old (or senile). I was sure I was able to count a formula easily (so much for the memory).

 

[bnsSteve]

Sorry my formula is actually more complicated, it does consist of a summary.

Field 1 is a distinct_count of accounts within another account number(certificate#): i.e, certificates have multiple accounts in it so i've counted how many accounts are in each certificate and grouped by certificate.
Field 2 is a sum of Field 3 that has either 1 or 0 in it. I tried using a count for this but it was taking the count for the 1 & 0's and not just the 1's.

So from these two fields (1&2) I've made the 20% formula. Now I would like to count up all the accounts that have 20% or greater.

Not sure if this is possible or if it makes sense. Thanks everyone, help is much appreciated.

Preview looks like this

Client Names
Cert# - Field1(DistCount of accounts) - Field2(Sum(field 3) - 20%Rule(Field1/Field2>.2, 1)

 

[FireGeek21]

What is Field 3? Is it a Table.Field or is it another formula?

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[bnsSteve]

Yep Field 3 is a table field, either indicated by 0 or 1 and is a number field. So each account will either have a 1 or 0 for field 3.

 

[lbass]

Please show the actual formula contents, not a paraphrase of it in the future, as you have led us astray here. If these are really summaries as you now indicate, then use a solution like FireGeek's (with some tweaks):

{@Reset} - put in higher order group header section that corresponds to the group level where you want the result, e.g., Group #1, and suppress:
WhilePrintingRecords;
NumberVar x := 0;

{@Accum} - put in GROUP #2 (or group section where your percentage formula is located) and suppress:

WhilePrintingRecords;
NumberVar x; //note this is NOT set to zero.
IF {@yourgroup#2summaryformula} > 0.2 THEN
x := x + 1;

{@Display} - put in footer section and show
WhilePrintingRecords;
NumberVar x;
"Total Count: " & ToText(x,0)

If you are confused about placement, you need to tell us what your group structure is, where your % formula is located, and at what group level you want the count.

-LB

 

[bnsSteve]

Sorry about that, I realized after I send my initial thread not enough info was provided. So this is basically what my flat file looks like.
What I would like to do is get a list of all the Clients who have billed >20% of their ACCTS under the same CERT. So say if Client AB has 1 cert with 10 accounts in it and 3 of those accounts are marked 1 meaning it has been billed. This means AB would be flagged for my list, since 3 of these accounts out of 10 in the same cert have been billed.

Sorry for the confusion, thanks again for all the help everyone.

Client ClientID CERT# ACCT# Billed
AB 00000071 CE55012 000097 0
AB 00000071 CE55014 000100 1
AB 00000071 CE55021 000097 0
AA 00000044 CE54811 000090 0
AA 00000044 CE54819 000085 1
AA 00000044 CE54819 000088 1
AA 00000044 CE54824 000096 1
AC 00000049 CE54824 000092 1
AC 00000049 CE54835 000096 1
AC 00000049 CE54835 000090 1

 

[lbass]

You describe a scenario and then provide a sample that seems not to relate to it. I am totally confused. Please try again and relate the narrative to the supplied data. I would organize the sample based on how you are currently grouping, and then show the expected results, also.

-LB

 

[bnsSteve]

OK sorry about that back to where I left off, let me try again.

From the data set I provided above, I had grouped by:
1)Client
2)CertID

Then I did a distinctCount of Acct#'s to give me the number of accounts in each Certificate(CertID). Placed in Group 2.

I then did a Sum of the billed column and placed that in Group 2 as well, which gave me the number of accounts that were billed per Certificate.

So this is what the preview looks like:

AB
CE55012 1 0
000097
CE55014 1 1
000100
CE55021 1 0
000097
AA ----Group 1
CE54811 1 0 ----Group 2
000090
CE54819 2 2
000085
000088

The 2nd column in Group 2 shoes the count of ACCT# in each CertID
The 3rd column in Group 2 shoes the sum of how many ACCT's billed in each CertID

What I would like to know is, which of the Clients have 20% or more of their ACCT's in each CertID have been billed. So if Client AC has 1 certificate with 10 ACCT's under that certificate and 3 of those ACCT's have been billed. Client AC would be selected in my count. If possible I would also like to know out of those Clients names, the count of how many Certificates had 20% or more accounts billed.

I hope this is clear enough, if not no worries thanks for all your help anyway. Much appreciated!

 

[lbass]

Still not clear and again you show an example and then reference a client AC that you haven't shown sample data for. Anyway, let me ask you these questions:

1-If AC has three certificates and one of them has more than 20% billed then AC would count once, but do you want to SEE the data for all three certificates?

2-You then say

If possible I would also like to know out of those Clients names, the count of how many Certificates had 20% or more accounts billed.

Do you mean that for the flagged Client AC you would want to see that only 1 certificate met the criteria? Or is this a total count across clients?


I'm still not following what the end report would look like--whether you intend to display only data that meets your criteria, whether you are trying to display flags/summaries only at the group level, or whether the goal is some final count at the report footer level.

-LB

 

[bnsSteve]

I used client AC just as an example, you can take AB or AA if that makes it easier to understand.

1) Yes client AC would be flagged once if one, two or all three of its certificates have 20% or more of its accounts billed. So as an end result I would have a list of Clients(only shown once) that have 1 or more certificates that breached the 20% rule.

2) As for the count of certificates, it's a secondary piece of information that I would like to know but not necessary as well. If possible I would like to get a count per lawyer of their breached certificates.

End report would look fairly simple, a list of all the clients with breached certificates and a count of how many of their cert breached. A clients name should only show up once.

Thanks.

 

[lbass]

So where does the lawyer fit in? Is there an outer group on lawyer? "Breached" = exceeded? That makes it sound like a bad thing...

-LB

 

[bnsSteve]

Lawyer = client, sorry we use them interchangeably and forget sometimes. Breach is not bad, just used the term to signify that their cert has reached the 20% rule.

 

[lbass]

Okay, so you should have two groups: Client, Cert. Place a formula like this in the Cert group header and suppress it:

//{@accum} to be placed in the cert group header:
whileprintingrecords;
numbervar pctbilled := sum({table.billed},{table.cert})%count({table.acct},{table.cert});
numbervar brch;
if pctbilled > 20 then
brch := brch + 1;

//{@reset} to be placed in the client group header:
whileprintingrecords;
numbervar brch;
if not inrepeatedgroupheader then
brch := 0;

//{@displaybrch} to be placed in the client group footer:
whileprintingrecords;
numbervar brch;

Then in the section expert suppress the client group header, the cert group header and group footer, and the details section and then select the group footer->suppress->x+2 and enter:

whileprintingrecords;
numbervar brch;
brch = 0 //note no colon

In the main report, drag the client group header into the group client footer.

This should give you only those clients with > 20% billed in at least one cert, with the number of certs meeting the criteria shown per client.

-LB

 

[bnsSteve]

Just tried it, works Great!

Thanks LB and FireGeek21 for the quick replies. Much appreciated.

 

[FireGeek21]

Nicely done LB. Thanks for following up with bnsSteve. I just got so crazy busy here that I couldn't follow up once we got all his information!

FireGeek21

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[bnsSteve]

Hi again,

I'm having a similar problem with a new query that I've been asked to do not sure how to use the last one for this one. I think it may be a bit simpler however,

Here's the data;

SOLICITOR_NAME CERTIFICATE_NUMBER ACCOUNT_NUMBER ELEC ELECTTYPE_DESC
BA 703 87 0 Unknown
BA 703 87 3 Indictment II
BA 703 87 0 Unknown
TH 851 51 0 Unknown
TH 759 81 0 Unknown
TH 759 64 2 Indictment I
DS 764 43 0 Unknown
DS 764 43 2 Indictment I
DS 764 43 0 Unknown
JY 114 31 3 Indictment II
JY 114 31 0 Unknown
JY 114 89 0 Unknown
GB 252 26 1 Summary Conviction
GB 212 91 3 Indictment II
GB 212 91 2 Indictment I
GB 239 73 1 Summary Conviction



What I need is a way to list the 'Solicitors' with a count of their distinct certificates and a count of how many of those certificates had an Indictment I or II. (The ELEC field corresponds to the ELEC descrip field, i.e, 0=unknown, 1=summary, 2=indictment I etc.)

Would be nice to have something like this

Name - # of cert - # of certs with indict I or II

Thanks again for all the help!