Row Count within 48 hours.
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=8681
Printed Date: 03 May 2024 at 10:06pm
Topic: Row Count within 48 hours.
Posted By: hegazy
Subject: Row Count within 48 hours.
Date Posted: 18 Dec 2009 at 8:49am
|
Good morning everyone. For the past few days I've been reading this site trying to understand crystal reports as this application is still new to me.
I am trying to get a number count on the number of tickets opened within the 48 hours. I have been unsuccessful in getting a ticket count. So I have: if {Incident.LastStatus} in ["Active", "Waiting", "Logged"] then if {Incident.CreatedDateTime} < currentdate and minute({Incident.CreatedDateTime}) >= 2880 then count ({Incident.LastStatus}) I am not sure if the minute() function is used properly here. Thank you. |
Replies:
Posted By: DBlank
Date Posted: 18 Dec 2009 at 9:35am
|
I think I would just use a dateadd of minutes here:
{Incident.CreatedDateTime} in dateadd('n',-2880,currentdatetime) to currentdatetime
|
Posted By: DBlank
Date Posted: 18 Dec 2009 at 9:59am
|
As for counting them here are 3 main ways (assuming you do not have duplicate data rows that you have not mentioned):
1. Create a formula field to make a 1 or 0 based on your critieria then sum that formula field:
Formula sample...if {Incident.LastStatus} in ["Active", "Waiting", "Logged"] and {Incident.CreatedDateTime} in dateadd('n',-2880,currentdatetime) to currentdatetime then 1 else 0
2. Use a Running Total to conditionally count the records
Field to summarize = incidentID (or {Incident.CreatedDateTime} if you want)
Type of Summary = Count
Evaluate= Use a formula
Enter the formula {Incident.LastStatus} in ["Active", "Waiting", "Logged"] and {Incident.CreatedDateTime} in dateadd('n',-2880,currentdatetime) to currentdatetime
Reset= Never
Place on report footer to see total.
3. Use variable formula to do the same thing as #2
|
Posted By: hegazy
Date Posted: 18 Dec 2009 at 10:33am
|
Thank you very much for the quick response. I didn't know that I can use dateadd() function for this or running totals.
Thanks again! |
Posted By: hegazy
Date Posted: 23 Dec 2009 at 6:27am
|
Another question:
Would the dateadd() be the same for an overall date meaning I have a fixed date 1/1/2009(beginning of the year) to the currentdatetime? |
Posted By: DBlank
Date Posted: 23 Dec 2009 at 7:47am
|
Do you mean you want to use a select statement to find any records with dates in this year?
You can use the dateadd() or dateserial() or the easiest would be
{table.datefield} in yeartodate
|
Posted By: hegazy
Date Posted: 23 Dec 2009 at 8:02am
|
Originally posted by DBlank
Do you mean you want to use a select statement to find any records with dates in this year? You can use the dateadd() or dateserial() or the easiest would be
{table.datefield} in yeartodate That is correct. When I put the running total (named overall) I get 0 entries (Using code below): {Incident.Source} in ["Logged", "Active","Waiting"] and {Incident.CreatedDateTime} in YearToDate |
Posted By: DBlank
Date Posted: 23 Dec 2009 at 8:05am
|
Hmmm.
Try:
{Incident.Source} in ["Logged", "Active","Waiting"] and
{Incident.CreatedDateTime} in datetime(year(currentdate),1,1,0,0,0) to currentdatetime |
Posted By: hegazy
Date Posted: 23 Dec 2009 at 9:12am
|
Hmmm...I'm still receiving 0 as a total result. Why would I be able to retrieve data for the 48 hours but not for the beginning of the year?
Also, may you explain to me what the 1's and 0's are for? {Incident.CreatedDateTime} in datetime(year(currentdate),1,1,0,0,0) Thank you. |
Posted By: DBlank
Date Posted: 23 Dec 2009 at 9:25am
|
Datetime(yyyy,month,day,hour,minute,second)...
1 and 0 are (in this case) to give you jan 1 at midnight...
month(1=jan), day(1=first),hours(0=midnight), minutes(0=none) and seconds(0=none)
Where exactly are you using this formula (select expert, formula, running total?)
You can test a it by creating it as a formula field and placing it on the detail section. It will place a TRUE if the condition is met and a FALSE if not.
Break your condition into 2 pieces and test each one to see where it is breaking.
Formula 1= {Incident.Source} in ["Logged", "Active","Waiting"]
Place it next to the SOurce field on details to check it
Formula 2= {Incident.CreatedDateTime} in datetime(year(currentdate),1,1,0,0,0) to currentdatetime
Place it next to the Createdatetime field on details to check it
What is working and what is not?
|
Posted By: hegazy
Date Posted: 23 Dec 2009 at 9:42am
|
I was doing it as a running total field (as I did with the 48 hour previously asked).
I broke it up in 2 parts in formula fields: Formula 1: {Incident.Source} in ["Logged", "Active","Waiting"] came out as false. Formula 2: whether using : {Incident.CreatedDateTime} in datetime(year(currentdate),1,1,0,0,0) to currentdatetime or {Incident.CreatedDateTime} in yeartodate These comes out as true. |
Posted By: DBlank
Date Posted: 23 Dec 2009 at 9:45am
| Did the formula 1 come out FALSE when you expected it to be TRUE? |
Posted By: hegazy
Date Posted: 23 Dec 2009 at 9:47am
|
You know what, I have found the solution to my error.
It seems like I have mixed up a different table from the one I have posted for this issue. It is now working. Thanks for your help and I apologize for the mix up!! 
|
Posted By: DBlank
Date Posted: 23 Dec 2009 at 9:50am
|
No problem and glad you got it to work.
If I am getting unexpected results I usualy deconstruct the formula(s) and look for the 'broken' part. You can usually figure out where the mistake is from there or at least point yourself in a good direction.
|
Posted By: Vertex
Date Posted: 23 Dec 2009 at 11:51am
|
count ({Order.OrderID}, {Order.CreateDateTime}, "monthly") ...for month so what can we count records for current year like same above technique "yearly" is not working here at the place of "Monthly" ------------- Hi Peoples |
Posted By: DBlank
Date Posted: 23 Dec 2009 at 11:57am
| "annually" but you have to have a group that supports this. |
Posted By: Vertex
Date Posted: 23 Dec 2009 at 12:06pm
|
Thanks "annually" is working here ------------- Hi Peoples |