Skip to Content
Author's profile photo E. Lokenberg

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.

/wp-content/uploads/2016/03/pic1_902677.png

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 🙂

Pic2.png

A definition of the three checkbox groups.

Pic3.png

Pic4.png

Pic5.png

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.

Pic6.png

Data sources

Just to clarify where the coding comes from…

Pic7.png

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 !

Assigned tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Andreas J A Schneider
      Andreas J A Schneider

      Exactly, what I needed. Thanks very much! 🙂

      Author's profile photo Former Member
      Former Member

      Hi ,

      Nice blog .you wrote push method?

      May I know what exactly it does in the script

      Thanks
      Varun

      Author's profile photo E. Lokenberg
      E. Lokenberg
      Blog Post Author

      It enables you to free select what crosstabs you would like to export to Excel.