PeopleSoft Compare Report in Excel Format with report filter

In my another doodle “PeopleSoft Compare Report in Excel Format” I have posted a SQL query that can be used to export the compare report into an excel sheet. In that post I assumed all checkboxes are checked in the Report Filter matrix during compare report. Latter I realized that this is not going to be situation in all cases. Sometimes we will do compare by only required checkboxes checked in the Report Filter matrix as shown in the below screenshot. In this doodle I have post the SQL query with the required conditions to pull the compare report to excel sheet for the filters checked in the screenshot.

 

Fields SOURCESTATUS and TARGETSTATUS in PSPROJECTITEM has the values from 0 to 6. Below are the description of each possible value of these fields. Based on the checkbox you do have checked in the report filter you need to have the SQL conditions built in the query

 

0 - Unknown

1 - Absent

2 - Changed

3 - Unchanged

4 - Custom Changed

5 - Custom Unchanged

6 - Same

 

 

SQL query for the above filter conditions:

 

SELECT PROJECTNAME, OBJECTVALUE1,OBJECTVALUE2,OBJECTVALUE3,OBJECTVALUE4,
case OBJECTTYPE
WHEN 0  THEN 'Record'
WHEN 1  THEN 'Index'
WHEN 2  THEN 'Field'
WHEN 3  THEN 'Field Format'
WHEN 4  THEN 'Translate Value'
WHEN 5  THEN 'Pages'
WHEN 6  THEN 'Menus'
WHEN 7  THEN 'Components'
WHEN 8  THEN 'Record PeopleCode'
WHEN 9  THEN 'Menu PeopleCode'
WHEN 10 THEN 'Query'
WHEN 11 THEN 'Tree Structures'
WHEN 12 THEN 'Trees'
WHEN 13 THEN 'Access group'
WHEN 14 THEN 'Color'
WHEN 15 THEN 'Style'
WHEN 16 THEN 'N/A'
WHEN 17 THEN 'Business process'
WHEN 18 THEN 'Activity'
WHEN 19 THEN 'Role'
WHEN 20 THEN 'Process Definition'
WHEN 21 THEN 'Server Definition'
WHEN 22 THEN 'Process Type Definition'
WHEN 23 THEN 'Job Definitions'
WHEN 24 THEN 'Recurrence Definition'
WHEN 25 THEN 'Message Catalog'
WHEN 26 THEN 'Dimension'
WHEN 27 THEN 'Cube Definitions'
WHEN 28 THEN 'Cube Instance Definitions'
WHEN 29 THEN 'Business Interlink'
WHEN 30 THEN 'SQL'
WHEN 31 THEN 'File Layout Definition'
WHEN 32 THEN 'Component Interfaces'
WHEN 33 THEN 'AE program'
WHEN 34 THEN 'AE section'
WHEN 35 THEN 'Message Node'
WHEN 36 THEN 'Message Channel'
WHEN 37 THEN 'Message'
WHEN 38 THEN 'Approval rule set'
WHEN 39 THEN 'Message PeopleCode'
WHEN 40 THEN 'Subscription PeopleCode'
WHEN 41 THEN 'N/A'
WHEN 42 THEN 'Component Interface PeopleCode'
WHEN 43 THEN 'AE PeopleCode'
WHEN 44 THEN 'Page PeopleCode'
WHEN 45 THEN 'Page Field PeopleCode'
WHEN 46 THEN 'Component PeopleCode'
WHEN 47 THEN 'Component Record PeopleCode'
WHEN 48 THEN 'Component Rec Fld PeopleCode'
WHEN 49 THEN 'Image'
WHEN 50 THEN 'Style sheet'
WHEN 51 THEN 'HTML'
WHEN 52 THEN 'Not used'
WHEN 53 THEN 'Permission List'
WHEN 54 THEN 'Portal Registry Definitions'
WHEN 55 THEN 'Portal Registry Structures'
WHEN 56 THEN 'URL Definitions'
WHEN 57 THEN 'Application Packages'
WHEN 58 THEN 'Application Package Peoplecode'
WHEN 59 THEN 'Portal Registry User Homepage'
WHEN 60 THEN 'Problem Type'
WHEN 61 THEN 'Archive Templates'
WHEN 62 THEN 'XSLT'
WHEN 63 THEN 'Portal Registry User Favorite'
WHEN 64 THEN 'Mobile Page'
WHEN 65 THEN 'Relationships'
WHEN 66 THEN 'Component Interface Property Peoplecode'
WHEN 67 THEN 'Optimization Models'
WHEN 68 THEN 'File References'
WHEN 69 THEN 'File Type Codes'
WHEN 70 THEN 'Archive Object Definitions'
WHEN 71 THEN 'Archive Templates (Type 2)'
WHEN 72 THEN 'Diagnostic Plug In'
WHEN 73 THEN 'Analytic Model'
WHEN 75 THEN 'Java Portlet user Preferences'
WHEN 76 THEN 'WSRP Remote Producers'
WHEN 77 THEN 'WSRP Remote Portlets'
WHEN 78 THEN 'WSRP Cloned Portlet Handles'
WHEN 79 THEN 'Services'
WHEN 80 THEN 'Service Operations'
WHEN 81 THEN 'Service Operation Handlers'
WHEN 82 THEN 'Service Operation Versions'
WHEN 83 THEN 'Service Operation Routings'
WHEN 84 THEN 'IB Queues'
WHEN 85 THEN 'XMLP Template Definitions'
WHEN 86 THEN 'XMLP Report Definitions'
WHEN 87 THEN 'XMLP File Definitions'
WHEN 88 THEN 'XMLP Data Source Definitions'
WHEN 89 THEN 'WSDL'
WHEN 90 THEN 'Message Schemas'
WHEN 91 THEN 'Connected Query Definitions'
WHEN 92 THEN 'Document'
WHEN 93 THEN 'XML Document'
WHEN 94 THEN 'Related Document'
WHEN 95 THEN 'Dependency Documents'
WHEN 96 THEN 'Document Schema'
WHEN 97 THEN 'EssbaseCubeDimensions'
WHEN 98 THEN 'EssbaseCubeOutlines'
WHEN 99 THEN 'EssbaseCubeConnections'
WHEN 100 THEN 'EssbaseCubeTemplates'
end OBJECTTYPE,
CASE UPGRADEACTION
        WHEN 0 THEN 'Copy'
        WHEN 1 THEN 'Delete'
        WHEN 2 THEN 'None'
        WHEN 3 THEN 'CopyProp'
END UPGRADEACTION,
CASE SOURCESTATUS
        WHEN 0 THEN 'Unknown'
        WHEN 1 THEN 'Absent'
        WHEN 2 THEN 'Changed'
        WHEN 3 THEN 'Unchanged'
        WHEN 4 THEN '*Changed'
        WHEN 5 THEN '*Unchanged'
        WHEN 6 THEN 'Same'
END SOURCESTATUS,
CASE TARGETSTATUS
        WHEN 0 THEN 'Unknown'
        WHEN 1 THEN 'Absent'
        WHEN 2 THEN 'Changed'
        WHEN 3 THEN 'Unchanged'
        WHEN 4 THEN '*Changed'
        WHEN 5 THEN '*Unchanged'
        WHEN 6 THEN 'Same'
END TARGETSTATUS
FROM PSPROJECTITEM WHERE PROJECTNAME LIKE 'Your Project Name'
and (
(sourcestatus = 5 and targetstatus = 1) or
(sourcestatus = 5 and targetstatus = 2) or
(sourcestatus = 5 and targetstatus = 3) or
(sourcestatus = 5 and targetstatus = 4) or
(sourcestatus = 5 and targetstatus = 5) or
(sourcestatus = 5 and targetstatus = 6) or
(sourcestatus = 4 and targetstatus = 1) or
(sourcestatus = 4 and targetstatus = 2) or
(sourcestatus = 4 and targetstatus = 3) or
(sourcestatus = 4 and targetstatus = 4) or
(sourcestatus = 4 and targetstatus = 5) or
(sourcestatus = 4 and targetstatus = 6) or
(sourcestatus = 1 and targetstatus = 4) or
(sourcestatus = 2 and targetstatus = 4) or
(sourcestatus = 3 and targetstatus = 4) or
(sourcestatus = 1 and targetstatus = 5) or
(sourcestatus = 2 and targetstatus = 5) or
(sourcestatus = 3 and targetstatus = 5)
)