We are at a critical stage trying to do our initial reporting from PeopleSoft Student. The Particular issue is dealing with effective dates. Our reporting team maintains that we need special views to flatten out the PS data. The PS functional team wants to switch back to Crystal Reports which builds its own sql view.

We are not allowed to use sql pass-thru.

Does anyone have experiences with PS Student? Perhaps this is no different from any PS effective dated tables.

This could be the end for WebFOCUS and this ERP project.

We are also contacting IBI but the user community has always been a great source of information.

This message has been edited. Last edited by: Kerry,

We use Banner and they do the same thing. The ycall it non destructive updates. The SQL guys use subqueries. I have used this technique.
Extract the HIGHEST 1 TERM_CODE_EFF per PIDM, Student. Output a FOCUS file. Then JOIN that to a HOST file.

-* Need a JOINKEY because JOINing TO a FOCUS file with multiple keys is not allowed.
-* You then need to create a corresponding JOINKEY for the Host file.
DEFINE FILE SCBCRSE
CRSE_MAX_KEY/A9 = SCBCRSE_SUBJ_CODE | SCBCRSE_CRSE_NUMB;
END
TABLE FILE SCBCRSE
PRINT *
CRSE_MAX_KEY
BY SCBCRSE_SUBJ_CODE
BY SCBCRSE_CRSE_NUMB
BY HIGHEST 1 SCBCRSE_EFF_TERM
-*WHERE SCBCRSE_SUBJ_CODE = 'BIOL'
-* Save as a FOCUS database with index
ON TABLE HOLD AS SCBCRSE_MAX_TERM FORMAT FOCUS INDEX CRSE_MAX_KEY
END
-*-EXIT
SET ALL=PASS
JOIN SFRSTCR_TERM_CODE AND SFRSTCR_CRN         IN SFRSTCR TO ALL SSBSECT_TERM_CODE AND SSBSECT_CRN IN SSBSECT AS J1
JOIN SSBSECT_JOINKEY WITH SSBSECT_TERM_CODE    IN SFRSTCR TO ALL CRSE_MAX_KEY                      IN SCBCRSE_MAX_TERM AS J2
JOIN SFRSTCR_RSTS_CODE                         IN SFRSTCR TO ALL STVRSTS_CODE                      IN STVRSTS AS J3
DEFINE FILE SFRSTCR
SSBSECT_JOINKEY/A9 WITH SSBSECT_TERM_CODE = SSBSECT_SUBJ_CODE | SSBSECT_CRSE_NUMB;
IPFILECODE/A1 = 'C';
JOINKEY/A11 = IPFILECODE | EDIT(SFRSTCR_PIDM);
JOINKEY2/A15 = SFRSTCR_TERM_CODE | SSBSECT_SUBJ_CODE |SSBSECT_CRSE_NUMB;
IPDUPSORT/A1 = IF JOINKEY EQ LAST JOINKEY AND JOINKEY2 EQ LAST JOINKEY2 THEN 'Z' ELSE 'A';
IPTITLE/A30 = IF SSBSECT_CRSE_TITLE EQ ' ' THEN SCBCRSE_TITLE ELSE SSBSECT_CRSE_TITLE;
IPCREDIT/P9.3 = IF SSBSECT_CRSE_TITLE EQ ' ' THEN SCBCRSE_CREDIT_HR_LOW ELSE SSBSECT_CREDIT_HR;
END
TABLE FILE SFRSTCR
PRINT
SFRSTCR_PIDM
SSBSECT_SUBJ_CODE  AS 'IPSUBJECT
SSBSECT_CRSE_NUMB  AS 'IPCOURSE'
IPTITLE
IPCREDIT
SFRSTCR_TERM_CODE  AS 'IPTERMCODE'
IPFILECODE
JOINKEY2
SSBSECT_JOINKEY
BY JOINKEY
BY IPDUPSORT
WHERE SFRSTCR_PIDM = '&PIDM'
WHERE SFRSTCR_GRDE_DATE IS MISSING
WHERE (NOT SFRSTCR_ERROR_FLAG IN ('F', 'D') OR SFRSTCR_ERROR_FLAG IS MISSING)
WHERE SSBSECT_GRADABLE_IND = 'Y'
WHERE STVRSTS_INCL_SECT_ENRL = 'Y'
WHERE (STVRSTS_GRADABLE_IND = 'Y' OR STVRSTS_AUTO_GRADE IS NOT MISSING)
ON TABLE HOLD AS INPROGRESS FORMAT FOCUS INDEX SFRSTCR_PIDM
END

Very nice Rick.

I have supported 3 UNC alliance schools(Banner), ongoing support for 1 one of them. WF is the best tool in the industry, and, is used by 11 of the 13 schools, exceptions are Chapel Hill(obviously, in the midst of a POC) and State.

BUT, if there is a lack of knowledge of the tool, and/or, how to pull data from a RDBMS, then POC's will surely fail.

Crystal can't touch WebFOCUS, especially, for it's output and flexibility in raw coding or GUI...

Tom

EDIT:

Effective Dates and PeopleSoft

This message has been edited. Last edited by: Tom Flynn,

The issue of PeopleSoft Effective Dated tables comes up periodically. There is some bad news and then good news. The bad news is that you do have to create custom views of the Effective Dated PeopleSoft tables to do reporting. The good news is that:

a) You do this inside of the PeopleSoft Development GUI (This used to be called PeopleTools, I don't know if it has been re-branded since Oracle took over)
b) It is quite easy to do. The first time you do it, it may seem difficult; but after the a few tries it takes less than 5 minutes per PS record.
c) IBI delivers sample SQL which simplifies the whole process
d) These views usually perform better than the standard PS-Query/Crystal technique of doing a correlated subquery with a MAX(EFFDT). Your mileage will vary, but typically there is a 15-25% improvement on a single table query - where the table has between 10K-100K rows. On queries with Joins, the improvement was even greater.
e) These techniques have been used successfully for over 10 years.


If you want more details, you can contact IBI Help and ask for the white paper on Point in Time Reporting for PeopleSoft; this provides detailed step by step instructions on how to configure these views You can also check out 2 articles I wrote for the IBI Systems Journals. One was tha Jan/Feb 1998 issue, it describes in general terms the issues involved in useing effective dated tables; the other is in the Nov/Dec 1998 issue and it describes in detail the solutions used for relational tables.

EricH

EricH,

Excellent, AND, the Nov/Dec 1998 document is still on-line. The problem is SRC states SQL passthrough is not an option. Barrier's/Hidden agenda's always hinder creativity/productivity. Are we not in the 21st century?

I couldn't find the others, on-line, that is.

I really, really, really don't want anyone/any company to fail with WebFOCUS. We've been dating for so long now...

Point in Time Reporting-SQL

I also went out to the new Enterprise Content Library: 192 matches for PeopleSoft. May help...

ECL search on PeopleSoft

This message has been edited. Last edited by: Tom Flynn,

Tom

You will not need to use SQL passthru, since the Point in Time (PiT) view is created inside of PeopleSoft. So once you have created the PiT view, it is simply another PeopleSoft data records just like any other. So a typical report would look like this:

 
TABLE FILE LOCATION_VW
   PRINT
      LOCATION
      DESCR
      etc
      etc

   WHERE (EFFDT LE '2009-01-01') AND (EFFDT_NXT GE '2009-01-01') ;
END
 

EricH

Eric,

Well, hopefully SRC reviews the thread he/she started. Lots of ammo.

Well done, Eric...

Tom

Thanks to everyone for the helpful replies. The Point in Time view/extract confirms what we thought and what we have been trying to tell the ERP team. Working on our own and with some help from IBI, we have been able to complete some of the reports that have been identified as critical for the PeopleSoft implementation. Even though we have been using FOCUS/WebFOCUS since 1990 in Chapel Hill, the ERP team is composed of a combination of new employees and contractors. They were not as convinced as we (Enterprise Reporting Team) are about the merits of continuing to use WebFOCUS.

So, back to PeopleSoft. We will be using some of what was said in this thread to continue making our case for PiT extracts. At the same time, we are thinking that maybe the PeopleSoft Adapter could be modified to handle the effective dates without having to do any sql pre-process and we will be having talks with IBI about that.

Thanks again for all the information.

Steve

Steve,

WAY-TO-GO!!!

Best wishes...

Sorry about the statement WebFOCUS not being there, it was BANNER...

Good work!!!

Tom

P.S. sent you a PM