How to use a parameter which a user can update in a calculated field in getFields (Data Studio Community Connector)

Clusive 0
Viewed 32

I have a single select parameter that I define in a data studio community connector in getConfig() as below. The parameter is then used as a dropdown data control in the report.

config
    .newSelectSingle()
    .setId("characteristic_selected")
    .setName("Characteristic selected")
    .addOption(
        config
          .newOptionBuilder()
          .setLabel("a")
          .setValue("a")
    )
    .addOption(
      config
        .newOptionBuilder()
        .setLabel("b")
        .setValue("b")
    )
    .addOption(
      config
        .newOptionBuilder()
        .setLabel("c")
        .setValue("c")
    )
    .addOption(
      config
        .newOptionBuilder()
        .setLabel("d")
        .setValue("d")
    )
    .addOption(
      config
        .newOptionBuilder()
        .setLabel("e")
        .setValue("e")
    )
    .setAllowOverride(true);

In getFields() I define Characteristic which then returns data from the database a, b, c, d or e:

  fields.newDimension()
    .setId('Characteristic')
    .setType(types.TEXT);

I am trying to define a calculated field which I will then use as a filter in my charts and tables to only display data for the option selected by the user in the dropdown data control. E.g. if the user selects "b", then only data labelled "b" for Characteristic will display.

My attempt (inspired by the answer here: How to use a Parameter in calculated field defined in getFields() (Google Data Studio Community Connector)?) is:

  fields.newDimension()
    .setId('Characteristic calc')
    .setDescription('Sets true if characteristic selected in dropdown is the same as the characteristic dimension field')
    .setFormula('$Characteristic = "' + request.configParams.characteristic_selected + '"')
    .setType(types.BOOLEAN);

I then apply a filter to the tables and charts only include Characteristic calc = True

The default is "a". When I first load the page, the data filters correctly and only displays "a". However when I select "b" from the dropdown data control, it still only displays data for "a". It appears that the code does not capture the updated configParam when changed in the report.

Note: if I set the calculated field up in the report as opposed to in the data studio connector, then it works correctly. I use the connector in lots of reports however, so it is annoying to have to create the calculated field each time I create a new report.

google-data-studio
1 Answers

Sorry, using the .setFormula will only work, if the parameter is only set during the source configuration. If the user can change it in the report the field calculation has to be done in the function getSchema(request).

function getSchema(request) {
  var fields = cc.getFields();
  var types = cc.FieldType;
 fields.newDimension()
    .setId('Characteristic_info_info')
    .setDescription('tempory field')
    .setType(types.TEXT);
 fields.newDimension()
    .setId('Characteristic calc')
    .setDescription('Sets true if characteristic selected in dropdown is the same as the characteristic dimension field')
    .setFormula('$Characteristic = $Characteristic_info_info')
    .setType(types.BOOLEAN);

Then set the column to the right value in this function:

function getData(request) {
....
  var schemaE = getFields(request).forIds(
    request.fields.map(function(field) {
      return field.name;
    })
  ).build();

  var out1=[];
  schemaE.forEach(a=>out1.push(a.name in params ? params[a.name] : 'nope:'+a.name ));

  var out= {
    schema: schemaE,
    rows: [{values:out1},{values:out1},{values:out1}] 
  };
console.log(schemaE)

console.log(request.configParams.characteristic_selected)

for(let col in schemaE){
if(schemaE[col].name=="Characteristic_info_info"){
for(let i in out.rows)
 {
   out.rows[i].values[col]=request.configParams.characteristic_selected+"!";
 }
}
}
  return out;
}

If a paramter could be used in .setFormula and not only $ for fields, life would be much easier.

userG7m710
Related