Crystal Reports .NET
Things I've learned, used or seen regarding Crystal Reports .NET. I use VS .NET 2003, so code is only guaranteed for that version.
If you receive a “Query Engine Error“ when working with ADO.NET datasets, there is a difference between the XSD you used to design your report from, and the structure of your dataset. To verify this, add a datagrid to your form, and comment out the code that creates your crystal report. Instead, bind the dataset to the datagrid, and view your form. Verify that all the columns have the exactl same names, and they are in the same order.
If you need to update your XSD, you can make small changes manually. To completely recreate your XSD, you can follow the easy steps in this post: Quick & Easy XSDs for Crystal Reports.
Once you have your updated XSD, you need to update your report. You can do that easily by following the steps in this post: Updating Crystal Reports .NET if You Change the XSD.
[edit: 3/14/2005]
I received the following comment via e-mail:
You should take note that the XSD doesn't have to have an element for every field that's in the dataset. I found that the error is actually caused by having a element in the XSD that is NOT in the dataset. You can use a subset of the dataset in the XSD and it will still work.
For Crystal Reports, your XSD only needs to contain the dataset fields you want. The rest will be ignored. It doesn't hurt anything to have every field listed in your XSD, and only map the ones you need to the report. But if you have a field in your XSD, that field MUST be in your dataset.
[/edit]
If you need additional help, post your question in the Crystal Reports forum over on http://asp.net. Business Objects also has a useful guide here: Reporting off ADO.NET Datasets.
Crystal Reports .NET Programming
If you're new to Crystal Reports, or just to CR .NET, this is the book you need. Part how-to, part programming reference, lots of simple examples in both C# and VB.NET.
Have you ever tried to move one field from one section to another (like from Group Header to Details) in the CR .NET IDE? You can't. But you can move two! That's right--Ctrl+Click and grab two fields, and drag them to another section (make sure you get the fields completely in the other section). This looks like a bug, but I haven't followed up on it yet.
When I need to move a single field from one section to another, I add a blank Text Object, grab both objects, and put them where I want them.
One reason to move a single field from one section to another is because when you add a field to a report from the Field Explorer, the designer automatically adds the field's title to the Page Header. If you have the Page Header all nice and pretty, you don't want to have to dig around to get the undesirable header field and delete it.
One reason to drag multiple fields between sections is to preserve their layout. If you were to delete a number of fields from your report, and then add them all back in the proper layout, you'd waste a lot of time. You'd also have to fix your report header again. Just grab them all and move them to the desired section.
I have a new Crystal Alliance article published today:
Merging and Password Protecting PDFs Exported by Crystal Reports
source: http://aspalliance.com/571
Crystal Reports has the ability to produce output in PDF format. Often, it is desirable to have multiple reports appear in the same PDF file. Crystal Reports does not have the ability to export multiple reports into the same PDF, or to password protect a PDF, but several third party components can process PDFs post-export from Crystal Reports. We'll use one .NET control named Dynamic PDF Merger to combine two PDFs into a single file, and return the single file to the end user.
I also show you how to add password protection to your exported PDF to prevent the end user from performing functions such as copying or printing the document.
In the Crystal Reports chat last night, Brian Bischof asked a question about a topic I wasn't aware of:
Q: I know that 2005 will support printing from collections. Will this be as simple as assigning the collection via the SetDataSource() method? Will it cover DataViews? etc.
A: Yes, it will be as simple as setting the collection into the ReportDocument/Table.SetDataSource() method, and it should cover DataViews as well.
Earlier this month, Scott Mitchell wondered how to do exactly this. In CR .NET 2003, the answer is to convert your collection to a dataset, and then use the dataset as your datasource. In Cr .NET 2005, you apparently won't need to do the conversion to a dataset--you can just set your report's datasource to the collection. This is hugely cool, especially for data that do not come from a database (but rather from an XML file, etc).
Also, dataviews will be supported. Again, cool. Using teh CR .NET engine for linking and filtering your report's data is not all that efficient. Now, you'll be able to use a dataview on your dataset, and set that dataview as your report's datasource. Rather than going back to the database each time a user wants to change the report presentation, you can cache your dataset the first time, and just create new dataviews each time the report parameters change. Way cool, and highly efficient in many cases.
Need a good way to play with SSRS? Maybe hosting from DiscountASP.net will work for you.
http://discountasp.net/features.aspx#addons
DiscountASP.NET is proud to announce the launch of Microsoft SQL Reporting Services !!
This powerful reporting solution significantly enhances the DiscountASP.NET SQL Hosting offering. SQL Reporting Services is offered as an optional addon for those who deploy web applications driven with a SQL database backend.
SQL Server 2000 Reporting Services is a server-based solution that enables the authoring, management, and delivery of reports. SQL Reporting Services comes with client tools that integrate with Visual Studio.NET, providing a common design environment for authoring reports.
Pretty good discussion of Crystal Reports .NEt vs. SSRS on ASP.NET:
http://asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=720164
This method isn't limited to just Crystal Reports, but that's where I see the question a lot:
CodeSnip: Opening Crystal Reports in a New Window in ASP.NET
http://aspalliance.com/525
<edit>
Note: This post has been updated and published as an article on ASPAlliance. Click the following links to read the full article:
Dissecting the "Logon Failed" Error in Crystal Reports .NET
source: http://aspalliance.com/532
The old content appears below.
</edit>
In his “Troubleshooting Database Login Errors using Crystal Reports with .NET” article (http://aspalliance.com/490), Eric Landes points out that he sees questions regarding the “Exception Details: CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed” message quite often in the newsgroups. That's an understatement. Since I use the “push” method, I never saw this error, but I decided to try and replicate the problem to see why this was happening--it seems to be a popular error. I had a suspicion that impersonation and the “double hop“ (where the ASP.NET application cannot pass the user's credentials) may play a role in this problem, since the methods in Eric's article don't solve the problem for every user it should.
To replicate the problem, I used an ASP.NET project connecting to either a remote production SQL Server database or a copy of the same database on my development machine (SQL Server Developer Edition). I created a stored procedure that was just a simple SELECT statement, and used this stored procedure as my report source (“Pull“ method). I probably created this report 20 different ways, testing various options, and here's what I found:
1) When you begin to design your report, you are prompted to connect to the database when you choose an OLEDB connection (CR.NET doesn't use ADO.NET, and ODBC requires a system DSN). Your choices are to use Integrated Security, or enter a user ID and password. If you choose Integrated Security, Visual Studio will use your credentials to connect to the database via Windows Authentication.
If you choose to enter a user ID and password, VS will connect using SQL Server authentication. You cannot enter another domain account in the user ID and password boxes and connect using those credentials. This information can only be used for SQL Server authentication. Even though you must enter a password at this stage, the password information is not stored within the report file for security reasons. The password is only used to establish the database connection. This means you must apply logon information as shown in Eric's article.
2) Integrated Security can only be used in two contexts: when inside Visual Studio (which, since it is a Windows Application, can directly use your network token to connect to the remote database), or when IIS and SQL Server are on the same production machine. If you try and use Integrated Security on a production IIS Server with a remote SQL Server, you will get the “Logon Failed” error. This is because the application cannot impersonate your credentials from the IIS Server to the SQL Server (the “double hop”) without kerberos or some sort of delegation set up. Kerberos is running on the network I tested, and normal ASP.NET apps impersonate my credentials to the SQL Server, so it appears that the Crystal Reports engine cannot take advantage of kerberos. Not every network has kerberos running, so if in doubt, ask the network admins.
If you are using the pull method, this means your only option to connect to a remote SQL Server is to create a new SQL Server login with minimum permissions (note: do not use 'sa' or some other administrative login--this is a bad security practice), and use that login's credentials in your code. This is actually not a bad thing at all, but I could not find mention of this in any Crystal documentation.
3) The Crystal Reports engine cannot switch from Integrated Security to SQL Server security. This means that if you design your report using Integrated Security, you must supply a domain user's credentials in code to log on to the SQL Server (you can change the user, but it must be a domain account). If you will be using a SQL Server account in production, it is very important that you use this account's credentials to connect to the database during the design phase. The CR designer caches database connection information, so I found it best to completely delete all current connections, then exit and reopen Visual Studio before designing my report. I could then make a fresh connection to the database using the credentials I wanted. You can check by right-clicking the connection in Server Explorer and choosing “properties”. If you're using a SQL Server account, you'll see a user name; if using Integrated Security, the user name will not appear. I found it was also best if there was an account with the same user name on my dev database as well as the production database. You should be able to use a different SQL Server account in production than in development, but I had occasional problems I couldn't explain when I tried to do so.
4) Sometimes, after making small changes (such as removing a field from the report), I would get the “logon failed” error. I found that after verifying the database (right-click on report, choose Database >> Verify Database) and recompiling the project, the problem went away.
Personally, I still prefer the push method. Since this method uses ADO.NET datasets, it can take advantage of the optimzed System.Data.SqlServer class for better performance, and security is handled in the connection string or by application impersonation as specified in the web.config file.
My second Crystal Alliance article was published today:
Automatically Printing Crystal Reports in ASP.NET
source: http://aspalliance.com/509
Unlike the Windows Forms CrystalReportViewer toolbar, the web-based CrystalReportViewer toolbar does not include a print button. For reports that are directly rendered as HTML, the user can simply use the browser's print button. For reports rendered as PDF or Excel, the user can use the print functionality in either the Acrobat Reader or Excel to print the report.
Most users can figure out how to print the report, but some still need a little help, and in some cases, clients request this as part of their application specs. In this article, we'll review two methods for automatically printing a Crystal Report using the web-based CrystalReportViewer: the server side ReportDoucment.PrintToPrinter method, and a small client-side JavaScript that uses the window.print method. We'll also discuss rendering a report in PDF and allowing the user to print that document as an alternative to the two other methods. Because of the disconnected nature of web client applications, and security considerations, there isn't a perfect way to make every web-based Crystal Report automatically print. But, under certain conditions, we can provide this functionality in user-friendly ways.
This is a specific zone meant for designers of Crystal Reports (their other zone--the Developer Zone--is meant more for reporting application developers).
Report Design Zone: http://www.businessobjects.com/products/dev_zone/reporting/default.asp
Choose the Correct Version
Selecting the right reporting tool for your projects isn't easy - now with four core editions of Crystal Reports 10 and complimentary editions bundled in Borland®, BEA®, and Microsoft® IDEs, you may not know which one is right for you. To help you decide which Crystal Reports edition matches your current requirements, we've created a basic feature by editions grid with a drill-down link to over 150 popular features, covering versions 6 to 10.
Integrate Crystal Reports 10 with J2EE
http://www.businessobjects.com/dev/0607
Crystal Reports for Visual Studio .NET documentation
source: http://support.businessobjects.com/library/kbase/articles/c2010837.asp
What's sad is that so many people have posted forum questions regarding CR .NET docs that Business Objects decided to make a KB entry telling where the documentation is.
What's sadder is that they think someone who isn't looking under the HELP menu is going to search the KB.
So, for all you people who look no further than Google for answers, here's your simple answer: “Help” menu, enter “Crystal Reports” in the index box (kind of like Google in function, but different output format). There are also a couple other methods in the KB article (link above).
Crystal Reports .NET Programming
If you're new to Crystal Reports, or just to CR .NET, this is the book you need. Part how-to, part programming reference, lots of simple examples in both C# and VB.NET.
Crystal Reports for Visual Studio .Net 2003 Startup Guide
source: http://www.businessobjects.com/products/dev_zone/gate/default.asp?file=cr_startguide&ref=devzone_netzone_resources
A brand new whitepaper by Paul Delcogliano of Progressive Systems Consulting, Inc. The whitepaper provides step-by-step instructions on the details of generating and displaying reports in various deployment scenarios and application environments using Crystal Reports for Visual Studio .NET 2003. Sample code is included with this download.