Tag Archive: SAP


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.

Thanks
L

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
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.

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

Format
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'
else
'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

Filter
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

Sort
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

Summarize
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

1.
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)
2.
Create a new formula called freight_total and enter the following:

sum({Orders.Freight});

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
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
crRed
else
crBlack;

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
crYellow
else
crNoColor;

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!

L

Installation

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:

http://service.sap.com/sap/bc/bsp/spn/bobj_download/main.htm

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.

Configuration

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!

L

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!

L

*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.

L

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.

L

%d bloggers like this: