Friday, December 18, 2009

EclipseCon Submissions - Last Chance to Propose a Talk

Oisín Hurley is one of the smartest, funniest and best looking people in the Eclipse community*  So when he says jump, I jump.

That's right it is the last day to get your EclipseCon submission for a talk in.  If you have been doing anything interesting with BIRT that you would like to share with the Eclipse community, we would love to hear about it.  There are lots of opportunities to present at different lengths.

Head on over to the EclipseCon submission site and make a proposal about how you are using BIRT.


*And totally immune to blatant attempts at flattery.

Monday, December 14, 2009

BIRT Plugins at Google Code

Blackboard, Inc. develops solutions for the K-12 school, college campus, workplace, and community that increase the impact of education by transforming the experience of education. Blackboard has chosen BIRT as its reporting and business intelligence tool.  

Over the last two years I have been lucky to work with Blackboard on their BIRT integration.  Working with their product and developers we have been able to enhance their core product with reports.  Each Blackboard report tells a story that can help their clients better perform their jobs.  

One of BIRT's features that was extremely helpful was the ability to customize and extend BIRT to meet Blackboards requirements.  Using the BIRT extension points we added functions and controls to BIRT that improved both the developer and users experience when interacting with BIRT.

Blackboard has agreed to release some of the generic portions of this work back to the BIRT community as open source contributions.  Rather than rolling these contributions into the core product (where it would quickly get lost amidst all the other code) we have created two small open source projects that can be used by the community.  These projects can be used either as fully functional products or as best practice sample code.  





BIRT Functions
Uses the Aggregate and Script Function extension point to add new functions to the BIRT product.



BIRT Controls
Uses the ReportItem extension point to create new elements that show up on BIRT reports.



Innovent Update Site
Hosts an Eclipse Update site for both projects. We will be adding new projects to this site as we finish up the code.

If you are a BIRT developer that just wants to get started you can use the following update link:
  http://innovent.googlecode.com/svn/trunk/update

If you would like to find out a bit more about the projects, please visit the project sites.


Thanks go out to thank both BIRT Exchange and the brand spanking new Eclipse Marketplace for providing access to our projects.  It is really great to have a couple of quality channels to publicize our work to the BIRT community.

Also, I want to mention the team that helped to build and get this code open sourced.  Steve Schafer from Innovent Solutions wrote a lot of the code and figured out the inner workings of the ReportItem exension point.  Blackboard had a whole team of people working on reports, in particular thanks to Heather, JoAnna, Michelle, Dan, Joe, Joel, and Manpreet.



    Friday, December 11, 2009

    Calling Client Side JavaScript from a BIRT Chart

    A couple of months ago I detailed a new feature for BIRT charts that allows multiple hyperlinks to be attached to one the supported events. That post is available here.

    In this post I will discuss using a BIRT Text element that contains script which executes within the client browser and contains functions that are called from rendered charts.

    General Information
    BIRT currently supports interactivity on many chart components like chart series, title, axis, and the legend. The components that support interactivity will depend on the type of chart being used. The events are client based events like mouse click, mouse over, key down, etc. Multiple events can be hooked and each is associated with an action. Actions define the behavior that should occur when a specific event happens. The actions available depend on what component the action is defined for and what chart output type is being used. Currently BIRT supports the following Actions.

    Hyperlink – Supports multiple hyperlinks, drill through, and linking to bookmarks.
    Show Toolip – Supports displaying tooltips. Data available for use in the tooltip will depend on the component.
    Highlight – Highlights the selected component. Most often used to highlight specific series or data point.
    Toggle Visibility – Toggles the visibility of the selected component. Most often used to change the visibility of a series or data point.
    Toggle DataPoint Visibility – Toggles the data point label visibility.
    Invoke Script – Invokes client side script.

    Additionally if you are using the Chart engine within an SWT, SVG, or Swing based application the engine supports adding a callback action that your code can use to interpret chart events.



    Highlight, Toggle Visibility, and Toggle DataPoint Visibility actions are only available when using an SVG output setting.



    If you use SVG, and you wish to test the report in the designer remember to set the Enable SVG chart in the Preview preferences for the Report Design Preference entry.



    More information on Chart Interactivity is available here. Also see the Chart FAQ for more details.

    InvokeScript Example
    The follow section details an example of using the invokeScript action on multiple Chart events.

    Assume that you have a Chart that displays a set of customers with a series value for each equal to the customer's credit limit.



    Suppose you want to display the customer details right below the chart when the mouse is moved over a specific data point. This can be done using the invokeScript action on the mouse over event for the chart series. To do this first create a table below the chart that is bound to the same dataset that the chart is using. You can then nest a table that calls another data set to retrieve customer information. In the attached example this will be the ChartData and CustomerInformation datasets respectively.



    By default this will generate a inner table that contains customer information for every customer represented in the chart. Obviously you do not want to display all of these at once, so create a new style in the style editor with only one property overridden – Text Block:Display set to no Display. Apply this new style to both the inner and outer tables.



    If you run the report after completing this step all the tables will be generated in the output but none will be displayed. This is different than using the visibility property which will not put the tables in the output. Enter the following bookmarks for the outer and inner tables.

    Outer table bookmark expression: "myoutertable";
    Inner table bookmark expression: "mytable"+row["CUSTOMERNUMBER"]

    This will assign the outer table id to myoutertable and a unique id for each inner table starting with mytable and the customer number for the given inner table appended to it. We can now use these with some client script to turn them on or off.

    Add a Text element below the two tables with the following value.


    <script>
    function clearSel(){
    var ot=document.getElementById("myoutertable");
    ot.style.display="none";
    var intbls=ot.getElementsByTagName("TABLE");
    for( jj=0; jj<intbls.length; jj++ ){
    intbls[jj].style.display = "none";
    }
    }
    function DisplayCustomer(cat) {
    clearSel();
    //alert(cat);
    document.getElementById("myoutertable").style.display="block"
    document.getElementById("mytable"+cat).style.display="block"

    }
    </script>



    Make sure to set the Text type to HTML.

    The clearSel function first finds the outer table and then locates every nested table and sets the display style for each to none. The outer table’s display style is also set to none. This will effectively hide both tables.

    The DisplayCustomer function first clears all current displayed tables. Next it uses a category passed in from the Chart to find the specific customer inner table and sets its display style to block. It also sets the outer table to be visible.

    To link these two functions to the Chart use the Interactivity button on the Value (Y) Series as shown in the picture below. Note that we use a mouse over event to call the DisplayCustomer function and a mouse click function to clear the tables.



    This produces the following output.



    As the mouse is moved over the different tubes the table below the chart will update. To clear the tables click on one of the tubes. You will notice that we passed categoryData to the DisplayCustomer JavaScript function. This is a predefined variable available to the invokeScript function that contains the category value for a specific datapoint. The example report lists the predefined variables and in what action they are available using label elements at the top of the report. These labels are hidden at run time.

    The example is available at BIRT Exchange.

    Tuesday, December 01, 2009

    BIRT Designer Classpath Changes

    Starting with the 2.5.2 branch of BIRT (Release date February 2010), configuring the classpath for the designer has been improved. This classpath setting is used by the BIRT engine when processing reports that use Java event handlers or make calls to external classes while in the designer. In previous releases of BIRT, the engine would add all Java projects within the same workspace to the classpath automatically. With BIRT 2.5.2 you will now be able configure the BIRT classpath globally or project specific. This setting is available in the preferences.



    In this example I have configured a project (BIRT Reports) to add the BirtEventHandlers Java project to the classpath that the BIRT engine uses when previewing the reports in the designer. Note these changes only affect the designer and you should deploy your event handler classes to the scriptlib directory in the deployed environment. More details are available in the bugzilla entry.

    Friday, November 20, 2009

    Accessing Spring Beans from the BIRT Designer

    Recently I have described methods that can be used to access Spring Beans from the BIRT Engine. These examples are intended to be illustrative and not comprehensive.

    More on BIRT and Spring

    Calling Spring Objects from BIRT Expressions and Event Handlers

    In both of these examples I used the BIRT engine to retrieve Spring objects within the scripting environment. In this post I am supplying an example that illustrates how to implement your own menu in the expression builder, so Spring objects can be called within the BIRT Designer. This will allow you to test your report prior to deployment.

    In this post:
    BIRT 2.3.1 - Adding Functions to the Expression Builder
    I described how to implement the org.eclipse.birt.core.ScriptFunctionService. The attached example is an implementation of this extension point. When using this example the expression builder will appear as follows.



    Notice that there is now a SpringFunctions sub-category, which provides one method callBean. This method takes a bean name and a method name and expects a returned string. To keep the example simple no arguments are supported and the method must return a string. The plugin.xml looks like the following:



    Provided with the example is a deployable J2EE application and the plugin that implements the ScriptFunctionService. See the readme for details on setting up the example. Also note that the deployable plugin is also attached in the example. You will need to add this to your designer and your runtime. This example works with both the Open Source BIRT designer and the Actuate BIRT designer.

    Also included is an example report with the following output.


    This example is available at Birt-Exchange.

    Friday, November 13, 2009

    It's Movember

    Movember, that time of year when the days grow shorter, the weather grows colder and the family gathers today for the holidays.  Of course in my family the kids are looking at me and laughing, and I am getting 'that' look from Gretchen my wife.

    Yes I am growing in a mustache in an effort to change the face of men's health.  To quote my team lead (I am a part of Team Fat Cyclist).

    "Movember is the month formerly known as November. During this month, men — manly men — grow mos as a way to call attention to themselves (hey, I’m just being honest here). 
    Then, when people ask you “Why are you growing a moustache?” — and they will ask you this question — you tell them about the cancers affecting men, and ask them to donate to your Mo donation page (the money will be channeled to the Prostate Cancer Foundation and to LiveStrong). 
    So really, a mo is kinda like wearing a pink ribbon for breast cancer awareness. But a lot more personal. And harder to remove. And it’s displayed a lot higher."
    -The Fat Cyclist (Elden Nelson)

    If you are interested in supporting me and helping to raise money for men's health issues, please visit my mo page and donate.

    If not me, please visit another great team, the Eclipse Momitters.

    Thursday, November 12, 2009

    More on BIRT and Spring

    In my previous post I discussed calling Spring objects within a BIRT report. That example used an architecture similar to the simplified diagram below.



    The Spring Context was injected in the BIRT app context and this gave the BIRT scripting environment access to the Spring Beans. This example could have been expanded further to the Open Source BIRT Viewer by adding a similar object to the Application Context for the BIRT Viewer. See this wiki page for more details. If you use the method described in the wiki page, you may prefer to implement this with a Spring RequestContextFilter.

    The simplified architecture for this setup is presented below.



    Another option is to use Spring Remoting and the Open Source BIRT Viewer tag libraries or standard URL integration. In this case you add the Spring jars to the WEB-INF/lib of the viewer and write a wrapper class for the Spring remote client. The wrapper class and Spring configuration file can then be added to the WEB-INF/lib directory of the Viewer to access your remote beans. The architecture for this setup would be as follows. Attached to this post is an example of this method.



    If you are using the Actuate Java Components technology, you can use the JSAPI within a standard HTML page or generate it in a Spring View which will include the BIRT report. This approach allows Flash charts to be populated by Spring Beans and report interactivity like dynamically grouping, sorting and filtering all without re-executing the report.



    The example, that is attached to this post, is a very simple Spring Remoting example. It contains two ANT projects, one for the server application and one that acts as the remote client wrapper. Once you download the example, build the server application and deploy it. Instructions are in a readme file. Then build the client wrapper jar and deploy it as described in the readme. The readme illustrates deploying to the Open Source BIRT Viewer or the Actuate Interactive Viewer. The example uses Spring HTTP Remoting, but any of the Spring Remoting technologies should work. An example report is provided for both deployed environments. Whether you are using the Open Source BIRT Report designer or the Actuate BIRT Designer, the expression to access the Spring Bean will be similar to the following.



    Output for the example is as follows.

    Open Source Viewer


    Actuate Interactive Viewer


    The Example is available at BIRT Exchange.

    Thursday, November 05, 2009

    Calling Spring Objects from BIRT Expressions and Event Handlers

    Several examples are already available on the web, which demonstrate calling the BIRT engine from a Spring MVC application.

    Integrating BIRT with Spring in a Web application
    and
    Eclipse BIRT in Spring web applications
    are a couple of examples.

    There is not much information on how to include Spring Beans within a report design. This post details an example of injecting the Spring ApplicationContext into BIRT’s AppContext object which will allow you to call your Spring Beans in BIRT expressions or event handlers. A link for the source is listed at the bottom. A readme file containing instructions for building the example is included in the download.

    You can include the BIRT runtime by following the post above or examining the download. The Example implements a Spring Controller with the following code.


    package org.eclipse.birt.spring;

    import javax.servlet.ServletContext;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;

    import org.eclipse.birt.report.engine.api.HTMLRenderOption;
    import org.eclipse.birt.report.engine.api.HTMLServerImageHandler;
    import org.eclipse.birt.report.engine.api.IReportEngine;
    import org.eclipse.birt.report.engine.api.IReportRunnable;
    import org.eclipse.birt.report.engine.api.IRunAndRenderTask;
    import org.springframework.web.servlet.ModelAndView;
    import org.springframework.web.servlet.mvc.AbstractController;

    public class BirtController extends AbstractController {

    private IReportEngine reportEngine;

    protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception {

    String reportName = request.getParameter("ReportName");
    ServletContext sc = request.getSession().getServletContext();
    reportEngine = BirtEngine.getBirtEngine(sc);
    IReportRunnable runnable = null;
    runnable = reportEngine.openReportDesign( sc.getRealPath("/Reports")+"/"+reportName );
    IRunAndRenderTask runAndRenderTask = reportEngine.createRunAndRenderTask(runnable);

    HTMLRenderOption options = new HTMLRenderOption();
    options.setOutputFormat("html");
    options.setOutputStream(response.getOutputStream());
    options.setImageHandler(new HTMLServerImageHandler());
    options.setBaseImageURL(request.getContextPath()+"/images");
    options.setImageDirectory(sc.getRealPath("/images"));
    runAndRenderTask.setRenderOption(options);
    runAndRenderTask.run();

    return null;
    }

    }


    Note that no error checking is implemented in this example. This controller class just extends the Spring AbstractController class passes the response object to the BIRT engine to output the report. The report name is retrieved from the request. Before running the report the BirtEngine class is used to retrieve the BIRT engine. This is virtually the same BirtEngine class used in the servlet example available in the BIRT wiki with some exceptions that are noted here.


    package org.eclipse.birt.spring;

    import java.io.InputStream;
    import java.io.IOException;
    import java.util.Properties;
    import java.util.logging.Level;

    import org.eclipse.birt.report.engine.api.EngineConfig;
    import org.eclipse.birt.report.engine.api.IReportEngine;
    import javax.servlet.*;
    import org.eclipse.birt.core.framework.PlatformServletContext;
    import org.eclipse.birt.core.framework.IPlatformContext;
    import org.eclipse.birt.core.framework.Platform;
    import org.eclipse.birt.core.exception.BirtException;
    import org.eclipse.birt.report.engine.api.IReportEngineFactory;
    import org.springframework.context.ApplicationContext;

    public class BirtEngine {

    private static IReportEngine birtEngine = null;

    private static Properties configProps = new Properties();

    private final static String configFile = "BirtConfig.properties";

    public static synchronized void initBirtConfig() {
    loadEngineProps();
    }
    public static synchronized IReportEngine getBirtEngine(ServletContext sc) {
    if (birtEngine == null)
    {
    //optionally load engine props
    //loadEngineProps();
    EngineConfig config = new EngineConfig();
    if( configProps != null){
    String logLevel = configProps.getProperty("logLevel");
    Level level = Level.OFF;
    if ("SEVERE".equalsIgnoreCase(logLevel))
    {
    level = Level.SEVERE;
    } else if ("WARNING".equalsIgnoreCase(logLevel))
    {
    level = Level.WARNING;
    } else if ("INFO".equalsIgnoreCase(logLevel))
    {
    level = Level.INFO;
    } else if ("CONFIG".equalsIgnoreCase(logLevel))
    {
    level = Level.CONFIG;
    } else if ("FINE".equalsIgnoreCase(logLevel))
    {
    level = Level.FINE;
    } else if ("FINER".equalsIgnoreCase(logLevel))
    {
    level = Level.FINER;
    } else if ("FINEST".equalsIgnoreCase(logLevel))
    {
    level = Level.FINEST;
    } else if ("OFF".equalsIgnoreCase(logLevel))
    {
    level = Level.OFF;
    }

    config.setLogConfig(configProps.getProperty("logDirectory"), level);
    }
    config.setEngineHome("");

    IPlatformContext context = new PlatformServletContext( sc );
    config.setPlatformContext( context );
    config.getAppContext().put("PARENT_CLASSLOADER", BirtEngine.class.getClassLoader());
    ApplicationContext sprCtx = ContextAccess.getApplicationContext();
    config.getAppContext().put("spring",sprCtx);
    try
    {
    Platform.startup( config );
    }
    catch ( BirtException e )
    {
    e.printStackTrace( );
    }

    IReportEngineFactory factory = (IReportEngineFactory) Platform
    .createFactoryObject( IReportEngineFactory.EXTENSION_REPORT_ENGINE_FACTORY );
    birtEngine = factory.createReportEngine( config );
    }
    return birtEngine;
    }
    public static synchronized void destroyBirtEngine() {
    if (birtEngine == null) {
    return;
    }
    birtEngine.destroy();
    Platform.shutdown();
    birtEngine = null;
    }
    public Object clone() throws CloneNotSupportedException {
    throw new CloneNotSupportedException();
    }
    private static void loadEngineProps() {
    try {
    //Config File must be in classpath
    ClassLoader cl = Thread.currentThread ().getContextClassLoader();
    InputStream in = null;
    in = cl.getResourceAsStream (configFile);
    configProps.load(in);
    in.close();
    } catch (IOException e) {
    e.printStackTrace();
    }
    }
    }

    This class just wraps access to the BIRT engine in a singleton.
    The notable changes are in these lines of code deal with the EngineConfig class.

    config.setEngineHome("");

    IPlatformContext context = new PlatformServletContext( sc );
    config.setPlatformContext( context );
    config.getAppContext().put("PARENT_CLASSLOADER", BirtEngine.class.getClassLoader());
    ApplicationContext sprCtx = ContextAccess.getApplicationContext();
    config.getAppContext().put("spring",sprCtx);


    The setEngineHome is passed a blank value and setting the PlatformContext to use a PlatformServletContext class will by default look for the BIRT plugins in the WEB-INF/Platform/plugins directory. Next we set the parent classloader for the report engine plugin so that classes available to the project will also be available to the BIRT engine. The final line gets the Spring ApplicationContext instance and loads it into the BIRT AppContext object and names it “spring”. The method used for retrieving the Spring context is described in a great post here.

    The ContextAccess class is defined as follows.


    package org.eclipse.birt.spring;
    import org.springframework.context.ApplicationContext;
    public class ContextAccess {
    private static ApplicationContext ctx;
    public static void setApplicationContext(ApplicationContext applicationContext) {
    ctx = applicationContext;
    }
    public static ApplicationContext getApplicationContext() {
    return ctx;
    }
    }


    This Class just contains methods to set and get the static variable ctx, which will contain the Spring ApplicationContext with the addition of one more class.



    package org.eclipse.birt.spring;

    import org.springframework.beans.BeansException;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.ApplicationContextAware;


    public class SpringContextProvider implements ApplicationContextAware {
    public void setApplicationContext(ApplicationContext ctx) throws BeansException {
    ContextAccess.setApplicationContext(ctx);
    }
    }



    This Class implements the ApplicationContextAware interface which causes the Spring framework to callback this class with the Spring context, when it is created. The Spring configuration file for this example looks like the following.

    <beans>
    <bean id="springContextProvider" class="org.eclipse.birt.spring.SpringContextProvider">
    </bean>
    <bean id="urlMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
    <property name="mappings">
    <props>
    <prop key="/runbirt.htm">birtController</prop>
    </props>
    </property>
    </bean>
    <bean id="birtEngine"
    class="org.eclipse.birt.spring.BirtEngine"
    destroy-method="destroyBirtEngine">
    </bean>
    <bean id="birtController"
    class="org.eclipse.birt.spring.BirtController">
    </bean>
    <bean id="carPojo"
    class="org.eclipse.birt.spring.CarPojo">
    </bean>

    </beans>

    We have a carPojo bean that is available. To access this from a BIRT expression, all we have to do is use the following syntax.


    var mypojo = spring.getBean("carPojo");


    You can then call the methods on the object. Eg mypojo.getYear();



    Output for the example is presented below.




    Caveats
    If you preview the report in the designer the report will not work. This is because the Spring context is not available to the designer. The report has to be deployed to test it. There are many ways this example could be extended to circumvent this issue. BIRT provides an extension point to enhance the expression builder which could be used with Spring Remoting to access the Spring context. BIRT also provides an extension to implement a BIRT application context object within the designer that could be used in this same fashion. I will try to implement one of these methods in the future to illustrate this concept.

    The example can be downloaded from Birt-Exchange.

    Tuesday, October 20, 2009

    Multiple Hyperlinks on BIRT Charts

    BIRT 2.5.1 was released a couple of weeks ago and with its release BIRT Charts now support multiple hyperlinks. To use this feature select any of the interactivity locations within the chart builder. Next select the add button under the hyperlinks list box.



    This will launch the hyperlink editor.



    Enter the name you wish to appear in the context menu. Next select the edit base URL button, which will display the Hyperlink Options editor.



    Here you have the option to link to a URI, a bookmark within the same report or to a bookmark in another report. Values from the current chart can be passed through to the target. For example the value of a slice could be passed to a google query. To do this select the URI radial and enter an expression similar to the one shown in the image below.



    When running the report containing the chart, the multiple hyperlinks menu will popup on whatever event you defined in the editor. In the above example this was defined on a mouse click event. So clicking on a slice of the pie will cause the multiple hyperlinks context menu to appear.



    This sample report is available at Birt-Exchange.

    Monday, September 28, 2009

    Calling BIRT reports from Wicket using Actuate’s JSAPI

    I have written several posts on how to use the Actuate JSAPI to integrate with various frameworks. In this post I will detail integrating with Wicket. For more information on the JSAPI see this post:

    Showing BIRT Reports using the Actuate JSAPI

    Wicket can call BIRT reports with the JSAPI in a similar fashion as other front end frameworks that allow AJAX based APIs, where JavaScript is just embedded into the HTML. An HTML file based on the Hello World Wicket example may look similar to the following.

    <html>
    <head>
    <title>Actuate JSAPI Wicket Example</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Viewer creation example</title>
    </head>
    <body>
    <b>
    <span wicket:id="message">message will be here</span>
    </b>

    <div id="acviewer" />

    <div id="jsapi_example_container"><script type="text/javascript"
    src="http://localhost:8080/ActuateJavaComponent/jsapi"></script> <script
    type="text/javascript" language="JavaScript">

    actuate.load("viewer");
    actuate.initialize("http://localhost:8080/ActuateJavaComponent/",
    null,
    null,
    null,
    initViewer);
    var viewer;
    function initViewer()
    {
    viewer = new actuate.Viewer("acviewer");
    var viewerwidth = 800;
    var viewerheight = 620;
    viewer.setWidth(viewerwidth);
    viewer.setHeight(viewerheight);
    run();
    }
    Using a Wicket Behavior to write out this code
    function run()
    {
    viewer.setParameters({"Customer":"CAF Imports"});
    viewer.setReportName("/Public/BIRT and BIRT Report Studio Examples/Customer Order History.rptdesign");
    viewer.submit();
    }

    </script></div>
    </body>
    </html>

    This example will look very familiar to other examples in the JSAPI overview post. The only real difference is the addition of the wicket message, which is generated by my instance of the Wicket Web Page class. You will notice that the run JavaScript function is hard coding the report name and parameters. This may be something you want to handle in the Java code, to make it more dynamic. One way of handling this is to use Wicket Behaviors to create custom components. You could use a Behavior to write out all the above JavaScript, but we will keep it simple and just write out the run script. So the first thing to do is to create a class that extends the AbstractBehavior class.


    package jsapi.wicket.sample;

    import org.apache.wicket.Component;
    import org.apache.wicket.Response;
    import org.apache.wicket.behavior.AbstractBehavior;
    import org.apache.wicket.util.string.JavascriptUtils;

    public class ReportComponent extends AbstractBehavior{

    private static final long serialVersionUID = 1L;

    public void onRendered(Component component) {
    Response response = component.getResponse();
    response.write(JavascriptUtils.SCRIPT_OPEN_TAG);
    response.write("function run(){");
    response.write("viewer.setParameters({\"Customer\":\"CAF Imports\"});");
    response.write("viewer.setReportName(\"/Public/BIRT and BIRT Report Studio Examples/Customer Order History.rptdesign\");");
    response.write("viewer.submit();}");
    response.write(JavascriptUtils.SCRIPT_CLOSE_TAG);
    }

    }


    This class overides the onRendered method to write out the entire run script from the HTML above. I can now modify my extended WebPage class to add an instance of the ReportComponent class.


    package jsapi.wicket.sample;

    import org.apache.wicket.PageParameters;
    import org.apache.wicket.markup.html.WebPage;
    import org.apache.wicket.markup.html.basic.Label;

    public class ReportParameter extends WebPage {

    private static final long serialVersionUID = 1L;

    public ReportParameter(final PageParameters parameters) {

    // Add the simplest type of label
    Label myLabel = new Label("message", "Actuate JSAPI Wicket Example");
    add(myLabel);
    ReportComponent rc = new ReportComponent();
    myLabel.add(rc);
    }
    }


    The script is written below the Label component.
    Finally the HTML should have the run JavaScript function commented out. The output from this example is as follows.


    The generated HTML looks like:



    Note that you can use the built in view time functions like selecting new parameters and rerunning the report without any additional code.



    The example files can be downloaded from Birt-Exchange.

    Thursday, September 03, 2009

    Calling BIRT reports from Flex using Actuate’s JSAPI

    Continuing on a series of posts I have written around Acuate’s JSAPI, this post details integrating BIRT reports with Flex. Previous posts detailed:

    Showing BIRT Reports using the Actuate JSAPI
    See this link for more details on what is available in the JSAPI.

    Calling BIRT Reports from PHP using Actuate’s JSAPI

    Calling BIRT Reports from ASP.NET using Actuate’s JSAPI

    BIRT Reports can contain Flash components using the Text element. Actuate has also extended the BIRT report designer to include Flash charts. As stated in previous post the JSAPI can be used to include BIRT content in just about any front end. BIRT currently does not support a Flash emitter, but a Flex component can call the JSAPI to include and modify BIRT content within the same HTML page. This post details how this can be achieved.

    The Flex SDK provides a Flex Ajax bridge that allows a Flex application to call and interact with Ajax based APIs. To illustrate how this can be used with Actuate’s JSAPI, I am going to build a Flex application that list a couple of reports. The application will also contain a button to execute the report. The output for the report will be written to another DIV element within the HTML wrapper.

    The mxml file is pretty simple and contains a DataGrid that calls an action script to load the names and paths of two reports.


    <?xml version="1.0" encoding="utf-8"?>
    <mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
    width="640" height="300" layout="vertical" backgroundColor="white">
    <fab:FABridge xmlns:fab="bridge.*" />

    <mx:Panel id="pnlMain" x="10" y="10" width="560" height="260"
    layout="absolute" title="BIRT Reports From Flex">
    <mx:DataGrid id="dgReports" x="10" y="10" initialize="initReports()" width="530" height="130">
    <mx:columns>
    <mx:DataGridColumn headerText="Report Name" width="160" dataField="name"/>
    <mx:DataGridColumn headerText="Report Path" dataField="path"/>
    </mx:columns>
    </mx:DataGrid>
    <mx:Button x="10" y="156" label="Run Report From Flex" id="flashButton" />
    </mx:Panel>
    <mx:Script>
    <![CDATA[
    import mx.collections.ArrayCollection;

    public function initReports():void
    {
    var reports:Array = new Array();
    reports.push({name: "Monthly Revenue Analysis", path: "/Public/BIRT and BIRT Report Studio Examples/Monthly Revenue Analysis.rptdesign"});
    reports.push({name: "Sales by Territory", path: "/Public/BIRT and BIRT Report Studio Examples/Sales by Territory.rptdesign"});
    var reportCollection:ArrayCollection = new ArrayCollection(reports);
    dgReports.dataProvider = reportCollection;
    dgReports.selectedIndex = 0;
    }
    ]]>
    </mx:Script>
    </mx:Application>


    The fab:FABridge xmlns:fab=”bridge.*” tag includes the Flex Ajax bridge library. In this example I just added a bridge directory to my application, which contained the FABridge.as and FABridge.js files that handle the bridge. These files are available in the Flex SDK. This application also contains a button (flashButton) that will be used by JavaScript later in the example.

    My wrapper HTML file contains a div element to display the application which is named BirtFlex. The flashvars parameter is used to set a unique bridge name for this application. The bridge name is used by JavaScript to get/set values within the Flex application.


    <div id="fply">
    <script language='javascript' charset='utf-8'>
    document.write("<object id='flexApp' classid='clsid:D27CDB6E-AE6D-11cf-96B8-444553540000' codebase='http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=8,5,0,0' type='application/x-shockwave-flash' height='300' width='640'>");
    document.write("<param name='flashvars' value='bridgeName=birtexample'/>");
    document.write("<param name='src' value='BirtFlex.swf'/>");
    document.write("<embed name='BirtFlexDemo' pluginspage='http://www.macromedia.com/go/getflashplayer' src='BirtFlex.swf' height='300' width='640' flashvars='bridgeName=birtexample'/>");
    document.write("</object>");
    </script>
    </div>


    For brevity, I have foregone error checking and player checks.
    To use the JSAPI the following code is added to the wrapper HTML file.


    <div id="jsapi_example_container">
    <script type="text/javascript" src="http://localhost:8080/ActuateJavaComponent/jsapi"></script>
    <script type="text/javascript" language="JavaScript">

    actuate.load("viewer");
    actuate.initialize("http://localhost:8080/ActuateJavaComponent/",
    null,
    null,
    null,
    initViewer);
    var viewer;
    function initViewer()
    {
    document.getElementById("run").disabled = true;
    viewer = new actuate.Viewer("acviewer");
    var viewerwidth = 500;
    var viewerheight = 620;
    viewer.setWidth(viewerwidth);
    viewer.setHeight(viewerheight);
    setupFlashCallBack();
    }
    function run()
    {
    var flexApp = FABridge.birtexample.root();
    var appValue = flexApp.getDgReports().getSelectedItem().path;
    viewer.setReportName(appValue);
    viewer.submit();
    }
    function setupFlashCallBack()
    {
    var initCallback = function()
    {
    //alert("Enabling");
    document.getElementById("run").disabled = false;
    }
    var btnCallback = function()
    {
    var flexApp = FABridge.birtexample.root();
    flexApp.getFlashButton().addEventListener("click",flashButtonClicked);
    }
    FABridge.addInitializationCallback("birtexample",initCallback);
    FABridge.addInitializationCallback("birtexample",btnCallback);
    }
    function flashButtonClicked(event)
    {
    run();
    }
    </script>
    </div>



    This code is very similar to previous examples with a few notable exceptions. When the JSAPI is initialized the call back function initViewer is set.


    actuate.initialize("http://localhost:8080/ActuateJavaComponent/",
    null,
    null,
    null,
    initViewer);



    Once the API is initialized, the initViewer JavaScript function is called.


    function initViewer()
    {
    document.getElementById("run").disabled = true;
    viewer = new actuate.Viewer("acviewer");
    var viewerwidth = 500;
    var viewerheight = 620;
    viewer.setWidth(viewerwidth);
    viewer.setHeight(viewerheight);
    setupFlashCallBack();
    }



    In addition to a button in the Flex application, the HTML wrapper also contains one to run the report. Not that two buttons are needed, but for illustrative purposes, this example contains two. In this example we disable the button until both the JSAPI and the Flex component are initialized. The Viewer JSAPI component is created as usual and then the setupFlashCallBack() function is called.


    function setupFlashCallBack()
    {
    var initCallback = function()
    {
    document.getElementById("run").disabled = false;
    }
    var btnCallback = function()
    {
    var flexApp = FABridge.birtexample.root();
    flexApp.getFlashButton().addEventListener("click",flashButtonClicked);
    }
    FABridge.addInitializationCallback("birtexample",initCallback);
    FABridge.addInitializationCallback("birtexample",btnCallback);
    }



    In this function two call back functions are registered. The first one (initCallback) is setup to let us know when the Flex application is loaded, which then enables the run button. The second one (btnCallback) registers an event handler that the bridge will call when the Flex button is pressed. This event handler JavaScript function simply calls the run JavaScript function.


    function flashButtonClicked(event)
    {
    run();
    }



    The JavaScript button has the run function setup on the onclick event.

    <input type="button" id="run" value="Run Report From JavaScript" onclick="run()" >

    So the report can be run from either button. The run function sets the report name and executes the API to run the report.

    function run()
    {
    var flexApp = FABridge.birtexample.root();
    var appValue = flexApp.getDgReports().getSelectedItem().path;
    viewer.setReportName(appValue);
    viewer.submit();
    }



    The report path is retrieved using the getDgReports().getSelectedItem().path command. The DataGrid in the mxml file is named dgReports

    <mx:DataGrid id="dgReports" x="10" y="10" initialize="initReports()" width="530" height="130">


    The bridge provides getter and setter methods for the items in the Flex application. The DataGrid contains two columns (name and path). The path contains the location of the report, so this is the field that is retrieved from the Flex application. See the Flex developers guide for more details on the bridge calls.



    This example can be downloaded at Birt-Exchange.

    Wednesday, August 26, 2009

    Using Script to Modify a BIRT Chart

    When designing BIRT reports that contain charts, the developer has many ways a chart can be customized. One way is to implement a script event handler either in JavaScript or Java. The chart engine currently supports over thirty hooks for implementing an event handler. These events are fired during the generation and rendering of the chart. Many items can be customized. For example, the data for a chart can be changed in the afterDatasetFilled script, or the entire chart model can be changed in the beforeGeneration script.


    In addition to changing the chart using the chart scripting hooks, the chart model can also be changed using the primary BIRT report script hooks. When making changes to the report, most developers will use the beforeFactory event script. This event is fired prior to running the report and offers a location to make simple or complex changes to the report. This includes modifying any charts that exist within the report. Simple chart properties can be changed using the Chart Simple API. An example of this approach is described here.

    For more complex changes, the Chart API can be called within the script. As an example, changing a specific chart series type is possible. BIRT 2.5 provides study charts which allow multiple Y-axes to be stacked, each with a different series type. Turning this feature on or off can also be done using script. These two changes can be done with the following script:

    First import the API packages that will be needed.

    importPackage(Packages.org.eclipse.birt.chart.model.data.impl);
    importPackage(Packages.org.eclipse.birt.chart.model.type.impl);
    importPackage(Packages.org.eclipse.birt.chart.model.attribute.impl);


    Retrieve the report design handle and find the chart. Note that the chart needs to be named. This can be done in the general properties for the chart.

    //Chart must be named
    cht = reportContext.getDesignHandle().findElement("MyChart");
    //get the chart model;
    mychart = cht.getReportItem().getProperty( "chart.instance" );



    Next retrieve the axes. In this example the chart has two Y-Axes. The second one contains the series to be modified.

    xAxis =mychart.getAxes().get(0);
    yAxis2 = xAxis.getAssociatedAxes().get(1);

    Clear all series definitions defined for the second Y-axis.

    yAxis2.getSeriesDefinitions().clear();

    The example report contains a combo parameter that allows the user to select the series type for the second Y-Axis. The parameter is checked and an appropriate series is created. Note that if an area series is created, the background is set to translucent.

    if( params["SeriesType"].value == "Line"){
    var ns = LineSeriesImpl.create();
    }else if( params["SeriesType"].value == "Bar"){
    var ns = BarSeriesImpl.create();
    }else if( params["SeriesType"].value == "Area"){
    var ns = AreaSeriesImpl.create();
    ns.setTranslucent(true);
    ns.getLineAttributes().setColor(ColorDefinitionImpl.TRANSPARENT())


    }

    Create a new series definition and set the appropriate query and series type.

    var sdNew = SeriesDefinitionImpl.create();
    sdNew.getSeriesPalette( ).shift( 0 );
    ns.getLabel().setVisible(true);
    var qry = QueryImpl.create("row[\"QUANTITYORDERED\"]" );
    ns.getDataDefinition().add(qry)
    sdNew.getSeries().add( ns );

    Assign the new series definition to the second Y-Axis.

    yAxis2.getSeriesDefinitions().add( sdNew );

    The example report also has a Boolean parameter that determines if the chart should be displayed as a study chart.


    if( params["StudyChart"].value == true ){
    mychart.setStudyLayout(true);
    }else{
    mychart.setStudyLayout(false);
    }

    Some of the output options for this example report are displayed below.


    This report is available at Birt-Exchange.

    Thursday, August 13, 2009

    Calling BIRT reports from PHP using Actuate’s JSAPI

    I have written a couple of post on using Actuate’s JSAPI to include BIRT content within your system. This API is AJAX based and allows content to be embedded within most web applications.

    These post can read here:
    Calling BIRT Reports from ASP.NET
    and
    Actuate's JSAPI

    In addition take a look at calling the BIRT Engine from PHP, which I blogged about a while back.

    Calling the JSAPI from PHP is not much different than any other front end. For example, including the interactive viewer can be done using the following code.



    <?php
    $JSAPI_Location='http://localhost:8080/ActuateJavaComponent/jsapi';
    $ReportFile='/Public/BIRT and BIRT Report Studio Examples/Customer Dashboard.rptdesign';
    ?>
    <HTML>
    <head>
    </head>
    <body>

    <div id="jsapi_example_container">
    <script type="text/javascript" src="<?php echo $JSAPI_Location ?>"></script>
    <script type="text/javascript" language="JavaScript">
    <!-- Load the viewer component of the Actuate object -->
    actuate.load("viewer");
    actuate.initialize("<?php echo substr($JSAPI_Location, 0, -5) ?>",
    null,
    null,
    null,
    initViewer);
    var viewer;
    function initViewer()
    {
    viewer = new actuate.Viewer("acviewer");
    <!-- register an event handler to catch exceptions -->
    viewer.registerEventHandler(actuate.viewer.EventConstants.ON_EXCEPTION, errorHandler);
    run();
    }
    function run()
    {

    viewer.setReportName("<?php echo $ReportFile ?>");
    viewer.submit();
    }
    function errorHandler(viewInstance, exception)
    {
    alert(exception.getMessage());
    }
    </script>
    <div id="acviewer"></div>
    </div>
    </body>
    </HTML>


    In this example I am setting the JSAPI location and report file to run using a couple of PHP variables. The JSAPI is initialized and the callback function initViewer is called which creates the viewer within the acviewer div element. The initViewer function then calls the run method which sets the report to run and executes it. The report is then displayed within the acviewer div element.



    If the report has parameters you can use the parameter component of the JSAPI or set them using PHP variables.

    Change the code above to add the parameter values.

    <?PHP
    $JSAPI_Location='http://localhost:8080/ActuateJavaComponent/jsapi';
    $ReportFile='/Public/BIRT and BIRT Report Studio Examples/Sales By Territory.rptdesign';
    $reportTerritory='NA';
    $reportYear=2004;
    ?>

    Next, within the run method set the parameters using code similar to the following.

    function run()
    {
    viewer.setParameters(
    {
    "Territory": "<?php echo $reportTerritory ?>",
    "currentYear": <?php echo $reportYear ?>
    }
    );
    viewer.setReportName("<?php echo $ReportFile ?>");
    viewer.submit();
    }

    To use the parameter component use code similar to the following.

    <html>
    <body>
    <?php
    $JSAPI_Location='http://localhost:8080/ActuateJavaComponent/jsapi';
    $ReportFile='/Public/BIRT and BIRT Report Studio Examples/DateTest.rptdesign';
    ?>


    <div id="jsapi_example_container">
    <script type="text/javascript" src="<?php echo $JSAPI_Location ?>"></script>
    <script type="text/javascript" language="JavaScript">
    var paramObj;
    var viewer;
    actuate.load("viewer");
    actuate.initialize("<?php echo substr($JSAPI_Location, 0, -5) ?>",
    null,
    null,
    null,
    init);

    function init()
    {
    <!-- parameter -->
    paramObj = new actuate.Parameter("parampane");
    <!-- register an event handler to catch exceptions -->
    paramObj.registerEventHandler(actuate.parameter.EventConstants.ON_EXCEPTION, parameterErrorHandler);
    <!-- viewer -->
    viewer = new actuate.Viewer("viewerpane");
    var viewerwidth = 800;
    var viewerheight = 600;
    <!-- register an event handler to catch exceptions -->
    viewer.registerEventHandler(actuate.viewer.EventConstants.ON_EXCEPTION, errorHandler);
    viewer.setWidth(viewerwidth);
    viewer.setHeight(viewerheight);
    runParam();
    }

    function runParam()
    {
    paramObj.setReportName("<?php echo $ReportFile ?>");
    paramObj.submit(function () {document.getElementById("run").style.visibility = 'visible';});
    }

    function run()
    {
    paramObj.downloadParameterValues(runNext);
    }

    function runNext( pvs )
    {
    viewer.setParameterValues(pvs);
    viewer.setReportName("<?php echo $ReportFile ?>");
    viewer.submit();
    }

    function errorHandler(viewInstance, exception)
    {
    alert(exception.getMessage());
    }

    function parameterErrorHandler(exception)
    {
    alert(exception.getMessage());
    }

    </script>
    <table border=1>
    <tr>
    <td><div id="parampane"></div></td>
    <td><input type="button" class="btn" id="run" value="Run Report" onclick="run()" style="visibility: hidden"></td>
    </tr>
    </table>
    <div id="viewerpane"></div>
    </div>
    </body>
    </html>


    The run function downloads the parameters that were set using the parameter component. Next the setParameterValues method is used to set the parameter values for the run of the report. Notice this example has a date parameter, and the JSAPI provides a date picker for this type of parameter.





    If you are already using some PHP class to implement a date picker, such as the one available at the Zebra PHP Components Framework blog, you can modify your code to retrieve the date from the id of the element the date is stored in. The above example would have the following modifications:

    <?php
    // include the class
    require "class.datepicker.php";
    // instantiate the object
    $dp=new datepicker();
    $dp->dateFormat="m/d/Y";

    $JSAPI_Location='http://localhost:8080/ActuateJavaComponent/jsapi';
    $ReportFile='/Public/BIRT and BIRT Report Studio Examples/DateTest.rptdesign';
    ?>

    function runNext( pvs )
    {
    //viewer.setParameterValues(pvs);
    viewer.setParameters({"currentYear":2003, "currentMonth":12, "DateParameter":document.getElementById("date").value});

    viewer.setReportName("");
    viewer.submit();
    }


    <table border=1>
    <tr>
    <td><input type="text" id="date"></td>
    <td><input type="button" value="Select Date" onclick="<?=$dp->show("date")?>"></td>
    </tr>

    <tr>
    <td><div id="parampane"></div></td>
    <td><input type="button" class="btn" id="run" value="Run Report" onclick="run()" style="visibility: hidden"></td>
    </tr>
    </table>
    <div id="viewerpane"></div>



    Thursday, July 09, 2009

    The Heavy Hand

    Today I received a foundation email with this little nugget in it:

    Pursuant to the Eclipse Foundation Bylaws, the Eclipse Foundation recently amended the Eclipse Foundation Bylaws and Membership Agreement. As required by the Eclipse Foundation Membership Agreement, we require your formal acceptance of these changes which became effective July 23, 2008 and are more fully described at: http://www.eclipse.org/membership/vote2008/.

    Formal acceptance is required by virtue of the Membership Agreement. It should be noted that these changes are not optional and apply to all Eclipse Members upon Membership renewal from July 23, 2008 onwards.  (emphasis added)
    Now I have no problem with the changes to the by laws.  In fact, they are great for my small company.  What I don't understand is why I need to agree in writing to these non-optional requirements?

    If the decision has all ready been made, why do I need to agree in writing?  Maybe it is just me, but it feels heavy handed (and pointless) to insist that I validate a decision after it has all ready been made.

    After all, if I don't like the decision I can just choose not to renew.

    Friday, June 26, 2009

    Passing JDBC ResultSet to a report

    Nice article on how to pass a JDBC resultset to a report at

    Techie.Ocean

    Friday, June 12, 2009

    Calling BIRT reports from ASP.NET using Actuate’s JSAPI

    A while back I wrote a blog entry about Actuate’s new JSAPI. This API is AJAX based and allows BIRT reports to be displayed using virtually any front end. The previous post points to an article on Birt-Exchange that describes the capabilities of the API.



    To include the API within an ASP.NET page is very simple. All that is needed is to add a script tag within the head tag and point it to the location you have installed the Actuate Java Component package.

    <head runat="server">
    <script type="text/javascript" language="JavaScript" src="http://localhost:8080/ActuateJavaComponent/jsapi"></script>
    </head>

    The viewer can now be created within any div statement in the page by using code similar to the following.

    <div id="viewer1" style="width: 800px; height: 600px; border-width: 1px; border-style: solid;">
    <script type="text/javascript" language="JavaScript">
    function createViewer() {
    var viewer1 = new actuate.Viewer("viewer1");
    viewer1.setReportName("/Public/BIRT and BIRT Report Studio Examples/" + "<%=DropDownList1.Text%>");
    viewer1.setWidth(600);
    viewer1.setHeight(450);
    viewer1.submit();

    }
    actuate.load("viewer");
    actuate.initialize("http://localhost:8080/ActuateJavaComponent/", null, null, null, createViewer);
    </script</div>

    In the above example we are setting the report name based on a drop down list that is populated with the following code.

    <asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True">
    <asp:ListItem>Customer Order History.rptdesign</asp:ListItem>
    <asp:ListItem>Sales by Territory.rptdesign</asp:ListItem>
    <asp:ListItem>Customer Dashboard.rptdesign</asp:ListItem>
    </asp:DropDownList>

    The viewer is created within the “viewer1” div tag.



    The API also supplies a component for presenting parameters. This component can be created in a similar method to the viewer component.

    function createParameter() {
    myParam = new actuate.Parameter("myDivContainer");
    myParam.setReportName("/Public/BIRT and BIRT Report Studio Examples/Sales By Territory.rptdesign");
    myParam.submit();
    }

    This will present a parameter page that is based on the parameter designs within the report. The Parameter component can be used in conjunction with the Viewer component to display both the parameters and the report within one page.


    <button onclick="runReport()">
    Run Report
    </button>

    <div id="myDivContainer" style="border-width: 1px; border-style: solid;">
    </div>
    <div id="myViewerDivContainer" style="border-width: 1px; border-style: solid;">
    </div>

    <script type="text/javascript" language="JavaScript">
    var myViewer = null;
    var myParam = null;
    function createParameter() {
    myParam = new actuate.Parameter("myDivContainer");
    myParam.setReportName("/Public/BIRT and BIRT Report Studio Examples/Sales By Territory.rptdesign");
    myParam.submit();
    }

    function runReport() {
    myViewer = new actuate.Viewer("myViewerDivContainer");
    myViewer.setReportName("/Public/BIRT and BIRT Report Studio Examples/Sales By Territory.rptdesign");
    myViewer.setParameters(myParam.getParameterMap();
    myViewer.submit();
    }
    actuate.load("parameter");
    actuate.load("viewer");
    actuate.initialize("http://localhost:8080/ActuateJavaComponent/", null, null, null, createParameter);
    </script>

    This line:
    myViewer.setParameters(myParam.getParameterMap();

    Sets the parameters entered in the parameter component to the viewer component. If you decide to create your own parameter controls, the values can be passed to viewer component using name value pairs.

    <form id="form1" runat="server">
    <asp:DropDownList ID="DropDownList1" runat="server">
    <asp:ListItem>NA</asp:ListItem>
    <asp:ListItem>Japan</asp:ListItem>
    <asp:ListItem>EMEA</asp:ListItem>
    </asp:DropDownList>
    </form>
    .
    .
    myViewer.setParameters({ "Territory": document.getElementById("DropDownList1").value });



    The examples are available here.

    Wednesday, May 27, 2009

    Change Page Layout

    Been busy writing reports lately, but I thought that I would try to publish a few quick tips that I have turned up.

    One of the issues I have faced is that when a report is being formatted for PDF layout, I want it to be constrained to the page dimensions and to have it in portrait style. At the same time, when it is displayed for HTML, I want to have it in Landscape mode since the rendering seems to be better for HTML.

    This little section of script placed in the beforeFactory method of the ReportItem will do that for you.

    var renderO = reportContext.getRenderOption().getOutputFormat();
    // change layout
    if (renderO == "pdf"){
        reportContext.getDesignHandle().getMasterPages().get(0).setProperty("orientation","portrait");
    } else if (renderO == "html")
        reportContext.getDesignHandle().getMasterPages().get(0).setProperty("orientation","landscape");
    
    
    

    Monday, May 18, 2009

    BIRT 2.5 M7 New and Notable

    BIRT 2.5 M7 was released earlier this month. This milestone adds features like, default parameter value scripting, the ability to paste HTML and RTF directly into a Text element, and independent Locale formatting. Crosstabs have also been improved to support dragging attributes directly from the data cube into the crosstab, independent visibility for a measure and its totals, and improved support for empty values associated with a time dimension. In addition a new interface is provided that allows your Java programs to track the progress of a BIRT report.



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

    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.