We help IT Professionals succeed at work.

How do I create a Bar Chart in Crystal Reports that combines values from two subreports?

Medium Priority
4,658 Views
Last Modified: 2013-11-15
Hello,

I am trying to create a simple bar chart in Crystal that shows Product Issue activity per month. It's really simple: total opened in the month, and total closed. I created a fromDate parameter and a toDate parameter (parameterX), and my "opened" sub-report has the DateOpened = parameterX, and my "closed" sub-report has the DateClosed = parameterX. This report seems to work fine - pulling all Product Isses opened per month, and all Product Issues closed per month, but now I would like my data in a chart that illustrates opened and closed side by side per month, based on parameterX. Any ideas? Can I even create a chart where my horizontal axis is based on the dates entered for parameterX, which actually corresponds to two separate date fields in my database (opened and closed)?

Any help would be appreciated. Thanks!
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
What data do you have in a record?

mlmcc

Author

Commented:
I'm just pulling the unique Issue Number (String) and the date opened (date) and date closed (date).
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I assume open reports have a NULL in the closed field.

I'll build a small databse and see what I can do.

mlmcc

Author

Commented:
Thanks. Yes, they have a NULL in the closed field.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:

Author

Commented:
Yes it is. It looks like I'll have to handle this before the data gets into Crystal with a Stored Procedure. I have yet to see a solution using Crystal itself that will allow me to group both records opened and closed by month and year, since the grouping has to be by either dateOpened or dateClosed. I tried working with dateVariables, but that didn't get me anywhere.

Thanks for your help,

Mark
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
As stated in the pther questiion, a record can be in one group or the other.

One way to try this would be to use a DATABASE COMMAND in Crystal.  I don't know if it will work or not but something like

SELECT MyTable.*, 'Closed' as OpenCloseField FROM Table1  WHERE {ClosedDateField} In {?DateRange}
UNION ALL
SELECT MyTable.*, 'OPEN' as OpenCloseField FROM Table1  WHERE {OpenDateField} In {?DateRange}

If it doesn't work in the report it may work in the database.

In that way each record will be either OPEN, Closed, or appear twice with one open and one closed.

mlmcc

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

Ask the Experts

Author

Commented:
Solved!

Thanks for the tip. I was not familiar with Union Queries before your post. I created a Database Command to join both date fields and list by IncidentID. This allows me to group everything by month. Here's the command that I am using:

SELECT DateClosed as 'Date', StandardShortInteger001 as 'Incident ID' FROM dbo.CorrectiveAction
UNION ALL
SELECT Date_Incident as 'Date', IncidentID as 'Incident ID' FROM dbo.Incident

I then included both my Incident and CorrectiveAction tables (linked by the unique IncidentID), and was able to list my DateClosed and Date_Incident fields, which I flagged (1 if they = Command.Date, else 0), which tells me if the date in the joined Command.Date field is an 'Open' or a 'Closed'. I grouped by my joined Command.Date field (by month), and charted my graph. My DateRange parameter is tied to the Command.Date field. I'm not sure if this is the most efficient method, but it works.

Thank you Experts for heading me in the right direction.

-Mark
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.