?
Solved

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

Posted on 2007-04-06
10
Medium Priority
?
1,040 Views
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?
0
Comment
Question by:CyprexxDev
[X]
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
10 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 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
http://support.businessobjects.com/communityCS/FilesAndUpdates/SCR8_VB_RDC_DynamicFormulas.exe.asp

How to report off an ADO.NET database in Crystal Reports for Visual Studio .NET
http://support.businessobjects.com/library/kbase/articles/c2010929.asp

Check this link
http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc
0
 
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
0
 
LVL 17

Expert Comment

by:MIKE
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

MikeV
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:CyprexxDev
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.
0
 
LVL 1

Author Comment

by:CyprexxDev
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.
0
 
LVL 101

Expert Comment

by:mlmcc
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
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
0
 
LVL 1

Author Comment

by:CyprexxDev
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!
0
 
LVL 101

Expert Comment

by:mlmcc
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

mlmcc
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 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