DOCX

Using the Crystal Reports ActiveX Object With InTouch or VB Data

By Vincent Mcdonald,2014-06-23 21:02
167 views 0
Using the Crystal Reports ActiveX Object With InTouch or VB Data

    Using the Crystal Reports ActiveX Object With InTouch or VB Data All Tech Notes and KBCD documents and software are provided "as is" without warranty of any kind. See the Terms of Use for more information.

    Topic#: 000738

    Created: October 1998

    Updated: October 28, 1999

    It is often desirable to generate a report based on either user-defined values or the current values that are stored in the InTouch tagnames. With the support of ActiveX in FactorySuite 2000 and Crystal Report’s ActiveX object (.OCX) and its parameter passing option, this has become a much simpler task. This topic will give you some options on how to use the Crystal Report ActiveX object to display reports based on data stored in InTouch (or VB) variables.

    Method 1

    This method uses the parameter option in Crystal Reports. This is a feature that was recently added to Crystal Reports which allows you to define a parameter field in the selection formula of the report. When this report is displayed, you are asked to supply the value for the parameter. Follow these steps:

    1. Create a report that has parameters defined in the selection

    criteria.

    a. Create a report in the normal fashion, but do not add a select

    option for the field that you want to define as a parameter.

    b. From the Crystal menus, select Insert/Parameter Field, then

    click the New button.

    c. Give your parameter a name, user prompt text, and select the

    data type.

    d. From the menus, select Report/Select Expert. Or use the icon

    and click the New button.

    e. Choose the column, the comparison operator and the parameter

    that you just created from the various drop down lists.

    f. Save the report.

    2. Configure the Crystal ActiveX object in InTouch or Microsoft;

    Visual Basic?:

    a. Add the object to an application window. It will only be

    visible in WindowMaker, so it doesn’t matter where it is

    located. Double-click the object.

    b. Click the General tab and enter the report file (.Rpt) with

    full path of where it was created in step 1. Enter the

    destination “To Window”.

    c. Click the Database tab and enter a Username for the database.

    If the report was created using ODBC, then the connect string

    must be specified. If the report was created with a native

    connection, then the connect string must be blank. The

    Connect string should look something like this:

    DSN=DSNName;UID=sa;PWD=; where DSN is the data source name

    configured with ODBC (e.g. DSNName), UID is a valid username

    to access the database (e.g. sa), and PWD is the user’s

    password (e.g. <blank>). Do not use any quotes.

    d. The remaining tabs are optional and they control the size and

    location of the window, printing options, and selection

    criteria. All of these, as well as the ones defined in steps

    a and b, can be accessed through the object’s properties at

    runtime.

     Write a script to launch the report. 3.

    a. Choose the type of script you want to use and add the following

    line to show the report.

    #CrystalReport1.PrintReport();

    where #CrystalReport1 is the name of the ActiveX object

    configured in step 2.

    b. When this script is executed in runtime, an entry box will

    appear asking you to enter the value(s) to be used for the

    parameter(s). Once a value is entered, the report will be

    displayed (or printed).

    Method 2

    This method appends to the selection criteria in Crystal Reports. Crystal

    reports considers the Where expression as two distinct sections. The first

    section is the links between the tables being used; the second section

    is the selection criteria to narrow down the result set. With the ActiveX

    object, you can add to or replace the selection criteria. 1. Create a report that does not have parameters defined in the

    selection criteria.

    a. Create a report in the normal fashion and do not add any

    parameters to the select option.

    b. If you plan on appending to the Select option, then enter any

    selection options that will not change. Do not add any

    selection options for the fields that you want to compare

    against InTouch tagnames.

    c. If you plan on replacing the Select option, then it doesn’t

    matter what is placed in the selection criteria, including

    parameters since it will all overwritten later on.

    d. Save the report.

    2. Configure the Crystal ActiveX object as described in step 2 for Method 1.

    3. Write a script to launch the report by appending to the selection criteria.

    a. Choose the type of script you want to use and add the following

    lines to show the report.

    #CrystalReport1.SelectionFormula="{ShipLog.Customer}='"+

    CustomerName +"'";

    #CrystalReport1.PrintReport();

    b. Use the SelectionFormula property to add to the selection

    criteria that is already being used in your report. The above

    example is comparing the Customer field in InTrack’s ShipLog

    table to the value in the InTouch tagname CustomerName.

    Notice brackets ({}) are used around the table name and column

    name. The maximum length is 131 characters.

    c. When this script is executed in runtime, the report will be

    immediately displayed.

    4. Write a script to launch the report by overwriting the selection criteria.

    5. Choose the type of script you want to use and add the following lines to show the report.

    #CrystalReport1.ReplaceSelectionFormula="{ShipLog.Customer}=’”+CustomerName+"'";

    #CrystalReport1.PrintReport();

    6. Use the ReplaceSelectionFormula property to replace the entire selection criteria defined in the report instead of appending to the end of it. Use this option only when you want to completely define the selection criteria. The maximum length is 131 characters.

Method 3

    This method passes values to parameters directly, which applies to Microsoft Visual Basic only. There are properties of the Crystal ActiveX that allow you to write directly to defined parameters. However, these parameters are arrays and they cannot be accessed by InTouch.

    1. Create a report by following step 1 of Method 1.

    2. Configure the Crystal ActiveX object as described in step 2 of

    Method 1.

    3. Writing the Visual Basic code to launch the report.

    a. The Crystal ActiveX object has a property for setting

    parameter values defined in the report. The ParameterFields

    property is an array type field.

    b. Use the following code to set a parameter value and display

    the report.

    CrystalReport1.ParameterFields(0)="Customer;"+CustomerNam

    e+ ";1";

    CrystalReport1.PrintReport();

    This will pass the value in the VB variable CustomerName to

    the Cystal Report’s Customer parameter.

    c. The format of the ParameterFields property is “Parameter

    Name;Value;SetValue” where SetValue is either zero or one.

    A zero will display the parameter entry dialog box for you

    to accept or change the value; a one will immediately display

    the report.

    d. The parameters are numbered in the order defined in the report

    starting at zero.

    e. If you are reporting from a stored procedure, then use the

    StoredProcParam array property instead of the

    ParameterFields property.

    Using the Crystal Reports ActiveX Control in InTouch and VB (Passing Parameters) If you have a report with a column called Customer Name in a table called

    Customer and you want to query for specific records like "ABC Co.", here's how to do it:

    (Be sure to enter the report path/name in the General tab of the object.) In InTouch:

    #CrystalReport1.SelectionFormula = "{Customer.Customer Name}= 'ABC

    Co.'";

    #CrystalReport1.PrintReport();

    In Visual Basic:

    Private Sub Command1_Click()

    CrystalReport1.SelectionFormula = "{Customer.Customer Name}= 'ABC Co.'"

    CrystalReport1.PrintReport

    End Sub

    Where the general form is:

    <ControlName>.<SelectionFormula>= "{<TableName>.<ColumnName>}=

    '<Query>'"

    J.Barkehanai, Update R.Clark

Report this document

For any questions or suggestions please email
cust-service@docsford.com