 |
 |
 |
 |
Click here to add PLEX WORLD. CRYSTOB: PLEX (OBSYDIAN, Cool:Plex, AllFusion Plex, CA Plex) - Crystal Reports Interfacing and FAQ to your list of favorites
I have been developing with Plex and Crystal Reports (CR) since 1995 and with these tools I have been able to generate the most complex and intricated reports I have been requested such as State-mandated forms, Financial Reports, and others without the need for additional tools. Crystal Reports is a very powerful tool that despite being classified as a 'Report Writer' can even be considered a Business Intelligence application for certain analysis and data manipulation tasks. Due to all these capabilities some IT professionals consider CR as a 'complex' solution with a very 'steep' learning curve and attempt to compare it with other much simpler software that can rapidly present tabular reports but lack the capabilities to generate dynamic formatting, multi-region forms with multiple independent queries pulling data from disparate sources and report sections interacting with one another, graphic analysis for trend reporting, etc. It is the intention of this site to provide guidelines for some common tasks that I have encountered using these technologies to conduct the entire Systems Reports' Lifecycle on projects spanning a variety of industries.
The intended audience for this document are OBSYDIAN (Cool:Plex) developers which have covered the introductory documentation on the tool at least (OBSYDIAN Getting Started/tutorial) and with a working knowledge of system reports analysis and development with Crystal Reports. Items, terms, and words in italics are concepts that can be found in Windows/OBSYDIAN (Cool:Plex) or Seagate's documentation.
This list of problems/solutions is based on the experience I have had developing with Plex and Crystal Reports. If you cannot find the response to a particular problem I suggest you do a search on Seagate's Knowledge Base. In addition, I encourage you to participate in the Seagate's User Forums where you can find solutions/get suggestions from developers around the world.
If you have questions, don't hesitate to drop me an email. I provide on-demand remote (100% via the internet) hourly consulting for the entire Development and Project Lifecycles at the most competitive cost in the market.
Developed by: Lucio Gayosso, MIS/M, BS, PLEX & Crystal Reports Expert (1999-2010)
Solution: Crystal Reports file was set to ‘No Printer’ in the ‘Printer Setup’ dialog. Deselected this option.
Note: This will also solve error "The Script Engine: CrystalReport has reported the error: Error in file filename.rpt: No default printer"
Solution: 'PrinterStart' and 'PrinterStop' properties were not correctly set when calling crystl32.ocx
Solution: Different field types combination were used on the 'Selection Formula' and this was conflicting with the 'Packaging' defined for Windows NT work station. Disabled ‘Enabled Extended Dynamic (package) support’ (ODBC Data Source Administrator). Another 'Package' was intalled later and this option was turned on again and report executed without incidents
Make use of the OBWIN class library Source Code objects that allow you to set /get parameters on/from the OCX Control (i.e. Get ActiveX char event param, Get ActiveX character property, Get ActiveX numeric property, Set ActiveX numeric property). On most of the 'API Calls' to these Source Codes you will be needing values for OBWIN/Control name, OBWIN/Property name, and OBWIN/Property value. Consult Crystal Reports' documentation for the actual 'literal' values you will be needing to create for the 'Property Name' (i.e. ParameterFields, StartPrinter, StopPrinter, etc.).
Examples of Scripts to control the OCX
| Connection (Source Code "ActiveX
Connection") |
| ActiveX Connection parameter FLD Reporting Directory (FIELDS/FileName,
128) ActiveX Connection script engine SYS VBScript 'Define report path and name |
| Override Printer (Source Code
"ActiveX Override Printer" ) |
| ActiveX Override Printer parameter FLD PrinterName (char,
70, case MIXED) ActiveX Override Printerscript engine SYS VBScript Source: Dim PrinterName |
| Set Parameters (Source Code "Set
Parameters") |
| This Source Code exemplifies how different parameters are set. In this example 5 parameters are passed to the RPT file: User ID, Court Agency Description, Report From Date, Report Through Date, and Court Agency Code. The RPT parameters have all been defined as type STRING. ActiveX Set Parameters parameter FLD User ID (Char, 30) ActiveX Set Parameters script engine SYS VBScript Source: Dim XYear Note that in the RPT File, parameters are numerated starting from 0.
Also in the Source Code the fields names used for parsing (like XCaCode)
cannot contain blanks. |
| Issue Print (Source Code "ActiveXPrint") |
| ActiveXPrint script engine SYS VBScript
Source: ' Direct report to printer |
| Preview Report (Source Code "ActiveXPreview
") |
| ActiveXPreview parameter FLD TargetPath (FIELDS/FullPath)
Source: ' Direct report to windowCrystalReport1.Destination = 0 ' Generate report CrystalReport1.Action = 1 |
Solution: Some run-time files were missing in deployed system. The run-time
files needed for the different export formats are not part of the standard
Crystal Reports deployment. Check the 'Runtime File Requirements' on-line
help (shipped with the tool) for details.
For 32-bit applications the required files (and their installation directory
in the target PC) are:
FILE LOCATION DESCRIPTION
U2FCR.DLL \WINDOWS\CRYSTAL Crystal Reports format 32-bit
U2FDIF.DLL \WINDOWS\CRYSTAL DIF format
U2FHTML.DLL \WINDOWS\CRYSTAL HTML format
U2FODBC.DLL \WINDOWS\CRYSTAL ODBC data source
U2FREC.DLL \WINDOWS\CRYSTAL Record format
U2FRTF.DLL \WINDOWS\CRYSTAL Rich Text Format
U2FSEPV.DLL \WINDOWS\CRYSTAL Comma Separated Values format
U2FTEXT.DLL \WINDOWS\CRYSTAL Text format
U2FWKS.DLL \WINDOWS\CRYSTAL Lotus 1-2-3 format
U2FWORDW.DLL \WINDOWS\CRYSTAL Microsoft Word for Windows format
U2FXLS.DLL \WINDOWS\CRYSTAL Microsoft Excel format
LF*N.DLL \WINDOWS\CRYSTAL Picture export formats.Only needed in HTML export.
5a. On Crystal Reports and exports. How can CR be exported? What are the procedures/challenges? (Plex 4.5 and above, Crystal Reports 8.5 and above).
Solution: You need to use Plex Source Code objects with Scripts (i.e. VBScript) to define the export process for the launched report. These scripts should acount for the opening, execution, and exporting of the report. Run-time files that are not part of the default list of required files for Crystal Reports deployment are required (check Crystal Reports on-line documentation to see the list of required run-times based on your export options). Some details are described in the following EDGE threads:
One suggested way to distribute report controlled through the Report Design Component (RDC) is by use of Crystal Reports Merge Modules.
Solution: It is possible to convert the reports in this scenario but it requires following several steps:
1. Open report in Crystal Report Designer.Solution: Go to Start | Settings | Printers, select the printer you will be accesing, right-mouse click on it, select properties, and press "Print Test Page". The print out will list the resources formatted the way NT can recognize.
Solution: Follow this procedure to modify your RPT file (this process assumes you have already defined an input parameter that is set by your Plex function and that contains the value of the column headers to display):
Solution: This is a generic message that means that Crystal Reports cannot understand a message returned from the database driver. Several conditions can produce this error. In the particular circumstances where this was found the Report File was trying to access a table whose fields where not identical to the ones on the corresponding table on the Development environment
Solution: The following EDGE Forum's Thread deals with this issue and provides the Plex Source Code objects needed to accomplish this task:
Notes: The Crystl32.ocx control is no longer supported in version 9.0 of Crystal Reports (it was included on V 5.8 but just for backward compatibility). Following are some basic instructions on how to get started using the RDC (Report Designer Component) to control Cristal Reports. These instructions do not provide all details to take advantage, their purpose is just to exemplify.
You may also want to check the following Seagate's documents:
CRYSTAL REPORTS. REPORT DESIGNER COMPONENT - TUTORIAL AND GETTING STARTED GUIDE
In addition, other documents related to the RDC can be found at :
Manipulating the RDC with Plex (using Visual C++) involves three steps:
I) Define and instantiate a variable to be used to manipulate the RDC COM object.
II) Instantiate an actual instance of the RDC COM object and assign it to a variable.
III) Manipulate the Properties and Methods and then output the report.I) and II)
THE APPLICATION OBJECT
Visual C++ and Delphi, for example, are two environments where you can use the functionality of the RDC automation server but you can't add the RDC directly to your projects. In these cases, you must access the Report Designer's Report object by creating an instance of the Application object and then calling the OpenReport method as well.
' Build the report name
XPath = Trim(&(1:))
XRptName = Trim(&(2:))
RptFileNme = Trim(XPath) & "\" & Trim(XRptName) & ".rpt"' Define and instantiate a variable to be used to manipulate the RDC COM object.
' Connect to the Crystal report via the Report Designer Control (RDC)
Set app = CreateObject("CrystalRuntime.Application")
Set rpt = app.OpenReport( RptFileNme, 1 )' Redirect the report to the correct datasource
szSource = Trim(&(3:))pUserID = Trim(&(4:))
pPassword = Trim(&(5:))
pServerName = Trim(&(6:))rpt.Database.Tables.Item(1).SetLogOnInfo Trim(&(3:)), ,Trim(&(4:)), Trim(&(5:))
NOTE: There is a syntax error in the Developer's Help file installed with Crystal Reports version 8.
The SetLogOnInfo method is used along with the Active Server Pages (ASP) Reports Server to access table data. The Developer's Help incorrectly states that the database DLL name must be referenced when using the SetLogOnInfo method.
The CORRECT syntax used to access table data with the SetLogonInfo method is:
object.SetLogOnInfo ServerName, DatabaseName, UserID, Password
Parameters
ServerName - Specifies the name of the server or ODBC data source where the database is located.
DatabaseName (Optional) - Specifies the name of the database. This is optional because Crystal Reports store this part of the credentials (unless manually removed with Database|Set Location option).
UserID (Optional) - Specifies a valid user name for logging on to the data source.
Password (Optional) - Specifies a valid password for logging on to the data source
III) A
WORKING WITH PARAMETER FIELDS
Crystal parameters and stored procedure parameters are both set through the ParameterFieldDefinition Object.Source Code "RDC Parameters" script engine VBScript
rpt.ParameterFields.Item(1).AddCurrentValue( CStr(&(1:)) )
rpt.ParameterFields.Item(2).AddCurrentValue( CStr(&(2:)) )
Where:&(1:) = Parameter 1
&(2:) = Parameter 2Here rpt has beed define when the report was 'open'
III) B
Output (print) the report.
Source Code 'CrystalRCDScripts.PrintReport' script engine VBScript
'Print report
rpt.PrintOut FALSE, &(1:)'Wait for the printer to complete
bDone = FALSE
While bDone = FALSE
bDone = app.CanClose
WendWhere:
&(1:) = PrinterCopies (Fields/Number, 2)
&(1:) = Parameter 1
Following are some useful Source Code objects to work with the RDC (suggestion, create a function to scope your objects, for example "CrystalRDCScripts"):
- Function "CrystalRDCScripts" Source Code SRC OpenReport
- Function "CrystalRDCScripts" Source Code SRC PreviewReport
- Function "CrystalRDCScripts" Source Code SRC SelectPrinter
- Function "CrystalRDCScripts" Source Code SRC PrintReport
- Function "CrystalRDCScripts" Source Code SRC CloseReport
- Function "CrystalRDCScripts" Source Code SRC ExportReportToPDF
CrystalRDCScripts.OpenReport parameter FLD reporting directory (FIELDS/FileName, 128)
CrystalRDCScripts.OpenReport parameter FLD Report Name (Narrative, 255)
CrystalRDCScripts.OpenReport parameter FLD database source name (char, 30, limits ALL)
CrystalRDCScripts.OpenReport parameter FLD database user ID (char, 30, limits ALL)
CrystalRDCScripts.OpenReport parameter FLD database password (char, 12, limits ALL, case MIXED)
CrystalRDCScripts.OpenReport parameter FLD database server name (char, 30, limits ALL)
CrystalRDCScripts.OpenReport parameter FLD database user ID (char, 30, limits ALL)
CrystalRDCScripts.OpenReport parameter FLD database password (char, 12, limits ALL, case MIXED)
CrystalRDCScripts.OpenReport parameter FLD database server name (char, 30, limits ALL)CrystalRDCScripts.OpenReport script engine SYS VBScript
Source:' Build the report name
XPath = Trim(&(1:))
XRptName = Trim(&(2:))
RptFileNme = Trim(XPath) & "\" & Trim(XRptName) & ".rpt"' Connect to the Crystal report via the Report Designer Control (RDC)
Set app = CreateObject("CrystalRuntime.Application")
Set rpt = app.OpenReport( RptFileNme, 1 )' Redirect the report to the correct datasource
szSource = Trim(&(3:))
pUserID = Trim(&(4:))
pPassword = Trim(&(5:))
pServerName = Trim(&(6:))rpt.Database.Tables.Item(1).SetLogOnInfo Trim(&(3:)), ,Trim(&(4:)), Trim(&(5:))
1) Add Crystal Reports Viewer control to panel
2) Make an API Call to the following "PreviewReport" Source Code after the report has been opened.CRViewer1.ReportSource = rpt
'View the Report
CRViewer1.ViewReportCrystalRDCScripts.SelectPrinter parameter FLD PrinterDriver (char, 70, case MIXED)
CrystalRDCScripts.SelectPrinter parameter FLD PrinterName (char, 70, case MIXED)
CrystalRDCScripts.SelectPrinter parameter FLD PrinterPort (char, 70, case MIXED)
CrystalRDCScripts.SelectPrinter parameter FLD PrinterDuplex (FIELDS/Number, 2)
CrystalRDCScripts.SelectPrinter parameter FLD PrinterForm (FIELDS/Number, 2, Values: Add the different page formats, for example: Legal, Letter, 10X14, 11X17, Envelope, etc.)
CrystalRDCScripts.SelectPrinter parameter FLD PrinterOrientation (FIELDS/Number, 2, Values: Default = 0, Landscape = 2, Portrait 1)
CrystalRDCScripts.SelectPrinter parameter FLD PrinterPaperSource (FIELDS/Number, 2, Values: Add the different paper sources formats, for example: Casette, Envelope, Form Source, Large Capacity, Large Format, Lower, Manual, Middle. Small Format, Tractor Feed, Update, etc.)CrystalRDCScripts.SelectPrinter script engine SYS VBScript
Source:
'Define the printer
szDriver = Trim(&(1:))
szPrinter = Trim(&(2:))
szPort = Trim(&(3:))
rpt.SelectPrinter szDriver, szPrinter, szPort'Set the print options
szDuplex = &(4:)
szSize = &(5:)
szOrient = &(6:)
szTray = &(7:)
rpt.PaperSize = szSize
rpt.PaperOrientation = szOrient
rpt.PaperSource = szTray
rpt.PrinterDuplex = szDuplexCrystalRDCScripts.PrintReport parameter FLD PrinterCopies (FIELDS/Number, 2)
CrystalRDCScripts.PrintReport script engine SYS VBScript
Source:
rpt.PrintOut FALSE, &(1:)' Wait for the printer to complete
bDone = FALSE
While bDone = FALSE
bDone = app.CanClose
WendCrystalRDCScripts.CloseReport script engine SYS VBScript Source:
' Clean up
Set rpt = Nothing
Set app = NothingCrystalRDCScripts.ExportReportToPDF parameter FLD TargetPath (FIELDS/FullPath) Source:
'Disk file
rpt.ExportOptions.DestinationType = 1
rpt.ExportOptions.DiskFileName = &(1:)
rpt.ExportOptions.FormatType = 31' Portable Document Format (PDF)
rpt.Export False
Solution: Follow this procedure on the RPT file:
For example:
if {?RptSrt} = "U"
then {REPORT_FILES_DAILY_DEPOST.CreateUser}
else if {?RptSrt} = "C"
then {REPORT_FILES_DAILY_DEPOST.CASE_NUMBER}
else if {?RptSrt} = "B"
then totext({REPORT_FILES_DAILY_DEPOST.BankAccount})
else ''
Note:
A particular challenge will take place if the fields to sort by are not the same kind as the output for the formula fields can only handle one data type. For these cases two different sort formulas will have to be created (one to handle the string fields sort and one the numeric fields sort) and two Groups (one for each formula) added, one right after the other.
Each of these two formulas will have to "clear" the value of the other one if a field is its output. For example:
Second formula:
if {?RptSrt} = "R"
then {REPORT_FILES_DAILY_DEPOST.LN_CASE_TRANS_RECEIPT_NBR}
else 0
Solution: Null dates are stored differently on these two servers. The correct formats for validation in Crystal Report's formulas are:
SQL Server: Date(1753,1,1)
AS400: Date(0001,1,1)
Solution: Check the following Seagate's Reports that demonstrate different types of label generation. This zip file contains various sample reports that demonstrate the creation of labels available in Crystal Reports 8.x. and provide instructions on how to setup a report to print mutiple duplicate mailing labels depending on a parameter that you will pass from your Plex application. Basically two methods will be shown to output the number of labels created:
The example report is set up using a Main report and a Sub report.
Solution: This message appears when trying to insert a second link between tables from different data sources. Go to Database|Set location and update the location for all tables on the report. Then try re-adding the links with Visual Linking Expert.
Solution: Read the following notes:ErrorMCH1210withCrystalReportsandClientAccessExpress.zip
Solution: Read the following notes: ErrorOLEObjectCouldNotBeLoaded.zip
Additional details:
Problem was encountered in the following formula that defined two different Record Selection formulas based on passing a User ID parameter ({?CrtUsr}) or not:
if {?CrtUsr} <> ''
then ( {CASE_TRANS_BY_VOIDED_DTE.CT1FYA} = {?CT1fyA} and
{CASE_TRANS_BY_VOIDED_DTE.JEEYA} in {?JE146A} to {?JE147A} and
{CASE_TRANS_BY_VOIDED_DTE.JEEZA} = {?CrtUsr}
)
else ( {CASE_TRANS_BY_VOIDED_DTE.CT1FYA} = {?CT1fyA} and
{CASE_TRANS_BY_VOIDED_DTE.JEEYA} in {?JE146A} to {?JE147A}
)
The view CASE_TRANS_BY_VOIDED_DTE.JEEYA contained the right indexing for the three fields involved in the two formulas and also, if the formulas were tested separately, indexing worked fine and performance was the expected one.
Solution: Using a 'If then Else' formula for your record selection prevents the record selection formula from being passed on to the SQL statement's WHERE clause. This means the record selection process will occur in SCR, instead of in the database. This could significantly increase the time it takes your report to process.
If CR 8 or higher is used, the Record Selection Formula can be written where the IF-THEN-ELSE is defined for the results the database fields to filter. The advantage of this approach is that it allows processing to occur on the server rather than the client machine. This speeds processing of the report as the indexing is kept during report execution.
In addition, by moving the filter criteria to the server, the generated SQL is visible on Database|Show SQL query.
For the above formula, the formula was rewritten to:
{CASE_TRANS_BY_VOIDED_DTE.CT1fyA} = {?CT1fyA} and
{CASE_TRANS_BY_VOIDED_DTE.JEeyA} in {?JE146A} to {?JE147A} and
{CASE_TRANS_BY_VOIDED_DTE.JEezA} = ( if {?CrtUsr} = ""
then {CASE_TRANS_BY_VOIDED_DTE.JEezA}
else {?CrtUsr}
)
More details can be found at: http://support.crystaldecisions.com/library/kbase/articles/c2004910.asp
Solution: Clear the ParameterFields property the elements in the array that are different on the reports. on Crystal OCX to fix invalid parameter error if Panel Status Report is run and then any other report in the same Report Control session
CrystalReport1.ParameterFields(7) = ""
If multiple parameters need to be cleared the following "Clear Report Parms" Source Code (script engine SYS = VBScript) can be created:
CrystalReport1.ParameterFields(0) = ""
CrystalReport1.ParameterFields(1) = ""
CrystalReport1.ParameterFields(2) = ""
CrystalReport1.ParameterFields(3) = ""
.
.
.
CrystalReport1.ParameterFields(n) = ""
Where:
n = Number of parameter for the report with the maximun number of parameters launched from the Plex function
Solution: On the OCX Control placed on the panel go to Custom Properties (the control needs to be visible on the panel designer to change these properties). On the Preview Window tab uncheck Print.
Note: If the global setting of the Print Button is not a viable solution (as in cases where multiple reports are triggered by the same interface) you can manipulate the OCX Property with a source code.
SetPreviewPrintButton VBScript Source Code:
Dim XShowButton
XShowButton = &(1:)
CrystalReport1.WindowShowPrintBtn = &(1)
Where
&(1:) = OBJECTS/*Boolean
To use barcodes you need to purchase a barcode font. A major barcode font vendor is Azalea
http://www.azalea.com/
Most common barcodes:
· Code 39 (Most popular. Code 39 character set includes both letters and numbers. Normally used on video rental stores, ID Cards, and labels)
· Code 128 (Code 128’s character set supports the lower 128 ASCII characters, including upper and lowercase letters, numbers, punctuation, and control codes. Commonly used on Shipping Industry, Inventory, and Tracking)
· Interleaved 2 of 5 (Interleaved 2 of 5 has a numeric-only character set and is very compact because every pair of numbers is mapped to one character. Mostly used on shipping industry, on the sides of corrugated boxes)
· PDF417 code (PDF417 uses TrueType fonts. Fonts print quickly and are easy to use from within other applications)
· UPC code
Once the Barcode fonts are installed. Download and install the User Function Library (UFL)
UFL is an Azalea Software's plug-in for Crystal Reports that adds the start and stop bars to the input string, calculates any necessary check digit(s), and maps the output into fonts’ character set (The UFL is needed because a chunk of text cannot be simply formatted in a barcode font and create a scannable bar code symbol)
To use the barcode just select it to change the output of a field in a formula field.
For additional details: Download the Scr_barcodes.pdf at http://support.crystaldecisions.com/docs
Note: There are also some free Barcode fonts but formulas will have to be created in crystal Reports to handle the control characters for the barcode (delimiters). An example of a free barcode I have used:
Font: Free 3 of 9 Barcode (free3of9.ttf)
Created By: Matthew Welch
E-Mail: daffy-duck@worldnet.att.net
Web Address: http://home.att.net/~daffy-duck
"*" & {JUROR_MASTER_PHYSICAL_TAT.COURT_AGENCY_CODE} & '-' & cstr({JUROR_MASTER_PHYSICAL_TAT.JURMSTYER},0, '') & '-' & cstr({JUROR_MASTER_PHYSICAL_TAT.JUROR_NUMBER},0, '') & "*"
Details on this formula:
* is a delimiter that indicates where the barcode starts/ends
- is a delimiter to strip out the three different fields with a Plex function
cstr({TableName.FieldName},0, '') is used to convert a number to a string without
making use of decimals on without a thousands indicator
USE OF BARCODES ON A PLEX FUNCTION
To read the Barcode on a Plex function just add a string or numeric field (depending on what kind of data you want to read) and if the field has focus and the barcode is scanned, the value is passed to the panel. The Barcode reader operates just as an input device (like a Keyboard) and all the string read is considered a single keystroke.
To trigger processing based on the string scanned, the barcode can embed a 'Postamble'. Typical postamble values: Carriage Return + Line Feed, Carriage Return, None. If using Carriage Return the Plex panel can trigger Action Diagram processing if the ENTER key is associated with a Logical Event; in this case when the barcode is read the string is passed to the panel and the postamble Carriage Return executes the Logical Event associated with the ENTER key.
Following is an example of a Crystal Report generated Barcode using Code 39 that was read through a Plex interface
Solution: The field could be deleted because it was used somewhere on the report (in this case it was an invisble field placed in a hidden text object).After deletion of the object the parameter could be deleted
Note: There are several places that a parameter can be used in a report so it is important to check them all. There are five primary places to check for the parameter:
· Query Parameter
· Stored Procedures Parameter
· Record Selection and Group Selection Formulas
· Conditional Formatting Formulas
· Formulas
For more details on how to locate where a Parameter Field is used look at the following Seagate's KB item
Solution: This is a error that can be caused by different reasons. In this particular case it was related to a Record Selection formula validating a non-existing database field (had to re-set tables location). For other reasons for this error check the following Seagate's Knowledge Base article
Solution: This is a limitation on the Formula Editor (validation of NULL fields).
A suggested solution is to Convert the NULL to the default value of their data type (0 for Numbers, "" for Strings, (0, 0, 0) for Dates, (0, 0, 0, 0, 0, 0) for Date-Time fields). The conversion of NULL to defaults is handled through.
For additional details see Crystal Decisions' Knowledge Base Article
Solution: The offset distance for the following sections (in this case, the Page Footer) was calculated incorrectly and despite having the Preview window display correctly, the Page Footer was truncated upon printing. This is a known bug in Crystal Reports 8.5. for which a patch exists (Service Pack 2). Link to patch and additional information available at: http://support.crystaldecisions.com/library/kbase/articles/c2010952.asp
Solution: You can COPY/PASTE formulas from one report to another. This will create the formula on the destination with all the logic in it.
NOTE: The COPY/PASTE technique in Crystal Reports just works with one object at the time but you can duplicate multiple objects if you embed all of them in a TEXT OBJECT. If only portions of formulas need to be copies or just the qualified names of the objects placed on the report need to be copies, select the object and COPY/PASTE into a text editor.
Solution: Create two formulas, one will count the number of records per page with a global variable (B) and another one will reset the variable value at the beginning of each page (A)
A) ResetRecordCounter with the following logic:
WhilePrintingRecords;
NumberVar RecordCounter := 0
Place this formula in the page header (and supress it).
B) DetailsRecordCounter with the following logic:
WhilePrintingRecords;
NumberVar RecordCounter;
RecordCounter := RecordCounter + 1;
Place this formula in the Details section of the report
Solution: The problem was that the Sort Sequence defined for the ODBC connection was defaulting to Hexadecimal (*Hex). When the ODBC DSN was changed to specify the sort sequence table QSYS/QSYSTRNTBL the indexes were correctly used. On Client Access Express V4.5 this setting can be made under the Language Tab, Sort Type: Sort based on specified table. On different PC/AS400 Communications Packages this ODBC setting might look different.
Solution: Problem due to to some incorrect setting for original printer used to setup report. Although no incorrect configuration was found for printer options, re-Set "Default Properties" on Printer Setup (on the report file).
If you don't want to accomplish this in the functions that insert the record in the DB you can do it in the report file by creating a formula with the following code:
stringVar InputField := {TableName.FieldName};
stringVar ResultField;
ResultField := replace(InputField,"StringToLookFor","ReplacementString");
ResultField;
And place this formula on the reports' Details section
Solution: A new report was created using summary fields from another (a template file). Delete and re-add them
Note: Alignment of numeric fields, even if no particular format is applied might be related to the Negative property. To set this property off, click on the field and go to Properties|Number|Customize|Negatives. Set this value to 'None'
Solution: User the following algorithm in a Report Designer formula:
NumberVar TimeChange := -5;
NumberVar SecPerDay := 86400;
NumberVar SecPerHr := 3600;
Truncate(({FieldName}-(TimeChange * SecPerHr))/SecPerDay) + Date(1970,1,1);
The formula returns the time based on a -5 hr time difference for the Eastern
Time Zone in the US from the Greenwich Median Time. For other time zones in
the US (with changes to TimeChange value): Central Time = -6, Mountain time
= -7, Pacific Time -8. Add this formula to the report details section (or
the apropriate section where the date field needs to be displayed).
Now, for the Time difference enter in another formula:
NumberVar TimeChange := -5;
NumberVar SecPerDay := 86400;
NumberVar SecPerHr := 3600;
btime( Remainder(({FieldName}-(TimeChange * SecPerHr)), SecPerDay)/SecPerDay);
In addition, other cases for date/timestamps conversion are covered in the following document:
Date-Time Formula Functions (PDF) . This document outlines the formula functions available for use with Date/Time fields and Date/Time strings in both the Crystal Reports Designer and Seagate Info Report Designer versions 5, 6, and 7. Also included are sample formulas that demonstrate some of these functions.
Solution: An easy approach is to compiling a report into an executable file whose automation can be handled by a Windows Scheduled Task.
For compiled reports (reports contained in a single EXEs):
Compiled reports are part of a report distribution feature supported in earlier
versions of Crystal Reports (prior to V8.0). This feature can be implemented
with CR 8.0 and 8.5 by using the "Compiled Reports and Distribution Expert"
available for download from CR's website (visit: http://support.businessobjects.com/communityCS/FilesAndUpdates/scr8_distr_expert.exe.asp).
For Crystal Reports version 9.0 and aboveit is now recomended by Business
Objects (CR new company) using the "Report Application Server"
(RAS) to provide end users with interactive report viewing capabilities. For
information on the RAS, download 'intro_ras.pdf' from: http://support.businessobjects.com/communityCS/TechnicalPapers/intro_ras.pdf.asp
. This PDF (Introduction to the Report Application Server Crystal
Reports 9) is a technical brief that provides an introduction to the
Report Application Server as a solution to deliver reports to users through
the Web or network.
Solution: You need to use the ASCII codes ( Chr(13) and Chr(10)) for such characters (attempting to use the actual keystrokes will result in formula errors). An example of formula that uses such characters (to form an address label):
// Expanded & concatenated Address Format:
// 1st Line:Street Nbr + Predirectional + Street Name + Street Type + Postdirectional
+ Unit Designation:
// 2nd Line: AddressLine2
// 3rd Line: City + State + Zip
{Addresses.StreetNumber} + " " + {Addresses.PreDirectional}
+ " " + {Addresses.StreetName} + " " + {Addresses.StreetType}
+ " " + {Addresses.PostDirectional} + " " + {Addresses.UnitDesignation}
+ Chr(13) + Chr(10)
+ {Addresses.AddressLine2} + Chr(13) + Chr(10) + {Addresses.CityOrParish}
+ " " + {Addresses.AddressState} + ", " + {Addresses.ZipCode}
Note: Special attention should be considered in using these characters when calling reports with the OCX control as described on this Knowledge Bases' article Passing a CR/LF into a formula using crystl32.ocx results in error 20515
Solution: The SERVER tab entry that defines the SQL default library entry conflicted with the access to the specified libraries. Cleared QGPL entry.
NOTE: For a runtime environment, a similar error was triggered upon launching a report even after having updated the ODBC DSN per the solution above. In this case the problem got resolved by deleting the ODBC DSN entry and adding it again making sure the QGPL library had been cleared.
Solution: The following formula displays a number representing the age based on a Birthdate field. Truncate(((Today - {spGET_ISC_MATERIAL_DATA.BIRTHDATE}) / 365), 0)
Solution: Enter the following Format Phone Number formula replacing
with your field
if (isnull({FieldName}) or {FieldName} = " ") then
' '
else
FormattingFormula = ""
Dim strLen
Dim str
Dim i
strLen = Len (FieldName })
For i = 1 To strLen
str = { FieldName }(i)
if IsNumeric (str) then
FormattingFormula = FormattingFormula & str
else
'Nothing is done'
end if
Next i
FormattingFormula = "(" & FormattingFormula (1 to 3) &
") " & FormattingFormula (4 to 6) & "-" &
FormattingFormula (7 to 10)
end if
Solution: In CR10 the option to remove DB Name is back (this option was
removed in CR90 but present in CR8.5). In addition the Override Qualified
Table Name property needs to be defined. See for more details:
How
to modify the fully qualified name for each table in Crystal Reports 9
Additionally, see the following related EDGE thread:
Crystal
9 - One Rpt working dinamicly with diferent Libs (AS400)
Solution: Problem due to an attempt to link tables that reside in different databases or accessed through different datasources. An approach is to re-set table locations for all tables so that they can be accessed through the same datasource. For additional details check on the following Knowledge Base article Linking Restriction Issues in Reports with Multiple SQL/ODBC Data Sources
Solution: problem was caused due to a secondary table on report linked both to primary and secondary tables. Removed the link to primary table as information to pull on report should have been accessed through secondary table. For additional details see the attached example.
Error was produced on a Crystal Report used as a template that did not include any table yet. Just added and linked files.
Solution: The previous() function is trying to evaluate a non-existing record for the 1st record on the report. A 'Record Counter' field to identify the first record needs to be created and used to determine whether the previous() function can be used or not on the current detail line.
if {#Record Count} = 1
then false (this will set the SUPPRESS PROPERTY to false meaning the detail
line is visible)
else if previous({Calendar_report.LN_CASENUM}) = {Calendar_report.LN_CASENUM}
and previous({Calendar_report.LN_CHARCD1})={Calendar_report.LN_CHARCD1} (in
this case 2 fields are tested from the previous detail line. If both values
are equal to current values, detail line is invisible)
then true
else false
A related approach can be to use the Next() function which evaluates a field in the next detail printed. Do the following:
if isnull({@NextCase})
then false
else true
Solution: report included a subreport that enforced an inner join for a table that returned no records. Changed to left outer join.
Other situations that presented a similar error condition:
Details: 22007:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetune from character string.
Original formula code:
{TableName.TimeStampField} = Date(1753, 01, 01)
Solution: Extract the date part of the timestamped field:
Date({TableName.TimeStampField}) = Date(1753, 01, 01)
Exact message:
Details: 22007:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.
Solution: Modify sub report filter criteria to pull data somehow, for example:
move filter criteria to Detail section's visibility [Section Expert|Suppress(No
Drill-Down)|Formula Editor) so that records are pulled but if they need
to be filtered out the section only hides them.
Solution: One table was not properly located. Use Database|Set Datasource Location.
Solution: Yes, the following code will exemplify how the CASE construct is entered (tested with CR 8.5):
Stringvar Result;
select {?IncludeStreetAddrOrDOB}
case "A" : Result := {@Address}
case "B" : Result := {@DOB}
case "C" : Result := {@DOB and Address}
end;
Result;
Italics represent the keywords.
A variable (Result) is declared.
Select indicates the field, formula, or parameter to be tested (in
this example a parameter that contains three posible values to specify whether
an Address, a DOB, or a combination of both fields will be printed).
Case test the three possible conditions (if value is "A",
the variable Result is assigned the results of the formula that gets an address,
if "B" the results of the formula with the Date of Birth if "C"
the results of the formula that gets the DOB & Address).
The formula ends up displaying the contents of variable Result.
Solution: Use the "Substring" operation (tested with CR8.5)
// Lucio Gayosso 03/29/05 Format Date6 field to include hyphens to form mask YY-MM-DD
stringVar YY:= {MVR_RequestFile.MVRDOB} [1 to 2];
stringVar MM:= {MVR_RequestFile.MVRDOB} [3 to 4];
stringVar DD:= {MVR_RequestFile.MVRDOB} [5 to 6];
stringVar FormattedDate;
FormattedDate := YY + '-' + MM + '-' + DD;
FormattedDate;
Additional information, from Crystal Report's help:
Substring A substring is simply a part of a larger string.
Subscript ([array]), Subscript (string)
Usage
x[y]
«Extract the y element from string x.»
x[y to z]
«Extract the y to z range of elements from string x.»
x[n]
«Extract the n element of array x.»
The subscript ranges are 1 origin; they start at 1 rather than 0.
Subscript is used to extract one or more characters from a text string or to extract an element from an array.
Example(s)
[100,233,466,998][3] = 466
«Returns 466; 466 is the third element in the array.»
LNAME [1] = S
«Returns S, where LNAME = Smith.»
Postal [6] = V
«Returns V, where Postal Code = T5A 9V2 (the space between A and 9 counts as an element).»
{customer.POSTAL CODE} [5 to 7] = "9V2"
«Returns 9V2, where Postal Code = T5A 9V2.»
{file.ITEMNUMBER} [4 to 5] = "40"
«Returns 40, where ItemNumber is A1/4020/B10.»
Comment(s)
Do not confuse Subscript with In String. While Subscript tests a target string
for the presence of an element and extracts the element (if found) from the
string; In String simply tests the target string for the presence of the element.
Solution: A registry key value that controls the Field Explorer docking feature changed rendering it invisible. Additional details on how to fix the Registry key look at the following Knowledge Base Article: The Field Explorer does not appear in the Crystal Reports Designer
Solution: To improve performance:
1) Use indexed fields
2) Ensure the "Use Indexes or Server For Speeed" flag in Report Options is checked
3) Avoid using Record Seleciton based on formulas, whenever possible. SQL Server will not understand the formula and all records will be passed from the server to the client PC.
4) Try to avoid Subscript ranges such as {FileName.FieldName}[1 to 10]
Additional details in document: Performance Considerations for Record Selection Formulas
Also related: Using parameters to change the logic of the record selection criteria
Solution: Patches available at: Crystal Reports Service Packs ** Updated March 2005 **
Note: You need to create a registry key to HKEY_CURRENT_USER or HKEY_LOCAL_MACHINE as follows (this won't be done by applying the patch):
Software\Crystal Decisions\9.0 or 10.0\Crystal Reports\Database\NumberOfYearToShift=100 or multiples of 100 as a String value.
Related information: Date and time values in the Report Design Component 9 and 10 are not correct
Solution: Define the following Formula object (Tested with CR10).
if {Table.Field} = ' '
then ' '
else "(" +
Left(Trim({Table.Field}),3) + ")" +
Mid(Trim({Table.Field}),4,3) + "-" +
Mid(Trim({Table.Field}),7,4) + " " + "EXT" + " "
+
Mid(Trim({Table.Field}),11)
Solution: An input parameter that had been defined as a string was passed a numeric value
Solution: This is caused by the time the Shared variable is evaluated. If
a Shared variable result is used in the same section where the subreport is
located it, a value to be used in the Main report would not exist. A subsequent
section will be able to retrieve the result.
Also, read the following related CR's Knowledge Base articles:
How
to Share Subreport Data with the Main Report (CR7)
Shared
variable returns 0 when the subreport is suppressed
Additionally, CR10 includes an option to suppress Blank subreports (Format
Subreport|Subreport tab|Suppress blank subreport)
Solution: This has to be worked in the PREVIEW window. Refresh your report
to get the chart to display all the labels that you need to modify. Right
mouse click and select "Edit Axes Label"
Additional informatin (from CR Online Help):
Changing the chart's legend text
On the Preview tab, click the text in your chart's legend to select it.
Tip: Be sure to select the text and not the entire legend.
Right-click the legend text and select Edit Axis Label from the shortcut menu.
In the Label Aliasing dialog box, add the text you want to see in the Displayed
Label field.
Click OK.
Solution: Formula to convert Numeric date field to ISO format (YYYY/MM/DD):
stringVar ConvDate := ToText({ElecConv.ICITCONVDT});
// Remove commas in date
ConvDate := Replace (ConvDate, ",","");
stringVar YearValue := Mid(ConvDate, 1, 4);
stringVar MonthValue := Mid(ConvDate, 5, 2);
stringVar DayValue := Mid(ConvDate, 7, 2);
if ConvDate <> ''
then YearValue + "/" + MonthValue + "/" + Dayvalue
else ''
Solution: A simple approach is to place the fields on the Report Header section instead of the Detail line. The Report Header prints The first record in the report
Solution:
a) Create a formula (ExtractLastChars) to read the string field and extract the characters that will be tested. For example: stringvar Suffix:=Right({FieldName},2); where 2 means the last two characters will be tested.
b) Create a second formula (TestIfNumeric) to validate whether the extracted characters represent a number. For example, IsNumeric({ExtractLastChars}); where the formula returns FALSE is the chars do not represent a number
c) Modify the Record selection criteria to read or discard records based on the boolean result from b). For example: not {@TestIfNumeric}
Solution: Problem caused by Crystal Report file including subreports where one of them was not using the same DB define for all others (corrected through ReportOptions|Set Location).
Solution: Incorrect table linking. A dependant table was linked to both primary table and dependant table.
Solution: This C/S WinC client with iSeries server application was trying to launch a CR file which was attempting to access a WinNT server DB. Corrected by allocating files to the appropriate DB2/400 DB
Solution: Even though the Plex client function that launches this CR was correctly passing the logon credentials, the problem was caused by the report including some files pointing to a different database. Re-set location for all tables on main report and subreports to point to files on the same server.
63B. A similar error was produced by a Crystal Report that included multiple subreports. This report worked consistently in one environment but not another that was set in the same way.
There was a COM method call failure. Please contact support with the following details. Function: PLEXFunctionName. Method Call: Crystal_Reports_Runtime00_V11.IReport.PrintOut COM Result: Logon failed.
Details: 28000:[IBM][System i Access ODBC Driver]Communication link failure. comm rc=8270 - CWBSY0270 - User profile will be disabled with next invalid password, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.10.61 [Database Vendor Code: 8270 ]
Solution: The problem was caused by some files in the main report were including a reference to the DB owner associated with the ODBC DSN originally used when the report was developed. Re-located all tables/views files in the main report and all subreports. The relocation removed all original DSN reference so that only the following properties were present on each file/view: Table Name and Overriden Qualified Table Name.
Solution: Define the subreport's ODBC connection via a connection string so that the password can be saved. The format of the connection string depends on the target database accessed by the ODBC Connection.
For the case at hand the subreport was accessing a Progress database where the connection string was obtained from
http://www.connectionstrings.com/?carrier=progress
Which is one of the multiple connection strings defined at:
http://www.connectionstrings.com/
And the resulting string was: DSN=DSNName;DB=DBName;HOST=HostNameSuchAslocalhost;PORT=8350;UID=UserID;PWD=Password
NOTE: After setting the password on a subreport via the ODBC String other users accessing the report were not able to logon as the password was not preserved for them. The solution was to define all connection credentials for the ODBC DSN and then update the connection string on the subreport to only include the DSN Name + password. This format was a trial and error as it seems that the ODBC String validation in Crystal clears or rearranges the entered values and no documentation on Crystal Report's online help explained such validation.
The final string was:
DSN=DSNName;PWD=Password which allowed successful launch for all users without having to specify the password.
Solution: Crystal Reports does not display all the values selected for a multi-value selection parameter therefore a work-around is needed. A solution is the creation of a formula that concatenates all values extracted from the prompt.
//Lucio Gayosso 05/01/05. Formula to extract and concatenate all values
selected PROMPT parameter field
whileprintingrecords;
Stringvar ParameterValues;
ParameterValues := join({?PROMPT}, ' ');
ParameterValues;
In the formula above, chr(10) include a line-feed character so that there
a line for each selected parameter that is displayed; any string can be joined
such as '' which would cause a blank space in between displayed values.
Additional details on this approach can be found at:
How to display all values in a multiple-value or ranged-value parameter
Note: One problem with displaying the entire list of selected values could be that depending on how many values are selected, font size, and report's section where the parameter is displayed, an overflow of data might cause the report to fail if no room to display all selected values is available. An approach to this situation is to specify only the first and last elements of the selection by transforming the extracted string into an array where only the 1st and Nth element would be displayed to form a resulting string (Value 1 through N). To implement this, enhance the above formula in the following way:
//Lucio Gayosso 05/01/05. Formula to extract and concatenate all values
selected on PROMPT parameter field
whileprintingrecords;
Stringvar ParameterValues;
ParameterValues := join({?PROMPT}, ' ');
ParameterValues;
// Determine the number of elements in the array derived from the multi-selection
numbervar NbrOfElements;
NbrOfElements := Count(Split(ParameterValues, ' '));
NbrOfElements;
// Display the 1st and Nth element
Stringvar FirstSelection := Split(ParameterValues, ' ')[1];
Stringvar LastSelection := Split(ParameterValues, ' ')[NbrOfElements];
// Display Resulting string
Stringvar Result := FirstSelection + ' through ' + LastSelection;
Result;
Solution: Create the following @TrimCommentField formula
// Lucio Gayosso . Replace the newline character (chr(10)) and carriage return (chr(13)) with "" to minimize number of total lines Replace({FieldName}, chrw(10)+chrw(13),'');
Solution: This involves the creation of a registry key using a Registry Editor such as regedit.exe as described in the following document (due to space constraints I cannot post here. Send me an email to request the How To Exceed The 1000 Record Limit for CR XI Dynamic Parameters.ZIP file)
Solution: As NOT LIKE "*STRING*" cannot directly be implemented it is possible to define an If/Then/Else statement in the Record Selection such as the following:
(
if UCase({TABLE.DESCRIPTION}) like "*MISSING*"
then {TABLE.DESCRIPTION} = 'XXXXXXXXXXXXX'
else {TABLE.DESCRIPTION} = {TABLE.DESCRIPTION}
)
In this example, if the comparison field includes the search string MISSING
in the field TABLE.DESCRIPTION then the record is filtered by selecting
the field DESCRIPTION with an invalid value such as XXXXXXXXXXXXX otherwise,
if the search string is not found then the record is retreived
Solution:
Solution:
The following is a list of additional resources for Crystal Reports:
 |
Crystaluser.com (USA) This company provides Crystal Reports training |
|
|
Chelsea Technologies Ltd (New Zealand) This company provides Crystal Reports training |
|
|
INFO Limited (New Zealand) This company provides Crystal Reports training |
|
|
Information Guide for Crystal Reports® resources for Crystal Reports users A list of resources and tutorials. |
|
|
Crystal Reports: Training and Consultancy. Mike Lewis Consultants Ltd. (Edinburgh, UK) In addition to services Mike offers tips and FAQs for Crystal Reports. |
|
|
101 Forum: Discuss Crystal . (New England, USA) The Crystal 101 Forum is a totally free forum for discussion of Crystal and its related products. |
|
|
.NET Zone A community for Crystal Reports .NET developers who use products from Business Objects. |
|
|
Developer Zone Tools to do your CR work, like sample applications, white papers and information about training.. |
|
|
Report Design Zone A Community for Report Designers. |
Do you have any problem/solution you would like to share? Send me an e-mail
and I'll get it posted.
If you found any information interesting or useful please mention it on the
Guestbook on the main page of Plex world.
If you could contribute to keep this site free, personal checks or paypal
payment are gladly accepted (no matter how small the contribution)
Developed by: Lucio Gayosso, MIS/M (1999-2010)
Home
Background music: Tangerine Dream's "Poland" (same album's name)
Use the controls to Stop, Start, Play the music
 |
Listen to: R@DIO
DIMENSION and let your mind fly... |
|
Escucha: R@DIO
DIMENSION y deja a tu mente volar...
|
Man soll nie nie sagen!
