Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Does a Crystal Reports report have to be bound to real data to create it?

Posted on 2007-04-06
Last Modified: 2013-11-15
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?
Question by:CyprexxDev
LVL 100

Accepted Solution

mlmcc earned 500 total points
ID: 18868029
One thing you can try is to add formulas to the report that return a static value.  You can change the formula from your application to return the field you want.

Link to a VB application that changes formula fields from the application

How to report off an ADO.NET database in Crystal Reports for Visual Studio .NET

Check this link

LVL 70

Expert Comment

by:Éric Moreau
ID: 18869042
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
LVL 17

Expert Comment

ID: 18869929
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Author Comment

ID: 18875901
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.

Author Comment

ID: 18875993
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.
LVL 100

Expert Comment

ID: 18876505
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

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?


Author Comment

ID: 18877444
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!
LVL 100

Expert Comment

ID: 18878657
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


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question