Site hosted by Angelfire.com: Build your free website today!


PLEX WORLD

CRYSTOB: The Obsydian/Plex - Crystal Reports interface Troubleshooting and FAQ



 


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)

 

NEWS


QUESTIONS/ISSUES AND SOLUTIONS

  1. Error "Request Cancelled by User" returned after selecting action to print a report.
  2. 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"

  1. Page Range entry not enabled on Preview window/Print dialog
  2. Solution: 'PrinterStart' and 'PrinterStop' properties were not correctly set when calling crystl32.ocx

  3. When executing a report against a DB2400 database by using crystl32.ocx and passing a 'Selection Formula' criteria to filter, received error "SQL0516 - Prepared statement STMT0002 not found"
  4. 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

  1. How do I interact with the OCX control placed on panels?
  2. 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 parameter FLD Report Name (Narrative, 255)
    ActiveX Connection parameter FLD database source name (char, 30, limits ALL)
    ActiveX Connection parameter FLD database user ID (char, 30, limits ALL)
    ActiveX Connection parameter FLD database password (char, 12, limits ALL, case MIXED)

    ActiveX Connection script engine SYS VBScript

    Source:

    'Define report path and name
    XPath = &(1:)
    XRptName = &(2:)
    RptFileNme = Trim(XPath) & "\" & Trim(XRptName) & ".rpt"
    CrystalReport1.ReportFileName = RptFileNme
    'Connect to proper database; DSN will direct to Oracle or SQL Server
    XDsn = "DSN=" & Trim(&(3:))
    XUid = "UID=" & Trim(&(4:))
    XPwd = "PWD=" & Trim(&(5:))
    XConn = " " + XDsn + ";" + XUid + ";" + XPwd + " "
    CrystalReport1.Connect= XConn
    'Also connect any subreports to the proper database
    nRpt = CrystalReport1.GetNSubreports
    nCurr=0
    while nCurr < nRpt
    szName = CrystalReport1.GetNthSubreportName(nCurr)
    CrystalReport1.SubreportToChange = szName
    CrystalReport1.Connect= XConn
    nCurr = nCurr + 1
    wend
    '*************** below example of how parms look ***********
    'CrystalReport1.Connect="DSN=genplexpc2;UID=pcss;PWD=sscp; DSQ=jems351a"
    'CrystalReport1.ReportFileName = "F:\Reports\jes\OracleReports\CSEFLD.rpt"

    Override Printer (Source Code "ActiveX Override Printer" )

    ActiveX Override Printer parameter FLD PrinterName (char, 70, case MIXED)
    ActiveX Override Printer parameter FLD PrinterDriver (char, 70, case MIXED)
    ActiveX Override Printer parameter FLD PrinterPort (char, 70, case MIXED)
    ActiveX Override Printer parameter FLD PrinterCopies (FIELDS/Number, 2)

    ActiveX Override Printerscript engine SYS VBScript

    Source:

    Dim PrinterName
    Dim PrinterDriver
    Dim PrinterPort
    Dim PrinterCopies
    PrtrName = RTrim(&(1:))
    PrtrDriver = RTrim(&(2:))
    PrtrPort = RTrim(&(3:))
    PrtrCopies = &(4:)
    ' example of printer info passed in
    'PrtrName = "\\GENPLEXPC\IBM Proprinter XL"
    'PrtrDriver = "winspool"
    'PrtrPort = "LPT1:"
    CrystalReport1.PrinterName = PrtrName
    'msgbox (prtrname)
    CrystalReport1.PrinterDriver = PrtrDriver
    'msgbox (prtrdriver)
    CrystalReport1.PrinterPort = PrtrPort
    'msgbox (prtrport)
    'msgbox (prtrcopies)
    if (PrtrCopies > 0) then
    CrystalReport1.CopiesToPrinter = PrtrCopies
    end if
    ' direct to printer
    'CrystalReport1.Destination = 1
    ' generate report
    'CrystalReport1.Action = 1

    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 parameter FLDCourt Agency Description (Char, 70)
    ActiveX Set Parameters parameter FLD Report From Date (Date ISO)
    ActiveX Set Parameters parameter FLD Report Thru Date (Date ISO)
    ActiveX Set Parameters parameter FLD Court Agency Code (Code, 5)

    ActiveX Set Parameters script engine SYS VBScript

    Source:

    Dim XYear
    Dim XMonth
    Dim XDay
    Dim XUser
    Dim XDesc
    Dim XCaCode
    Dim XSort
    ' set parms up
    XUser = &(1:)
    XDesc = &(2:)
    CrystalReport1.ParameterFields(0) = "CT1n5A;" + XUser + ";TRUE"
    CrystalReport1.ParameterFields(1) = "CT1fzA;" + XDesc + ";TRUE"
    'set up the from date
    'CrystalReport1.ParameterFields(2) = "JE146A;date(1999,01,01);TRUE"
    XYear = Left(&(3:),4)
    XMonth = Mid(&(3:),6,2)
    XDay = Mid(&(3:),9,2)
    CrystalReport1.ParameterFields(2) = "JE146A;date(" + XYear + "," + XMonth + " ," + Xday + ");TRUE"
    'set up the thru date
    XYear = Left(&(4:),4)
    XMonth = Mid(&(4:),6,2)
    XDay = Mid(&(4:),9,2)
    CrystalReport1.ParameterFields(3) = "JE147A;date(" + XYear + "," + XMonth + " ," + Xday + ");TRUE"
    'set up court agency code
    XCaCode = &(5:)
    CrystalReport1.ParameterFields(4) = "CT1fyA;" + XCaCode + ";TRUE"

    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
    CrystalReport1.Destination = 1
    ' Generate report
    CrystalReport1.Action = 1

    Preview Report (Source Code "ActiveXPreview ")
    ActiveXPreview parameter FLD TargetPath (FIELDS/FullPath)

    Source:

    ' Direct report to window
    CrystalReport1.Destination = 0
    ' Generate report
    CrystalReport1.Action = 1

  1. On a deployed report the "Export" button is enabled but action does not execute correctly. Report runs fine in development station.
  2. 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:

    CR9 Runtime Files & Export.

    One suggested way to distribute report controlled through the Report Design Component (RDC) is by use of Crystal Reports Merge Modules.

  1. A group of reports have already been completed and are running well against one server (ORACLE). Now we want to 'convert' them to run against an AS/400 server, but the tables and fields implementation names generated with OBSYDIAN do not correspond to the ones used in the source database (and due to a design issue we cannot change the implementation names as the AS/400 does not support long names as ORACLE does). The tables' file and record formats and the fields lenghts and types are equivalent though.
  2. Solution: It is possible to convert the reports in this scenario but it requires following several steps:

    1. Open report in Crystal Report Designer.
    2. Select Database | Set Location menu option.
    3. Highlight the first table in list. Press Set Location button.
    4. You will be prompted to log on to the 'source' databse (Oracle SQL Server in this example). Enter User and password.
    5. On the Choose SQL Table panel press Log On Server button and select the 'target' database (the DB2/400)from the list of servers.
    6. Enter User and password.
    7. Select the corresponding table from the SQL_Table list.
    8. Back on Set Location panel, highlight the next table and press Set Location.
    9. Select the AS/400's database from the 'SQL Data Bases' list and the corresponding table from SQL Tables list.
    10. Repeat steps 8 and 9 for all required tables.
    11. Press 'Done' button when finished.
    12. Answer 'Yes' to 'Verify Database' dialog message (will received message stating database files have changed and will prompt for approval to fix the report, answer yes).
    13. From the 'Map Fields' dialog map the fields in the oracle database table to the appropriate field in the AS400 table.
    Note: You will have to map all fields from the original table (on the left) to fields from the new table (on the right). Crystal will not display a target field if it can not match the type, if this is the case, uncheck Match Type check box. This will display all fields from new table. Make the appropriate selection.
    14. Select Set Alias from the Database menu. Change table name to actual.
    15. If report has subreport(s) double click on a subreport field.
    16. Repeat steps 8 through 14 for each subreport.
    17. Right mouse click on a subreport and choose Change Subreport Links
    18. For each entry in the Field(s) to link to change Subreport parameter field to use and make sure select data in Subreport based on field is correct.
    19. Select 'OK' when finished.
    20. Double click on the subreport field. 21. Select Report | Edit Selection formula | Record menu option
    22. Make sure each formula is correct. When relinking the subreport in step 19, new parameter formulas are created and the originals may still be present. Delete the originals as they are now incorrect.
    23. Check syntax, save and close.
    24. Repeat step 18 thorough 24 for each sub-report
  1. How can I find out the PrinterName, Printer Driver, and PrinterPort values to pass in th API CAll to the Crystal Report Control (Crystl32.ocx)? The values Working with a network printer (under Windows NT V4.0).
  2. 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.

  1. How can I define report Header lines that display different information based on input parameter at run time? This to generate dynamic report column names (Plex 4.5 / Cyrstal Reports 8.5.02)
  2. 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):

  1. A report fails with error "Error detected by database DLL" during runtime on an end user PC. The report works fine on the Development environment. (Plex 4.5 / Cyrstal Reports 8.5.02 / AS400 Server)
  2. 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


    If you need to find additional information on how to fix this problem, Seagate offers a white pape for Download "Troubleshooting: "Error Detected by Database DLL"" by searching for the file cr_error_detected_by_db_dll.pdf" at: http://support.crystaldecisions.com/docs.
  1. How can I interface with reports created with Crystal Reports V9.0? On this version the RDC is the only method available (no OCX support). (Plex 4.5 / Crystal Report 9.0)
  2. Solution: The following EDGE Forum's Thread deals with this issue and provides the Plex Source Code objects needed to accomplish this task:


    Using Crystal Reports' RDC

    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

    How Visual Basic Dvelopers can migrate from the Crystal Report Engine Automation Server to the Report Designer Component

    In addition, other documents related to the RDC can be found at :

    Seagate's 'Developer Zone'

    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 2

    Here 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
    Wend

    Where:

    &(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
    OpenReport

    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:))

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

    SelectPrinter

    CrystalRDCScripts.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 = szDuplex

    PrintReport

    CrystalRDCScripts.PrintReport parameter FLD PrinterCopies (FIELDS/Number, 2)

    CrystalRDCScripts.PrintReport script engine SYS VBScript


    Source:

    'Print
    rpt.PrintOut FALSE, &(1:)

    ' Wait for the printer to complete
    bDone = FALSE
    While bDone = FALSE
    bDone = app.CanClose
    Wend

    CloseReport
    CrystalRDCScripts.CloseReport script engine SYS VBScript

    Source:

    ' Clean up
    Set rpt = Nothing
    Set app = Nothing

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

     

  1. How can I create a Crystal report that sorts the Detail section dynamically based on a input parameter passed from a Plex application? (Plex 4.5, Crystal Report 8.5.0.21)
  2. Solution: Follow this procedure on the RPT file:

  1. Create an input parameter field (i.e.{?RptSrt}) that will receive its input from the calling Plex panel and that will represent the different sort options
  2. Create a formula field where the output will be the Details section field you will be sorting by for each one of the {?RptSrt} values
  3. 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 ''

  1. Add the formula field to your Details region and supress it.
  2. Create a Group based on that formula (this will at run time define the dynamic sorting)
  3. 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

  1. I get a "Server has not yet been opened" while trying to print a report (Plex 4.5, Crystal Report 8.5.0.217 using Crystl32.OCX control)
  2. Solution: The most common cause of the "Server Has Not Yet Been Opened" error is that the Plex application used to interface with the report cannot successfully connect to the report's datasource (DSN), in this case veriofy the connection information for the DSN entry used for the reports execution. There are several other reasons why a database connection may not be successful tht are discussed in Seagate's technical brief Troubleshooting "Server Has Not Yet Been Opened -2147192184" error.
  1. A report that filters based on Null dates was originally developed for SQL Server. Now an AS400 version has been created and the tables relocated to the new server. Report executes correctly but dates validation no longer works (Interface developed with Plex 4.5, Crystal Reports 8.5)
  2. 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)

  1. How can I create a Crystal Report file to print labels that will receive an input parameter (from a Plex interface) with the number of labels to print? (Interface developed with Plex 4.5, Crystal Reports 8.5, OCX Control)
  2. 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.

  1. Error "Invalid link file. Not all fields on same index expression". (Interface developed with Plex 4.5, Crystal Reports 8.5, OCX Control)
  2. 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.

  1. I get an ODBC error MCH1210 when running Crystal Reports trying to access an AS/400 (Plex 4.5, Crystal Reports 8.5, Client Access Express V4.5
  2. Solution: Read the following notes:ErrorMCH1210withCrystalReportsandClientAccessExpress.zip

  1. When I try to open a Crystal Report developed on another work station I get the error "an OLE object in this report could not be loaded"and the report cannot be opened. Both PCs have the same Crystal Reports configuration (Crystal V8.5)
  2. Solution: Read the following notes: ErrorOLEObjectCouldNotBeLoaded.zip

  1. Error "The Script Engine: Microsoft VBScript runtime error. has reported the error: Object Required: 'CrystalReport1'" when running a Crystal Report through Plex interface using crystl32.OCX Control (Plex 4.5, Crystal report V8.5)
Solution: CrystalReport1 object was not created successfully (by the statement "CrystalReport1.ReportFileName = RptFileNme" on Plex's Source Code to define the Report to open) because the OCX Control was not registered correctly. Delete the control and re-add it to the panel.
  1. Using parameters to change the logic of the Record Selection Criteria on Crystal Reports makes the reports run slowly even if the correct indexing is defined on tables/views (Plex 4.5 interface, Crystal Reports V8.5)

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

  1. A Plex function calls two different reports each one with different input parameters (one contains an additional parameter). Validation sets the parameters. During run-time if one or the other report is called and the function is closed and opened to run the other report execution works as expected. If both are executed running the one with more parameters first an error occurs: Error "Invalid Parameter" (Plex 4.5, Crystal Report 8.5)

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

  1. How can I disable the Printer on the Crystal Reports Viewer controlled through a Plex interface using the OCX Control)? (Plex 4.5, Crystal Report 8.5)

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

  1. How can I work with Barcodes (print/read them)? (Plex 4.5, Crystal Reports 8.5)

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

An example of how a Crystal Report's formula is created to concatenate/encode three different fields with different types into a single barcode:

"*" & {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


Note: As amount of data to print on a barcode increases the barcode gets widder with the potential of having some bars not visible (because the box in Crystal Reports that contains the barcode has fixed dimensions) which will lead to reading errors (if some bars are missed the entire data string is rejected). A solution is to decrease the font size to account for the maximum length for all fields that make up the barcode.
  1. I am unable to delete a Parameter Field on a report. I select the field and try to delete an get the message "The field "?ParameterName" is being used in the report canvas. This action cannot be undone. Are you sure you want to delete this field?" and after pressing YES I get a second message "The field "?Parameter name" could not be deleted." "To remove this field you must remove all references to it in the report." (Plex 4.5 interface calling a Crystal Report 8.5 using the OCX Control)

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

  1. When trying to run a report that inputs date type parameters I get in some cases the following Error "The Script engine: Crystal Report has reported the error: Invalid parameter field value The statement which caused the error is:" and the report does not display. (Cool:Plex 4.5 controlling Crystal Reports through Crystal OCX)
Solution: The problem turned out to be the input date type parameter fields receiving a valid date in some cases and a BLANK value on others (that produced the error). Corrected by setting the minimiun date values for the parameter fields before calling the Crystal Report.
  1. Error "The script Engine: Crystal Report has reported the error: Not enough memory for operation" (Plex 4.4 interfacing to Crystal Report through OCX Control with ODBC connection for AS400)

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

  1. How can I evaluate NULL field values in formulas or record selection criteria? Crystal Reports does not recognize these values as 0 (numeric fields) or '' (Strings) (Crystal Report 8.5)

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

  1. Formatting multi-column report sections may add or push sections below page end (Crystal Reports 8.5)

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

  1. How can I duplicate functionality from one Crystal Report to another? (Crystal Reports 8.5)

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.

  1. How can I print a record counter that resets on a page change? (Crystal Reports 8.5)

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

  1. How can I improve performance on a Plex Client Server Win/As400 application that executes Crystal Reports? Indexes seem no to be used even after corrrectly defining the views and linking them correctly on Crystal Reports. (plex 4.5 - Win/AS400 generator, Client Access Express 4.5 for ODBC connection, Crystal Reports 8.5)
  2. 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.

  1. When printing reports I get the report printed correctly followed by a page with a message at the top: "PCL XL error Warning: IllegalMediaSource". (Cool: Plex 4.5 interface printing Crystal Reports 8.5 through OCX control).
  2. 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).

  1. How can I replace all instances of a string by another string in a field? (Plex 4.5/Crystal Report 8.5).
  2. 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

  1. A Plex function that launches report control through OCX Control is compiled with no error. When the report is launched the following error occurs: The script engine Microsoft VBScript compilation error has reported the error: Expected end of statement. Report fails execution.
  2. Solution: The Source Code object that sets the values for the Report Parameters included a syntax error that was not validated by Plex compilation but that failed during run-time. Inthis case it was an unexpected ; at the end of the statement CrystalReport1.ParametersFields(0)=&(1:);
  1. Total fields cannot be aligned for numeric fields even if using guidelines (Crystal Report 8.5)
  2. 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'

  1. For an assimilation project I need to convert date/timpestamps fields stored in a SQL Server database as a number of seconds from a date (Jan 1, 1970 Greenwich Mean Time). How can I perfom such conversion in Crystal Reports 8.5?
  2. 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);

    Note that this formula returns the time format in military format.

    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.

  1. What is the simples way to automate reports execution?
  2. 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.

  1. How can I insert Carriage Returns/Line Feed on formatting formulas (CR 8.5)
  2. 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

  1. Error using Client Access driver to setup ODBC DSN to execute Crystal Reports V8.5 "ODBC error: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - FileName in DatabaseName type *FILE not found". Problem occurs with Version 5 Release 3 Modification 0 of Client Access but not with previous Client Access Express' driver (V4 Release 5 Modification level 0). All properties are identically set on both versions.
  2. 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.

  3. How can I create a formula to calculate age? (CR 8 and above)
  4. Solution: The following formula displays a number representing the age based on a Birthdate field. Truncate(((Today - {spGET_ISC_MATERIAL_DATA.BIRTHDATE}) / 365), 0)
  5. How can I create a formula to format a phone number (numeric field)? (CR 8 and above)
  6. 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

  7. How can the DB Name (fully qualified name for each table) be removed in CR10 and CR9? This is necessary to create 'generic' reports that can run in multiple deployed locations where the DB tables and views are identifical but the DB name can differ
  8. 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)

  9. Error "Invalid file link. Not all fields in same index expression" when attempting a second link between two table in Visual Linking Expert (CR 8.5)
  10. 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

  11. Error: "Query Engine Error: 'If tables are already linked then the Join Type cannot change'". CR10 design and runtime
  12. 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.

  13. Error: 'The script engine: Crystal Report has reported the error: No error The statement which caused the error is:' (CR10)
  14. Error was produced on a Crystal Report used as a template that did not include any table yet. Just added and linked files.

  15. I want to omit a detail line under certain critera. I created a formula that makes use of the previous() function to test the values in which the details' SUPPRESS property is set but the formula does not execute correctly for the first record (Crystal Reports Designed V8.5-10)
  16. 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:

  1. Error "Failed to open a rowset" followed by "Column qualifier or table TABLENAME undefined" (CR 10)
  2. 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)

  1. During run-time received error: The table '' could not be found. Report does not launch (CR10, runtime)
  2. Solution: One table was not properly located. Use Database|Set Datasource Location.

  3. On Crystal Reports formulas, is it possible to substitute a sequence of nested IF-THEN-ELSE by a CASE construct to simplify syntax?
  4. 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.

  1. How can I substring a non-iso date string to extract its components in a CR formula?
  2. 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.

  1. The Field Explorer is invisible and does not display by pressing the Field Explorer Button. Problem in both Design/Preview windows. (CR10)
  2. 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

  1. What are the general considerations during report design to improve performace? (Tested with CR8.5).
  2. 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

  1. NULL 400 dates (0001, 01, 01) being interpreted as 2001, 01, 01 (CR10)
  2. 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

  1. How can I Format a string representing a phone number to define a MASK? (Format: (AREA)3DIGITS-4DIGITS EXT)
  2. 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)

  1. Error "The strig is non-numeric during run-time (CR10)" during run-time (CR10).
  2. Solution: An input parameter that had been defined as a string was passed a numeric value

  1. Why does a Shared variable defined in a subreport cannot be read in the main report? The purpose of this shared variable is to pass a count of the subreport records to determine whether subreport should be displayed on Main report or not (CR10)
  2. 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)

  1. How can I change the legend's text for each one of the columns on one of my report's charts? The options available in "Chart Expert" only allow setting the Scales and Legend Heading. The current labels used are defined by the implementation name of the fields used to calculate the chart (CR10).
  2. 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.

  1. How can I convert a numeric date to ISO format? (CR10)
  2. 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 ''

  1. How can I create a report that only displays the 1st record retrieved?
  2. 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

  1. How can I filter by the last characters of a given string field if these correspond to a number? (CR10)
  2. 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}

  1. There was a COM method call failure. Please contact support with the following details. Function: FunctionName. Method Call: COMMethodNameInAPlexFunction. COM Result: -2147189176
  2. 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).

  1. Query Engine Error: 'This query cannot be performed. An illegal link cycle was detected'
  2. Solution: Incorrect table linking. A dependant table was linked to both primary table and dependant table.

  1. Received error "Failed to open a rowset. Details: HY000:[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL5016 - Qualified object name 400FILENAME not valid when launching a 400 report via Plex function
  2. 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

  1. A CR running against an Iseries (400) fails with error Logon failed. Details: 28000:[IBM][iSeries Access ODBC Driver]Communication link failure. Comm rc=8002 CWBSY0002 Password for user USERNAME on server SERVERNAME is not correct, Password length = 0, Prompt Mode = Never, System IP Address = IPADRESS
  2. 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.

  1. How can I save the password used for an ODBC connection so that when the Crystal Report file is launched no popup prompts the user to enter such value? This report includes a parent report that accesses data with an ODBC different from the one used on the subreport where the password needs to be saved (CRXI).
  2. 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.

  1. How can I display all the values selected on a Parameter Field (multiple-value or ranged-value parameter)? (CR XI)
  2. 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;

  1. How can I remove Carriage Returns and Line Feeds on a Comment field to reduce the number of lines the field needs to print?
  2. 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),'');

  1. How can I work around the 1,000 record limit on the values dd
  2. 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)

  1. How can I implement a NOT LIKE statement in a report's record selection formula to exclude all rows that include a given string?
  2. 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

  1. Solution:

  1. Solution:

 

CRYSTAL REPORTS RESOURCES

The following is a list of additional resources for Crystal Reports:

 
OBSYDIAN FAQ
Crystaluser.com (USA) This company provides Crystal Reports training
CRYSTOB FAQ
Chelsea Technologies Ltd (New Zealand) This company provides Crystal Reports training
METATASKS
INFO Limited (New Zealand) This company provides Crystal Reports training
METATASKS
Information Guide for Crystal Reports® resources for Crystal Reports users A list of resources and tutorials.
METATASKS
Crystal Reports: Training and Consultancy. Mike Lewis Consultants Ltd. (Edinburgh, UK) In addition to services Mike offers tips and FAQs for Crystal Reports.
METATASKS
101 Forum: Discuss Crystal . (New England, USA) The Crystal 101 Forum is a totally free forum for discussion of Crystal and its related products.
METATASKS
.NET Zone A community for Crystal Reports .NET developers who use products from Business Objects.
METATASKS
Developer Zone Tools to do your CR work, like sample applications, white papers and information about training..
METATASKS
Report Design Zone A Community for Report Designers.

 

 

DOWNLOADS

CRYSTAL REPORTS ON THE NEWS

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)


 


You are visitor

Email: l_gayosso@hotmail.com



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!