INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Access

Reporting off an ADO.net dataset using Visual Studio.net by LikePork
Posted: 23 Dec 04

Hello,

I've had a tough time finding good information about this topic, but I'm grateful for any help received so I'm going to try to give some back.

I assume for this FAQ you have used an ADO.net dataset and Crystal Reports at least a few times , though maybe not together.  I won't be talking about how to create your dataset or fill it with xml data, or how to edit your report in the VS editor.

Reporting using ADO can be great because often not all your data is coming from a DB, you'll have data from your program, or data in xml that's needed for the report as well.  Also, it gives you a chance to format all your data from the DB, or xml or wherever, to avoid packing your report with tons of formatting formulas.  I trust vb or C# code a lot more than those Crystal Formulas, and prefer having as few tasks as possible for Crystal to take care of.

The first step to take is to design a .xsd xml schema.  This schema is used by Crystal to "know" what data to expect.  You can create one using the VS designer, or "cheat" and after creating your dataset that you will be passing to crystal, use the .WriteXmlSchema(fileName) to create one.  Pay special attention and notice the "Table_Id" fields that are automatically generated.  I don't want to talk too much about them, but notice that relations between the tables in your dataset will use a key that is hidden from you sometimes.  You'll be able to see them by doing a "watch" on your dataset with the VS debugger or when the xsd is brought into Crystal.

Once you have an xsd file that has all the same table and field names as your dataset, you can set the "database" in Crystal to it.  In VS in the Field Explorer window right click the DataFields item, then select "Add/Remove Database".  Go to "More Data Sources", then ADO.Net(XML), then click "Make new Connection".  Select your xsd, then pick the Tables from it you'll be using for the report.  Note that if you make changes to your xsd, updating it into Crystal is a little trickier than it should be.  

To make sure it updates, right click Database fields and select "Log On/Off Server", then Log off the xsd file(lol, I'm serious).  Then select the updated xsd in the same manner as you'd done before, try not to lose all the fields on your report while doing it(you'll have to add the updated tables under different aliases, then remove the old tables and change back the table's names).

So now you've got all the fields in your report and are ready to do your Crystal magic, just one more thing to worry about, linking the Tables.  Crystal allows you to link the tables any way you see fit, but it seems to work a little funny for ADO tables.  The linking that crystal generates automatically will be wrong(trust me), so you'll have to set it up yourself.  You'll probably use those "hidden fields" I mentioned earlier to link the tables, but you could also add your own ID fields and handle the linking yourself(I mean put values in these fields in your code and group by these ids instead of the generated ones).  Linking isn't a big challenge, but if it's wrong you'get the Query Engine Error, which is the same error message as a bad table name so it may be a bit to work out.  

Finally, when deploying your app, you'll need an extra 2 merge modules if you are using ADO.net.  Info about these can be found here: http://support.businessobjects.com/library/kbase/articles/c2011164.asp

Ok, that's all for now.  This help help you get on your way to using ADO.net data in your Crystal Reports.  Maybe I'll add some info to this FAQ if there are questions or when I learn more.  Good Luck!

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close