Solved

Crystal Report and Datatable

Posted on 2006-11-16
14
1,447 Views
Last Modified: 2012-06-21
I have a Crystal Report which I designed by importing an access table and selecting the fields i wanted.

The actual report is generated not from the access table, but from a datatable which is filled from a query on the access table and others

The problem is the DataTable has a couple of extra columns which are added separately before the DataTable is  used as the crystal report datasource

The extra column names do not exist in any database, but I know what they will always be named. How can I add report fields in the Crystal report to take the data from these extra columns, without having to design an access table with the extra columns and appropriate field names, attach the access table to the report to design it using the extra fields, and then takeg the fields out of the access table again or simply delete the access table, as the data will always come from a datatable generated on the fly. I have tried adding unbound formula fields of the same name but i just get blank entries,. Is their a way to add fields to the crystal report similar to database fields so that the crystal report will recognise all the columns in the DataTable?
0
Comment
Question by:JeffvClayton
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 6

Expert Comment

by:riyazthad
ID: 17957229
Create a temporary table having all fields including your two fields. Create Crystal report based on this table. Once finished report, delete that table. If you are passing dataTable with additional fields, it will work.
0
 
LVL 3

Author Comment

by:JeffvClayton
ID: 17957293
Yes , thats the solution i put in the question, but is their a way of doing it without having to make a temporary table?
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17957324
just an opinion, not an answer.
i recommend you steer clear of Crystal Reports......... just my opinion
www.devexpress.com has a nice reporting control much more flexible than CR. (as i say this, i am using CR in my apps, but has given me such hell that i intend to make the switch pronto)
NY
0
 
LVL 6

Accepted Solution

by:
riyazthad earned 250 total points
ID: 17957704
Another way is create a XML file from dataset. and then give this as source to crystal report.
0
 
LVL 14

Assisted Solution

by:Ramuncikas
Ramuncikas earned 250 total points
ID: 17957733
riyazthad,
you did not metion the developement environment you are using.

I'm using VS2005 Pro. I solved similar problem this way:
1. create a dataset for your project and add a table there with the required schema
2. create a CR report and point it's datasource to the created dataset (project datasources)
3. design your report
4. use code below to make your report get data from your dataset:

Dim sRpt As New MyReport 'name of your report
Dim sDS As New MyDataSet 'name of your dataset

Dim sConn as New OLEDBConnection(<MyConnectionString>) 'connection to database
Dim sCmd as New OLEDBCommand 'command return data
sCmd.Connection = sConn
sCmd.CommandText = "SELECT * FROM MyTable"

Dim sDA As New OLEDBDataAdapter(sCmd) 'dataadapter to fill your dataset
sDA.Fill (sDS)

sRpt.SetDataSource(sDS) 'bind dataset to your report
Me.MyCRViewer.ReportSource = sRpt 'bind report object to form's CR report viewer

You could place this code into form's load event or in button's click event in case you'd like to query user for parameters that could effect the data returned.

Advantages of using this technique are:
* you do not have to connect to real datasource at design time
* you do not need any temporary objects as suggested here
* you can develope your application having a database schema only (in case of teamwork)

HTH
Ramuncikas
0
 
LVL 6

Expert Comment

by:riyazthad
ID: 17957808
Can u explain step 3 little bit more? Is that solve his additional fields issue?

Thanks
0
 
LVL 3

Author Comment

by:JeffvClayton
ID: 17957837
Yes thats another way to do it, although the  problem is that i dont know how many extra fields i will have before hand, as the sql that generates the datatable is built up in code, adding fields from different databases as necessary. I was hoping their was a way to some how attach a formula field to a DataTable column, have seen this banded around but can't get it to work  

 cr.DataDefinition.FormulaFields("F1").Text = {DataTable.ColumnName}
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Author Comment

by:JeffvClayton
ID: 17957871
PS Using VB Net 2005
0
 
LVL 14

Expert Comment

by:Ramuncikas
ID: 17957982
Well, my suggestion would be useful only when datasource for report has a fixed number of fields. That's a disadvantage.
But on the other hand how report will react when you pass a data with different schema than it was created with? To say the truth I've never tried.
On the other hand report could be designed with maximum possible fields and then report textbox's suppress property could be used to write a condition when a field should be hidden in a report.
0
 
LVL 3

Author Comment

by:JeffvClayton
ID: 17958083
Yes it all comes back to having to design a schema with lots of fields, I will keep this question open for now in case someone knows how to define a formula field as a field object and tie it in to a datatable field
0
 
LVL 14

Expert Comment

by:Ramuncikas
ID: 17958134
How a number of possible fields shown differ? I mean what is the minimum and the maximum number of fields you'll have to show?
0
 
LVL 6

Expert Comment

by:riyazthad
ID: 17958144
Ramuncikas , Thanks for that ...

JeffvClayton , I think you can try with creating UnBound Fields. I think it will not show untill you bound it.

so u can make few more room there.
0
 
LVL 3

Author Comment

by:JeffvClayton
ID: 17963924
After spending a lot of time looking at this and various user guides at businessobjects.com  the answer is you have to define field names at design time, whether that means using xml schemas, linking to a database table or creating a text file of field names with no particular type of database in mind, the bottom line is the crystal report has to know field names beforehand, you can't define them at runtime.
0
 
LVL 3

Author Comment

by:JeffvClayton
ID: 17963943
Thanks for all the replies, maybe the next version of crystal reports will allow a formula to be passed a field object.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

11 Experts available now in Live!

Get 1:1 Help Now