Help creating line chart - daily snapshot

[Phinnegan]

Let me start by saying thanks once again to synapsevampire who answered my last question. Unfortunately, I'm back for more help on the same topic:

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.

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. &quot;select count(*) where status = 'green' and submitteddate <= <date>&quot;
If I had that query in each cell in my crosstab, and hardcoded the &quot;SubmittedDate&quot; and &quot;Status&quot; 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 appreciate you taking the time to read.

Phin.

 

[IdoMillet]

The only way to get &quot;snapshot&quot; effect is to either store snapshot information somewhere in your database (information as-of each date) or store the status change information as records.

Are you saving snapshot information somewhere?

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Phinnegan]

IdoMillet:

Thanks for the reply. Just want to be sure I got you clearly: For the saving of snapshot info (which I'm not currently doing) are you suggesting something like setting up a job in the db (MSSQL) to write the snapshot info to another table or db each day, from which I could get the reports I want with records that look like:
Date Green Red Blue Yellow
11/20/2002 2 5 0 1
11/21/2002 3 4 2 1

OK, I can wrap my head around that no problem. Sounds like a good idea.

I'm not sure I know what you mean, though, by storing the status change information as records? Do you mean something similar to what I wrote above, except in the G/R/B/Y fields rather than saving counts, just saving the differential from the previous day?

I've always been doing reports like this in excel, I'm astonished to find out that something like Crystal can't do this easily. Of course, the joke is on me as I should have known this ahead of time!

Is it possible to write a VB macro to loop through recordsets and such in crystal? Might such a method offer me a solution?

Thanks very much for your help,
Phin