After taking a step back...

scojones

I think we may be doing this the wrong way, so I'm going to give a little story here and ask for everyone's recommendations...

<backend>
We have a change management system, Clear Quest, which sits on top of MS SQL Server. Information on defects (bugs) for projects are kept here.

We have our code on UNIX servers, we use Clear Case (it's like cvs), developers checkin/checkout files, all of the code for the projects is kept here. We have two perl scripts which get the amount of Code Churn, and Code Coverage, for projects, and put the results into a text file on the UNIX server.

We have a MySQL database, which stores the amount of man hours (effort) put into a project.

Some project managers store data in Excel files on their computers, others uses Excel to pull data from the above databases, and store them on their computers.
</backend>

Several of us are required to build 14 reports based on all the data in the databases, and in the data contained in the Excel documents, we are using Crystal Reports Bussiness Objects Enterprise XI.
The reports we create can be published to Enterprise and scheduled there, our users will login to enterprise and view their reports from there.

I have suggested that we create a database to store the data from the Excel files, and the data from the text files the perl scripts create. This way everything is centralized, and we don't have to worry about files on people's computers. My idea is that we write some macro / module that gets the info in the files, and simply inserts it into the database.
<rant>
I have about 2 years database experience, but NO crystal reports experience, and I'm the poor fool that has to create the reports with crystal. I've suggested getting someone with crystal reports experience but they don't want to know.
</rant>

So basically we are going to create 14 general reports, that can be used by any project manager to view every aspect of his project with these reports, with minimal modifications to get this framework to work for him.

I have to say I'm finding it quite hard. The data I'm getting with Crystal Reports is not matching the data I get with reports I create in Excel, and I have no reason why. I've contacted Crystal Reports in Dublin, but they haven't gotten back to me, and I don't expect that they will because they don't know the data contained in the database.

But anyway, my point is, are we going about this the wrong way? Have any of you guys had similar experience? Is our architecture silly?
If you need more info (I'm aware I was not very descriptive) just ask.

Thanks alot in advance.

amen

well I think Its back to basics
Crystal Reports is just that a reporting engine so it will only ever display(report) on what you tell it. In the past when using Crystal I would always write an sp to generate my data and the access the sp from crystal reports. That way I know my data is correct and I can manage the changs to my sp. So have you written the sp and is the data correct ?

As for the the database to store all the data that sounds like a really good idea. You should be able to use a macro to export your data from excel into MS SQL.

Maybe instead of using Crystal Reports have a look at MS SQL Reporting Server (its free) and allows you to generate reports from SPs with multiple different outputs (csv, text, Excel etc) and it can be accessed via the web.

if you post more specific issues with your reports such as the SQL, Crystal Report functions and sample data you may get more responses

scojones

amen wrote:

well I think Its back to basics
Crystal Reports is just that a reporting engine so it will only ever display(report) on what you tell it. In the past when using Crystal I would always write an sp to generate my data and the access the sp from crystal reports. That way I know my data is correct and I can manage the changs to my sp. So have you written the sp and is the data correct ?

What do you mean by sp?

As for the the database to store all the data that sounds like a really good idea. You should be able to use a macro to export your data from excel into MS SQL.

Excellent

Maybe instead of using Crystal Reports have a look at MS SQL Reporting Server (its free) and allows you to generate reports from SPs with multiple different outputs (csv, text, Excel etc) and it can be accessed via the web.

Not an option, we have already bought Crystal Reports Enterprise

bonkey

If the data is correctly stored in the database, you should be able to write an SQL query to retrieve what it is you want.

If you can do this, but Crystal's internal method of doing join-equivalent operations isn't working for you....why not just create a view on your database which is based off the (corrrect) SQL Query I mention in the paragraph above.

Then tell Crystal to retrieve from the view. No join-equivalent operations. No lookups. No chance for it to bollix your data.

If you can't write the SQL statement to produce correct info, then Crystal is clearly not where the problem lies.

jc

scojones

Cheers bonkey, I'll have a look at that tomorrow and let you know how I get on.

amen

sorry sp = Stored Procedure in MS SQL Server
its basically some precompiled SQL
write your sql in an SP then call it from Crystal Reports
Much easier
If you need a hand post your problem