Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Crystal reports with media objects

  1. #1
    New Member
    Join Date
    Aug 2007
    Posts
    6

    Crystal reports with media objects

    How does the subreport in CR need to be structured to include E1 media objects on a CR reports? Are there any special database connectivity required to support the process. Currently using E1 8.11.

    Thanks


  2. #2
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251

    Re: Crystal reports with media objects

    How are you connecting to E1 Data currently from Crystal?
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  3. #3
    New Member
    Join Date
    Aug 2007
    Posts
    6

    Re: Crystal reports with media objects

    Larry,

    We are using Oracle tools to connect CR with the E1 database. One of our developers has used some of the instructions you have published in the past but they get hung up on trying to use the GDTXFT field in CR. It is defined as a picture in CR and they can't get text into that field in the sub-report. Any suggestions?

  4. #4
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251

    Re: Crystal reports with media objects

    "Oracle Tools"? not sure what that is. If you're using the data connection to JDE rather than a native database connection then, yeah, I'd believe that approach wouldn't work. You run into lots of limitations with non-native data connections - particularly one's developed by JDE/Peoplesoft.
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  5. #5
    New Member
    Join Date
    Aug 2007
    Posts
    6

    Re: Crystal reports with media objects

    We are able to create CR reports at will. The issue is including a media object from E1 on the CR report. Do you know if there is anything we can do with the GDTXFT definition in CR that is not allowing us to move the media object text into the data item in the report.

    Thanks

  6. #6
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251

    Re: Crystal reports with media objects

    What is your underlying database?
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  7. #7
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251

    Re: Crystal reports with media objects

    If you are on a Oracle Database - then there is another step I did in creating our database view of the F00165 Table.

    The view looks like this:

    ------------------------------------------------------
    connect PRODDTA/XXXXXX@XXXXXX;
    CREATE OR REPLACE VIEW f00165_view(
    NameObject_GDOBNM ,
    GenericTextKey_GDTXKY ,
    SeqNo_GDMOSEQN ,
    MediaObjType_GDGTMOTYPE ,
    LanguagePreference_GDLNGP ,
    UpdatedByUser_GDUSER ,
    DateUpdated_GDUPMJ ,
    TimeOfDay_GDTDAY ,
    ObjectTypeName_GDGTITNM ,
    QueueName_GDQUNAM ,
    FileName_GDGTFILENM ,
    MediaObjectVariableLeng_GDTXVC)
    AS SELECT
    GDOBNM,
    GDTXKY,
    GDMOSEQN,
    GDGTMOTYPE,
    GDLNGP,
    GDUSER,
    JDE2Date(GDUPMJ),
    GDTDAY,
    GDGTITNM,
    GDQUNAM,
    GDGTFILENM,
    JDETextFix(ROWID)
    FROM F00165
    WHERE GDGTMOTYPE = 0
    WITH READ ONLY;
    ---------------------------------------------------------------------

    Note that there are 2 Oracle Function Calls in this select.
    The first one, JDE2DATE, just converts the JDE Julian Date to a standard date.
    The second, JDETextFix(ROWID), does data type conversion on the BLOB data field. Here's the function I wrote to do this:
    ---------------------------------------------------------------------
    CREATE OR REPLACE FUNCTION "PRODDTA"."JDETEXTFIX" (p_rowid in
    ROWID) RETURN VARCHAR2
    IS
    v_textout VARCHAR2(4000);
    v_lob BLOB;
    v_buffer RAW(8192);
    v_offset INTEGER := 1;
    v_textlen BINARY_INTEGER := 8192;
    v_rawlen NUMBER;
    v_from_set RAW(2);
    v_to_set RAW(1);
    v_output RAW(8192);
    BEGIN
    SELECT gdtxft INTO v_lob
    FROM F00165 where ROWID = p_rowid;
    DBMS_LOB.READ (v_lob, v_textlen, v_offset,v_buffer);
    v_textout := ' ';
    v_from_set := HEXTORAW('7D00');
    v_to_set := HEXTORAW('7D');
    v_output := UTL_RAW.TRANSLATE (v_buffer, v_from_set, v_to_set);
    v_rawlen := UTL_RAW.LENGTH (v_output);
    if (v_rawlen > 4000) then
    v_rawlen := 4000;
    end if;
    if (v_rawlen > 0) then
    v_textout := UTL_RAW.CAST_TO_VARCHAR2 (UTL_RAW.SUBSTR(v_output,1,v_rawlen));
    end if;
    RETURN (v_textout);
    EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN (v_textout);
    END;
    ---------------------------------------------------------------------------
    Under Crystal 10 and 11 I don't believe the extra steps to remove the leading double-byte character (HEX(00)) is neccessary. But the conversion to the VARCHAR2 data type still is. There's probably a better way to do this now but this function was written 5 years ago and still works so . . .
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  8. #8
    New Member
    Join Date
    Aug 2007
    Posts
    6

    Re: Crystal reports with media objects

    We set up the view per your instructions. Worked great. Thanks.

    As a result of the set up when we try to retrieve the media object we get the entire BLOB from F00165. Is there a way to parse out just the text note embedded in the BLOB for printing on a report?

  9. #9
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,251

    Re: Crystal reports with media objects

    When you say you get the entire blob, do you mean that when multiple text attachments exist, you see all of them? or something else?
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  10. #10
    New Member
    Join Date
    Aug 2007
    Posts
    6

    Re: Crystal reports with media objects

    We see the following:

    {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl {\f0\fswiss\fprq2\fcha
    rset0 Courier New;}}
    {\colortbl ;\red0\green0\blue0;}
    \viewkind4\uc1\pard\cf1\f0\fs20 ORANGE LABEL AND BLUE STRETCH WRAP\par
    }
    All I want from this record is "ORANGE LABEL NAD BLUE STRETCH WRAP". What we have set up thus far does not get us to the specific text in the record.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.