• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Crystal reports with media objects

jccoop

Member
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
 

jccoop

Member
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?
 

Larry_Jones

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

jccoop

Member
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
 

Larry_Jones

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

jccoop

Member
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?
 

Larry_Jones

Member
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?
 

jccoop

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

JEMILLER

Member
Media Object Text is encoded as RTF (Rich Text Format). You will need to strip out the RTF tags. If you search the web for "RTF strip" or "RTF stripper" you will find a number of C/C++ code examples. I have used these in the past to implement an RTF-stripping business function. If you need to achieve this in PL/SQL then you will probably have to implement one yourself. I did a quick search for RTF Strip PL/SQL and found a fews discussions but no actual PL/SQL code.
 

DSauve

Member
jccoop,

In your Crystal Report definition, for the media object field, click on Field Properties, and go into the Paragraph tab. Under Text Interpretation, select "RTF Text" and see if that makes it appear more readable. Good luck!
 

Larry_Jones

Member
What Don said.

Not only will it be more readable, it will appear exactly as entered/displayed on JDE screens, with all formatting, font sizing, bullets, etc, unlike UBEs which have all formatting information stripped out.

All you have to do is tell Crystal that its RTF ...
 

cbudoris

Member
[ QUOTE ]
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 . . .

[/ QUOTE ]

You sir are a steely eyed missle man. This function & view were the [censored], just what I needed.
 
Top