Solved

Crystal Report and Datatable

Posted on 2006-11-16
14
1,459 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
[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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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