Summarizing two separate values in Crystal Reports per Month

Hello Experts,

I am trying to summarize two separate values that relate to one single date range within a Crystal Report. So, I have a date range parameter, and I am pulling all records that have an OpenDate within that date range, and all records that have a ClosedDate within that date range. I have used formula fields to flag all records opened and all records closed per month (see attached file). Now, I just need to summarize my results for each month within my date range. So, for example, November 2008 should have 3 total opened and 7 total closed. Any ideas on how to formulate this? I would like to tally my results in a side by side bar chart.

Thanks!
Marcos27

Crystal-Reports---Complaints-per.pdf
Marcos27Asked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
Good point about the efficiency.  I got so tied up in trying to think of a way to get the counts, I wasn't thinking about that at all.  A subreport would have to basically reread the same records as the main report, so if the main report is reading a lot of records, subreports may not be a practical solution.

 James
0
 
MIKESoftware Solutions ConsultantCommented:
You'll need to use Variables to do this. Set them to ZERO in the GROUP HEADER and then them to capture and calculate the SUM by Month.

What are the contents of your OPEN FLAG and CLOSED FLAG formulas, please post them here.

Thanks
M
0
 
Marcos27Author Commented:
Open Flag = if ({Incident.Date_Incident}={?Date Range}) then 1 else 0
Closed Flag = if ({CorrectiveAction.DateClosed}={?Date Range}) then 1 else 0

The problem that I'm having is that I can't group them all together by month because each month is determined by a different date field . . .
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
James0628Commented:
I think the only way that you might be able to get these counts in a single chart is if you do them before the data gets to CR.  For example, if you're using MS SQL, you could create a stored procedure or view, or a CR "command", which is similar to a stored procedure, and use sub-queries to generate those counts, so that the report doesn't have to do it.  I don't know if you'd be able to get those counts in a chart together either, but it seems like your best bet.

 You could use subreports to produce the counts, with one subreport for each field.  Of course you couldn't get the counts for the two fields combined in a single chart that way, but you might be able to have each subreport produce a chart and put the two subreports (ie. charts) side by side.

 Forgetting the chart, you could get counts for the two date fields using variables.  I'd use arrays, with a "control" array that had one entry for each month, and two other arrays with the counts for each month for those two fields.  But there's no way that you could get those counts in a chart.

 One other possibility might be to have a control table with an entry for every date, or just every month, in the date range and then join from that to the tables with the date fields.  So, for example, you'd have a record in the control table with 02/13/09 and it would join with every record in the Incident table and the CorrectiveAction table where the corresponding date field matched.  That way you've grouped the two date fields together.  Of course I have no idea how that will fit in with the rest of your report.  Probably not very well, unless these counts are the main, if not only, thing on the report.  Hmm.  Maybe you could do that in a subreport, and have the subreport produce your combined chart.  I think that might work.

 James
0
 
MIKESoftware Solutions ConsultantCommented:
I'd recommend Stored Procedure or some other SQL Script....as the best way to handle the counts efficiently.
0
 
Marcos27Author Commented:
Thank you James!

 I was able to create a Union Query via the Database Command within Crystal, which allowed me to join my date fields. I then used other tables in my report to determine if the date was an 'Open' date or a 'Closed' date. I then flagged my 'Open' and 'Closed' dates, and grouped by month from the joined Date field, and charted the results.

For more details on the solution, see this related question:
http://www.experts-exchange.com/Database/Data_Manipulation/Data_Mining/Q_24130961.html#a23654171

Thanks again everyone.
- Mark
0
 
James0628Commented:
I see.  So you used a query to get a list of the open and closed dates, with the difference being that instead of them being in two different fields, they were now in the same field, so you could group them together.  Nice.  Thanks for posting the solution.

 And you're welcome.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.