Multi Column Report

Data Structure

This is tutorial how to create multiple columns on a report. The report designer has no support for multi column rendering; Like in Word where you can set a "Number of Columns" property on a paragraph. As Workaround create a temporary table with exactly the same fields as needed on the report. For example if you want to display SalesId, CustAccount, ItemId and Qty from the SalesLine, the temporary table for two columns has 8 columns.

The getFreeSlot() Method is needed in the report to determine if there is a free column slot or if it is necessary to create a new line on the table. <xpp> public static TmpSalesTable2Col getFreeSlot(TmpSalesTable2Col tmp,int column) {

   ;

   if(column==1)
   {
       select firstonly tmp where tmp.SalesId_1 == "";
       return tmp;
   }
   else if(column==2)
   {
       select firstonly tmp where tmp.SalesId_2 == "";
       return tmp;
   }
   else
   {
       throw error("Column number must be between 0 and 1");
   }

} </xpp>

Report

Create a new report and declare a variable from type of the temporary table. <xpp> public class ReportRun extends ObjectRun {

   TmpSalesTable2Col   tmpSalesTable;

} </xpp>

Create a new method to populate the temporary table. In this example the SalesLine is fetched. If it was created in 2007 it belongs to column number 1 and if it was created in 2008 it belongs to column number 2. I assume here that there are only rows for 2007 or 2008. Otherwise it would be good to filter the select by year. The find() method is used to find a free slot; That means a record exists but with a free slot for the needed column. <xpp> private void populateTmpTable() {

   SalesLine           salesLine;
   TmpSalesTable2Col   temp;
   int col;
   ;

   while select salesLine
   order by SalesId
   {
       // 2007 is first column
       if(year(salesLine.createdDate)==2007)
       {
           temp = TmpSalesTable2Col::getFreeSlot(tmpSalesTable,1);
           // existing record with free slot in first column
           if(temp)
           {
               temp.SalesId_1 = salesLine.SalesId;
               temp.ItemId_1 = salesLine.ItemId;
               temp.CustAccount_1 = salesLine.CustAccount;
               temp.Qty_1 = salesLine.QtyOrdered;
               temp.update();
           }
           else
           {
               tmpSalesTable.clear();
               tmpSalesTable.SalesId_1 = salesLine.SalesId;
               tmpSalesTable.ItemId_1 = salesLine.ItemId;
               tmpSalesTable.CustAccount_1 = salesLine.CustAccount;
               tmpSalesTable.Qty_1 = salesLine.QtyOrdered;
               tmpSalesTable.insert();
           }
       }
       // 2008 is second column
       if(year(salesLine.createdDate)==2008)
       {
           temp = TmpSalesTable2Col::getFreeSlot(tmpSalesTable,2);
           // existing record with free slot in second column
           if(temp)
           {
               temp.SalesId_2 = salesLine.SalesId;
               temp.ItemId_2 = salesLine.ItemId;
               temp.CustAccount_2 = salesLine.CustAccount;
               temp.Qty_2 = salesLine.QtyOrdered;
               temp.update();
           }
           else
           {
               tmpSalesTable.clear();
               tmpSalesTable.SalesId_2 = salesLine.SalesId;
               tmpSalesTable.ItemId_2 = salesLine.ItemId;
               tmpSalesTable.CustAccount_2 = salesLine.CustAccount;
               tmpSalesTable.Qty_2 = salesLine.QtyOrdered;
               tmpSalesTable.insert();
           }
       }
   }

} </xpp>

Override the fetch() method on the report. Call the populateTmpTable and then send line after line to the report design. <xpp> public boolean fetch() {

   ;
   element.populateTmpTable();

   while select tmpSalesTable
   {
       element.send(tmpSalesTable);
   }

   return true;

} </xpp>

Create a new section group for TmpSalesTable2Col and a Body. Put the fields from the table in the body or use a FiledGroup. If necessary add a Sum section, header etc. The report should look like this