How do I create a Bar Chart in Crystal Reports that combines values from two subreports?
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!
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!
ASKER
I'm just pulling the unique Issue Number (String) and the date opened (date) and date closed (date).
I assume open reports have a NULL in the closed field.
I'll build a small databse and see what I can do.
mlmcc
I'll build a small databse and see what I can do.
mlmcc
ASKER
Thanks. Yes, they have a NULL in the closed field.
Is this related to this one?
https://www.experts-exchange.com/questions/24138480/Summarizing-two-separate-values-in-Crystal-Reports-per-Month.html
mlmcc
https://www.experts-exchange.com/questions/24138480/Summarizing-two-separate-values-in-Crystal-Reports-per-Month.html
mlmcc
ASKER
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
Thanks for your help,
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
mlmcc