asked on

Develop Reports in .NET

I decided I'd go with crystal reports for an app I'm developing in .NET. I'm using the built-in Crystal Reports control. I have *several* reports that need to be made, some are parameter and some aren't. Ultimately my goal is develop a lot of different and not spend a huge amount of time on it and not distribute a lot of unnecessary files with my application.

My thinking was to do this. Create a TTX file, then create the report based off that TTX file, and at runtime fill it with a dataset that was filled by a DataAdapter (OleDB in this case) and make that the datasource.

So, ok I created the TTX and made the report design, I accepted all defaults really after setting the Grouping that I wanted. Then I set that data source of the report to my dataset (which I created through code, not with the "generate dataset" and all that ). So what happens is that when the report viewer opens it shows the formatting I made and all that but no data is there and I created a DataGrid on the same form which uses the same dataset and it has all the data there.

What I'm looking for is an answer to how can I achieve my goal the best, I want it to be versatile and not spend a huge amount of time on it. What do you guys suggest and how can I fix this problem? I have tried setting the data source of the report to a datatable but that results in "Query Engine Error" from the Report Viewer. I have even tried to set the report's data source to "da.Tables(0)" and that yields nothing. This particular query is very simple.

Thanks for your help. (This is urgent and may be involved, hence all the points).

cyberdevil67

Hi NetworkArchitek,

The simple answer is whether you use the IDE built in Designer for the reports or the Crystal Report designer always use OLEDB (ADO), this makes the report use a direct connection to the database. If you need to reuse the report on another database or server you can modify the properties of the data in the report on the fly to use that database (This is what we do to save time and saves us from having to ever touch the report again).

Now as far as the report goes you need to do something like this.

Although you did not specify VB.Net or C# I'll give you the CSharp version.

ReportDocument report = new ReportDocument();
report.Load("Report.rpt");
report.SetDatabaseLogon("Username","Password"); //Never use a trusted connection always send the password in the program nad make sure its an account that is doesn't have full rights.

report.SetParamaterValue("Param1",Value);

CrystalReportViewer1.ReportSource = report;

Hope this helps and if you have any further questions I'll be glad to help.

Cheers!

NetworkArchitek

ASKER

Yeah, VB or C#, either way is fine. Ok, sorry if I'm a bit slow but you could go into a little more detail. You say use (ADO), I'm using ADO.NET and those kinds of methods, are saying to go down ADO? I've only used ADO.NET.

ReportDocument report = new ReportDocument();
report.Load("Report.rpt");

(I understand C# but since I wrote this in VB I'll just leave my code in that format) Why do I need the "report.Load()" ? So maybe to just reiterate I made the report layout and all that using a TTX file I made. So let's say it is CrystalReport1. So the way I'm using it is like this:

Dim ds As New DataSet
Dim rpt As New CrystalReport5 '' Again, all this report has is the field data from the TTX
oCmd.CommandText = "..." ' This is just a SQL query, it works.

Dim oAdapter As New OleDbDataAdapter(oCmd)
oAdapter.Fill(ds)

rpt.SetDataSource(ds) ''' I have tried rpt.SetDataSource(ds.Tables(0))
CrystalReportViewer1.ReportSource = rpt

And as I said before, I get the field headings and all that kind of stuff but it is empty. I tried with filling a datatable but I get the error I said before. (right now this is just hitting an Access DB but I will use proper security when I migrate it to something else)

Ok, given that that is what I'm doing can you expound on what I should do or how to implement your solution properly? Thanks

cyberdevil67

no I actually said OLEDB (ADO) its different, and this is from the report designer when you make the connection to your database to place the data in your report.

Ok report.Load is a good habbit to get into. Because if you develop many reports you can make a custom class to load the report an view it even change the properties etc which is what we do. So in this case I could have a drop down box with a list of reports or buttons and then can load the report with one line of code with the report to load. Its a habbit because it minimises the code needed. It also allowes for you to change the properties of the report, and as I also said we design one report that we can sell to clients and we don't need to change the report (touch it by opening changing the db connection) as this is all handled by code. Saves us work in the long run.

The code you are using to open the database should be done in the report, this allows you to make changes to one location and one location only, for example if I embed a report into an application I would have to rebuild the entire application, but if I make it so that I load the report, and all that is needed to be changed is the report then I open the report, make the change and redoply the report only, if the database changes then you need to reuild your coded queries as well and makes no sense to really do it this way.

Look at it this way, do you want to save yourself work or create more work. I am suggesting a better wat to load modify and deploy without having to rewrite code, after many years of writing reports and modifying code this is the easiest way to make your work easier.

So lets say I write an application, and the client wants an extra field in the report, with your way you are rewriting the code to get the extra field, rebuild the entire project and redoply the entire project. My way that I am showing you means that I open the report add the field run the report see if it works and ask the client to put the report into a certain directory, thats it. Which is the reason behind the report.Load();

Any more questions, glad to help where I can.

NetworkArchitek

ASKER

Ok, I think I see what you're saying. So I just forget datasets and all that for reporting and just use the CR communicate directly to the DB and then all the *actual* code that I need are these lines:

ReportDocument report = new ReportDocument();
report.Load("Report.rpt");
report.SetDatabaseLogon("Username","Password");
report.SetParamaterValue("Param1",Value);

CrystalReportViewer1.ReportSource = report;

Is that right? I see what you mean with OleDB (ADO) now, at first I thought you were suggesting to use COM Interop, but that's actually what CR calls it, ok. I definitely see how report.Load() saves code, the only reason I was going the other way was that I wasn't sure if this kind of way would be good design or not. Ok so have I understood the explanation and do you think this is a good way to go?

One last thing, with parameters, the SetParameter, is the SetParameter() method the way to pass them? So if I had let's say a MachineID, BeginDate, EndDate I could pass it as:

report.SetParameter("Param1", strID)
report.SetParameter("Param2", dteBegin)
report.SetParameter("Param3", dteEnd)

Thanks for your help. =)

ASKER CERTIFIED SOLUTION

cyberdevil67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NetworkArchitek

ASKER

Yes, this is definitely very helpful. I will try it this way, I'm pretty sure it will work out fine. Really I need to get a demo out real quick so if I want to do it the other way I'll think about it but this should *work* and that's the main thing. Writing a seperate class for this is a great idea that I hadn't thought of so I'll think about that. I appreciate your help!

cyberdevil67

No problem, very glad to help.