Solved

Opened and Closed Trendline

Posted on 2011-02-20
8
238 Views
Last Modified: 2012-05-11
I have a customercases table contains the caseID, the createddatetime, and the resolveddatetime.

For a rolling six month period, I need to chart the number of cases opened and the number cases resolved per week on a single line graph (two lines).

What's the best way of formulating the selection query and how can I graph the two values?  
0
Comment
Question by:c0fee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 34947741
Try using a crosstab to display your data,...then right-click the top left corner of the crosstab and choose the CHART option....pick your LINE chart and format as needed....

hope it helps....
0
 
LVL 2

Author Comment

by:c0fee
ID: 34948645
Hi,Thanks but I don't think this works quite right.  Using the table alone, doesn't provide me a "global" date to group the values by.  I need some means of saying "for every day that exists in my date time range parameter, count the distinct number of CaseIDs that have a resolveddatetime for each day, and also count the distinct number of CaseIDs that have a createddatetime for each day".
0
 
LVL 35

Expert Comment

by:James0628
ID: 34950140
If I'm following you, the basic problem is that each record could be included in two different groups (createddatetime and resolveddatetime).  I think you may need to create a table will all of the dates in it and then JOIN the customercases table to that twice, once based on createddatetime and once based on resolveddatetime, to get separate "created" and "resolved" counts for each date.

 I'm not sure if you'll be able to do that directly in CR.  It may depend on exactly what you want.  If you only want to see the dates that had a "created" or "resolved" record, then you might be fine.  But if you want to include the dates that had neither, you're going to need to use a LEFT OUTER JOIN, but CR sometimes changes those to INNER JOINS, in which case you won't get those dates.  So, you may need to write your own query (eg. in a CR Command) that creates the counts for the report.

 James
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 2

Author Comment

by:c0fee
ID: 34983753
Thanks James.  I need a way to do it in CR.  It would be very difficult for me to pass the request to have a database table filled with just dates.  Any other ideas?
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 34986810
Well, it doesn't necessarily have to be a table.  It could be an Excel file, for example.  That presents its own limitations, starting with the fact that probably only one person will be able to access the file (ie. run the report) at a time, but it is a possibility.

 Assuming that I was correct and you want to include each record in two different and overlapping groups, one based on createddatetime and one based on resolveddatetime, then I don't know if there's any other/good way to do that in CR.  Then again, I don't work with charts much.  If no one else suggests anything, you could try the "Request Attention" link to see if you can get someone else to join in.

 James
0
 
LVL 17

Expert Comment

by:MIKE
ID: 34988901
Manual Crosstab using the actual report as the basis. IFyou data will work within a Crosstab, but Crystal's Crosstab won't handle the advanced formatting or placement of the cell logic, then you can switch over to a MANUAL CROSSTAB which is basically building ALL of the logic within the reporting sections so that the final report has the appearance of the CR automatic crosstab, but in actuality it is very advanced Crystal Report....

Hope it helps,....
0
 
LVL 2

Author Comment

by:c0fee
ID: 35031041
Thanks to both of you!  I decided to create a dummy view that has each date required.  CRXIUser2005 - I must be really dense because I don't know how to switch to a manual crosstab.  But I'll keep it in mind for future research.
0
 
LVL 35

Expert Comment

by:James0628
ID: 35042731
"Manual cross-tab" just means that you create something that looks like a cross-tab, but you actually do everything yourself.  Create formulas that are used to produce the data for each column and that kind of thing.  I don't really think that that would help with your basic problem of trying to include records in two different groups (one based on createddatetime and one based on resolveddatetime).

 James
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question