Sometimes technical, sometimes not, never a kitty

From the mailbag, in response to Exporting to Excel in Crystal Reports .NET - Perfect Excel Exports

Hi, I was searching in the internet about how to solve a problem I have and found your name in an asp alliance article, I left a post there but I prefer to email you.
The problem is this:
Im developing a project with VS 2005 and Crystal Reports, I need to export two reports to Excel but not in just two different xls files but just one with different sheets, if you know how to help me please

Crystal Reports isn't designed for this.  If you want to easily create Excel from data, you need to look into a product like Spreadsheet Gear, OfficeWriter or ActiveXLS.  I've used the free ExcelXmlWriter with great success.

I receive a lot of feedback from my ASP Alliance articles, usually comments.  I don't typically answer via e-mail, since that doesn't help the community.  Today I received the following message, and felt it was blog worthy.

Referring URL: http://aspalliance.com/532
Dear Rechard,

I have always been using Datasets and they have given me consistent Results. In fact, I DO NOT USE Report Viewer but export CR Report to a PDF File and then give it to the user as a URL.

BUT, WHEN DATA IS VERY LARGE, THE DATASETS GIVE PROBLEMS such as out of Memory, server not available, Timeouts, hanging etc. I have asked this question to many forums as to how to handle large data sets in ASP.Net with CR.Net. But, nobody has given me any satisfying answer.

I really want to know as to how to solve this problem.

Will You please Help Me.

With Warm Regards,

DN

Here's the problem--the amount of memory which can be utilized by an ASP.NET web app is 2GB (3GB if you use the /3GB switch).  If your dataset exceeds 2GB on a Windows 2003 Server, you will get a Server Not Available error.  Less than but close to 2GB will cause a significant decrease in performance or warnings about memory.

The answer is simple--you need a smaller dataset!  A 2GB dataset is far too large, especially when you're feeding it into Crystal Reports.  It sounds like you are dumping every bit of raw data into the report, and having the report process and present the data.  You really need to have your database queries doing more of the filtering and processing, then return a significantly smaller dataset.  In a web environment, it's best if you only do presentation in your reports--leave the calculations and filtering to the database.

This is a question I see very often, and you can set the paper orientation in code-behind as such:

ReportDocument.PrintOptions.PaperOrientation = CrystalDecisions.[Shared].PaperOrientation.Landscape

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.

 

Now playing: Rent - Out Tonight

If you haven't read the article "CodeSnip: Opening Crystal Reports in a New Window in ASP.NET" (http://aspalliance.com/525), do that before you read this.  I received this comment recently:

When I tried this, it was blocked by Windows XP SP2. When I said to temporary allow blocked items, it stated it had to refresh, and then nothing happen.  I got the original page back, not the new page containing the report.

Any suggestions.

My first suggestion is to make sure there are no other pop-up blockers installed and running.  Many antivirus programs install pop-up blockers (sometimes called "ad blockers"), and every search engine toolbar I know of also includes a pop-up blocker.  I use this codesnip in an application I wrote and support; on one support call, we found four pop-up blockers installed (XP SP2, McAfee, Google and Yahoo toolbars).

I also suggest permanently allowing pop-ups from the site you're working on--add your site to the "allowed sites" list (Tools >> Popup Blocker>> Popup Blocker Settings, then enter the URL of your site).  This will always allow pop-ups from the site in question, and will make debugging easier.

If you are exporting to PDF, XLS, etc. in this window, there are a couple of other settings you may need to adjust.  Go to Tools >> Internet Options >>Security, and choose the zone your website is in (Internet or Local Intranet), and click "Custom Level".  Scroll down about 1/3 of the way, to the Downloads section, and Enable both "Automatic prompting for file downloads" and "File download".  In my case, I've had to adjust these settings for both zones, since I develop and test in Local Intranet, but deploy to Internet.

I have yet to have a problem after doing all of these things.  You may need to close and restart IE to get all the settings to take effect.

 

Now playing: Marc Broussard - Home

How to report off a flat XML file through a Visual Studio .NET application

source: http://support.businessobjects.com/library/kbase/articles/c2015522.asp?ref=devzone_net_howto

This short KB article has sample code that demonstrates how to trpoty off a flat XML file using Crystal Reports .NET.

There are several causes for this message, you should read all the information before trying any one of these solutions.

1) This is a known issue in some versions of Crystal Reports, and there is a hot fix at http://support.businessobjects.com/library/kbase/articles/c2012318.asp.

Note that although the article's metadata indicate this hotfix is for CR .NET, the body copy speaks only of CR 9.0.  As such, apply this hotfix with some trepidation.  On Win XP systems, you may want to make a retore point in case the hotfix messes up your installation.

2) On a Win 98 machine, exporting to HTML 3.2 or 4.0.  There is no known fix at this time.  http://support.businessobjects.com/library/kbase/articles/c2013300.asp

This error message seems to stem from problems in crqe.dll, which is the Crystal Reports Query Engine.  If you search the knowledgebase at support.businessonjects.com for crqe.dll, you'll find additional articles that aren't specific to this error message that may also help/

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.

 

 

 

I have a web forms application based on Crystal Reports .NET 2003, and one of the options I present users is the choice between HTML output (using CrystalReportViewer), or export to PDF or XLS.  All the reports from this application open in a new window (see http://aspalliance.com/525), which adds an additional problem with Win XP SP2.  Usually, setting the pop-up blocker to "always allow pop-ups from this site" fixes the problem, but not in this recent case. 

In this recent case, the popup window opened normally, and HTML output worked fine.  For PDf and XLS output, the popup would open and close in an instant, with no prompt to download the file.  We eventually figured out that prompting for file downloads, and downloads themselves, can be disabled with IE6 and XP SP2.  Previous versions of IE allowed you to disable downloads, but the prompt disable was new to me.

Long story short, if your users aren't being prompted to download the output file, change the IE settings by navigationg Tools >> Internet Options >> Security >> Custom Level, and make sure "Automatic promting for file downloads" and "file download" are both enabled.

That window in the Crystal Reports .NET designer that shows the database fields, formula fields, etc. is called the “Field Explorer”, unless you need to reopen it.  If you need to reopen the Field Explorer, navigate View >> Other Windows >> Document Outline.  In the CR Designer, the Document Outline becomes the Field Explorer.  On other pages, it's just the Document Outline.

Laying out a Crystal Report can be stressful, especially when you're guessing at how wide to make a field so string data fits.  The problem is, string database fields (such as nvarchar) are measured in characters, but text felds are measured in inches or centimeters.  You could guess at the field width, run the report, see if everything fits, and keep adjusting and running until the entire database field fits the field on your report.

Or you can be smart.

Open your database (or database schema, whatever you are using), and see the maximum length of the data field.  Let's say we have a varchar(50).  We know the maximum number of characters is 50.  On your report, add a Text Object, and make it kind of wide.  Type in 50 M's (like this: MMMMMMMMMM...).  Resize the text object until all the M's are visible.  Drag the database field onto your report, and make it the same width as the 50 M text object.  That is the maximum width your database field will be.

In proportional fonts (such as Times New Roman or Verdana), the uppercase M is typically the widest letter (in monospaced fonts like Courier New, all the letters are the same width).  Using the letter M is an old typesetter trick, and carries over into today's world.  If you have ever used CSS, and set a font size to '1 em', the 'em' unit is equivalent to the width of the M in that font face (this is different than the <EM> tag, which italicizes the text).  The 'em' unit does not have a definite width (such as a cm or in), but is a relative width in each font based on that font's specification.

Design tip of the sporadic time interval:

If you need to change the size or orientation of a Crystal Report, you can do this in the VS IDE by right-clicking your report and navigating Designer >> Printer Setup.  On the first tab, you can choose the paper size and orientation.  Hit OK to return to the IDE, and the report canvas will automatically resize to what you just specified.

I don't recommend setting any of the advanced printer options in your report unless you know the people printing the report will be using the same printer (such as a network printer).  Also, if you can't fit your report on letter (or A4 for the metric world), the user is probably going to have to change the paper in the printer, which will more than likely cause some grumbling.

If your report is too wide for landscape, consider grouping the data into a more hierarchical report (not every report can be grouped, but many can).  Not only will this improve the readability, but it'll help fit the report on a standard paper size.

Right-click in the whitespace on your report, not on a section heading.  You get different menus when you right-click on a section head.

If changing the printer information in your report generates printer errors (such as “Invalid Printer Specified“) when viewed on other machines, try changing the printer information at run time.  Here is a knowledgebase article that will help:

How to Change Printers at Runtime using Crystal Reports for Visual Studio .NET

If you want to set up a report for 8.5x14, you need to select a printer capable of printing 8.5x14 paper.  The location to choose the paper size depends on the printer driver.  In most HP printers, there is a tab called “Paper“.

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.

 

 

 

 

One method of creating Crystal Reports is to use an ADO.NET Dataset (for good advice please read Reporting Off ADO.NET Datasets).  To create the report from a dataset, you have to model the dataset in an XML Schema Document, which has the .XSD extension.  You use the XSD as the 'source' of your report at design time, and at runtime, set the data source to the ADO.NET dataset in memory.

From time to time, requirements for your reports change, and you have to change the dataset.  If you used an XSD to design your report, you need to follow these steps to update your report:

1) Update your XSD.  Either make the changes manually, or re-create the entire document, whichever is easier.
2) Open your report in design view in VS .NET.
3) Open the Field Explorer, and right-click on "Database Fields".
4) Choose "Set Location..." from the menu.
5) Choose the data source you wish to update in the "Current Data Source" dropdownlist.  You can choose either the entire dataset, or just a single table (in many cases, there will only be one table per dataset, so either choice is as good as the other in this case).
6) In the "Replace with" window, expand "More Data Sources", then "ADO.NET (XML)".
7) Double-click "Make New Connection".  You may see your dataset listed, however, I have found from experience that sometimes you need to overwrite the cached version.
8) In the new window, browse to your XSD and choose "Finish".  Leave "Use Visual Studio Data Class" unchecked.
9) If you have a table chosen in the "Current Data Source" dropdownlist, highlight the replacement table you just added, and click "Replace".  If you have a dataset chosen, highlight the replacement dataset and click "Replace".  If the "Replace" button is not activated, you need to make sure you have the correct structure chosen in both places.
10) Click "Close".  You may be asked to remap fields if any field changed its data type.  To map the fields, un-check "match type", and match fields one by one, clicking "Map" after each selection.  Do this until all the fields that need to be re-mapped have been re-mapped.
11) You should now see all yoru changes reflected in the Fields Viewer (you may have to expand "Database Fields").

Brian Bischof, author of Crystal Reports .NET Programming, has posted a comparison of SQL Server Reporting Services and Crystal Reports:

During 2004, Microsoft grabbed the attention of the Visual Studio .NET community by announcing a new reporting product: SQL Server Reporting Services (SSRS). Not only did they promise to give programmers a new reporting tool, but it was going to be free as well. Suddenly everyone was comparing Reporting Services to Crystal Reports - the report designer that has been bundled with VB since VB 3 and integrated into Visual Studio .Net (and will also be included in the next release of Visual Studio .Net 2005).

The goal of this paper is to illustrate differences between SSRS and the current version of Crystal Reports, Crystal Reports XI (version 11). Each product has its strengths and weaknesses and these are highlighted here. It's important to evaluate each product and consider which one works best for your application's reporting requirements.

 

This question has passed my threshold of answering as forum posts, so it's going on the blog:

Does anyone know how to export a crystal report into a seperate adobe reader instead of embedding the reader into the current browser window?

I am successful in exporting to pdf and excel, but it would be great if I could bring up a "save as or open" prompt.

Any ideas would be greatly appreciated.

The "save as or open" prompt is something the browser does automatically when it encounters a file type it can't open itself (either unknown file type or viewer program unknown).  There is a setting in Acrobat Reader you can use to override this, but it's one the clint has to make--you can't script it.  For Reader 6, go to Edit >> Preferences >> Internet, and uncheck "Display PDF In Browser".

If this doesn't solve your problem, try reading CodeSnip: Opening Crystal Reports in a New Window in ASP.NET.

These questions were received as a private comment in response to my Automatically Printing Crystal Reports in ASP.NET article:

Very useful and interesting article. Thanks a lot. I wanted to use this in a .Net app. Can we determine the number of copies to be printed dynamically using the second method(javascript)? Say If the user clicks on the print button (HTML submit button), the application would pre-determine how many copies can be printed for that user and automatically print them?
Also, the second window was opening in full size for me instead of 0 height and width. Please let me know what I'm doing wrong here?
Again...this is a wonderful article.

To the best of my knowledge, I don't think you can set the number of copies to print through JavaScript.  When printing from a browser, all print settings are handled through the browser's print dialog, and I don't know of any way to script that without getting into ActiveX and trust permissions.

It's tough to tell what's going on in the second question because there are a couple of code snippets that open new windows.  My best guess would be that the script string isn't being renered correctly.  View the source of the parent page, and look for the rendered script string.  Make sure it's correct after it's rendered.

"A Crystal Reports job failed because a free license could not be obtained in the time allocated. More licenses can be purchased direct from Crystal Decisions or through the Crystal Decisions Online Store."

This error can be due to a number of factors.  If you have deployed your application on a server and get this message, it means you possibly entered the wrong keycode when you registered Crystal Reports .NET 2003.  You can follow these instructions from Business Objects to reset the keycode: http://support.businessobjects.com/library/kbase/articles/c2012716.asp.  You will have to register your CR .NET again when you open a report in Visual Studio.

Something else to check is that all the versions of the referenced components are correct.  In my recent case, I was preparing for a demonstration on my laptop.  I had installed CR 10 before installing Visual Studio 2003.  When I created my demo project in Visual Studio, all of the referenced Crystal Decisions components were 9.1.5000.0 except for CrystalDecisions.CrystalReports.Engine.  That component was version 10.0.3300.0.  By deleting this incorrect reference, and adding the correct version, the error was resolved.

I see this question quite often in the Crystal Reports forum at ASP.NET.  My advice is always to download the web samples at:

Visual Basic .NET Web Samples

C# .NET Web Sample Applications

Then, follow the Readme file to install the “Discrete Parameters” example.  This has been my guide, and it works great.

Additional help and tutorials are in the CR SDK:

Crystal Reports .NET SDK – Additional Conceptual Documentation and Tutorials

This file contains additional conceptual information and tutorials for using Crystal Reports in Visual Studio .NET (including a developer reference). This documentation applies to Crystal Reports 9 & 10 and Crystal Reports for Visual Studio .NET 2002 & 2003. Sample applications built using these tutorials are available for download (cr_net_sdk_tutorial_samples_en.zip).

Crystal Reports .NET SDK – Sample Applications from Tutorials

This file contains C# and VB .NET Windows and web sample applications. These samples were built using the tutorials provided in the ‘Crystal Reports .NET SDK – Additional Documentation and Tutorials’ (cr_net_sdk_additional_en.zip). These sample applications applies to: Crystal Reports 9 & 10 and Crystal Reports for Visual Studio .NET 2002 & 2003.  

A number of methods out there use the CrystalReportViewer control to set parameters.  That's great unless you need to export your report to PDF (or XLS or RTF).  This is a code snippet I use, which sets parameter values directly on the Report Document object:

  rptCount = New ReportDocument
  rptCount.Load(Server.MapPath("reportname.rpt"))

  ''Get the collection of parameters from the report
  crParameterFieldDefinitions = rptCount.DataDefinition.ParameterFields
  ''Access the specified parameter from the collection
  crParameter1 = crParameterFieldDefinitions.Item("Param1")
  crParameter2 = crParameterFieldDefinitions.Item(“Param2")

  ''Get the current values from the parameter field.  At this point
  ''there are zero values set.
  crParameter1Values = crParameter1.CurrentValues
  crParameter2Values = crParameter2.CurrentValues

  ''Set the current values for the parameter field
  crDiscrete1Value = New ParameterDiscreteValue
  crDiscrete1Value.Value = Request.Form(“param1value“)

  crDiscrete2Value = New ParameterDiscreteValue
  crDiscrete2Value.Value = Request.Form(“param2value“)

  ''Add the first current value for the parameter field
  crParameter1Values.Add(crDiscrete1Value)
  crParameter2Values.Add(crDiscrete2Value)

  ''All current parameter values must be applied for the parameter field.
  crParameter1.ApplyCurrentValues(crParameter1Values)
  crParameter2.ApplyCurrentValues(crParameter2Values)

Once you get used to working with parameters, you can “automagically” display them on your web pages.  This series of articles shows you how to get a list of parameters from your report file, and display them on your web form, where you can prompt for inputs:

Automagically displaying a Crystal Reports Parameters - Part I
Automagically displaying a Crystal Reports Parameters - Part II
Automagically displaying a Crystal Reports Parameters - Part III

If you're new to Crystal Reports .NET, get this book:

Crystal Reports .NET Programming

It is both an introduction to Crystal Reports .NET, as well as a object model programming guide/reference.  Examples are in both VB.NET and C#.