| Author |
Message |
justjon
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 10
|
   Topic: Count Specific Words in Field
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?
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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)?
|
|
IP Logged |
|
justjon
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 10
|
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).
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
justjon
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 10
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
Edited by DBlank - 16 Apr 2009 at 7:17am
|
|
IP Logged |
|
JohnT
Groupie
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
|
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 ?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
justjon
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 10
|
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. :)
Edited by justjon - 17 Apr 2009 at 5:22am
|
|
IP Logged |
|
justjon
Newbie
Joined: 05 Mar 2009
Online Status: Offline
Posts: 10
|
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?
|
|
IP Logged |
|
|
|
