Author |
Message |
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 28 Feb 2014 at 2:12pm |
Guys thanks so much for the help! I will have to read up on how to do this, and I will. Won't this still cause an issue of having multiple records in the same month. If a ticket is opened and closed in the same month, won't it group two different line records in the month. Sorry, while I do code a bit - CR is fairly new to me.
Also if I may, please make sure I'm not going the wrong way to a solution. All I'm trying to show is all tickets opened or closed by month over a number of years. And I was hoping to just have a single line record in a month if the ticket was either opened or closed (or both) in that month.
Sorry if I'm being redundant...
|
IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 03 Mar 2014 at 3:39am |
If your ticket numbers are issued in sequence, you could find out the first ticket number for each month and make 12 separate but similar reports...one for each month.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Mar 2014 at 4:00am |
martin,
you may need to rethink how you want it counted as you will count a ticket twice if it runs over a month regardless of how you count it with an open or close in the same month.
If you pull in a unique idetifier you can always do a distinct count on that field.
|
IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 03 Mar 2014 at 6:19am |
Originally posted by DBlankmartin,
you may need to rethink how you want it counted as you will count a ticket twice if it runs over a month regardless of how you count it with an open or close in the same month.
If you pull in a unique idetifier you can always do a distinct count on that field.
Agree. He must be referencing the open ticket when creating the closing ticket...somehow...
|
IP Logged |
|
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 03 Mar 2014 at 7:03am |
Again thanks - I really appreciate the feedback! I need to show tickets opened and closed in each month. I have two other formula fields counting Opened and Closed tickets for a history line graph. So I think on the total counts I'm OK (if understand the way CR processes this). For example Total Closed running total field is based on Count of TicketNumber where ticket status is "C" / Reset Never. I think that is what you referenced doing above. That seems to map out correctly and produce a good line graph.
The detail section I want to show the tickets opened and closed by month and be able to have those totals at the bottom of the month. I suppose I could still use a running total field and reset it by month - correct? That would just leave how to show the line items for that month, and while it would be nice to have a single line, using the duplicate record approach should handle that - correct?
If all that is correct, I'm fuzzy on the last part - can you point to something to get me going in that route?
Again thanks so much...
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Mar 2014 at 7:18am |
The running total (RT) wil not work if the fields span over the month. The reset breaks it from doing what you want. But if you don't reset it you won't have your monthly counts.
You likely can get creative and use 24 running totals with conditional suppression but I think you are just looking for something simpler.
I think you should really consider how you are building your data set to accomidate your output need.
Do you have rights to creating a view or stored procedure in your data source or are you limited to crystal only solution? Can you write a command?
Edited by DBlank - 03 Mar 2014 at 7:19am
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 03 Mar 2014 at 7:49am |
depending on how many months, you could use variables, and only increment the correct one...or an array...though I don't know much about graphs, since no one ever asks me for them, so my solutions may not work.
|
IP Logged |
|
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 03 Mar 2014 at 7:55am |
DBlank - I'm not sure I understand RT not working. If I have 4 different RT's - 2 of which are tracking Open and Close by ticket number Count with reset set to "never" and 2 different tracking open and close set to ticket open/close date Count and "reset on the Group change" - will that not give me both monthly and full report counts? Those numbers are looking right in general. That is to say if I group on close only or open only - the monthly numbers look right as do the overall numbers. It is just that I can't do both.
I do not have access to the data source views, just limited command build. I was looking at the following over the weekend based on your initial post:
http://publib.boulder.ibm.com/infocenter/iadthelp/v6r0/index.jsp?topic=/com.businessobjects.integration.eclipse.doc.crdesigner/reportdesigner/crtskdefiningvirtualtablesbasedonacommandsqlquery.htm
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Mar 2014 at 8:50am |
The monthly RT's that span months are a problem because the one row will fall into only one month and therefore only b econuted in one RT.
I will guess you would be groupin on the open date which will mean the close of the ticket is in the wrong month never appears it the subsequent month. If you grouped on the close date then it would have missed the open record because it was not in teh prior months data.
As lockwelle suggests variables might work (they are about the same as the RT's here) but I doubt you would be able to graph them as you wanted.
|
IP Logged |
|
martinz
Newbie
Joined: 27 Feb 2014
Online Status: Offline
Posts: 12
|
Posted: 03 Mar 2014 at 9:02am |
I'm going off the assumption that at some point I will be able to list both opened and closed by month. So that is really the whole point of the original question. When I grouped on Open or Close - it was just to test the numbers, not how I want the final report to work. I was testing the logic - which did produce the proper monthly and overall total. But of course only looking at either Open or Close in a single run. So in essence two reports - one for Open and one for Close.
|
IP Logged |
|
|