Parameters:

• dataSet : DataSet object.
• title : String, the title of XLS.
• groupByFields : List of Map objects.
• calculatedFields : List of Map objects.

JAVA Example:

String view = "test-xls-crosstable-1d.axvw";

String groupByFields =
"[{fullName:'wr.month',groupBy:'true',
showTotals:'true',title:'Month', isNumeric:false}]";
List<Map<String, String>> groupByFieldsList =
EventHandlerBase.fromJSONArray(new JSONArray(groupByFields));

DataSourceService service = new DataSourceService();
DataSet dataSet =
service.getGroupingDataSet(view, "crossTableByMonth_ds", null, null,"1d",
ReportUtility.getGroupByFieldNames(groupByFieldsList), null, null, null);

String calculatedFields =
"[{fullName:'wr.total_requests',groupBy:'false',
showTotals:'true',title:'Work requests', isNumeric:true}]";
List<Map<String, Object>> calculatedFieldsList =
EventHandlerBase.fromJSONArray(new JSONArray(calculatedFields));

com.archibus.ext.report.xls.CrossTab1DBuilder reportBuilder =
new com.archibus.ext.report.xls.CrossTab1DBuilder();

reportBuilder.build(dataSet, "Test a 1D CrossTab XLS report",
groupByFieldsList, calculatedFieldsList);

String fileName = reportBuilder.getFileName();
String url = reportBuilder.getURL();

Parameters:

• dataSet : DataSet object.
• title : String. The title of XLS.
• groupByFields : List of Map objects.
• calculatedFields : List of Map objects.

JAVA Example:

String view = "test-xls-crosstable-2d.axvw";

String groupByFields =
"[{fullName:'property.ctry_id',groupBy:'true',
showTotals:'true',title:'Country Code'},
{fullName:'property.status',groupBy:'true',
showTotals:'true',title:'Property Status'}]";
List<Map<String, String>> groupByFieldsList =
EventHandlerBase.fromJSONArray(new JSONArray(groupByFields));

DataSourceService service = new DataSourceService();
DataSet dataSet =
service.getGroupingDataSet(view, "propViewAnalysis2d_ds", null, null,"2d",
ReportUtility.getGroupByFieldNames(groupByFieldsList), null, null, null);

String calculatedFields =
"[{fullName:'property.area_summary',groupBy:'false',
showTotals:'true',title:'Area Summary', isNumeric:true},
{fullName:'property.property_count',groupBy:'false',
showTotals:'true',title:'Property Count', isNumeric:true}]";
List<Map<String, Object>> calculatedFieldsList =
EventHandlerBase.fromJSONArray(new JSONArray(calculatedFields));

com.archibus.ext.report.xls.CrossTab2DBuilder reportBuilder =
new com.archibus.ext.report.xls.CrossTab2DBuilder();

reportBuilder.build(dataSet, "Test a 2D CrossTab XLS report",
groupByFieldsList,calculatedFieldsList);

String fileName = reportBuilder.getFileName();
String url = reportBuilder.getURL();

Parameters:

• row : integer, row index.
• column : integer, column index.
• title : String, group by field title.
• color : XlsBuilder.Color object.

2). writeCalculatedFieldTitle(final int row,
final int column,
final String title)

Parameters:

• row : integer, row index.
• column : integer, column index.
• title : String, calculated field title.

3). addCustomTotalRow(final int totalRows,
final int totalColumns,
final List<Map<String, Object>> calculatedFields,
final DataSet dataset)

Parameters:

• totalRows : integer, total row number built in XLS sheet.
• totalColumns : integer, total column number built in XLS sheet.
• calculatedFields : List of Map objects.
• dataSet : DataSet1D object.

NOTES:

By overwriting this method, developers may add their own total row at the bottom of the XLS sheet. All the method parameters values will be passed by the core, and developers may overwrite them and write back to XLS.

JAVA Example:

1). Create a sub class of the standard class CrossTab1DBuilder and implement custom methods

public class Custom1DCrossTabXLSBuilder extends CrossTab1DBuilder {
/**
* Overwrite Group By Field Title.
*/
/** {@inheritDoc} */
@Override
public void writeGroupByFieldTitle(final int row, final int column,
final String title, final XlsBuilder.Color color) {
writeFieldTitle(row, column, "GroupBy: " + title, color);
}

/**
* Overwrite calculated Field Title.
*/
/** {@inheritDoc} */
@Override
public void writeCalculatedFieldTitle(final int row, final int column,
final String title) {
writeFieldTitle(row, column, "Calculated: " + title, this.rowHeaderColoring);
}

/**
* Add a total row at the bottom of XLS sheet.
*/
@Override
public void addCustomTotalRow(final int totalRows, final int totalColumns,
final List<Map<String, Object>> calculatedFields, final DataSet dataset) {
calculatedFields.get(0);
final int row = totalRows;
final DataSet1D dataSet1D = (DataSet1D) dataset;
final org.json.JSONArray totalValuesArray = dataSet1D.getTotals();
final JSONObject totalValues = (JSONObject) totalValuesArray.get(0);
final JSONObject totalValue = (JSONObject) totalValues.get("wr.total_requests");

for (int i = 0; i < calculatedFields.size(); i++) {
addCustomTotalColumn(row, 1 + i, Double.valueOf(totalValue.getString("n")), 2);
}
}
}

2). Call Custom1DCrossTabXLSBuilder to build a custom XLS 1D CrossTab report

public class TestCustom1DCrossTabXLSBuilder extends DataSourceTestBase {
/**
* view name.
*/
private static final String VIEW = "test-xls-crosstable-1d.axvw";

/**
* GROUPBYFIELDS.
*/
private static final String GROUPBYFIELDS = "[{fullName:'wr.month',groupBy:'true',"
+ "showTotals:'true',title:'Month', isNumeric:false}]";

/**
* calculatedFields.
*/
private static final String CALCULATEDFIELDS = "[{fullName:'wr.total_requests',groupBy:'false',"
+ "showTotals:'true',title:'Work requests', isNumeric:true}]";

/**
*
* @throws ParseException if JSONArray throws.
*/
public void testCustomizedBuild() throws ParseException {

final List<Map<String, String>> groupByFieldsList = EventHandlerBase
.fromJSONArray(new JSONArray(GROUPBYFIELDS));

final DataSourceService service = new DataSourceService();
final DataSet dataSet = service.getGroupingDataSet(VIEW, "crossTableByMonth_ds", null,
null, "1d", ReportUtility.getGroupByFieldNames(groupByFieldsList), null, null, null);

final List<Map<String, Object>> calculatedFieldsList = EventHandlerBase
.fromJSONArray(new JSONArray(CALCULATEDFIELDS));

final Custom1DCrossTabXLSBuilder reportBuilder = new Custom1DCrossTabXLSBuilder();

reportBuilder.build(dataSet, "Test a 1D CrossTab XLS report", groupByFieldsList,
calculatedFieldsList);
String fileName = reportBuilder.getFileName();
String url = reportBuilder.getURL();
}
}

Parameters:

• row : integer, row index.
• column : integer, column index.
• title : String, first group by field title.
• color : XlsBuilder.Color object.

2). writeTitleOfSecondGroupByField(final int row,
final int column,
final String title,
final XlsBuilder.Color color)

Parameters:

• row : integer, row index.
• column : integer, column index.
• title : String, second group by field title.
• color : XlsBuilder.Color object.

3). writeCalculatedFieldTitle(final int row,
final int column,
final String title)

Parameters:

• row : integer, row index.
• column : integer, column index.
• title : String, second group by field title.

4). writeFieldValue(final Map<String, Object> calculatedField,
final int row,
final int col,
final Object value)

Parameters:

• calculatedFields : List of Map objects.
• row : integer, row index.
• column : integer, column index.
• value : Object, calculated field value.

5). addCustomTotalRow(final int totalRows,
final int totalColumns,
final List<Map<String, Object>> calculatedFields,
final DataSet dataset)

Parameters:

• totalRows : integer, total row number built in XLS sheet.
• totalColumns : integer, total column number built in XLS sheet.
• calculatedFields : List of Map objects.
• dataSet : DataSet2D object.

JAVA Example:

1). Create a sub class of the standard class CrossTab2DBuilder and implement custom methods

public class Cutom2DCrossTabXLSBuilder extends CrossTab2DBuilder {
/**
* OverWrite first group by field's title.
*/
/** {@inheritDoc} */
@Override
public void writeTitleOfFirstGroupByField(final int row, final int column, final String title,
final XlsBuilder.Color color) {
writeGroupByFieldTitle(row, column, "First: " + title, color);
}

/**
* OverWrite second group by field's title.
*/
/** {@inheritDoc} */
@Override
public void writeTitleOfSecondGroupByField(final int row, final int column, final String title,
final XlsBuilder.Color color) {
writeGroupByFieldTitle(row, column, "Second: " + title, color);
}

/**
* OverWrite calculated field's title.
*/
/** {@inheritDoc} */
@Override
public void writeCalculatedFieldTitle(final int row, final int column, final String title) {
writeFieldTitle(row, column, "Calculated: " + title, this.rowHeaderColoring);
}

/**
* OverWrite field's value.
*/
/** {@inheritDoc} */
@Override
public void writeFieldValue(final Map<String, Object> calculatedField, final int row,
final int col, final Object value) {

if (StringUtil.notNullOrEmpty(value) && isNumeric(calculatedField)) {
// add minus
final Double numericValue = Double.valueOf(value.toString() + "10");
writeFieldValue(calculatedField, row, col, numericValue, null);
} else {
writeFieldValue(calculatedField, row, col, value, null);
}
}

/**
* Add a custom total row.
*/
/** {@inheritDoc} */
@Override
public void addCustomTotalRow(final int totalRows, final int totalColumns,
final List<Map<String, Object>> calculatedFields, final DataSet dataset) {
final Map<String, Object> calculatedField = calculatedFields.get(0);
this.getStringValue("id", calculatedField);
final DataSet2D dataSet2D = (DataSet2D) dataset;
final JSONArray columnValues = dataSet2D.getColumnValues();
final JSONArray rowValues = dataSet2D.getRowValues();

final int row = totalRows + 2;

for (int j = 0; j < columnValues.length(); j++) {
Double result = 0.00;
// add up each column value for all calculated fields
for (int r = 0; r < rowValues.length(); r++) {
final Object temp = this.xlsBuilder.getCellData(4 + r, 3 + j);
if (temp != null) {
final Double numericValue = Double.valueOf(temp.toString());
result += numericValue;
}
}


addCustomTotalColumn(row, 3 + j + this.nRowDimensionFields, result, 2);
}

if (rowValues.length() > 0) {
Double result = 0.00;
// add up total column value for all calculated fields
for (int r = 0; r < rowValues.length(); r++) {
final Object temp = this.xlsBuilder.getCellData(4 + r, 2);
if (temp != null) {
final Double numericValue = Double.valueOf(temp.toString());
result += numericValue;
}
}
addCustomTotalColumn(row, 2, result, 2);
writeFieldTitle(row, 1 + this.nRowDimensionFields, "Sum for two calculated fields:",
this.totalColoring);
}

}
}

2). Call Custom2DCrossTabXLSBuilder to build a custom XLS 2D CrossTab report

public class TestCustom2DCrossTabXLSBuilder extends DataSourceTestBase {
/**
* view name.
*/
private static final String VIEW = "test-xls-crosstable-2d.axvw";

/**
* GROUPBYFIELDS.
*/
private static final String GROUPBYFIELDS = "[{fullName:'property.ctry_id',groupBy:'true',showTotals:'true',"
+ "title:'Country Code'},{fullName:'property.status',groupBy:'true',showTotals:'true',title:'Property Status'}]";

/**
* calculatedFields.
*/
private static final String CALCULATEDFIELDS = "[{fullName:'property.area_summary',groupBy:'false',"
+ "showTotals:'true',title:'Area Summary', isNumeric:true},{fullName:'property.property_count',"
+ "groupBy:'false',showTotals:'true',title:'Property Count', isNumeric:true}]";

/**
*
* @throws ParseException if JSONArray throws.
*/
public void testCustomizedBuild() throws ParseException {

final List<Map<String, String>> groupByFieldsList = EventHandlerBase
.fromJSONArray(new JSONArray(GROUPBYFIELDS));
final DataSourceService service = new DataSourceService();
final DataSet dataSet = service.getGroupingDataSet(VIEW, "propViewAnalysis2d_ds", null,
null, "2d", ReportUtility.getGroupByFieldNames(groupByFieldsList), null, null, null);

final List<Map<String, Object>> calculatedFieldsList = EventHandlerBase
.fromJSONArray(new JSONArray(CALCULATEDFIELDS));
final Custom2DCrossTabXLSBuilder reportBuilder = new Custom2DCrossTabXLSBuilder();
reportBuilder.build(dataSet, "Test a crossTable 2D XLS report", groupByFieldsList,
calculatedFieldsList);
String fileName = reportBuilder.getFileName();
String url = reportBuilder.getURL();
}
}