count occurance of text from a formula

[RepRider]

Crystal XI with SQL database

I have the formula below in a report to act as a prompt to a provider to close a consumer based on various lapse times from a last service.

I now want to count how many records have been flagged as a total count and also to show what percentage of all of the current displayed records in the report are flagged with this text.

Can anyone show me what to do? I tried to put a grand summary in but it just gave me a number that is equal to all the records accessed I think.

Any help is appreciated!



if
(
{Consumer_Service_View.begin_date} <= DataDate-90 and
ucase({Employee_View.people_last_name}) = "ALZHEIMERS"
) OR
(
{Consumer_Service_View.begin_date} <= DataDate-365 and
ucase({Employee_View.people_last_name}) in ["RESPITE","OBRA"]
) OR
(
not(ucase({Employee_View.people_last_name}) in ["ALZHEIMERS","RESPITE","OBRA"]) and
{Consumer_Service_View.begin_date} <= DataDate-30
) then ToText ("CLOSE?") else ""

 

[dgillz]

If you want to count, then make your result a 1 or a zero, then sun the count. You may also want to look at the instr() function to test if the string contains the substring you are looking for, rather than exactly equaling it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[RepRider]

So I would use the same formula except to have it display 1 or 0 then sum on that column?

 

[RepRider]

I did the above and still got the same number... 5 million something! It should be around 180.

 

[lbass]

Try the following:

Create a new formula called {@Null} by opening and saving a formula without entering anything. Then change your formula to:

if
(
{Consumer_Service_View.begin_date} <= DataDate-90 and
ucase({Employee_View.people_last_name}) = "ALZHEIMERS"
) OR
(
{Consumer_Service_View.begin_date} <= DataDate-365 and
ucase({Employee_View.people_last_name}) in ["RESPITE","OBRA"]
) OR
(
not(ucase({Employee_View.people_last_name}) in ["ALZHEIMERS","RESPITE","OBRA"]) and
{Consumer_Service_View.begin_date} <= DataDate-30
) then
{table.uniqueID} else
tonumber({@Null})

You would use an ID field that is unique to a record. I'm assuming it would be a number field, but if it is a string, then remove the tonumber() in the formula above. Then right click on this formula and insert a distinctcount on it. You can then compare the results to the distinctcount of the ID field for al customers.

-LB

 

[RepRider]

I have not been able to get this to work yet and I need to find what I'm doing wrong as I need the data soon!

My @CloseorSchedule formula (below) puts text in a column for all groups based on the criteria of the formula. I need to count within each group how many rows show the textnresult of the formula below. One group many have 52 total rows but only 10 rows have the "Close or Schedule" text added based on the formula. I would also like the count to show as a percent... such as 19.2% in the footer for the group and the grand total.

@CloseorSchedule
if
(
{Consumer_Service_View.begin_date} <= DataDate-90 and
ucase({Employee_View.people_last_name}) = "ALZHEIMERS"
) OR
(
{Consumer_Service_View.begin_date} <= DataDate-365 and
ucase({Employee_View.people_last_name}) in ["RESPITE","OBRA"]
) OR
(
not(ucase({Employee_View.people_last_name}) in ["ALZHEIMERS","RESPITE","OBRA"]) and
{Consumer_Service_View.begin_date} <= DataDate-30
)

then ToText ("CLOSE! or SCHEDULE?") else {@Null}

The Report has 7 groupings. GH4 (consumer_view.people_first_name) displays the row data per consumer and GF2 (Employee_View.People_last_name) displays the group totals by employee. All the rest of the groups are not displayed in the report.

In trying to get the count of the text column I created another formula @CloseCount based the input above.

@CloseCount
if
(
{Consumer_Service_View.begin_date} <= DataDate-90 and
ucase({Employee_View.people_last_name}) = "ALZHEIMERS"
) OR
(
{Consumer_Service_View.begin_date} <= DataDate-365 and
ucase({Employee_View.people_last_name}) in ["RESPITE","OBRA"]
) OR
(
not(ucase({Employee_View.people_last_name}) in ["ALZHEIMERS","RESPITE","OBRA"]) and
{Consumer_Service_View.begin_date} <= DataDate-30
) then 1

This gives me ones or zeros and does align with the close or count text. When I try to count or sum on this formula I get the total number of records (58k+). If I try distinctcount then I get a count of 1.

I did your suggestion lbass and I get a count but it's still not correct although much closer than anything else.

It's probably something basic that I am just not getting but I don't know it.

What other information should I be giving?

 

[lbass]

Inserting a count on your first formula should work unless you have row inflation. If you do, then recreate the same formula but have it end with:

then (table.uniqueID} else {@null}

...as I suggested earlier. The ID should be whatever ID field is unique to each row in your report.

-LB