Technical Questions: Count Specific Words in Field

Count Specific Words in Field

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=6103
Printed Date: 03 May 2024 at 1:36pm

Topic: Count Specific Words in Field

Posted By: justjon
Subject: Count Specific Words in Field
Date Posted: 16 Apr 2009 at 6:19am

Hi guys/girls,

I have a problem.

I'm trying to figure out a way to make an adding and subtracting count for a specific word(s) in a report.

For example.

If a field was to contain "User has succsessfully logged in" then I would want it to display 1, 2, 3 etc. BUT If it was to show "User has logged off" then I would want it to subtract from whichever number it was at. The starting number would obviously be 0.

Can anyone help?

Replies:

Posted By: DBlank
Date Posted: 16 Apr 2009 at 6:48am

Do you need to display this per row or are you just looking for a total sum per user (or grouping)?

Posted By: justjon
Date Posted: 16 Apr 2009 at 6:50am

Total sum per the whole report (So it searches the whole field of a report, through any amount of pages that are produced).

Posted By: DBlank
Date Posted: 16 Apr 2009 at 7:02am

I assumed there are only 2 values available in this field but added in the option for a 0 here just in case...

Create a formula as "LogInSum" (or whatever you want):

if {table.logintextfield}="User has succsessfully logged in" then 1 else

if {table.logintextfield}="User has logged off" then -1 else 0

You can now Sum the formula for the report for your total (or per group also if needed).

If you need to see a counting per row yuo can use it in a Running Total.

Posted By: justjon
Date Posted: 16 Apr 2009 at 7:06am

Thank you very much, where would I create the formula from scratch? As it would need to show the number - I tried a text field but this didn't seem to be the case.

Posted By: DBlank
Date Posted: 16 Apr 2009 at 7:15am

Sorry was not clear.

In your Field Explorer, right click on the Formula Fields and select New.

You will get a pop up window. Type in the name of the formula here (my example was LogInSum).

Place the formula there. Replace the {table.logintextfield} portion with the actual table and field names from your DB that has the string you are looking for.

Now the formula is available for placing in the report if you want. It is a nice way to validate that it is doing things as you want but is ugly and will need to be suppressed or removed from the display eventually.

It will only display a 1, 0 or -1.

If you want to see it counting through out the report you will need to use it in a running total using that formula field. Let me know if you need help with that.

Posted By: JohnT
Date Posted: 16 Apr 2009 at 7:30am

Your initial post said

If a field was to contain "User has succsessfully logged in".

Does that mean the string "User has successfully logged in" is all that would be in the field or does it mean the string would be there in addition to other strings ? Could it say something like "User has successfully logged in at 15:00 on April 15, 2009" and you want this because it contains the string ?

Posted By: DBlank
Date Posted: 16 Apr 2009 at 7:35am

John is correct that the counting formula would have to be altered if there is other possible text in the string as he suggested to look for the string in the field rather than an exact match as the first formula is doing:

if instr({table.logintextfield},"User has succsessfully logged in")>0 then 1 else

if instr({table.logintextfield},"User has logged off")>0 then -1 else 0

Posted By: justjon
Date Posted: 17 Apr 2009 at 5:03am

Hey,

Thanks a lot DB, if you could post help with the running total that would be grately appreciated, as I will need a count on the whole report. :)

Posted By: justjon
Date Posted: 17 Apr 2009 at 5:38am

Ok, have added the formula fine. However, the field sticks at 0.00, even though there are log ons, any ideas?

Posted By: DBlank
Date Posted: 17 Apr 2009 at 6:11am

Lets fix the formula then get the RT after that.
Does the string have any variation from the two items in the formula? Like replacing an actual user name for " user" or adding items at the end like john suggested?
And which version did you use?

Posted By: JohnT
Date Posted: 17 Apr 2009 at 11:52am

Dblank gave you the formula to use. Why don't you try dragging that field to your detail line to see if the field is correctly getting set to -1, 0, or 1. That might help you find the problem. This field must be set correctly before your counts will be accurate.

Posted By: DBlank
Date Posted: 17 Apr 2009 at 1:51pm

try this for the formula...

if instr({table.logintextfield},"logged in")>0 then 1 else

if instr({table.logintextfield},"logged off")>0 then -1 else 0

For your Running Total:

Right click on Running total and select NEW

Name it "LogCounter"

Field to summarize is : your formula field from above

Type of summary: SUM

Evaluate : for each record

Reset: Never

Place this on your details row and assuming the formula is fixed you should see it sum incrementally.

For a final total after the last record do insert summary as a SUM on the formula field and palce it in the report footer or just the RT field in the footer (as well as the details).