Tag Archive: Crystal

Crystal Report Data Source Updater

To address a long standing bug/issue with the Crystal Reports application forcing you to update every table/view/sproc one by one I built a quick/dirty application in c#.

You will need .NET installed and the Crystal runtime v13 to run it: http://snk.to/f-cztp0jk7

Here’s the code… no error trapping etc… I may improve it in the future:

            ReportDocument rpt = new ReportDocument();
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Crystal Reports (*.rpt)|*.rpt";
            ConnectionInfo ci = new ConnectionInfo();
            ci.ServerName = Microsoft.VisualBasic.Interaction.InputBox("Server");
            ci.DatabaseName = Microsoft.VisualBasic.Interaction.InputBox("Database");
            ci.UserID = Microsoft.VisualBasic.Interaction.InputBox("User");
            ci.Password = Microsoft.VisualBasic.Interaction.InputBox("Password");
            ci.IntegratedSecurity = (Microsoft.VisualBasic.Interaction.InputBox("SSPI (Y/N)") == "Y");
            TableLogOnInfo tli = new TableLogOnInfo();
            tli.ConnectionInfo = ci;
            for (int i = 0; i < rpt.Database.Tables.Count; i++)
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.DefaultExt = ".rpt";

Been busy- shocking lack of updates… watch this space :)

As discussed in my earlier post Crystal Reports 2011 Installation there appears to be a bug with the Crystal Reports 2011 installation. If you are experiencing this bug (INS00140) download the attached and follow these instructions:

Search for isKeyCodeValid.exe in the installation files and replace with http://tickett.net/downloads/crystal/isKeyCodeValid.exe

Please note: You will need the Microsoft .NET Framework v4 installed or the patch will not work.


Following on from https://tickett.wordpress.com/2011/05/19/crystal-reports-training-tutorial-part-2-building-a-basic-report/ I’m going to skim through the use of parameters (I initially thought this would be a really brief part of the tutorial then realised there is actually quite a lot to them!).

Parameter Types
Boolean: True or false (dropdown)

Currency: To the best of my knowledge this behaves the same as the number parameter type (textbox with validation)
Date: Pick from a calendar
DateTime: Pick from a calendar (textbox with validation for time)

Number: Textbox with validation
String: Textbox
Time: Textbox with validation

Parameter Options
I’m not going to cover all of the options- I recommend you experiment with them all to see how they behave for yourself
Prompt Text: Most common of all the options- this allows us to name our parameter something and have a more user friendly prompt

Allow Multiple Values: Goes without saying (see above)
*Note* Parameters which accept multiple values are great for your selection criteria but can be tricky to use for conditional formatting (the parameter now contains an array)
Prompt With Description Only: See below example (set to false)- you can see the values and corresponding descriptions

Now set to true- you can only see the descriptions

Allow Custom Value: See below example (set to true)- you may pick a value from the list or enter your own value

Now set to false- you are forced to pick a value from the list

Allow Range Values: Personally I always avoid the use of range values, and instead I have 2 prompts- one for the bottom of the range and one for the top (FromDate, ToDate for example)
Optional Prompt: Will allow the user to leave the parameter blank
*Note* If using optional parameters you must incorporate the HasValue function every time you access the parameter or the report will fail!
Edit Mask: For string parameter types only

  • “A” (allows an alphanumeric character and requires the entry of a character in the parameter value).
  • “a” (allows an alphanumeric character and does not require the entry of a character in the parameter value).
  • “0” (allows a digit [0 to 9] and requires the entry of a character in the parameter value).
  • “9” (allows a digit or a space, and does not require the entry of a character in the parameter value).
  • “#” (allows a digit, space, or plus/minus sign, and does not require the entry of a character in the parameter value).
  • “L” (allows a letter [A to Z], and requires the entry of a character in the parameter value).
  • “?” (allows a letter, and does not require the entry of a character in the parameter value).
  • “&” (allows any character or space, and requires the entry of a character in the parameter value).
  • “C” (allows any character or space, and does not require the entry of a character in the parameter value).
  • “. , : ; – /” (separator characters). Inserting separator characters into an edit mask is something like hard coding the formatting for the parameter field. When the field is placed on the report, the separator character will appear in the field object frame, like this: LLLL/0000. This example depicts an edit mask that requires four letters followed by four numbers.
  • “<” (causes subsequent characters to be converted to lowercase).
  • “>” (causes subsequent characters to be converted to uppercase).
  • “” (causes the subsequent character to be displayed as a literal). For example, the edit mask “A” would display a parameter value of “A.” If the edit mask is “00A00,” then a valid parameter value would consist of two digits, the letter “A,” and then two additional digits.
  • “Password”. Allows you to set the edit mask to “Password,” you can create conditional formulas specifying that certain sections of the report become visible only when certain user passwords are entered.

Report Layout
We can use the parameters to drive how our report looks (almost all section and field attributes can be determined by the value of parameters)

Create a string parameter called report_title and place it in the report header

Refresh the report, and enter some text for the report title

Let’s try one more example- create a new boolean parameter called hide_detail

Now in the section expert for our page header and detail sections we will enter a conditional suppression formula

Refresh the report choosing detail
And then choosing summary

Selection Criteria
Most commonly report parameters are used to determine our record selection formula
Create a number parameter EmployeeID and modify the record selection expert
{Orders.CustomerID} = {?Customer}
Enter a value

You will see your report displaying only records which meet your criteria

By default parameters are static and provide either:
-a textbox
-a dropdown list of predetermined values (entered while designing the report)
However- you can also create dynamic parameters which pull from a field in the database by selecting dynamic from the list of values box in the top right hand corner for the edit parameter window

Click the value field and select CustomerID
Now modify the record selection formula:
{Orders.CustomerID} = {?Customer}
And refresh the report

If a new value appears in the database they will appear the next time the report parameter is displayed

Cascading parameters can be created where you want to prompt for say Country then State (the selected value from the first parameter will impact the list of available values from the second parameter). I’m not going to cover this.

If you feel I’ve missed anything or have something to add please comment!


Hopefully you’ve read Part 1 – Installation / Configuration and you’re ready to start building your first report? I’ll be using Microsoft’s sample database Northwind on SQL Server for this part of the tutorial but you should be able to adapt almost any datasource to the examples. So let’s get started…

Create a blank report (File -> New -> Blank Report)
The Database Expert will automatically appear
Expand the OLE DB (ADO) node and double click Make New Connection

Select Microsoft OLE DB Provider for SQL Server and click Next
Enter your server login details, select the Northwind database and click Finish
*Note* If you are using Integrated Security ensure the User ID field is blank before you tick the Integrated Security box or else it will fail
Expand the server, database, schema & tables nodes and double click the Orders table to add it to the selected tables pane then click OK (we’re going to start real basic with just this one table)

Add Fields
If it isn’t already visible, open the Field Explorer (View -> Field Explorer)
Enable the display of field types (right click in the field explorer and tick Show Field Type)

Highlight the 6 fields as above and drag them into the detail section of the report. You will notice headers are automatically inserted.

Refresh the report (View -> Preview or Ctrl-R or F5 or the refesh icon from the toolbar)

I’m not going to go into detail, but now is a good time to explore the various formatting options for each field type. Right click a field and choose Format Field
For all fields you’ll see a standard set of formatting options (on the Common, Border, Font & Border tabs) but depending on the field type you will see one of the following tabs: Paragraph, Number or Date and Time
I chose to hide the time from the OrderDate and RequiredDate fields and remove the comma from the OrderID

Create a Formula Field
A formula field can be used for many purposes but will primarily be used to manipulate one or more fields
Right click Formula Fields -> New in the field explorer. We’re going to create a field to show whether the order has shipped, so let’s call it has_shipped and click OK

Take a moment to familiarise yourself with the Field Tree, Function Tree & Operator Tree- these list every field, function & operation available to us in our formula
*Note* Pay attention to the Default Values For Nulls dropdown (which hopefully you configured In the global Crystal options whilst reading Part 1 of this tutorial)
Move down to formula area and type an open curly brace { – the autocomplete popup should appear

*Note* If the autocomplete popup does not appear you probably need to update to the latest service pack
Enter the following formula:

if isnull({Orders.ShippedDate}) then
'Not Shipped'

Click Save and close and drag the formula from the field explorer into the detail section of your report
*Note* This saves the formula only and does not save the actually report
Return to the report preview to see your formula in action

Report -> Selection Formulas -> Record
Try experimenting- here are a few examples:

  • {Orders.Freight} < 10
  • {Orders.EmployeeID} = 3

After changing the record selection formula each time click Save and Close and refresh the report preview

Report -> Record Sort Expert
You can drag, double click or use the buttons to add sort fields

Use the radio buttons to determine whether the fields are sorted in ascending or descending order
After changing the record sort criteria click OK and refresh the report preview

There are 3 main ways to your data- I wont get into which to use when and why yet but I will briefly demonstrate each of them

Highlight the Freight field in the detail section of your report and click the Summary icon (or Insert -> Summary)

Refresh the report and scroll to the report footer to see your summary field
Delete the summary field so we can try method 2 (right click -> Delete or select the field and press delete/backspace)
Create a new formula called freight_total and enter the following:


Drag the new formula field into the report footer directly below the freight column

Refresh the report and scroll to the report footer to see your formula field

Delete the formula field so we can try method 3
From the field explorer right click Running Total Fields -> New

Let’s call our running total freight_total and choose Orders.Freight as out field to summarize
Drag the running total field into the report footer directly below the freight column
Refresh the report and scroll to the report footer to see your running total

Conditional Formatting
Another concept you’re probably familiar with and I’m just going to show you a few examples
1. Highlight freight costs > £50
Right click the freight field in the detail section of your report -> Format Field

The X-2 icon indicates fields which can be determined by evaluating a formula
Click the icon next to the Color dropdown and enter the formula

if {Orders.Freight} > 50 then

Click Save and close then OK
Refresh the report

2. Highlight rows where order was shipped after the required date
Right click the detail section -> Section Expert
*Note* Sometimes the section you right clicked on isn’t automatically highlighted in the section expert, so always check before proceeding to modify the section properties
Switch to the Color tab and click the conditional formatting formula icon and enter the following

if {Orders.ShippedDate} > {Orders.RequiredDate} then

Click Save and Close

Click OK and refresh the report

Wow- that took longer than expected. I’ve tried to skim the surface and will likely revisit a lot of the topics later in the tutorial.

If you feel I’ve missed anything or have something to add please comment!



I didn’t initially intend to include installation, but decided it was worth a brief mention.

As with many companies and products, SAP (and formerly Business Objects) have added new functionality and fixed bugs through the release of service packs. If you already have Crystal Reports installed, you have probably noticed the “check for updates” feature doesn’t work? Pop to the SAP website to check for the latest service pack:


Currently: Crystal Reports XI Release 2 Service Pack 6, Crystal Reports 2008 Service Pack 3, and as yet no service packs have been released for Crystal Reports 2011.

Most of the service packs have been released as “full builds”- this means you don’t need Crystal Reports installed (it is included within the service pack itself). If you are running a fairly old version of Crystal, I encourage you to uninstall and start afresh with the latest full build.


Now you have the latest service pack installed we can get started configuring your environment.

Launch Crystal Reports and access the options dialog from the File menu. I would encourage you to review each and all of the options- if configured incorrectly they can lead to insanity- however, if configured correctly they can save you a lot of time!

Here are a few of my recommendations (most of the other options should default to my preferred configuration or vary from report to report):

  • Layout -> Design View -> Grid -> Check
  • Database -> Tables and Fields -> Sort Fields Alphabetically -> Check
  • Database -> Advanced Options -> Automatic Smart Linking -> Uncheck
  • Formula Editor -> Null Treatment -> Default Values for Nulls
  • Reporting -> Save Data With Report -> Uncheck

That’s all for part 1- if you feel I’ve missed anything or have something to add please comment!


I ran a small training session a while back and wanted to flesh out my notes so they can hopefully form an online tutorial. Here’s the outline:

  1. Installation / Configuration
  2. Building a Report – The Basics
  3. Parameters
  4. Sub-Reports
  5. Grouping
  6. Cross-Tabs
  7. Understanding the Underlying SQL
  8. Limitations
  9. Tips/Tricks

The structure may vary a little- but all of the above topics should be covered at some point. I aim to post a few sections everyday (optimistic). Expect the first part shortly!


*UPDATE* I have now uploaded the file so you can download directly (see https://tickett.wordpress.com/2011/08/07/crystal-reports-2011-installation-bugfix-patch/)

*UPDATE* Official word from SAP: This should only be affecting Windows XP 32-bit machines that have KB2467175 installed. The removal of KB2467175 resolves the issue.

I bit the bullet and purchased the Crystal Reports 2011 upgrade (from the US store). Unfortunately when I tried to install I got an error:

The Product keycode is not valid. INS00140

Google only turned up one result: http://forums.sdn.sap.com/thread.jspa?threadID=1946287 which didn’t have a solution.

I ran process monitor from sysinternals to have a look if a registry check for previous version(s) was failing or likewise and found an installation log file which contain the following entries:

18:10:22.727 Calling Validator Entry Point..
18:10:22.727 [validate_ProductKey]acceptedKeyTypes CR.EnableCR;XC.EnableAddOn
18:10:22.727 [validate_ProductKey]appname: "C:\Documents and Settings\Lee\Desktop\tmp\DATA_UNITS\CrystalReports\dunit\product.crystalreports-4.0-core-32\actions\\isKeyCodeValid.exe"
18:10:22.742 [validate_ProductKey]argument: -keycode ******** -version 140 -property CR.EnableCR
18:10:22.742 [validate_ProductKey]initDir: C:\Documents and Settings\Lee\Desktop\tmp\DATA_UNITS\CrystalReports\dunit\product.crystalreports-4.0-core-32\actions\
18:10:22.742 [run_external_executable] cmdLine: "C:\Documents and Settings\Lee\Desktop\tmp\DATA_UNITS\CrystalReports\dunit\product.crystalreports-4.0-core-32\actions\\isKeyCodeValid.exe" -keycode ******** -version 140 -property CR.EnableCR
18:10:23.043 [run_external_executable] Exit code: -1072365566
18:10:23.043 [validate_ProductKey]keyTypes [CR.EnableCR] is bad
18:10:23.043 [validate_ProductKey]appname: "C:\Documents and Settings\Lee\Desktop\tmp\DATA_UNITS\CrystalReports\dunit\product.crystalreports-4.0-core-32\actions\\isKeyCodeValid.exe"
18:10:23.043 [validate_ProductKey]argument: -keycode ******** -version 60 -property XC.EnableAddOn
18:10:23.043 [validate_ProductKey]initDir: C:\Documents and Settings\Lee\Desktop\tmp\DATA_UNITS\CrystalReports\dunit\product.crystalreports-4.0-core-32\actions\
18:10:23.043 [run_external_executable] cmdLine: "C:\Documents and Settings\Lee\Desktop\tmp\DATA_UNITS\CrystalReports\dunit\product.crystalreports-4.0-core-32\actions\\isKeyCodeValid.exe" -keycode ******** -version 60 -property XC.EnableAddOn
18:10:23.343 [run_external_executable] Exit code: -1072365566
18:10:23.343 [validate_ProductKey]keyTypes [XC.EnableAddOn] is bad
18:10:23.343 Validation Failed for : ProductKey

So it looks like isKeyCodeValid.exe isn’t returning what is expected. I tried running the executable from the commandline and it simply errors. I guess there’s a bug?

Anyway, I wrote a command line app which simply returns errorcode 1 and replaced isKeyCodeValid.exe with my app. Voila!

Expect an update once I’ve had a chance to play with it!

Get in touch if you experience the same error and need help getting past it.


It’s been a long time coming, the last version was 2008. When Visual Studio 2010 started development and SAP released Crystal Reports 2010 for Visual Studio I was hopeful a new version was on the horizon- a long time has passed since then but finally Crystal Reports 2011 was released 03/05/11.

There seems to be very little new from what I’ve read but I will most likely still be trying it.

I can’t make much sense of the pricing:

UK Store- Full Version £500.25, Upgrade £310.50
US Store- Full Version $495 (£302), Upgrade $295 (£180)

Although I guess we should be used to companies trying to rip us off in the UK by now!

SAP / Business Objects Store: http://store.businessobjects.com/store/bobjamer/DisplayHomePage

Crystal Reports 2011 Release Notes: http://help.sap.com/businessobject/product_guides/cr2011/en/xi4_cr2011_release_notes_en.pdf

Crystal Reports 2011 User Guide: http://help.sap.com/businessobject/product_guides/CR2011/en/xi4_cr_usergde_en.pdf

Hopefully I’ll be posting another update shortly.


I just had a colleague ask me how to do this and was quite surprised you can only rotate by 90° or 270°.

After a little googling (finding a lot of “no, it can’t be done”) i found this: http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_20651257.html

Great thinking outside of the box and worked a treat. For those of you who can’t see the solution (ExpertsExchange sometimes make you register to view solutions)…

  • Orient the paper the opposite way to how you want it to come out (so portrait if you want landscape)
  • Add the field
  • Right click, properties and set text rotation to 90° or 270°



%d bloggers like this: