[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Summarizing two separate values in Crystal Reports per Month

Posted on 2009-02-12
7
Medium Priority
?
855 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:Marcos27
  • 3
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 23632749
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
 

Author Comment

by:Marcos27
ID: 23633217
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
 
LVL 35

Expert Comment

by:James0628
ID: 23638923
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
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
LVL 17

Expert Comment

by:MIKE
ID: 23642674
I'd recommend Stored Procedure or some other SQL Script....as the best way to handle the counts efficiently.
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1500 total points
ID: 23644522
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
 

Author Comment

by:Marcos27
ID: 23675965
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
 
LVL 35

Expert Comment

by:James0628
ID: 23676851
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

830 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