Greetings,

If you are a customer using WebFOCUS against a Peoplesoft data source, I wanted to relay that Information Builders has added Effective-date support in the PS adapter starting in version 7.7.02. Our institution stumbled on this new feature, and it is VERY SLICK.

No more "BY HIGHEST 1 EFFDT" hacks are needed, nor passing it through HOLD files (or WHERE TOTAL) to apply filters. IBI has added subquery support to the MFDs, and now there are built-in filters on the synonym creation for CURRENT_RECORD based on the system date -- so all that a developer needs to do is "WHERE CURRENT_RECORD EQ 1" to get the most current effective-dated row. And you can apply you normal filters in one pass of the source table.

I applaud IBI's support for effective dates in Peoplesoft. You really did a fine job that has made WebFOCUS a valuable tool for supporting at Nebraska higher education institutions!

Thanks,

Dan
University of Nebraska at Omaha

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

Nice! Thanks for sharing that Dan.

Not being a PeopleSoft user myself I'd still be be interested in seeing how this new feature (subqueries in MFD) can be leveraged to other applications ...

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

Here's some details for an example. The production ATHL_PART_STAT table is for student athletes and is effective-dated - Keys are EMPLID, SPORT and EFFDT. IBI created three defined fields ENDDATEX, ENDDATE to calculate the row's effective date boundaries and MAXEFFDT to calculate the max effective date based on the system date. Then they also added a filter CURRENT_RECORD for whether the row in question is the current EFFDT'd row in effect.

Their setup even allows for screening out future-dated rows.

I haven't fully distilled all that they're doing (not sure where all the documentation is located), but we've been using the CURRENT_RECORD filter and it's been GREAT.

-- Dan in Omaha

FILE=csprdnu_athl_part_stat,                                                    
SUFFIX=SQLORA, REMARKS=                                                         
'Athletic Participation Status',$                                               
SEGNAME=CSPRDNU_ATHL_PART_STAT, SEGTYPE=S0,$                                    
  FIELD=EMPLID                              ,EMPLID,                            
    A11         ,A11         ,MISSING=OFF,                                      
    TITLE='ID',DESC='EmplID', $                                                 
  FIELD=SPORT                               ,SPORT,                             
    A3          ,A3          ,MISSING=OFF,                                      
    TITLE='Sport',DESC='Sport', $                                               
  FIELD=EFFDT                               ,EFFDT,                             
    YYMD        ,DATE        ,MISSING=OFF,                                      
    TITLE='Eff Date',DESC='Effective Date', $                                   
  FIELD=ATHL_PARTIC_CD                      ,ATHL_PARTIC_CD,                    
    A5          ,A5          ,MISSING=OFF,                                      
    TITLE='Ath Par Cd',DESC='Athletic Participation Code', $                    
  FIELD=NCAA_ELIGIBLE                       ,NCAA_ELIGIBLE,                     
    A1          ,A1          ,MISSING=OFF,                                      
    TITLE='NCAA Elgbl',DESC='NCAA Eligible', $                                  
  FIELD=CUR_PARTICIPANT                     ,CUR_PARTICIPANT,                   
    A1          ,A1          ,MISSING=OFF,                                      
    TITLE='Participnt',DESC='Current Participant', $                            
  FIELD=DESCRLONG                           ,DESCRLONG,                         
    A500V       ,A500V       ,MISSING=ON ,                                      
    TITLE='Descr',DESC='Description', $                                         
  FIELDNAME=ENDDATEX,USAGE=YYMD,MISSING=ON,                                     
    ACCESS_PROPERTY=(INTERNAL),                                                 
    SUB_QUERY=MIN.EFFDT;,                                                       
    CRFILE=csprdnu_athl_part_stat,                                              
    CRTAG=SQ,                                                                   
    JOIN_WHERE =                                                                
    EMPLID EQ SQ.EMPLID AND                                                     
    SPORT EQ SQ.SPORT AND                                                       
    SQ.EFFDT GT EFFDT;,$                                                        
   VARIABLE NAME = &YYMD,$                                                      
  FIELDNAME=MAXEFFDT,USAGE=YYMD,MISSING=ON,                                     
    ACCESS_PROPERTY=(INTERNAL),                                                 
    SUB_QUERY=MAX.EFFDT;,                                                       
    CRFILE=csprdnu_athl_part_stat,                                              
    CRTAG=SQ,                                                                   
    JOIN_WHERE =                                                                
    EMPLID EQ SQ.EMPLID AND                                                     
    SPORT EQ SQ.SPORT AND                                                       
    SQ.EFFDT LE &YYMD ;,$                                                       
  DEFINE ENDDATE/YYMD MISSING ON = IF ENDDATEX IS MISSING THEN                  
    '39991231'                                                                  
     ELSE DATEADD(ENDDATEX,'D',-1);                                             
    TITLE='Effective End Date', $                                               
  FILTER CURRENT_RECORD=EFFDT EQ MAXEFFDT                                       
    ;,                                                                          
    TITLE='Current Record', $                                                   
END 

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