How would I create a trend report in Crystal Reports?

I would like to create a report (or ultimately a dashboard) that looks at a particular value (a status for a particular record (e.g. "new", "in process", "completed")).  There is no data of the status at any particular time so all I can query is how many records are in each status at any given snapshot in time, but I would like to know where we stand and how these are changing over time.  Is there a way to automatically run the report (e.g. daily) and have all of the prior results be part of the most current report?

I hope I explained this well.

Thank you,
Bruce
bruce schroederAsked:
Who is Participating?
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.

David Johnson, CD, MVPRetiredCommented:
I would think that you need another table linked to each project that contains the status and date or another table that saves the results i.e.
status1,status2,status3,status4,...,date and you can compare the current results to the previous results or the results on a specific date, you have to save the results every time you run it.
Mike McCrackenSenior ConsultantCommented:
The only way to do it is as above.

You need to create a table and save the values each time you run the report with the date and time of the run.
Without the previous values and a value to trend by you can't build a trend report.

You could just save the run number.

mlmcc
bruce schroederAuthor Commented:
I was afraid of that.  I failed to mention that I have read-only access to the SQL db so I guess I  can't create any new tables in that db.

Can I create a local file or table in another db somewhere else?

I have Crystal Reports Server (but I have not used it yet so I don't know if that will give me any more capabilities).

Thanks

Bruce
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Read On!
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
3rd-party Crystal Reports automation tools (see list here) can help with this scenario.
Here is a description of how my Visual CUT software solves this type of scenario by automating ODBC data export.

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
Mike McCrackenSenior ConsultantCommented:
You can create the database table anywhere you want so long as other users have access to it.  You could even use an Excel spreadsheet.

mlmcc
bruce schroederAuthor Commented:
So I have created a table in  new database (separate database from the one I will be querying).

So assuming I am able to query what I need and generate a report with the current statuses, how do I then write the new data to the new database?

I guess what I want is:

1) Read the historical data from the "new" database table
2) Query the active database to get the current snapshot of statuses
3) Write the data (all of it or just add the new) to the "new" database?

Thanks in advance.

Bruce
Mike McCrackenSenior ConsultantCommented:
Did you look at the solution provided by Ido?

Generally this would be done in n application built for the purpose.

mlmcc
bruce schroederAuthor Commented:
Was hoping not to purchase another application but to do this from within Crystal if Possible.
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
afaik, it is not possible with just Crystal.
Mike McCrackenSenior ConsultantCommented:
Agree.  I don't know of a way to do it in Crystal itself.

mlmcc
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.