Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Reporting off an ADO.net dataset using Visual Studio.net
Data Access
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!
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!