Export List data to an Excel Document

Sometimes it's usefull to have the possiblity to extract the list towards an excel document. I know that reporting can do the job, but nevertheless it can be usefull.

Here you've an possible way on how to do.

  1. Create an new report file at location %NX_ROOT%\site\mods\web_rpt.
    Hereby an example of a report which will be used when viewing a list of Requests.

    // Report: DP-crsum.rpt (based on 1.3 of crsum.rpt)
    //
    // Description: SDT 19319 - ITIL Incident Summary Report Export to Excel
    //
    // Group by: None
    //
    // Sort by: open_date DESC
    //
    // Input: Standard Where Clause
    //
    // Algorith: N/A
    //
    ////////////////////////////////////////////////////////////////////////////
    // Created: 21/12/2007
    ////////////////////////////////////////////////////////////////////////////
    ////////////////////////////////////////////////////////////////////////////
    // $Log: DP-crsum.rpt,v $
    //
    ////////////////////////////////////////////////////////////////////////////
    PAGE HEADER {
    }
    PAGE FOOTER {
    }
    ////////////////////////////////////////////////////////////////////////////
    // Outside Block
    ////////////////////////////////////////////////////////////////////////////
    BLOCK in ("SELECT id FROM Impact", "WHERE enum = 1") {
    ////////////////////////////////////////////////////////////////////////////
    // Main Block
    ////////////////////////////////////////////////////////////////////////////
    BLOCK cr ("SELECT \
    ref_num, id, status, active_flag, open_date, close_date, \
    customer, description, priority, summary, assignee, group_id \
    FROM Call_Req",
    $# > 1 ? $1 : "") SORT "open_date DESC" {
    ccnt = count(cr);
    HEADER {
    <HTML>
    <BODY>
    Press the button to start Excel and display your listed data.
    <SCRIPT LANGUAGE="VBScript">
    </SCRIPT>
    <SCRIPT LANGUAGE="JScript">
    function AutomateExcel()
    \{
    var counter=1;
    // Start Excel and get Application object.
    var oXL = new ActiveXObject("Excel.Application");
    oXL.Visible = true;
    // Get a new workbook.
    var oWB = oXL.Workbooks.Add();
    var oSheet = oWB.ActiveSheet;
    }
    BLOCK crs ("SELECT sym FROM Cr_Status", "WHERE #code = ?", cr::status) {}
    BLOCK grp ("SELECT last_name FROM ca_contact", "WHERE id = ?", cr::group_id) {}
    anal_name = deref (assignee);
    cust_name = deref (customer);
    grp_name = grp::last_name;
    pr = deref (priority);
    summ = summary;
    stat = crs::sym;
    odate = open_date DATE;
    PRINT {
    if(counter<=[ccnt>)\{
    oSheet.Cells(counter,1).Value = "[ref_num>";
    oSheet.Cells(counter,2).Value = "[cust_name>";
    oSheet.Cells(counter,3).Value = "[anal_name>";
    oSheet.Cells(counter,4).Value = "[odate>";
    oSheet.Cells(counter,5).Value = "[stat>";
    oSheet.Cells(counter,6).Value = "[pr>";
    counter++;
    \};
    }
    FOOTER {
    // Make sure Excel is visible and give the user control
    // of Excel's lifetime.
    oXL.Visible = true;
    oXL.UserControl = true;
    \}
    </SCRIPT>
    <P><INPUT id=button1 type=button value="Start Excel"
    onclick="AutomateExcel"></P>
    </BODY>
    </HTML>
    }
    } // end cr
    PRINT (count(cr) == 0){
    <p>
    <center><b>Your Query Did Not Return Any Matching Records</b></center>
    <p>
    }
    } // end in

  2. Open Web Screen Painter
  3. Open the file Analyst - Default - menubar_sd.htmpl
  4. Locate the following line:

    <PDM_MACRO name=startMenu parentid=mReports>

    Add the following line

    <PDM_MACRO name=menuItem label="Summary CSV..." function="ahdtop.popup_report('\" + w.rptName[2] + \"','\" + escape(w.argSearchSqlClause.replace(/'/g,'\\\\\\'')) + \"','\" + w.propFormName1 + \"')">

    before the line

    <PDM_MACRO name=endMenu>

  5. Save the file towards Analyst - ITIL - menubar_sd.htmpl.
  6. Open the file Analyst - Default - list_cr.htmpl
  7. Locate the following line:

    var rptName = new Array("crsum.rpt", "crdtl.rpt");

    And replace by this:

    var rptName = new Array("crsum.rpt", "crdtl.rpt", "DP-crsum.rpt");

  8. Save the file towards Analyst - ITIL - list_cr.htmpl
  9. Publish the changed pages.

Check now the results.

  1. Go to the Request list.
  2. Select Reports - Summary CSV...



  3. An window will popup



  4. And will end on this screen on which you need to press the button for launching Excel with the requested data.



  5. You may have the following screen (depends on your security settings). Press 'Yes'



  6. An Excel sheet will be opened containing your data