Excel report creation on page through peoplecode

Question

I am trying to create report in excel format through peoplecode. Requirement: There is page is having grid containing multiple columns. When manager logins, he can see his direct reporters information on this grid.. There is button provided again to drill down for further reporters. My requirement is to create report on the same page contain same columns information for both direct and indirect reporters. My approach is like: I made one sql to pull all direct reporter and its drilldown indirect reporters and then fetching details of these all columns from tables and calculating derived fields. Please let me is there any other approach

Answer 1

Easier and convenient approach for creating reports in Peoplesoft is through PSQuery in Query Manager. Instead of hardcoded peoplecode, you may create the SQL script by inserting the necessary records and fields, then provide the criteria as required. This can also be scheduled as desired through Scheduled Query. Your thoughts?

Answer 2

Excel can read a basic XML data lay out. E.G.

<?xml version="1.0"?>
   <?mso-application progid="Excel.Sheet"?>
   <Workbook>
    <Worksheet ss:Name="Sheet1">
     <Table>
      <Row>
        <Cell>
           <Data ss:Type="String">Item Number</Data>
        </Cell>
        <Cell>
          <Data ss:Type="String">Description</Data>
        </Cell>
        <Cell ss:StyleID="s21">
           <Data ss:Type="String">Item Barcode</Data>
       </Cell>
       </Row>
     </Worksheet>
    </Workbook>

You can easily build and XMLDoc in a PeopleSoft IScript that then returns the XML as a download.

eg

%Response.SetContentType("application/octet-stream");
%Response.SetHeader("Content-Disposition", "attachment; filename=reportfile.xls");