• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Help creating line chart - daily snapshot

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.

  • 3
  • 2
1 Solution
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?

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.

PhinneganAuthor Commented:
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!
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

PhinneganAuthor Commented:
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.
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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