sort repeated incident
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6433
Printed Date: 05 May 2024 at 1:55am
Topic: sort repeated incident
Posted By: rp123
Subject: sort repeated incident
Date Posted: 21 May 2009 at 6:07pm
Hi All,
I have a database field {probsummarym1.brief.description} with following data:
| 1 |
03/03/2009 at 11:40:47 Process A
is down on Shine1 |
| 2 |
03/03/2009 at 11:51:48 Process A is down on Shine1 |
| 3 |
03/03/2009 at 11:40:49 Process B is down on Shine2 |
| 4 |
03/03/2009 at 2:28:51 Process B is down on Shine2 |
| 5 |
03/03/2009 at 11:40:52 Process C is down on Shine3 |
| 6 |
03/03/2009 at 1:40:40 Process C is down on Shine3 |
| 7 |
03/03/2009 at 11:40:52 Process D is down on Shine3 |
| 8 |
03/04/2009 at 16:40:55 Process D is down on Shine3 |
| 9 |
beijing IS DOWN after pinging server |
| 10 |
beijing IS DOWN after pinging server |
| 11 |
honolulu IS DOWN after pinging server |
| 12 |
honolulu IS DOWN after pinging server |
| 13 |
saigon IS DOWN after pinging server |
| 14 |
saigon IS DOWN after pinging server |
I wanted to sort the data and bring the repeated incident so I created group on {probsummarym1.brief.description} but the problem is that its just bring the repeated data with the common heading like line no: 9,10,11,12,13,14 only but not the line 1...to 9.
Because the data in line 1..9 has different times that's why it do not bring the data in the report as you can see its a repeated incident with a different timing?
My question is how can I bring the data from line 1..9 in my final report as well?
Please help me.
rp123
-------------
rp
|
Replies:
Posted By: DBlank
Date Posted: 22 May 2009 at 2:56pm
|
If I understand you correctly...
You will need to create a formula field from the string that strips data into a unified format for grouping and then group on the formula field.
That being said I am not sure how you are defining a "repeated incident" that you want to group together. My guess would be that you just need to strip out the date time part if it exists. If that is correct your formula field would be:
if isdate(left({probsummarym1.brief.description} mailto:%7b@_%7d,10 - ,10 )) then
right({probsummarym1.brief.description} mailto:%7b@_%7d,len%28%7b@_%7d%29-instr%28%7b@_%7d,Process%29+1 - ,len({probsummarym1.brief.description})-instr({probsummarym1.brief.description},"Process")+1 )
else {probsummarym1.brief.description}
|
Posted By: rp123
Date Posted: 22 May 2009 at 9:10pm
Yes you are right, I want remove the date time from the data?
Anyway I ran your formula its not removing the datetime from data.
-------------
rp
|
Posted By: DBlank
Date Posted: 23 May 2009 at 7:13am
looks like I forgot the first condition ending...try:
if isdate(left({probsummarym1.brief.description} mailto:%7b@_%7d,10 - ,10 ))=true then
right({probsummarym1.brief.description} mailto:%7b@_%7d,len%28%7b@_%7d%29-instr%28%7b@_%7d,Process%29+1 - ,len({probsummarym1.brief.description})-instr({probsummarym1.brief.description},"Process")+1 )
else {probsummarym1.brief.description}
if that does not work try:
if instr({probsummarym1.brief.description},"Process")>0 then
right({probsummarym1.brief.description} mailto:%7b@_%7d,len%28%7b@_%7d%29-instr%28%7b@_%7d,Process%29+1 - ,len({probsummarym1.brief.description})-instr({probsummarym1.brief.description},"Process")+1 )
else {probsummarym1.brief.description}
|
Posted By: rp123
Date Posted: 27 May 2009 at 6:28pm
Hi,
I tried both formulas but its still working.
Thankx
-------------
rp
|
Posted By: DBlank
Date Posted: 27 May 2009 at 7:50pm
|
Just to be clear here. You created a formula field with the formula of:
if isdate(left({probsummarym1.brief.description} mailto:%7b@_%7d,10 - ,10 ))=true then
right({probsummarym1.brief.description} mailto:%7b@_%7d,len%28%7b@_%7d%29-instr%28%7b@_%7d,Process%29+1 - ,len({probsummarym1.brief.description})-instr({probsummarym1.brief.description},"Process")+1 )
else {probsummarym1.brief.description}
You placed this formula field on you report. THis formula field does not strip the date out.
What is it returning?
Also, do all of the rows that have a date in them also have the word 'Process'?
|
Posted By: rp123
Date Posted: 27 May 2009 at 8:30pm
I created the formula and put it in my detail field.
So when I run the report it still bringing the same data w/o date/time strip off from the data.
The data with date/time format do not usually have a "process" field it can be anything like "patrol" or "host" etc.
-------------
rp
|
Posted By: rahulwalawalkar
Date Posted: 28 May 2009 at 12:54am
|
Hi
what i would suggest to the DBblanks formula is to replace the then part with below code and see if it works.
right({probsummarym1.brief.description} mailto:%7b@_%7d,len%28%7b@_%7d%29-instr%28%7b@_%7d,Process%29+1 - , mailto:%7b@ext%7d%29-instrrev%28%7b@ext%7d,:%29-2 - len({probsummarym1.brief.description} mailto:%7b@ext%7d%29-instrrev%28%7b@ext%7d,:%29-2 - )-instrrev({probsummarym1.brief.description},":")-2 )
cheers
Rahul
|
Posted By: DBlank
Date Posted: 28 May 2009 at 7:51am
|
Thanks Rahul.
RP, the formula I gave you was based under the impression that all of the "date rows" had the word 'Process' in them. It was looking for that word and trimming from that. Since this is not the case it was not working.
Rahul's suggestion is doing the same process but keying off the first time the ':' character appears reading right to left.
To avoid further confusion there are two things that must be in place for this to work for you.
1. When the row starts with a date there is never a : field to the right of the seconds indicator in the string we are trimming. You will have to scan your data to figure that out.
2. The first part of the formula is working which is looking a the first 10 characters and seeing if it is a date. You can create a formula field to check this. isdate(left({probsummarym1.brief.description},10))
If you put this in your report it should say TRUE for any date rows and FALSE for anything else.
IF both of these are OK then use Rahuls formula as:
if isdate(left({probsummarym1.brief.description},10))=true then
right({probsummarym1.brief.description},len({probsummarym1.brief.description})-instrrev({probsummarym1.brief.description},":")-2)
else {probsummarym1.brief.description}
Hope this clears it up for you.
|
Posted By: rp123
Date Posted: 30 May 2009 at 7:27pm
Hi Guys,
Thanks a lot. Its working now.
I need some more help bcoz I have to create the charts based on repeated incidents:
1. Is there a way we can eliminate the single incidents and keep the repeated incidents in the report?
2. I want assign a single word to the incident .i.e.
Process H1 is down on qdvp14 --- list as "Process down" in the report
shine127 IS DOWN after pinging server - list as "server down" in the report
Instrument is down - list as "instrument issue" in the report
-------------
rp
|
Posted By: DBlank
Date Posted: 30 May 2009 at 7:48pm
|
1. you can do a count on your group records and add a select statement count(table.field,groupfield)>1 ...this has to go in the group select expert section.
2. you can use an if-then -else-if statment to create single wording... Probably use an instr function but you have to have the logic work for all of your naming conventions and go thru every version if then per word you want grouped on.
it will be something like:
if instr({probsummarym1.brief.description},"Process H1 is down on qdvp14")>0 then "Process Down" else
if instr({probsummarym1.brief.description},"shine127 IS DOWN")>0 then "Server Down" else
if instr({probsummarym1.brief.description},"Instrument is down")>0 then "Instrument Issue" else ...
|
Posted By: rp123
Date Posted: 04 Jun 2009 at 1:39am
Thanks for the reply.
I Actually I want to rephrase my question:
I want to list the word which is written after the "process" in the brief description: Here is the example:
| 1 |
03/03/2009 at 11:40:47 Process A
is down on Shine1 |
| 2 |
03/03/2009 at 11:51:48 Process A is down on Shine1 |
| 3 |
03/03/2009 at 11:40:49 Process B is down on Shine2 |
| 4 |
03/03/2009 at 2:28:51 Process B is down on Shine2 |
| 5 |
03/03/2009 at 11:40:52 Process C is down on Shine3 |
| 6 |
03/03/2009 at 1:40:40 Process C is down on Shine3 |
| 7 |
03/03/2009 at 11:40:52 Process D is down on Shine3 |
| 8 |
03/04/2009 at 16:40:55 Process D is down on Shine3
|
So In this case I want to list "Process D is down on Shine3" as D in the report. So whatever written after word "process" I want to list that in the report.
Can you please help. I am very new to crystal report and I am learning phase.
Thankx
-------------
rp
|
Posted By: DBlank
Date Posted: 04 Jun 2009 at 7:31am
|
Is this always a one character repsonse as in your examples above
Process A
Process B
Process C
or are these really words...
Process Alpha
Process Beta
Process Sigma
?
|
Posted By: rp123
Date Posted: 04 Jun 2009 at 6:36pm
|
Yes they are listed as follows:
Process Alpha
Process Beta
Process Sigma.............
-------------
rp
|
Posted By: DBlank
Date Posted: 05 Jun 2009 at 7:45am
|
You can handle this by creating a formula field to strip the original field down to everything after the word "process" then use another formula field to strip that down to the first word left. I know there is a way to do this in one formuloa but it is eluding me right now.
StripFormula:
if instr({probsummarym1.brief.description},"Process")>0 then mid({probsummarym1.brief.description} mailto:%7b@_%7d,instr%28%7b@_%7d,Process%29+8 - ,instr({probsummarym1.brief.description},"Process")+8 ) else {probsummarym1.brief.description}
To get the final formula result (e.g. Process alpha) another fomrula as "Process Result":
if instr({probsummarym1.brief.description},"Process")>0 then "Process " + left( mailto:%7b@StripFormula%7d,instr%28%7b@StripFormula - {@StripFormula},instr({@StripFormula }," ")) else
{probsummarym1.brief.description}
|
|