Crystal Report and Datatable

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?
LVL 3
JeffvClaytonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
riyazthadConnect With a Mentor Commented:
Another way is create a XML file from dataset. and then give this as source to crystal report.
0
 
riyazthadCommented:
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
 
JeffvClaytonAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
newyuppieCommented:
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
 
RamuncikasConnect With a Mentor Commented:
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
 
riyazthadCommented:
Can u explain step 3 little bit more? Is that solve his additional fields issue?

Thanks
0
 
JeffvClaytonAuthor Commented:
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
 
JeffvClaytonAuthor Commented:
PS Using VB Net 2005
0
 
RamuncikasCommented:
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
 
JeffvClaytonAuthor Commented:
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
 
RamuncikasCommented:
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
 
riyazthadCommented:
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
 
JeffvClaytonAuthor Commented:
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
 
JeffvClaytonAuthor Commented:
Thanks for all the replies, maybe the next version of crystal reports will allow a formula to be passed a field object.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.