c0fee
asked on
Opened and Closed Trendline
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?
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?
ASKER
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".
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
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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,....
Hope it helps,....
ASKER
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.
"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
James
hope it helps....