Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4622
  • Last Modified:

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!
0
Marcos27
Asked:
Marcos27
  • 4
  • 4
1 Solution
 
mlmccCommented:
What data do you have in a record?

mlmcc
0
 
Marcos27Author Commented:
I'm just pulling the unique Issue Number (String) and the date opened (date) and date closed (date).
0
 
mlmccCommented:
I assume open reports have a NULL in the closed field.

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

mlmcc
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Marcos27Author Commented:
Thanks. Yes, they have a NULL in the closed field.
0
 
mlmccCommented:
0
 
Marcos27Author 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
0
 
mlmccCommented:
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

0
 
Marcos27Author 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
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now