Line Graph - Display correct values for "received" and "completed" service requests

I'm new to Line graphs, so any help with this would be appreciated.  I'd like to create a line graph in Crystal Reports with the last 24 months displayed on the x-axis.  
On the y-axis I need a integer value that represents a count for:
      a."Received" support tickets
      b."Completed" support tickets.

I've attached a screen shot of what it should look like (I was able to mock something up quickly in Excel).  Here is the problem that I'm having with creating this graph in Crystal...my counts are not showing correctly for "Completed" support tickets because I'm using the "Received" date (sort order = for each month) along the x-axis.

If I switch the "On Change Of" option from "Received" date to "Completed" date, then my "completed" count is correct, but my "Received" count is incorrect.  What are my options for getting around this problem or maybe I've missed something all together?

Hope I've explained this good enough for someone to provide guidance.  Let me know if more explanation is needed. Excel Line graph that I need to reproduce in Crystal
w00kAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
You may have to use 2 charts.

Have you tried the multiple axis chart option?

mlmcc
0
w00kAuthor Commented:
Multiple axis chart optioin?  No, I have not tried that option, but I'm open to giving it a shot. Where is the option located?
0
mlmccCommented:
Try this

Open the report
Preview it
Right click the chart
Click CHART OPTIONS
CLick AXES tab
Click DUAL AXES

mlmcc
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

w00kAuthor Commented:
mlmcc,
I don't beleive that is going to do what I need. The problem would still be that the x-axis (month name) is shared between both received and completed requests.  Certainly there must be some solution to this problem.  What about subreports?  Could i accomplish this by using subreports (1 for received requests and another for completed requests)??
0
mlmccCommented:
That would do it but you would have 2 separate graphs.  You could do it with 2 separate graphs in the main report.

WHat is in each record?
Is it like
SupportId, DateReceived, DateCompleted, other fields

mlmcc
0
w00kAuthor Commented:
mlmcc,
Yes, the data fields in the report includes a received_date, completed_date, request_id and some other fields that I'm not using on my report.  If at all possible, I wanted to have one line for received_requests and one line for completed requests on a single chart as shown in the image above. I did try using subreports to get this done (one for received_date and another for completed_date) and all of my counts were correct, but this doesn't help much because I'm not able to plot the values from each of the subreports on my line chart in my main report.  
0
mlmccCommented:
Look at the attached report.

Basic method
I created the report using a COMMAND as the data source
SELECT YourTable.request_id, YourTable.received_date, "REC" as TheDate
FROM YourTable
UNION ALL
SELECT YourTable.request_id, YourTable.completed_date, "COM" as TheDate
FROM YourTable

I then added a chart to the report header
Type - line chart
Data - Click the CROSS TAB option
    On change of - received_date
    Subdivided by - TheDate
    Show - Count of request_id

mlmcc

Q-27510782.rpt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
w00kAuthor Commented:
Thanks again mlmcc...this worked exactly how I wanted it to.  Much thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.