How to Convert Date Parameter Field to julian and select.

detroit1

Active Member
Hi All,

I am trying to use Date Prameter to pass values and use them in Data Selection for RCDJ Received Date which is in Julian Format. What is the simplest way to do this?

Has anyone done data selection this way?
 
I dont understand your question what is the problem...if you using any date in data selection you can input normal date(mm/dd/yyyy)
 
Yo Detroit !

The simplest method (in my opinion) is to do it right once. This means to create a Database VIEW that does the necessary transformations on date and numeric fields so that they are normalized with respect to date data types and correct number of decimal places. In your crystal report you then reference the database VIEW instead of the TABLE.

What you do is create a database FUNCTION that accepts a JDE Date field as input and returns a DATE data type as output. Your database VIEW select statement then wraps the date field (RCDJ) with your DB function. Numeric fields just divide by 10^number-decimal-places.
You failed to provide any info on your database configuration so I suggest you search the forums for a database specific example of converting the julian date to a standard date type.

Here's an example of how the VIEW would look in Oracle:

CREATE OR REPLACE VIEW f3313_view (
Capacity_Mode_CRCAPM ,
Cost_Center_CRMCU ,
Capacity_Type_CRCQT ,
UnitOfMeasure_CRUM ,
Unit_Type_CRUNTY ,
Date_Start_CRSTRT ,
Qty_Transaction_CRTRQT )
AS SELECT
CRCAPM,
CRMCU,
CRCQT,
CRUM,
CRUNTY,
JDE2Date(CRSTRT),
CRTRQT/10000
FROM F3313
WITH READ ONLY;

Here's the Oracle Function to convert the JDE Julian Date to a standard date. Note that others have come up with more elegant solutions for other DBMS' - but this is the only one I've seen posted for Oracle.
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION "PRODDTA"."JDE2DATE" (jdedate IN NUMBER)
RETURN DATE
IS
v_dateout DATE;
v_jdedate NUMBER(6,0) :=0;
v_year NUMBER(4,0) :=0;
v_day NUMBER(3,0) :=0;

BEGIN
v_dateout := TO_DATE(19000101,'YYYY MM DD') ;
if (jdedate IS NOT NULL) AND (jdedate != 0) then
v_jdedate :=jdedate;
v_year := (v_jdedate / 1000) + 1900;
v_day := MOD(v_jdedate,1000) - 1;
v_dateout := TRUNC(TO_DATE(v_year,'YYYY'),'YEAR') + v_day;
end if;

RETURN (v_dateout);

END;
----------------------------------------------------------------------

One thing you may want to change is the default date value if the date field is 0. That's the line that says:
v_dateout := TO_DATE(19000101,'YYYY MM DD') ;
This statement causes the date to be set to Jan 01, 1900 when there is no date value. The alternative is to set the date value to Null as follows:
v_dateout := NULL;
We don't use the NULL value because no other fields in OW are NULL and we try to avoid confusing some of our less technical report writers with the NULL concept. They have been taught that if the date is 01/01/1900 then it is a zero date.


Regards,
 
Thanks Larry. Sorry forgot to mention the database info, its AS400 migrating to 810 from World. Using crystal to write some reports used in World.
 
Hi Larry,

One question, we are using the Peoplesoft Integration tool for Crystal and could see the tables and views and one thing we noticed that as compared to ODBC report runs very slowly.

Any thoughts on why is it so slow?

Thanks
 
When you say "Peoplesoft Integration Tool" are you referring to ODA?

If you are then that explains why. ODA is basically an E1 specific ODBC driver. With ODA there are several additional layers of code that must be gotten through for each data transfer - this slows performance significantly as you have found. In my opinion its just not a robust solution. If you're interested in a product where you don't have to setup database views, etc then check out JDEDIRECT from www.unitysolutions.com.

Regards,
 
The PeopleSoft Tools integration for Crystal Reports is something new for E1 8.9 onwards and in essence uses a XML interface – this might explain why you’re seeing a performance issue ?
 
The E1 Crystal integration works by using the enterprise server's CallObject kernels to execute queries and return data via an XML stream. This XMLList feature has been around since XE. The XMLCallObject is the same interface point that WebMethods/XPI uses to talk to E1.

Yep, it is definitely slower that any sort of direct database access. It works fine but I have found that it simply can't give the performance needed for typical ad-hoc user reporting. It is probably suited for summarised reporting where Crystal does not have to read through a large resultset.

Regards,
 
Back
Top