Help creating line chart - daily snapshot
Posted on 2003-02-25
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.