| Author |
Message |
Canacchione
Newbie
Joined: 12 Jan 2014
Location: Italy
Online Status: Offline
Posts: 6
|
  
Topic: weekly "not yet closed" tickets graph
Posted: 15 Jan 2014 at 10:14pm |
|
Good morning,
I am new with Crystal Reports 2008 and I have 2 questions.
I have made a report that show a lot of summary values for tickets opened and closed in year 2013, so, in my "selection" I have used the query "OPEN_TIME>#01/01/2013 00:00:00# and OPEN_TIME<#01/01/2014 00:00:00#"
Now, in this same report, I have to make a graph that will show the amount of tickets opened but not closed, week by week, from 30/08/2013 (my week start on friday at 18:00 o'clock), for example:
in week 30/08/2013-06/09/2013 we opened 27 tickets and we closed 11 tickets (delta=+16)
in week 06/09/2013-13/09/2013 we opened 41 tickets and we closed 24 tickets (delta=+17)
in week 13/09/2013-20/09/2013 we opened 45 tickets and we closed 38 tickets (delta=+7)
in week 20/09/2013-27/09/2013 we opened 47 tickets and we closed 52 tickets (delta=-5)
in week 27/09/2013-04/10/2013 we opened 49 tickets and we closed 58 tickets (delta=-9)
in week 04/10/2013-11/10/2013 we opened 48 tickets and we closed 62 tickets (delta=-14)
... and so on ...
In my graph I have to show values:
30/08/2013-06/09/2013: 16
06/09/2013-13/09/2013: 33 (16+17)
13/09/2013-20/09/2013: 40 (16+17+7)
20/09/2013-27/09/2013: 35 (16+17+7-5)
27/09/2013-04/10/2013: 36 (16+17+7-5-9)
04/10/2013-11/10/2013: 22 (16+17+7-5-9-14)
... and so on ...
Question 1:
How can I restrict my data so that in graph I will consider only tickets with OPEN_TIME>#30/08/2013 18:00:00# ?
The thing I have thought is that I can create another report (with only this graph) in which my "selection" is "OPEN_TIME>#30/08/2013 18:00:00# and OPEN_TIME<#03/01/2014 18:00:00#"; but is this the right way to proceed?
Question 2:
I have made 2 formulas that identify the opening week and the closing week for every ticket (using DatePart function), but I have no idea on how to extract and visualize my values in graph (it's something like a running total of my "delta" but I am not sure); and, sincerely, I don't know if it is possible with Crystal Reports!
Thank you in advance
Kind regards
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Jan 2014 at 8:30am |
question 1.
a. you can use a sub report which limits your data
b. group your data into inside and outside your range and place the graph inside this group header but suppress it for the group you don't want to see it on
c. use NULLing trick to return NULL on records outside you date range and that would exclude them from the chart
question2
are the open dates and close dates on the same row of data for each ticket?
If so I am not sure how you are getting your current values unless you are making unique running totals per perios and running through all of the data rows...?
You might consider using a store proc or a command to alter your data the table onto itself for open and close records to be separated.
|
| IP Logged |
|
Canacchione
Newbie
Joined: 12 Jan 2014
Location: Italy
Online Status: Offline
Posts: 6
|
Posted: 16 Jan 2014 at 10:59pm |
|
Hi DBlank, thank you for answer
I am glad to see that the "a" answer of question 1 was exactly like mine; now I will try to follow the "c" answer, making a formula that returns NULL value if OPEN_DATE<#03/01/2014 18:00:00#
Question 2
I am not sure to have understood your question but I'll try to answer: every ticket have his OPEN_DATE and CLOSE_DATE (except for tickets that are not yet closed at the moment of report generation, these tickets have CLOSE_DATE=NULL).
Every week I have to count the amount of tickets that are yet in a OPEN status at the end of the week (just to show the progress, week by week, of my assignment group in ability of resolving tickets), but I would avoid to create a new array on DB that counts every week the delta between the opened tickets and the closed tickets from 30th of August to the date of report generation.
I need to know what I have to use as variables in my graph.
First I thought to create a RT that count the tickets, week by week, opened in every week, and a RT that count the tickets, week by week, closed in every week; but I need to display in my graph the difference between these 2 RT week by week and I am not able to do.
Please, every suggestion is useful: can I use something in Crystal Reports that avoid me to create something, like store procedure, in DB?
thank you very much
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Jan 2014 at 4:35am |
Some of the issue you will run into is that a row is only grouped once and it is grouped, in your set up, on open date. However it may not be closed until the next week. Therefore it will not be counted as closed or it will be counted as closed in the wrong week.
What I think you need is one table with a date field and a int field with a 1 or -1.
If the date field is an open date ticket then it is a +1, if the date field is a close date then a -1.
then you can group on the date and sum the int field and allow one ticket to appear across weeks (opened inh week 1 and closed in week 2)
Then you can just use a simple graph.
A Command object in Crystal would allow you to do this if you did not want to make a stored proc.
|
| IP Logged |
|
Canacchione
Newbie
Joined: 12 Jan 2014
Location: Italy
Online Status: Offline
Posts: 6
|
Posted: 21 Jan 2014 at 11:33pm |
how can I create a table with this 2 values in Crystal Reports? do I have to create a formula for every week from 30/08/2013 to the print date?
can I create an array in Crystal Reports with 2 entry (one for the week number from 30/08/2013 and one for the amount of "not yet closed" tickets) and use it to be graphed?
what Command Object do I have to use? on graph?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Jan 2014 at 5:14am |
a command object is like a built in stored procedure
you can write it as two select statments to union your data sets together
You will have to clean it up some but the idea is like this:
select ticketid,opendate as datefield, 1 as counter
from table
where opendate is >8/30/2013
UNION
select ticketid,closedate as datefield, (-1) as counter
from table
where closedate is >8/30/2013
This will give you one row of table with a seperate row for each open and close ticket with the same date field to group on
then you can group on the date field set to the week and sum the 'counter' field
Edited by DBlank - 22 Jan 2014 at 5:14am
|
| IP Logged |
|
Canacchione
Newbie
Joined: 12 Jan 2014
Location: Italy
Online Status: Offline
Posts: 6
|
Posted: 23 Jan 2014 at 10:04pm |
Hi DBlank, that's exactly what I was looking for!
Now everything seems to be cool, except of one thing: when Crystal Reports generate the Print Preview it seems to work with more than 1000000 records! The result of my command (in SQL server) generate only 8000 records (the UNION of my table).
I also eliminate the link between the source table (table with every ticket from 02/04/2013 to now) and the result of Command Object (the UNION of table that generate 8000 records).
I really don't understand why my Preview elaborate more than 1000000 records (and crash, if I do not stop the work!).
P.S. I don't know how to give you points for your answer on this forum, please tell me also this
|
| IP Logged |
|
Canacchione
Newbie
Joined: 12 Jan 2014
Location: Italy
Online Status: Offline
Posts: 6
|
Posted: 24 Jan 2014 at 4:02am |
I solved the problem of 1000000 records (I use a subreport to avoid the link) but now I have the last thing to do:
I can use the sum of the counter in the graph to see the "delta" of "closed ticket" minus "open ticket" week by week
Now, to show the progress of "ticket not yet closed" I have to use a Running Total of the sum of counter that increments week by week; but my RT gives only values like always -1 (week by week), so that in graph I see:
-1, -2, -3, -4, -5, -6, -7, -8, -9, etc. etc.
(a line from 0 to -21)
can you help in this?
thank you
|
| IP Logged |
|
Canacchione
Newbie
Joined: 12 Jan 2014
Location: Italy
Online Status: Offline
Posts: 6
|
Posted: 28 Jan 2014 at 2:34am |
Sorry, I was completely mad!!!
I was seeing another thing, my running total is perfect
Thank you very much DBlank, you were very helpful
Don't know how to thank you
|
| IP Logged |
|
|
|
