We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Summarizing two separate values in Crystal Reports per Month

Marcos27
Marcos27 asked
on
Medium Priority
886 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
Comment
Watch Question

MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

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 . . .
CERTIFIED EXPERT

Commented:
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
MIKESoftware Solutions Consultant
CERTIFIED EXPERT
Top Expert 2006

Commented:
I'd recommend Stored Procedure or some other SQL Script....as the best way to handle the counts efficiently.
CERTIFIED EXPERT
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
CERTIFIED EXPERT

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.