Creating NetSuite Saved Searches with Multiple Line Items

I'm working on a NetSuite Saved Search using Kit/Package (Item) records that have multiple line items. For instance, each Kit/Package is comprised of two Item SKUs.

At the moment, my Saved Search is pulling in each Item SKU on its own individual line. How can I keep each Kit/Package on one line with the different Item SKUs pulled into their own columns? Can I create one column that always pulls in Item SKU #1 and another that always pulls in Item SKU #2?

The record I am looking to pull in is {memberitem}. Is there a way to tell it to pull in {memberitem}, {memberitem1}, and {memberitem2} into different columns?

First of all - the only way I see to have a search combining 2 member items on one row is to group the fields, i.e. - to create a summary search.

If you need this search to be used as a base for Advanced PDF Template, the only way is to write a script (as the summary Searches are not allowed in Advanced PDF/HTML templates).

Here I have created for you a suitelet script, that perform this PDF generation. Of course I have the xml inside the code, but you could load the pre-saved XML file from the file cabinet. Also, you could create a saved search and just load it in the script. Keep in mind that all the fields at ITEM level should be with summary type "GROUP"

/**
 * @NApiVersion 2.x
 * @NScriptType Suitelet
 * @NModuleScope SameAccount
 */
define(['N/render','N/search'],

function(render, search) {


    function onRequest(context) {

        var kititemSearchObj = search.create({
            type: "kititem",
            filters: [
              ["type","anyof","Kit"]
            ],
            columns: [
              search.createColumn({
                 name: "itemid",
                 summary: "GROUP"
              }),
              search.createColumn({
                 name: "displayname",
                 summary: "GROUP"
              }),
              search.createColumn({
                 name: "salesdescription",
                 summary: "GROUP"
              }),
              search.createColumn({
                 name: "baseprice",
                 summary: "GROUP"
              }),
              search.createColumn({
                 name: "formulatext",
                 summary: "MAX",
                 formula: "CASE WHEN {memberline}=1 THEN {memberitem} ELSE null END"
              }),
              search.createColumn({
                 name: "formulatext",
                 summary: "MAX",
                 formula: "CASE WHEN {memberline}=2 THEN {memberitem} ELSE null END"
              })
            ]
        });


        var myCustomObject = {

                KitItems : []
        };

        kititemSearchObj.run().each(function(r){
            var aRow = {
                    linenum : myCustomObject.KitItems.length + 1,
                    itemid : r.getValue({
                             name: "itemid",
                             summary: "GROUP"
                          }),
                    displayname :  r.getValue({
                             name: "displayname",
                             summary: "GROUP"
                          }),
                    salesdescription :  r.getValue({
                             name: "salesdescription",
                             summary: "GROUP"
                          }),
                    baseprice :  r.getValue({
                             name: "baseprice",
                             summary: "GROUP"
                          }), 
                    memberitem1 :  r.getValue({
                             name: "formulatext",
                             summary: "MAX",
                          }), 
                    memberitem2 :  r.getValue({
                             name: "formulatext_1",
                             summary: "MAX",
                          })

            };
            myCustomObject.KitItems.push(aRow);

           return true;
        });

        var renderer = render.create();

        var xmlStr = 
            '<?xml version="1.0"?>\n'+
            '<!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd">\n' +
            '<pdf>\n<body size="A4">\n'+
            '<#if results.KitItems?has_content>'+
            '<table><!-- start rows --><#list results.KitItems as line>'+
            '  <tr><td>${line.linenum}</td>'+
            '      <td>${line.itemid}</td>'+
            '      <td>${line.displayname}</td>'+
            '      <td>${line.salesdescription}</td>'+
            '      <td>${line.baseprice}</td>'+
            '      <td>${line.memberitem1}</td>'+
            '      <td>${line.memberitem2}</td>'+
            '  </tr>'+
            '  </#list>'+
            '</table>'+
            '</#if>'+
            '</body>\n</pdf>';

        renderer.templateContent = xmlStr;

        renderer.addCustomDataSource({
            format: render.DataSource.OBJECT,
            alias: "results",
            data: myCustomObject
            });        

        var pdfResult = renderer.renderAsPdf();
        pdfResult.name = 'kitItems.pdf';

        context.response.writeFile({
            file: pdfResult,
            isInline : true
        });

    }

    return {
        onRequest: onRequest
    };

});