Help creating line chart - daily snapshot

Posted on 2003-02-25
Medium Priority
Last Modified: 2012-05-04
My project has switched over to Crystal after I had everything the way I wanted it in Excel. It seemed so easy in Excel, now it seems impossible with Crystal, which I'm sure is not the way it is supposed to be. I'm entirely new to Crystal, so I'll try to explain what I had in excel to give you an idea of what I'm trying to accomplish.

It seemed so easy in Excel, now it seems impossible with Crystal, which I'm sure is not the way it is supposed to be.

My data source contained records with a "SubmittedDate", and "Active" flag,  and a "Status" (say red, green, yellow and blue). The spreadsheet would connect and collect all records from the source each time the spreadsheet was opened. I then had a macroe that would loop through all the records (now copied into an excel sheet) and populate a matrix on the spreadsheet showing all the number of "active" records for each "status" broken out by "SubmittedDate". Basically, this gives me an up to date snapshot of the state of my database for each day over time. So for example, if yesterday I had 3 active records with a status of "green", but today one of those records was set to inactive and no new greens were added, then when I run the sheet again tomorrow, the 3 green from yesterday's date will be there, but only 2 green will show under today's date. This point is important because it has tripped me up in successfully trying to use a RunningTotal field in Crystal. Sometimes the total will go down.

So basically it's a trend line graph that I can look at and say "Oh, look, on Tuesday 18th we had 7 active greens, that peaked at 15 active greens on the 21st, but now on the 25th it's back down to 4".

All of the crosstab graphs I have attempted have only shown the count of each status submitted on each date, rather than a snapshot of the count of all records of each status broken out by date. It seems like I need a separate query for each cell in the crosstab:
ie. "select count(*) where status = 'green' and submitteddate <= <date>"
If I had that query in each cell in my crosstab, and hardcoded the "SubmittedDate" and "Status" values, that would give me the chart I want. But I'm sure there's a more efficient way than to connect to the data source 300 times!

Clear as mud? I'm happy to clarify anything.

Thank you for any help you can offer, I hope 150 points is an appropriate amount for a question of this nature. Like I said, I'm new to Crystal so a response such as "use a Running Total field", while appreciated VERY much, won't be much help to me. I humbly request a little more hand-holding.

Thank you again, I appreciate you taking the time to read.

Question by:Phinnegan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 12

Expert Comment

ID: 8021110
Is the data in your data source sequenced by date?  In other words, do you records look kind of like this ...

Date        Green Red Blue
11/20/2003    3    2   1
11/21/2003    4    2   0
11/22/2003    8    3   1

If not, what does the data you are asking Crystal Reports to create the chart from look like?

LVL 101

Expert Comment

ID: 8022978
Why was the project switched to CR?  

It seems to me that every tool has its place, its strengths and weaknesses.

CR is wonderful for reports but the charts and graphs are its weak point.  

I guess my business sense is showing through that if you had the Excel spreadsheets working why reinvent the wheel just because a new tool is available.


Author Comment

ID: 8025419
The data in my datasource is not sequenced by date, but I have grouped it by date in my attempts so far. The data looks more like this:
[u]Date[/u]    [u]Status[/u]
11/20/2002     green
11/20/2002     green
11/20/2002     blue
11/21/2002     red
11/21/2002     yellow

and so on and so on. (there are other fields, but these are the ones I'm concerned with).
Crystal Reports was introduced because a 3rd party application we began using for a different purpose used it also. The charts and graphs I'm working on were a secondary priority and not given a great deal of consideration at decision time, now I'm stuck using it. I agree with your final comment, but I'd rather perform than complain, so I'm stuck with what I got.

Thanks for your interest -- looking forward to more info!
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

LVL 101

Accepted Solution

mlmcc earned 600 total points
ID: 8029531
In Access I built a table as you described

SubmittedDate and Status

I then wrote 4 queries.
3 to count each color by date and the 4th to join them together

SELECT tbl_status.SubmittedDate, Count(tbl_status.status) AS Blue
FROM tbl_status
WHERE (((tbl_status.status)="Blue"))
GROUP BY tbl_status.SubmittedDate;

SELECT tbl_status.SubmittedDate, Count(tbl_status.status) AS Green
FROM tbl_status
WHERE (((tbl_status.status)="Green"))
GROUP BY tbl_status.SubmittedDate;

SELECT tbl_status.SubmittedDate, Count(tbl_status.status) AS Red
FROM tbl_status
WHERE (((tbl_status.status)="Red"))
GROUP BY tbl_status.SubmittedDate;

The final query is
SELECT tbl_status.SubmittedDate, qryBlue.Blue, qryGreen.Green, qryRed.Red
FROM ((tbl_status LEFT JOIN qryBlue ON tbl_status.SubmittedDate = qryBlue.SubmittedDate) LEFT JOIN qryGreen ON tbl_status.SubmittedDate = qryGreen.SubmittedDate) LEFT JOIN qryRed ON tbl_status.SubmittedDate = qryRed.SubmittedDate
GROUP BY tbl_status.SubmittedDate, qryBlue.Blue, qryGreen.Green, qryRed.Red;

You don't state what database you are using but something like that should be possible.  I tried it with some simple data and it seems to work  You should be able to build your crosstabs from that.


Author Comment

ID: 8036323
Thank you mlmcc for your help. I ended up creating views in my MS SQL based upon the queries that you provided, rather than implementing them as queries in Crystal. Your solution has helped me a great deal.

Thank you.
LVL 101

Expert Comment

ID: 8038664
I agree with your decision.  I base all my reports on queries or stored procedures in the database rather than building them in CR.  

For one reason it is easier.  The other is I generally am using a query that was used to display data on the screen in a form or grid so I know the user will be getting the same information.

Glad I could help


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question