Does a Crystal Reports report have to be bound to real data to create it?
I'm having a few problems with Crystal Reports, but I'm trying to solve them one at a time. There doesn't seem to be a good source of info out there specific to using Crystal Reports from a completely unbound Access application. I see some code snippets here and there, but no good overall resource. If anyone can point me to one that I find very helpful, I'll start a second thread and assign another 500 points to that person.
Currently, my problem is I cannot seem to create a report that allows for fields that have not yet been created. When I originally create the report, I have to bind it to a stored procedure, table or view (I using SQL Server for all my data) to add the fields to the report. When I call the report from within Access, I change all these data sources on the fly since we utilize more than one data source with duplicate structures. What I want to be able to do is add fields to the ADODB Recordsets in Access and have these fields show on the report. I cannot add these fields to the report, however, since they do not exist in the original data source when the report is designed from the start (I'm sure I can, I just don't know how). When I try to add a field to the report that does not exist in the data source, I get an error saying "This field name is unknown". I understand that it is unknown to Crystal Reports when I add it, but the field will be known when I attach the ADODB recordset when the report is actually run. How can I overcome this?
Currently, my problem is I cannot seem to create a report that allows for fields that have not yet been created. When I originally create the report, I have to bind it to a stored procedure, table or view (I using SQL Server for all my data) to add the fields to the report. When I call the report from within Access, I change all these data sources on the fly since we utilize more than one data source with duplicate structures. What I want to be able to do is add fields to the ADODB Recordsets in Access and have these fields show on the report. I cannot add these fields to the report, however, since they do not exist in the original data source when the report is designed from the start (I'm sure I can, I just don't know how). When I try to add a field to the report that does not exist in the data source, I get an error saying "This field name is unknown". I understand that it is unknown to Crystal Reports when I add it, but the field will be known when I attach the ADODB recordset when the report is actually run. How can I overcome this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Éric Moreau
I prefer to create a dataset in my application (containing data from whatever I want) and push it to CR. See an article I have written: http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx
Create a SQL Script that will create "in essence" virtual table so that you can use this table in your query as the MISSING DATA.
For example...let's say you need to populate DATES that are not in your normal data base table. Then simply create a SQL Script that can do this at the database ADD COMMAND level....and add it there...this will the appear as a TABLE for use in your Crystral Report. In this example, the table could simply be a TABLE of CALENDER DATES for as far in the future as you need. Then you can join this table to your DATA TABLE...so that IF there is a day (for example) where ther where NO SALES..then this table will allow you to have a NULL OR ZERO be placed on that day so that your CHART...or CROSSTAB will look more standard in regard to numbers of days....like 01/01/07, 01/02/07, 01/03/07..ETC..ETC...
In order to use this techique...create the script in your Query Analyzing Tool like MS SQL Query Analyzer or TOAD for SQL....then...in CRYSTAL go to the DATABASE EXPERT >> CREATE A NEW CONNECTION >> choose your datasource..and there you should see the ADD COMMAND option at or near your datasource. Open that up...and copy / paste your SQL there...Crystal will check it out to see if it is valid...and then give you the OK button to complete. Click OK and your SQL script will appear as a TABLE in your report.
Hope it helps..lemeno
MikeV
For example...let's say you need to populate DATES that are not in your normal data base table. Then simply create a SQL Script that can do this at the database ADD COMMAND level....and add it there...this will the appear as a TABLE for use in your Crystral Report. In this example, the table could simply be a TABLE of CALENDER DATES for as far in the future as you need. Then you can join this table to your DATA TABLE...so that IF there is a day (for example) where ther where NO SALES..then this table will allow you to have a NULL OR ZERO be placed on that day so that your CHART...or CROSSTAB will look more standard in regard to numbers of days....like 01/01/07, 01/02/07, 01/03/07..ETC..ETC...
In order to use this techique...create the script in your Query Analyzing Tool like MS SQL Query Analyzer or TOAD for SQL....then...in CRYSTAL go to the DATABASE EXPERT >> CREATE A NEW CONNECTION >> choose your datasource..and there you should see the ADD COMMAND option at or near your datasource. Open that up...and copy / paste your SQL there...Crystal will check it out to see if it is valid...and then give you the OK button to complete. Click OK and your SQL script will appear as a TABLE in your report.
Hope it helps..lemeno
MikeV
ASKER
As for the 500 point maximum, I was going to offer another 500 if a different question was answered as a side topic to this. It was not part of the same question in the first place. If that's not allowed, I certainly will not do it. Sorry for the misunderstanding.
ASKER
I appreciate the comments and have received some useful information - especially from mlmcc's first link. Most of this information, however, is for VB.NET - I'm using an access application. Though I can read the VB.NET code, methods such as the one that create the XSD file from a dataset are not available in VB6 (VBA) and that is the core behind the articles mentioned. If I could create this same type of file (or a TTX file) from a recordset in VBA it would be very helpful but I don't think that's possible. I can create my field definition files manually but that's going to be a whole lot of time and effort given we are converting over 200 Access Reports from data bound MS Access queries to unbound Crystal Reports ADO recordsets. This has been one of my main problems in finding a good article on this stuff.
Are you staying with the Access database or is that changing also.
There are tools that claim to be able to convert Access reports to Crystal
Check this page about halfway down
http://www.kenhamady.com/bookmarks.html
I have not used them nor have i heard anything either way.
Do you still plan to run fromAccess with VBA or will you be using another application platform?
mlmcc
There are tools that claim to be able to convert Access reports to Crystal
Check this page about halfway down
http://www.kenhamady.com/bookmarks.html
I have not used them nor have i heard anything either way.
Do you still plan to run fromAccess with VBA or will you be using another application platform?
mlmcc
ASKER
Yes, we are staying with Access, but are writing an existing application from scratch. There are many reasons we decided to stay with Access, many of which are too complicated to try to explain here. Regardless, since we are rewriting the applciation, I cannot simply convert the reports. The backend data structure is completely different. The reports have to report on the same data, but the data is stored completely differently than it was before. Moreover, every conversion utility I've ever seen is horrible at converting bound reports or forms to their unbound ADO counterparts.
To summarize, when I say "convert" the reports to Crystal, I should really say "rewrite" the reports in crystal. A conversion simply would not suffice. Thanks!
To summarize, when I say "convert" the reports to Crystal, I should really say "rewrite" the reports in crystal. A conversion simply would not suffice. Thanks!
Just wanted to be sure you knew about the conversion programs. I agree most that I have seen do a poor job. I haven't used any that convert to Crystal but we converted our Access program to VB using a conversion program. The forms weren't too bad but much of the code had to be rewritten.
Glad i could help
mlmcc
Glad i could help
mlmcc