BIRT 2.5 M6 New and Notable

Just wanted to drop a note to say the new and notable for BIRT 2.5 M6 is now available. Some great features are making their way into BIRT 2.5, like z ordering for combination charts, improved 508 compliance, project configuration changes, and better image marker support.



To read more about the new features, go here. As always we appreciate feedback on the new features.

BIRT Multi-Select Statements

Note: re-published to fix typos in the opening paragraph.  Oh the perils of late night posting.

In BIRT 2.3.1 a new extension point was added to support the creation of user defined functions.  Jason has all ready blogged about the ScriptFunctionExecutor here .  In this post, I would like to show how this extension point can be used to automate handling Multi-Select input parameters in SQL statements.

BIRT 2.3 added support for Multi-Select parameters.  The most typical application of this technology is to support using an IN statement within a SQL where clause.  Imagine having the following SQL:

select status   
from orders
where state in (?)

What you would like to be able to do is have a user select multiple parameters from a drop down list, and then have them work in the query.  At this time, BIRT does not support this, mostly because of the way JDBC property binding works.  JDBC would require a new parameter binding (?) to be added for each parameter value that was passed, e.g.

where state in (?, ?, ?)

The problem is that you don't know how many parameters are passed when you design the report.  The work around that most people use is to write an event handler that will insert SQL into the query in the BeforeOpen method of the DataSet.  

There are two problems with this approach is that you leave yourself wide open for a SQL Injection attack.  First, you have SQL in the DataSet editor and in the BeforeOpen method,  this makes the code more brittle and difficult to maintain.  The second is that you leave yourself wide open to a SQL Injection attack.  (If you don't know, please follow the link).  

What I wanted to do was figure out a way that I could simplify using multi-select statements that would have some safe guards against a SQL injection attack.  The ScriptFunctionExtension is a great way to do just that.  

First, we want to modify our SQL so that the parameters are defined within the SQL.  To use my function your SQL will look like this:

select status   
from orders
where status in ('MS:pStatus') and
state in ('MS:pState')

In this scenario you have two parameters pStatus and pState that are defined in the report parameters.  Then you modify the BeforeOpen method of your DataSet to have the following code:

this.queryText = BlackboardFunctions
            .MultiSelectSql(this.queryText, reportContext);

That's is all you have to do.  All of the SQL modification and checks for injection strings are done for you in the ScriptFunction.   Now the caveats: I realize that there are still SQL Injection issues here.  I welcome your suggestions on how to reduce the risk.  There is a way that this can be done using Java Bind variables, but it is significantly more difficult.

If you would like to download the full source to the plugin, I have it on my Subversion server here.  For the curious, the remainder of this article will talk about how it was done.

First, let's look at the ScriptFunctionFactory.  I absolutely hate having to keep code coordinated in more than one place.  The ScriptFunction requires that you define the Script in the plugin.xml and in the FunctionFactory.  In addition, you will have the actual ScriptFunctionExecutor classes.

What I did was came up with a simple rule, the name of the Function (in plugin.xml), is the name of the ScriptFunctionExecutor class.  This simplifies my FunctionFactory to just use the name passed in to find the appropriate class:

    public IScriptFunctionExecutor getFunctionExecutor(String functionName )
        throws BirtException {
        String fullClassName = plugin_id + "." + functionName;
        try {
            Class<? extends IScriptFunctionExecutor> functionClass = Class
                    .forName( fullClassName )
                    .asSubclass( IScriptFunctionExecutor.class );
            IScriptFunctionExecutor scriptFunction = functionClass
                    .newInstance();
            return scriptFunction;
        }
        catch ( Exception e ) {
            e.printStackTrace();
            throw new BirtException( plugin_id, "Unable to find class: "
                                                            + fullClassName,
                                                getResourceBundle(), e );
        }
    }

Next we need to have the ScriptFunction code that walks through the queryText replacing the messages with the appropriate parameter values

public Object execute( Object[] args, IScriptFunctionContext context ) throws BirtException {
    final String sqlText = args[ 0 ].toString();
    IReportContext reportContext = super.getReportContext( args[ 1 ] );

    // find the Multi-Select replacement string
    List<String> matchList = new ArrayList<String>();
    try {
        Pattern regex = Pattern.compile( "\\('MS:[A-Za-z0-9]+'\\)" );
        Matcher regexMatcher = regex.matcher( sqlText );
        while ( regexMatcher.find() ) {
            matchList.add( regexMatcher.group() );
        }
    }
    catch ( PatternSyntaxException ex ) {
        // Syntax error in the regular expression
    }

    String rtnStr = sqlText;
    // iterate through each multi-select parameter
    // in the sql and replace with appropriate parameter values
    for ( final String matchStr : matchList ) {
        String searchFor = matchStr.substring( 2, matchStr.length() - 2 );
        String paramName = searchFor.substring( 3 );
        Object obj = reportContext.getParameterValue( paramName );
        if ( obj == null )
            removeLine( matchStr, rtnStr );
        if ( obj instanceof Object[] ) {
            StringBuffer sb = new StringBuffer();
            Object[] pVals = (Object[]) obj;
            for ( int i = 0; i < pVals.length; i++ ) {
                super.testSqlInjection( pVals[ i ].toString() );
                sb.append( pVals[ i ].toString() );
                if ( i < pVals.length - 1 ) {
                    sb.append( "', '" );
                }
            }
            rtnStr = rtnStr.replaceAll( searchFor, sb.toString() );
        }

    }
    return rtnStr;
}

There are two sub-functions that are lurking in that text.  First, a common task for ScriptFunctions will be getting the ReportContext from the EventHandler, while not difficult, it makes sense to have appropriate error handling and testing, so I put that in an abstract super class.

protected IReportContext getReportContext( final Object rcArgument )
    throws BirtException {
    if ( rcArgument == null ) {
        throw new BirtException( InnoventFunctionFactory.plugin_id,
                                            "ReportContext object is null in "
                                                    + this.getClass()
                                                            .getSimpleName(),
                                            InnoventFunctionFactory
                                                    .getResourceBundle() );
    }
    if ( ( rcArgument instanceof IReportContext ) != true ) {
        throw new BirtException( InnoventFunctionFactory.plugin_id,
                                            "ReportCtxt is not instance of IReportContext in "
                                                    + this.getClass()
                                                            .getSimpleName(),
                                            InnoventFunctionFactory
                                                    .getResourceBundle() );
    }
    return (IReportContext) rcArgument;
}

Not completely tricky, but who wants to write that code over and over again?

The other statement is the call to test for likely SQL Injection tokens.  I know the list is not complete, but what do you expect when you have free advice.

    protected void testSqlInjection( String paramValue )
        throws BirtException {
        List<String> errList = new ArrayList<String>();
        try {
            Pattern regex = Pattern.compile( "[%]|[']|[;]|[(]|[)]|[=]" );
            Matcher regexMatcher = regex.matcher( paramValue );
            while ( regexMatcher.find() ) {
                errList.add( regexMatcher.group() );
            }
        }
        catch ( PatternSyntaxException ex ) {
            // Syntax error in the regular expression
        }
        if ( errList == null || errList.size() == 0 ) {
            return;
        }
        // Uh oh, something is fishy in this parameter
        StringBuffer sb = new StringBuffer();
        sb.append( "Failure to add parameter value :\n" );
        sb.append( paramValue );
        sb
                .append( "The following values are not allowed in parameters \n{ " );
        for ( String err : errList ) {
            sb.append( err );
            sb.append( " " );
        }
        sb.append( " }" );
        throw new BirtException( InnoventFunctionFactory.plugin_id, sb
                .toString(), InnoventFunctionFactory.getResourceBundle() );
    }

Well that is about it.  It is probably a lot easier to just download the code and have a look.

I hope to see you all at EclipseCon in two weeks.  We have a number of really great talks on BIRT including a four hour tutorial by John Ward.  I am teaming up with Seth Grimes to have a discussion about the value of Business Intelligence. I'll be showing a few BIRT reports, but I am going to try and keep a layer or two above the gory implementation details.

One talk that I am really looking forward to will show how you can integrate BIRT into your Rich Internet Applications (RAP) projects.  This talk is by Benny Muskalla from EclipseSource and it promises to be really good.

Innovent Solutions, my company, will be at the exhibitors hall.  If you enjoy the blog, drop by and say hi.

Raising Errors in Event Handlers

Let me start with a simple scenario.  You are writing a BIRT event handler and you expect the user to set a UserDefinedProperty that will guide the event handler code.  How do you handle the situation where the user forgets to add the UserDefinedVariable?  How do you provide a message back to the user to help them solve the problem?

In the past I have focused on using logging and stepping through code.  While effective, it requires the person using your event handler code to have a relatively high level of sophistication.  Wouldn't it be great if you could pass an error message from your event handler code to the ReportEngine, so that the message would be displayed through the standard error handling mechanism?

The methods that are used to add exceptions to a report are not visible from the Script API.  I hope that we can get an enhancement into BIRT 2.5 that will make this easy to do, but what if you want to add code like this to the early version of BIRT.

It turns out that it is easy enough to do using Java reflection.  The ReportContext object wraps an instance of the ExecutionContext.  ExecutionContext supports a method:
     addException(BirtException exception)


All we need to do is figure out how to expose this method to our EventHandlers (Java or JavaScript).  To do this I created a static method called addBirtException, the complete code listing is as follows:

public static void addBirtException(IReportContext reportContext , String errorMessage) {
    addBirtException(reportContext , errorMessage, BirtException.WARNING);
}

public static void addBirtException(IReportContext reportContext , String errorMessage, Integer severity) {
    
    BirtException be = new BirtException("org.eclipse.birt.report.engine", errorMessage, new Object[]{""} );
    be.setSeverity(severity);

    try {
        // get the protect field 'context' from reportContext
        Class rciClass = reportContext.getClass();
        Field fieldFromScript = rciClass.getDeclaredField("context");
        if (fieldFromScript == null) {
            return;
        }

        // instantiate the ExecutionContext object that
        // populates the context field
        fieldFromScript.setAccessible(true);
        Object execContext = fieldFromScript.get(reportContext);

        // now get a handle to the addException method on ExecutionObject
        Class execClass = execContext.getClass();
        Method addMethod = execClass.getMethod("addException", new Class[] { BirtException.class });

        // finally invoke the method which will add the BirtException 
        // to the report
        addMethod.setAccessible(true);
        addMethod.invoke(execContext, new Object[] { be });

        // Lots of ways for this to break...
    } catch (Exception e) {
        logger.warning(e.getMessage());
        e.printStackTrace();
    } 
    return;
}

Once you have the ScriptUtil class in your classpath you can call it from your Java class by  adding this code:

    ScriptUtil.addBirtException(reportContext, "Simple Error Message", BirtException.WARNING);

Will cause your report to run with the following error messages to display in your report.

But why stop there.  Now that you have the ability you can go ahead and provide detailed help back to the people that are using your report or event handler class:

    ScriptUtil.addBirtException(reportContext, "Simple Error Message", BirtException.WARNING);

    StringBuffer sb = new StringBuffer();
    sb.append("Complex error message that is designed to help a\n");
    sb.append("report developer that is using your event handler\n");
    sb.append("to successfully debug an improper implementation.\n");
    sb.append("Could also be used to help end users identify report\n");
    sb.append("issues to support and help desk staff.\n");
    sb.append("\t - Can include error codes from other products\n");
    sb.append("\t - Can include domain specific messages.\n\n");
    sb.append("No more looking around for message logs or tracing through\n");
    sb.append("gnarly stack traces");
    ScriptUtil.addBirtException(reportContext, sb.toString(), BirtException.WARNING);

gives you:

This code is also easily callable from JavaScript.

Packages.deapi_event_handler.ScriptUtil.addBirtException(addBirtException, "my message");

So pretty cool eh?

But there is more, the BirtException supports localization, resource bundles, formatting all of the features that you may want in advanced report development.

Using BIRT with PHP

Just wanted to post a note saying The Server Side just published an article we worked on that demonstrates how BIRT can be called using PHP. This article covers using the BIRT Report Engine within PHP to implement running and rendering of reports and includes details on how to handle drill through and BIRT libraries.

The article is available here.

A version of the integration (Containing BIRT 2.3.1) is also available on BIRT Exchange here. Be sure to look at the notes in the download page that discuss JDBC drivers and event handler jars.

Single Sign On with the Actuate BIRT Viewer

In an earlier post (Showing BIRT Reports using the Actuate JSAPI), we talked about using the new Actuate JSAPI to integrate report content within your application. This article also discussed deploying the Actuate BIRT Viewer (contained in the Deployment Kit), which extends the capabilities of the open source Viewer to include quite a few interactive features, such as dynamic resorting, filtering, grouping and report storage. As a follow-up to that article, I wanted to discuss security integration using the Actuate BIRT Viewer. We just finished up this article and it is posted on BIRT Exchange. This article discusses single sign on and how it can be accomplished under the different deployment models that are available.

BIRT - Reversing a Chart Scale.

The Chart Engine that is delivered as part of the BIRT project offers a lot of functionality. Not only can you extend most of its features, the chart engine also offers hooks to do scripting (either in Java or JavaScript). Scripting can be very useful for modifying the chart based on parameters or data. As an example assume you have a chart that is displayed as follows:



Assume these numbers represent percent of sales returns and the lower the bar the better. In some cases larger bars graphically represent better values, so instead of displaying the chart as above the user may wish to see the chart as follows.



One way to achieve this effect is to modify the values and labels in chart script. To modify or add scripts to a chart select the chart and then the script tab within the designer. Bear in mind that although this example illustrates building the event handler in JavaScript it can also be done with Java.

The first thing to setup is the scale for the chart, which we want to tie to a report parameter. So you can enter a script like:


//Global scale
gblscale = 100.00;


//this is the first event called for charting
//So set scale here
function beforeDataSetFilled( series, idsp, icsc )
{
gblscale = icsc.getExternalContext().getScriptable().getParameterValue("Scale");
}

We set the scale for the chart to the parameter value in the beforeDataSetFilled event because this is the first event called for chart scripts. This variable will be used in other scripts for calculating labels.

The next script we need to override is the afterDataSetFilled event handler. This script can be used to modify the values used in a chart.

function afterDataSetFilled(series, dataSet, icsc)
{
if( series.getSeriesIdentifier() == "series1"){
var list = dataSet.getValues();
for ( i=0; i < list.length; i=i+1)
{
list[i] = gblscale - list[i];
}
}
}



This script first checks the series identifier as this call is made for every series, but we only want to change the bar series. The series identifier should be entered in the chart wizard. This can be done in the third tab of the chart wizard.



Once we know that we are working with the correct series, we can modify the values by calling the dataSet.getValues. In the script we are just subtracting the values from the global scale and resetting the value. So if the global scale is 100, a 5 value becomes 95.

We can then set the scale for the entire chart in the beforeGeneration event as follows.


function beforeGeneration(chart, icsc)
{
importPackage( Packages.org.eclipse.birt.chart.model.data.impl );
xAxis = chart.getBaseAxes()[0];
yAxis = chart.getOrthogonalAxes( xAxis, true)[0]
yscale = yAxis.getScale();
yscale.setStep (10);
yscale.setMin( NumberDataElementImpl.create(0) )
yscale.setMax( NumberDataElementImpl.create(gblscale) )
yAxis.setScale(yscale);

}

By default this script sets the Y axis to range from 0 to whatever the parameter value is set to. We now can reverse the labels by adding a beforeDrawAxisLabel script.

function beforeDrawAxisLabel(axis, label, icsc)
{
importPackage(Packages.org.eclipse.birt.chart.model.attribute);
if (axis.getType() == AxisType.LINEAR_LITERAL){
var val = parseFloat(label.getCaption().getValue());
var newval = gblscale - val;
label.getCaption().setValue(newval);
}
}

This script only changes the labels. Internally the chart is still scaled from 0 to the parameter value. So if the scale is 100 and the original value is 5, its new value is 95 and the bar will be drawn from 100 to 5. Internally this is still 0 – 95, but we reversed the labels. The last script we may want to add can change the data point labels. This script is called beforeDrawDataPointLabel and is called whenever the chart renders labels on the bars.

function beforeDrawDataPointLabel(dph, label, icsc)
{
var val = parseFloat(label.getCaption().getValue());
var newval = gblscale - val;
newval = newval.toFixed(2);
label.getCaption().setValue(newval);
}

In this script we just reverse the value label. This example can be downloaded at
BIRT Exchange.

Friday is the last Day for EclipseCon 2009 proposals

I just wanted to put a post up to let everyone know that the deadline for submissions for EclipseCon 2009 has been extended to Friday (11/28/2008 5pm PST). We are still looking for some use case proposals in the Reporting track and would appreciate feedback on the existing submissions. If you are interested, the EclipseCon 2009 page is located here.