Conditional Table Filters

Just ran across this and it is a nice technique for those situations where you are limited to table based filtering of data. Typically, I focus on data filtering as far up stream as possible. It is better to filter data at the source (in the where clause for JDBC). Next, I use DataSet based filtering.

But sometimes you can't filter at the Source or the DataSet, which is where table based filtering comes in. The issue with table based filters is that there is no good way in the UI to implement conditional filtering. For instance, imagine you have a data driven parameter multi-select parameter and you want to limit the choices to the values from that parameter.

This is relatively simple to do, you just create a multi-select parameter and add a filter to a table.

So what happens if your user wants to select all of the values in the list? They need to select each of the values from the pick list that appears. Unfortunately, with large pick lists this is a little tricky for the end user. In addition, table based filtering on large pick list may create performance issues.

What you would like to do is make the parameter optional, and if the value for the parameter is not set, then just don't filter on that parameter value.

The UI does not support this type of behavior, but it is easy to do through a small amount of script on the table in the onPrepare method.

importPackage( Packages.org.eclipse.birt.report.engine.api.script.element ); 
importPackage( Packages.org.eclipse.birt.report.model.api.simpleapi ); 

if(params["Region"].value!= null){
    var filterCondition = StructureScriptAPIFactory.createFilterCondition(); 
    filterCondition.setExpr("row['COUNTRY']"); 
    filterCondition.setOperator("eq");
    filterCondition.setValue1("params[\"Region\"]");
    
    var filterKey = filterCondition.getStructure();
    var filterItem = SimpleElementFactory.getInstance().createFilterCondition( filterKey )
    this.addFilterCondition( filterItem );
}

Now how would you handle the situation if you had multiple filter conditions that you wanted to add?  A trivial re-factor and you can create a general purpose method add filter method.

// generic function to add a filter to a table
function addFilterItem (table, rowExpr, paramExpr){
  
  var filterCondition = StructureScriptAPIFactory.createFilterCondition(); 
  filterCondition.setOperator("in");
  filterCondition.setExpr(rowExpr); 
  filterCondition.setValue1(paramExpr);

  // could do in one step, shows DEAPI creation steps
  var filterKey = filterCondition.getStructure();
  var filterItem = SimpleElementFactory.getInstance().createFilterCondition( filterKey );

  table.addFilterCondition(filterItem);
}

This method can then be placed into a JavaScript file, a Script extension point, or a global location.  Calling the file from the table is equally easy.

// add filters dynamically
if (params["Country"].value != null )
  addFilterItem (this, 'row["COUNTRY"]' , 'params["Country"].value' );

if (params["City"].value != null)
  addFilterItem (this, 'row["CITY"]' , 'params["City"].value' );

It is important to recognize that the second and third parameters that are being passed are expressions and not values.  So what is happening is we are creating a filter, and that filter will use the express row["COUNTRY"] to look up the appropriate country values.  That is why you need to pass the exact expression through in the function.

If you are having trouble figuring out the appropriate expression, just build the expression that you want in the UI, and then search through the XML for the word filter. You will see the expressions in the XML. Just copy these expressions and pass them to your function. Just delete the static filter and you are all set.  Yes, you can use different operators then the IN clause, but it seems the most appropriate for this type of filtering.