Solved

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

Posted on 2007-04-06
10
1,030 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
10 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:CyprexxDev
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:CyprexxDev
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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 100

Expert Comment

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now