Multiple crosstabs to Excel

Hi y’all,

I came accross an issue that I solved and I’d like to share this with you. It’s about how a user can select a various number of crosstabs to export to Excel.

General

In this case there are 9 crosstabs defined where the user can select which need to be exported to Excel. The standard automated Excel export functionality will be used (all separate crosstabs will be exported into worksheets).

Layout user interface

In the example below there are three pre-defined reports available in totals, per region and per market.

Three vertical checkbox groups were created, it is currently not possible to create a 3 x 3 checkbox group (DS1.6 SP1).

Definition

An extra global variable type Crosstab has to be created. Did try a local variable but that didn’t do the job 🙂

A definition of the three checkbox groups.

Crosstabs

Create a new panel and place 9 crosstabs in it. Since it’s only meant for exporting, the panel is kept small and placed behind an image (or anything else that covers it). Make sure the names of the crosstabs are equal to the names used for the checkbox members.

Data sources

Just to clarify where the coding comes from…

Export button event

The export button contains al the code to read the selected checkboxes and translate these into an array that is added to the export to Excel function.

// Read values from the 3 checkbox groups (CBGR).

var lv_tot_arr = CBGR_TOT.getSelectedValues();

var lv_reg_arr = CBGR_REG.getSelectedValues();

var lv_mkt_arr = CBGR_MKT.getSelectedValues();

// Create crosstab array

var lv_CTArr = [CT1];

// Remove element from the array

lv_CTArr.pop();

lv_tot_arr.forEach(function(element, index) {

  if (element == “CT1”) {

         gvCT = CT1;

         lv_CTArr.push(gvCT);

         CT1.setDataSource(DS_1);

  }

  if (element == “CT2”) {

         gvCT = CT2;

         lv_CTArr.push(gvCT);

         CT2.setDataSource(DS_2);

  }

  if (element == “CT3”) {

         gvCT = CT3;

         lv_CTArr.push(gvCT);

         CT3.setDataSource(DS_3);

  }

});

lv_reg_arr.forEach(function(element, index) {

  if (element == “CT4”) {

         gvCT = CT4;

         lv_CTArr.push(gvCT);

         CT4.setDataSource(DS_1_1);

  }

  if (element == “CT5”) {

         gvCT = CT5;

         lv_CTArr.push(gvCT);

         CT5.setDataSource(DS_2_1);

  }

  if (element == “CT6”) {

         gvCT = CT6;

         lv_CTArr.push(gvCT);

         CT6.setDataSource(DS_3_1);

  }

});

lv_mkt_arr.forEach(function(element, index) {

  if (element == “CT7”) {

         gvCT = CT7;

         lv_CTArr.push(gvCT);

         CT7.setDataSource(DS_1_2);

  }

  if (element == “CT8”) {

         gvCT = CT8;

         lv_CTArr.push(gvCT);

         CT8.setDataSource(DS_2_2);

  }

  if (element == “CT9”) {

         gvCT = CT9;

         lv_CTArr.push(gvCT);

         CT9.setDataSource(DS_3_2);

  }

});

APPLICATION.export(ExportType.EXCEL_xlsx,lv_CTArr);

Have phun !