Wednesday, June 09, 2010

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.

13 comments:

paladine said...

Hi Scott

Firstly, your post was very useful for me but I want to ask a question

When I want to use 'between' operator, I take an exception.
Is there any source for me finding more information ?

filterCondition.setExpr("row['r_real_1']");
filterCondition.setOperator("between"); filterCondition.setValue1("params[\"minLoad\"]");
filterCondition.setValue2("params[\"maxLoad\"]");

Thanks in advance

Jason Weathersby said...

Is this filter on a table?

Can you try:
filterCondition.setValue1("params[\"minLoad\"].value");
filterCondition.setValue2("params[\"maxLoad\"].value");

paladine said...

Thanks Jason,

I found different operator names from BIRT forums (ex : greater than or equal = ge) and now it works

Art Vandelay said...

Hi, Scott,

Thank you for this script! I had one problem though - I couldn't get the filters to work on the browser. In the eclipse plugin preview it worked fine...
I still cannot understand why, but I tried to change bits here and there and finally changing StructureScriptAPIFactory.createFilterCondition() to SimpleElementFactory.getInstance().createFilterCondition() made it work! I am pressed for time presently, but I intend to understand why this happened later. Would greatly appreciate if you shared your thoughts on this.

Thanks muchly!

arun said...

Hi,
Can anyone tells me about filtering for chart geration. My query is to generate a chart either fist 10/20 rows or last 10/20 rows. i have wrote the qery according to below.

importPackage( Packages.org.eclipse.birt.report.engine.api.script.element );
importPackage( Packages.org.eclipse.birt.report.model.api.simpleapi );
if(params["selection"].value = "TOP"){
var filtercondition= StructureScriptAPIFactory.createFilterCondition();

filterCondition.setExpr("row['SVCNAME']");

filterCondition.setOperator("top-n");
filterCondition.setValue1("params[\"Count\"].value");


var filterKey = filterCondition.getStructure();
var filterItem = SimpleElementFactory.getInstance().createFilterCondition( filterKey )
this.addFilterCondition( filterItem );

}
else if(params["selection"].value = "BOTTOM"){

var filtercondition= StructureScriptAPIFactory.createFilterCondition();
filterCondition.setExpr("row['SVCNAME']");
filterCondition.setOperator("bottom-n");
filterCondition.setValue1("params[\"Count\"].value");

var filterKey = filterCondition.getStructure();
var filterItem = SimpleElementFactory.getInstance().createFilterCondition( filterKey )
this.addFilterCondition( filterItem );
}

while i wrote this, error was generating.

can anyone helps me.

Jason Weathersby said...

Try Something like:

importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.report.model.api.elements);
chthandle = reportContext.getDesignHandle().findElement("mychart");
fc = StructureFactory.createFilterCond();
fc.setExpr("row[\"ORDERNUMBER\"]");
fc.setOperator(DesignChoiceConstants.MAP_OPERATOR_EQ);
fc.setValue1("10101");
ph = chthandle.getPropertyHandle("filter");
ph.addItem(fc);

arun said...

Hi Jason,

Could you please tell me clearly about this coding.
importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.report.model.api.elements);
chthandle = reportContext.getDesignHandle().findElement("mychart");
fc = StructureFactory.createFilterCond();
fc.setExpr("row[\"ORDERNUMBER\"]");
fc.setOperator(DesignChoiceConstants.MAP_OPERATOR_EQ);
fc.setValue1("10101");
ph = chthandle.getPropertyHandle("filter");
ph.addItem(fc).

arun said...

Hi Jason,

I tryed to implement by using your code, But i want to display the chart either top 10/20 rows or Bottom 10/20 rows.


please tell me how to implement this by using the code.

Jason Weathersby said...

Try

importPackage(Packages.org.eclipse.birt.report.model.api);
importPackage(Packages.org.eclipse.birt.report.model.api.elements);
chthandle = reportContext.getDesignHandle().findElement("mychart");
fc = StructureFactory.createFilterCond();
fc.setExpr("row[\"QUANTITYORDERED\"]");
fc.setOperator(DesignChoiceConstants.FILTER_OPERATOR_BOTTOM_N);
fc.setValue1(4);
ph = chthandle.getPropertyHandle("filter");
ph.addItem(fc);

Or uze:
FILTER_OPERATOR_TOP_N

kareppa said...

i have 100 images in folder,i am giving all images path in csv(ex:- C:/new folder/2.jpg ,C:/new folder/2.jpg like),reading csv file how can i display all 100 images in birt report

Amar Shah said...

Hi Jason, Is there way to do something similar on OnCreate method. Same approach what you used to filter dynamically using report parmaeters, I need to do it using value in dataset. I have nested table, main list and 4 sub sub tables in list. both list and all tables is bound to same data set. List is filtered to how summary record. table is to show Detial records. Currently for all rows in list, there is filtering happening for all 4 sub tables. However we have filed in summary row clearly indicating that there will be no detail row for specific sub table. I would like to avoid filtering altogather. It seems filtering is getting costlier operation and slowing down our report. Dynamic filtering Or totaly avoid data bindging at run time of report is our requirement.

Anonymous said...

Hi Jason,

I want to filter data on my report wioth time
Say a record is for 01/01/15 12:00 AM

So, i want to filter this through dta time report parameter.

Please help!!!

Neha

Naveen said...

Hello All,

I am facing an issue with passing huge set of multiple values to a filter condition with IN operator.
Values looks like: 'abc,'def',.....
Report executes with a value of length 7800 characters. Any value beyond this length resulting 'page count faile' error.

Any suggestion is much appreciated!!