Employment Position History Report

Fred Gravel

Summary

Grouping data

Content

I am creating a BI Report that basically lists out all the Jobs an employee has had during their employment, so of course my initial grouping is EMPLID. (Sample XML File is attached) My issue is that if an employee starts with JOBCODE1, goes to JOBCODE2, then back to JOBCODE1 and then JOBCODE3 the report has to put out 4 lines. In addition the report needs to calculate the length of time in each position. It does this by using the EFFDT from the first line of each JOBCODE and the EFFDT from the last line of the JOBCODE calculate the difference. Each JOBCODE/position could have anything from a single row to multiple rows, but the report needs to show only a single row for each. IE:

EMPLID
JOBCODE1     DATA     DATA     DATA    LENGTH OF SERVICE
JOBCODE2     DATA     DATA     DATA    LENGTH OF SERVICE
JOBCODE1     DATA     DATA     DATA    LENGTH OF SERVICE
JOBCODE3     DATA     DATA     DATA    LENGTH OF SERVICE

The code snippet I have included is in the <?for-each:current-group()?>  In it I set a variable 'print' that at the end of the snippit is tested to see if 'T' and will print out the detail line. The end of the 'if' statement is at the other end, just before the end of the for each loop.

My code does produce a detail line for each JOBCODE but it also produces a blank line for each row in the xml. Have tried rearranging code and rewriting until my eyes have crossed .... In short ... HELP!!
 

Code Snippet

<?for-each:current-group()?>
<?xdoxslt:set_variable($_XDOCTX,'sJob',A.JOBCODE)?>
<?xdoxslt:set_variable($_XDOCTX,'sNextJob',(following-sibling::row[1]/A.JOBCODE))?>
<?xdoxslt:set_variable($_XDOCTX,'sEmplID',A.EMPLID)?>
<?xdoxslt:set_variable($_XDOCTX,'sNextEmplID',(following-sibling::row[1]/A.EMPLID))?>
<?choose:?>
    <?when:((xdoxslt:get_variable($_XDOCTX,'sNextJob') != xdoxslt:get_variable($_XDOCTX,'sJob')) or (xdoxslt:get_variable($_XDOCTX,'sNextEmplID') != xdoxslt:get_variable($_XDOCTX,'sEmplID')) or (position()=last()) )?>
        <?xdoxslt:set_variable($_XDOCTX,'dServiceDt',B.SERVICE_DT)?>
        <?xdoxslt:set_variable($_XDOCTX,'sDesc',D.DESCR)?>
        <?xdoxslt:set_variable($_XDOCTX,'sDept',A.DEPTID)?>
        <?choose:?>
            <?when:xdoxslt:get_variable($_XDOCTX,'pass')='1'?>
                <?xdoxslt:set_variable($_XDOCTX,'dDate1',A.EFFDT)?>
                <?xdoxslt:set_variable($_XDOCTX,'pass','1')?>
            <?end when?>
            <?otherwise:?>
                <?xdoxslt:set_variable($_XDOCTX,'pass','1')?>
        <?end otherwise?>
        <?end choose?>
        <?choose:?>
            <?when:(xdoxslt:get_variable($_XDOCTX,'sNextEmplID') = xdoxslt:get_variable($_XDOCTX,'sEmplID'))?>
                <?xdoxslt:set_variable($_XDOCTX,'dDate2',A.EFFDT)?>
            <?end when?>
            <?otherwise:?>
                <?xdoxslt:set_variable($_XDOCTX,'dDate2',xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE))?>
            <?end otherwise?>
        <?end choose?>
        <?xdoxslt:set_variable($_XDOCTX,'nYears', xdoxslt:date_diff('y',xdoxslt:get_variable($_XDOCTX,'dDate1'),xdoxslt:get_variable($_XDOCTX,'dDate2'),$_XDOLOCALE, $_XDOTIMEZONE))?>
        <?xdoxslt:set_variable($_XDOCTX,'nTtlMonths', xdoxslt:date_diff('m',xdoxslt:get_variable($_XDOCTX,'dDate1'),xdoxslt:get_variable($_XDOCTX,'dDate2'),$_XDOLOCALE, $_XDOTIMEZONE))?>
        <?xdoxslt:set_variable($_XDOCTX,'nDiff', xdoxslt:date_diff('m',xdoxslt:get_variable($_XDOCTX,'dDate1'),xdoxslt:get_variable($_XDOCTX,'dDate2'),$_XDOLOCALE, $_XDOTIMEZONE)-(xdoxslt:get_variable($_XDOCTX,'nYears')*12))?>
        <?xdoxslt:set_variable($_XDOCTX,'nJobs',xdoxslt:get_variable($_XDOCTX,'nJobs')+1)?>
        <?xdoxslt:set_variable($_XDOCTX,'print','T')?>
    <?end when?>
    <?otherwise:?>
        <?choose:?>
            <?when:xdoxslt:get_variable($_XDOCTX,'pass')='1'?>
                <?xdoxslt:set_variable($_XDOCTX,'dDate1',A.EFFDT)?>
                <?xdoxslt:set_variable($_XDOCTX,'pass','2')?>
            <?end when?>
            <?otherwise:?>
                <?xdoxslt:set_variable($_XDOCTX,'pass','2')?>
            <?end otherwise?>
        <?end choose?>
        <?xdoxslt:set_variable($_XDOCTX,'print','F')?>
    <?end otherwise?>
<?end choose?>
<?if:xdoxslt:get_variable($_XDOCTX,'print') = 'T'?>

Fred Gravel

Thanks all ... re-wrote the code in a slightly different way and it started working. When I get a chance I will analyze what I did on the 59302 code rewrite that was different from the other 59301 other times