Thursday, April 30, 2009

BIRT Cascaded Parameters

BIRT provides the capability to use dynamic parameters to present the end user with a list of choices that are populated from a dataset. This is very useful but can cause issues when the dataset returns many rows of data. To reduce the number of items in any parameter, the developer can use a cascaded parameter, which allows multiple levels and multiple datasets. When the user selects the first level parameter in a cascaded parameter group the second level in the group is automatically re-queried.

For example, if you have a customer detail report that allows the end user to select a particular customer, you could create a dynamic parameter that retrieves all the customer names from your database. The user would then select the one they are interested in, and a detail report would be generated on the selected customer. If you have thousands of customers this task becomes more difficult for the end user to navigate. To remedy this, you could create a cascaded parameter that has as its first level, the country and once that value is selected, all customers for the specific country would be listed. This assumes you have a field like country to that can be used to reduce the number of entries. If you do not have a field like this, it may be possible to create your own using script.

If we take the customer list example, one way to reduce the number of items in any of the parameter list box, would be to sort the first level of the cascade alphabetically. So the first level of the cascade could be tied to a scripted datasource that returns the letters of the alphabet. Another option is to do a distinct query on the customers within the database that retrieves only the first letter like:

select distinct SUBSTR( CUSTOMERNAME, 1, 1)
from customers

The second level in the cascade would then be defined with the following query.

select customername
from customers
where customername like ?

The question mark represents a data set input parameter, which is not the same as a report parameter. The dataset parameter can be linked to a report parameter or set programmatically using script. In this example we need to use script to add a wildcard to the query. So in the beforeOpen script of the second level query, we could do this:

inputParams["alpha"] = params["FirstLetter"]+"%";

In this example, alpha is the dataset parameter and the FirstLetter is the first level report parameter in the cascade group. In this case we are only adding the wildcard to the query.

The resultant parameter entry screen would look like:



This report is available here. Another example using a scripted data source is available here.

Tuesday, April 14, 2009

Multi-Select Parameters - Part 2

 In a previous post I described how you could create a simple method to use multi-select parameters with a SQL IN clause in a generic fashion.  In that post I referenced that the right way to do this is to use Java Bind variables

There is a way that this can be done using Java Bind variables, but it is significantly more difficult.
But I did not explain how that is done.  Today I gave a webinar on how you can use the Design Engine API and the BIRT Script Function extension point to dynamically add parameter binding to your report designs.

The DEAPI code is a bit tricky to use, but thanks to the ScriptFunction extension, any one can use the function quickly and easily.

All of the code and the slides for the project are available on the Innovent Subversion server here.  You can use the subclipse plugin to download this code as an Eclipse project.

The slides for the presentation are included here:

BIRT Multi Select Parameters

Wednesday, April 01, 2009

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.

Sunday, March 08, 2009

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.

Wednesday, March 04, 2009

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.

Tuesday, March 03, 2009

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.

Monday, January 26, 2009

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.