Eclipse BIRT

Innovent Solutions has performed periodic comparisons of the reporting portion of Eclipse BIRT, Jaspersoft and Pentaho starting in 2010 with an update in 2012. In 2015, we have decided to revisit the reporting products to evaluate what has changed ... [More] and to update our comparison.To see the full review please visit our website to read the articles: Overview  Comparison Matrix BIRT Review TIBCO Jaspersoft Studio Review Pentaho Report Designer Review [Less]

Innovent Solutions has performed periodic comparisons of the reporting portion of Eclipse BIRT, Jaspersoft and Pentaho starting in 2010 with an update in 2012. In 2015, we have decided to revisit the reporting products to evaluate what has changed ... [More] and to update our comparison.To see the full review please visit our website to read the articles:Overview Comparison MatrixBIRT ReviewTIBCO Jaspersoft Studio ReviewPentaho Report Designer Review [Less]

BIRT provides sorting capabilities on many report items, including Tables, Lists, Cross Tabs, Charts and Groups.  Sorting is configured using a sort key expression.  The expression is usually tied to data that is returned in a BIRT data set.  In ... [More] addition to supporting sort direction, BIRT also supports locale specific sorting and sort strength.  The expression is entered at various locations in the designer and depends on what report item is being sorted.  For Cross Tabs, Lists and Tables the sorting expression can be entered in the property view, under the sorting tab. The sorting expression for Charts is defined in the Select Data tab of the chart wizard, next to the expression for the category or optional grouping expression. Group sorting is defined in the Group Editor. If your BIRT report uses a grouped table, by default the group will be sorted by your key expression.  In some cases this may not be desirable.  Specifically when the data is pre-sorted in the order you want it to appear.  BIRT provides an advanced property to turn this behavior off.  You can access this property (“Sort by groups”) in the advanced table of the property editor after selecting the table.   When sorting groups, Tables, Lists and Crosstabs, BIRT supports multiple expressions and the data will be sorted using the first expression, followed by subsequent expressions.  While most sort expressions are quite simple, it is possible to create a custom sort order using a more complicated expression.  Expressions can multi-lined, use report parameters and even call out to external classes.  The following multi-line sort expression returns the third order line number first in the custom sort. var ol = row["ORDERLINENUMBER"]; if( ol == 3 ){ 1 }else if( ol == 1 ){ 2 }else if( ol == 2 ){ 3 }else if( ol == 4 ){ 4 }  Sort expressions can also be created and applied in a BIRT JavaScript event handler.  For example the following beforeFactory script adds a sort expression to a table group. importPackage(Packages.org.eclipse.birt.report.model.api); importPackage(Packages.org.eclipse.birt.report.model.api.elements); importPackage(Packages.java.util); importPackage(Packages.com.ibm.icu.util); //Name Your Table tbl = reportContext.getDesignHandle().findElement("mytable"); var lc = newULocale(reportContext.getLocale()); sc = StructureFactory.createSortKey(); sc.setKey("row[\""+params["SortOn"].value+"\"]"); sc.setDirection(params["SortOrder"].value); sc.setLocale(lc); var tblGroup = tbl.getGroups().get(0); //to put the sort on the table use the table handle //instead of the group handle ph = tblGroup.getPropertyHandle(TableHandle.SORT_PROP); ph.addItem(sc);  The examples shown in this post are available at BIRT-Exchange. [Less]

BIRT provides sorting capabilities on many report items, including Tables, Lists, Cross Tabs, Charts and Groups.  Sorting is configured using a sort key expression.  The expression is usually tied to data that is returned in a BIRT data set.  In ... [More] addition to supporting sort direction, BIRT also supports locale specific sorting and sort strength.  The expression is entered at various locations in the designer and depends on what report item is being sorted.  For Cross Tabs, Lists and Tables the sorting expression can be entered in the property view, under the sorting tab. The sorting expression for Charts is defined in the Select Data tab of the chart wizard, next to the expression for the category or optional grouping expression. Group sorting is defined in the Group Editor. If your BIRT report uses a grouped table, by default the group will be sorted by your key expression.  In some cases this may not be desirable.  Specifically when the data is pre-sorted in the order you want it to appear.  BIRT provides an advanced property to turn this behavior off.  You can access this property (“Sort by groups”) in the advanced table of the property editor after selecting the table.   When sorting groups, Tables, Lists and Crosstabs, BIRT supports multiple expressions and the data will be sorted using the first expression, followed by subsequent expressions.  While most sort expressions are quite simple, it is possible to create a custom sort order using a more complicated expression.  Expressions can multi-lined, use report parameters and even call out to external classes.  The following multi-line sort expression returns the third order line number first in the custom sort. var ol = row["ORDERLINENUMBER"]; if( ol == 3 ){ 1 }else if( ol == 1 ){ 2 }else if( ol == 2 ){ 3 }else if( ol == 4 ){ 4 }  Sort expressions can also be created and applied in a BIRT JavaScript event handler.  For example the following beforeFactory script adds a sort expression to a table group. importPackage(Packages.org.eclipse.birt.report.model.api); importPackage(Packages.org.eclipse.birt.report.model.api.elements); importPackage(Packages.java.util); importPackage(Packages.com.ibm.icu.util); //Name Your Table tbl = reportContext.getDesignHandle().findElement("mytable"); var lc = newULocale(reportContext.getLocale()); sc = StructureFactory.createSortKey(); sc.setKey("row[\""+params["SortOn"].value+"\"]"); sc.setDirection(params["SortOrder"].value); sc.setLocale(lc); var tblGroup = tbl.getGroups().get(0); //to put the sort on the table use the table handle //instead of the group handle ph = tblGroup.getPropertyHandle(TableHandle.SORT_PROP); ph.addItem(sc);  The examples shown in this post are available at BIRT-Exchange. [Less]

BIRT provides sorting capabilities on many report items, including Tables, Lists, Cross Tabs, Charts and Groups.  Sorting is configured using a sort key expression.  The expression is usually tied to data that is returned in a BIRT data set.  In ... [More] addition to supporting sort direction, BIRT also supports locale specific sorting and sort strength.  The expression is entered at various locations in the designer and depends on what report item is being sorted.  For Cross Tabs, Lists and Tables the sorting expression can be entered in the property view, under the sorting tab. The sorting expression for Charts is defined in the Select Data tab of the chart wizard, next to the expression for the category or optional grouping expression. Group sorting is defined in the Group Editor. If your BIRT report uses a grouped table, by default the group will be sorted by your key expression.  In some cases this may not be desirable.  Specifically when the data is pre-sorted in the order you want it to appear.  BIRT provides an advanced property to turn this behavior off.  You can access this property (“Sort by groups”) in the advanced table of the property editor after selecting the table.   When sorting groups, Tables, Lists and Crosstabs, BIRT supports multiple expressions and the data will be sorted using the first expression, followed by subsequent expressions.  While most sort expressions are quite simple, it is possible to create a custom sort order using a more complicated expression.  Expressions can multi-lined, use report parameters and even call out to external classes.  The following multi-line sort expression returns the third order line number first in the custom sort. var ol = row["ORDERLINENUMBER"];if( ol == 3 ){1}else if( ol == 1 ){2}else if( ol == 2 ){3}else if( ol == 4 ){4}  Sort expressions can also be created and applied in a BIRT JavaScript event handler.  For example the following beforeFactory script adds a sort expression to a table group. importPackage(Packages.org.eclipse.birt.report.model.api);importPackage(Packages.org.eclipse.birt.report.model.api.elements);importPackage(Packages.java.util);importPackage(Packages.com.ibm.icu.util);//Name Your Tabletbl = reportContext.getDesignHandle().findElement("mytable");var lc = newULocale(reportContext.getLocale());sc = StructureFactory.createSortKey();sc.setKey("row[\""+params["SortOn"].value+"\"]");sc.setDirection(params["SortOrder"].value);sc.setLocale(lc);var tblGroup = tbl.getGroups().get(0);//to put the sort on the table use the table handle//instead of the group handleph = tblGroup.getPropertyHandle(TableHandle.SORT_PROP);ph.addItem(sc); The examples shown in this post are available at BIRT-Exchange. [Less]

BIRT offers many ways data can be retrieved, including extending JDBC drivers, scripted data sources and implementing the Open Data Access (ODA) framework extensions.  Each of these methods has pros and cons associated with them. Generally the most ... [More] re-usable method is to implement the ODA extension which allows adding a personalized GUI to the BIRT designer.  Many examples of this approach are available on Birt-Exchange.  The quickest method is to implement a scripted data source, which involves writing some Java or JavaScript to retrieve values.  This post illustrates retrieving a Google BigQuery using the scripted data source approach.   For this report we will use the births by year query described here.    The query is defined as "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year"The first thing we will need to do is to create a Java class that will call BigQuery.  The class for this example is shown below.  More details on the BigQuery Java API are available here. packagebirt.big.query.sample; import java.io.File; import java.io.IOException; importjava.security.GeneralSecurityException; import java.util.List; importcom.google.api.client.googleapis.auth.oauth2.GoogleCredential; importcom.google.api.client.http.HttpTransport; importcom.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.JsonFactory; importcom.google.api.client.json.jackson.JacksonFactory; importcom.google.api.services.bigquery.Bigquery; importcom.google.api.services.bigquery.model.QueryRequest; importcom.google.api.services.bigquery.model.QueryResponse; importcom.google.api.services.bigquery.model.TableCell; importcom.google.api.services.bigquery.model.TableRow; public class GetBigQueryData {         private static final String SCOPE = "https://www.googleapis.com/auth/bigquery";         private static final HttpTransport TRANSPORT = new NetHttpTransport();         private static final JsonFactory JSON_FACTORY = new JacksonFactory();         private Bigquery bigquery;         private static String QUERY = "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year";         public void setupConnection() throwsGeneralSecurityException, IOException{                GoogleCredential credential = new GoogleCredential.Builder().setTransport(TRANSPORT)                               .setJsonFactory(JSON_FACTORY)                                .setServiceAccountId("[email protected]")                               .setServiceAccountScopes(SCOPE)                               .setServiceAccountPrivateKeyFromP12File(                                              new File("locationofyourkey.p12")).build();                bigquery = new Bigquery.Builder(TRANSPORT, JSON_FACTORY, credential)                .setApplicationName("BigQuery-Service-Accounts/0.1")                .setHttpRequestInitializer(credential).build();         }         public Listthrows IOException{                QueryRequest queryInfo = new QueryRequest().setQuery(query);                Bigquery.Jobs.Query queryRequest = bigquery.jobs().query("yourprojectid", queryInfo);                QueryResponse queryResponse = queryRequest.execute();                returnqueryResponse.getRows();         }         public static void main(String[] args) throws IOException, GeneralSecurityException {                GetBigQueryData bq = new GetBigQueryData();                bq.setupConnection();                ListQUERY);                if( rows != null ){                        for (TableRow row : rows) {                              for (TableCell field: row.getF()) {                               System.out.printf("%s--", field.getV());                              }                        }                }         } }   This is a simple class based on the Google BigQuery examples.  Note that this project was configured to use a service account.  If you plan to use the above code make sure to do the same for your project and enter the correct service account id and private key.  You will also need to specify your project id when creating the query request.  This example has two methods that will be called from BIRT setupConnection and executeQuery.  The setupConnection method just builds our GoogleCredentials object and the executeQuery method executes the passed in query string and returns the query response.  To make this class available to BIRT you can either add it to BIRT as a jar or create a Java project in the same workspace as the BIRT Report project.  For this post, a Java project was created (Named BirtGoogleBigQueryExample).  Using the BIRT Classpath preference, the Java project and the Google jars where added to the report project (Named BirtGoogleBigQueryReport). Select Configure Project Specific Settings and click on the BIRT report project (BirtGoogleBigQueryReport in this example).  Next click on the Add Projects button and add the Java project (BirtGoogleBigQueryExample in this example).  Finally select the Add JARs button and add the Google BigQuery API jars to the Report Project classpath. You can now create a Scripted Data Source in BIRT that calls this class.  First create a new report and in the data explorer view, right click on Data Sources and select New Data Source. Select Scripted Data Source and Click Finish.  Next right click on the Data Sets node and select New Data Set. Select the scripted data source you created and click on the next button.  Enter two columns, one for year and one for births.  Set their type to integer. For this example only two events need to be implemented.  Both are on the scripted data set.  An open and a fetch event must be implemented.  To do this select the scripted data set in the data explorer view and click on the script tab in the report editor.  Enter the following script for the open event. importPackage(Packages.birt.big.query.sample); importPackage(Packages.com.google.api.services.bigquery.model); importPackage(Packages.java.util); var bq = new GetBigQueryData(); bq.setupConnection(); var mrows = bq.executeQuery("SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year"); iter = mrows.iterator();This script imports the required packages and calls the setupConnection method from the class we created earlier.  The query is then executed by calling our executeQuery method.  An iterator is then created to use in the fetch method.  Select the fetch event and enter the following script. if( iter.hasNext()){  tr = iter.next();  myrow = tr.getF();  row["Year"] = parseFloat(myrow.get(0).getV());  row["Births"] = parseInt(myrow.get(1).getV());  return true; }else{  return false; }With BIRT scripted data sets the fetch method is continuously called until a false is returned.  In this example each of the BigQuery rows is iterated and the column values are set.  You can now use the data from the query in your report.   When deploying the report make sure to have the BigQuery jars and the custom class in the classpath of your runtime.  In most cases you will just copy the jars to the WEB-INF/lib of the viewer.  The example Java Project and Report Project are available at Birt-Exchange.  [Less]

BIRT offers many ways data can be retrieved, including extending JDBC drivers, scripted data sources and implementing the Open Data Access (ODA) framework extensions.  Each of these methods has pros and cons associated with them. Generally the most ... [More] re-usable method is to implement the ODA extension which allows adding a personalized GUI to the BIRT designer.  Many examples of this approach are available on Birt-Exchange.  The quickest method is to implement a scripted data source, which involves writing some Java or JavaScript to retrieve values.  This post illustrates retrieving a Google BigQuery using the scripted data source approach.   For this report we will use the births by year query described here.    The query is defined as "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year"The first thing we will need to do is to create a Java class that will call BigQuery.  The class for this example is shown below.  More details on the BigQuery Java API are available here. packagebirt.big.query.sample;import java.io.File;import java.io.IOException;importjava.security.GeneralSecurityException;import java.util.List;importcom.google.api.client.googleapis.auth.oauth2.GoogleCredential;importcom.google.api.client.http.HttpTransport;importcom.google.api.client.http.javanet.NetHttpTransport;import com.google.api.client.json.JsonFactory;importcom.google.api.client.json.jackson.JacksonFactory;importcom.google.api.services.bigquery.Bigquery;importcom.google.api.services.bigquery.model.QueryRequest;importcom.google.api.services.bigquery.model.QueryResponse;importcom.google.api.services.bigquery.model.TableCell;importcom.google.api.services.bigquery.model.TableRow;public class GetBigQueryData {        private static final String SCOPE = "https://www.googleapis.com/auth/bigquery";        private static final HttpTransport TRANSPORT = new NetHttpTransport();        private static final JsonFactory JSON_FACTORY = new JacksonFactory();        private Bigquery bigquery;        private static String QUERY = "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year";        public void setupConnection() throwsGeneralSecurityException, IOException{               GoogleCredential credential = new GoogleCredential.Builder().setTransport(TRANSPORT)                              .setJsonFactory(JSON_FACTORY)                               .setServiceAccountId("[email protected]")                              .setServiceAccountScopes(SCOPE)                              .setServiceAccountPrivateKeyFromP12File(                                             new File("locationofyourkey.p12")).build();               bigquery = new Bigquery.Builder(TRANSPORT, JSON_FACTORY, credential)               .setApplicationName("BigQuery-Service-Accounts/0.1")               .setHttpRequestInitializer(credential).build();        }        public ListexecuteQuery( String query) throws IOException{               QueryRequest queryInfo = new QueryRequest().setQuery(query);               Bigquery.Jobs.Query queryRequest = bigquery.jobs().query("yourprojectid", queryInfo);               QueryResponse queryResponse = queryRequest.execute();               returnqueryResponse.getRows();        }        public static void main(String[] args) throws IOException, GeneralSecurityException {               GetBigQueryData bq = new GetBigQueryData();               bq.setupConnection();               Listrows = bq.executeQuery(QUERY);               if( rows != null ){                       for (TableRow row : rows) {                             for (TableCell field: row.getF()) {                              System.out.printf("%s--", field.getV());                             }                       }               }        }}  This is a simple class based on the Google BigQuery examples.  Note that this project was configured to use a service account.  If you plan to use the above code make sure to do the same for your project and enter the correct service account id and private key.  You will also need to specify your project id when creating the query request.  This example has two methods that will be called from BIRT setupConnection and executeQuery.  The setupConnection method just builds our GoogleCredentials object and the executeQuery method executes the passed in query string and returns the query response.  To make this class available to BIRT you can either add it to BIRT as a jar or create a Java project in the same workspace as the BIRT Report project.  For this post, a Java project was created (Named BirtGoogleBigQueryExample).  Using the BIRT Classpath preference, the Java project and the Google jars where added to the report project (Named BirtGoogleBigQueryReport). Select Configure Project Specific Settings and click on the BIRT report project (BirtGoogleBigQueryReport in this example).  Next click on the Add Projects button and add the Java project (BirtGoogleBigQueryExample in this example).  Finally select the Add JARs button and add the Google BigQuery API jars to the Report Project classpath. You can now create a Scripted Data Source in BIRT that calls this class.  First create a new report and in the data explorer view, right click on Data Sources and select New Data Source. Select Scripted Data Source and Click Finish.  Next right click on the Data Sets node and select New Data Set. Select the scripted data source you created and click on the next button.  Enter two columns, one for year and one for births.  Set their type to integer. For this example only two events need to be implemented.  Both are on the scripted data set.  An open and a fetch event must be implemented.  To do this select the scripted data set in the data explorer view and click on the script tab in the report editor.  Enter the following script for the open event.importPackage(Packages.birt.big.query.sample);importPackage(Packages.com.google.api.services.bigquery.model);importPackage(Packages.java.util);var bq = new GetBigQueryData();bq.setupConnection();var mrows = bq.executeQuery("SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year");iter = mrows.iterator();This script imports the required packages and calls the setupConnection method from the class we created earlier.  The query is then executed by calling our executeQuery method.  An iterator is then created to use in the fetch method.  Select the fetch event and enter the following script.if( iter.hasNext()){ tr = iter.next(); myrow = tr.getF(); row["Year"] = parseFloat(myrow.get(0).getV()); row["Births"] = parseInt(myrow.get(1).getV()); return true;}else{ return false;}With BIRT scripted data sets the fetch method is continuously called until a false is returned.  In this example each of the BigQuery rows is iterated and the column values are set.  You can now use the data from the query in your report.   When deploying the report make sure to have the BigQuery jars and the custom class in the classpath of your runtime.  In most cases you will just copy the jars to the WEB-INF/lib of the viewer.  The example Java Project and Report Project are available at Birt-Exchange.  [Less]

BIRT offers many ways data can be retrieved, including extending JDBC drivers, scripted data sources and implementing the Open Data Access (ODA) framework extensions.  Each of these methods has pros and cons associated with them. Generally the most ... [More] re-usable method is to implement the ODA extension which allows adding a personalized GUI to the BIRT designer.  Many examples of this approach are available on Birt-Exchange.  The quickest method is to implement a scripted data source, which involves writing some Java or JavaScript to retrieve values.  This post illustrates retrieving a Google BigQuery using the scripted data source approach.   For this report we will use the births by year query described here.    The query is defined as "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year"The first thing we will need to do is to create a Java class that will call BigQuery.  The class for this example is shown below.  More details on the BigQuery Java API are available here. packagebirt.big.query.sample;import java.io.File;import java.io.IOException;importjava.security.GeneralSecurityException;import java.util.List;importcom.google.api.client.googleapis.auth.oauth2.GoogleCredential;importcom.google.api.client.http.HttpTransport;importcom.google.api.client.http.javanet.NetHttpTransport;import com.google.api.client.json.JsonFactory;importcom.google.api.client.json.jackson.JacksonFactory;importcom.google.api.services.bigquery.Bigquery;importcom.google.api.services.bigquery.model.QueryRequest;importcom.google.api.services.bigquery.model.QueryResponse;importcom.google.api.services.bigquery.model.TableCell;importcom.google.api.services.bigquery.model.TableRow;public class GetBigQueryData {        private static final String SCOPE = "https://www.googleapis.com/auth/bigquery";        private static final HttpTransport TRANSPORT = new NetHttpTransport();        private static final JsonFactory JSON_FACTORY = new JacksonFactory();        private Bigquery bigquery;        private static String QUERY = "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year";        public void setupConnection() throwsGeneralSecurityException, IOException{               GoogleCredential credential = new GoogleCredential.Builder().setTransport(TRANSPORT)                              .setJsonFactory(JSON_FACTORY)                               .setServiceAccountId("[email protected]")                              .setServiceAccountScopes(SCOPE)                              .setServiceAccountPrivateKeyFromP12File(                                             new File("locationofyourkey.p12")).build();               bigquery = new Bigquery.Builder(TRANSPORT, JSON_FACTORY, credential)               .setApplicationName("BigQuery-Service-Accounts/0.1")               .setHttpRequestInitializer(credential).build();        }        public ListexecuteQuery( String query) throws IOException{               QueryRequest queryInfo = new QueryRequest().setQuery(query);               Bigquery.Jobs.Query queryRequest = bigquery.jobs().query("yourprojectid", queryInfo);               QueryResponse queryResponse = queryRequest.execute();               returnqueryResponse.getRows();        }        public static void main(String[] args) throws IOException, GeneralSecurityException {               GetBigQueryData bq = new GetBigQueryData();               bq.setupConnection();               Listrows = bq.executeQuery(QUERY);               if( rows != null ){                       for (TableRow row : rows) {                             for (TableCell field: row.getF()) {                              System.out.printf("%s--", field.getV());                             }                       }               }        }}  This is a simple class based on the Google BigQuery examples.  Note that this project was configured to use a service account.  If you plan to use the above code make sure to do the same for your project and enter the correct service account id and private key.  You will also need to specify your project id when creating the query request.  This example has two methods that will be called from BIRT setupConnection and executeQuery.  The setupConnection method just builds our GoogleCredentials object and the executeQuery method executes the passed in query string and returns the query response.  To make this class available to BIRT you can either add it to BIRT as a jar or create a Java project in the same workspace as the BIRT Report project.  For this post, a Java project was created (Named BirtGoogleBigQueryExample).  Using the BIRT Classpath preference, the Java project and the Google jars where added to the report project (Named BirtGoogleBigQueryReport). Select Configure Project Specific Settings and click on the BIRT report project (BirtGoogleBigQueryReport in this example).  Next click on the Add Projects button and add the Java project (BirtGoogleBigQueryExample in this example).  Finally select the Add JARs button and add the Google BigQuery API jars to the Report Project classpath. You can now create a Scripted Data Source in BIRT that calls this class.  First create a new report and in the data explorer view, right click on Data Sources and select New Data Source. Select Scripted Data Source and Click Finish.  Next right click on the Data Sets node and select New Data Set. Select the scripted data source you created and click on the next button.  Enter two columns, one for year and one for births.  Set their type to integer. For this example only two events need to be implemented.  Both are on the scripted data set.  An open and a fetch event must be implemented.  To do this select the scripted data set in the data explorer view and click on the script tab in the report editor.  Enter the following script for the open event.importPackage(Packages.birt.big.query.sample);importPackage(Packages.com.google.api.services.bigquery.model);importPackage(Packages.java.util);var bq = new GetBigQueryData();bq.setupConnection();var mrows = bq.executeQuery("SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year");iter = mrows.iterator();This script imports the required packages and calls the setupConnection method from the class we created earlier.  The query is then executed by calling our executeQuery method.  An iterator is then created to use in the fetch method.  Select the fetch event and enter the following script.if( iter.hasNext()){ tr = iter.next(); myrow = tr.getF(); row["Year"] = parseFloat(myrow.get(0).getV()); row["Births"] = parseInt(myrow.get(1).getV()); return true;}else{ return false;}With BIRT scripted data sets the fetch method is continuously called until a false is returned.  In this example each of the BigQuery rows is iterated and the column values are set.  You can now use the data from the query in your report.   When deploying the report make sure to have the BigQuery jars and the custom class in the classpath of your runtime.  In most cases you will just copy the jars to the WEB-INF/lib of the viewer.  The example Java Project and Report Project are available at Birt-Exchange.  [Less]

BIRT offers many ways data can be retrieved, including extending JDBC drivers, scripted data sources and implementing the Open Data Access (ODA) framework extensions.  Each of these methods has pros and cons associated with them. Generally the most ... [More] re-usable method is to implement the ODA extension which allows adding a personalized GUI to the BIRT designer.  Many examples of this approach are available on Birt-Exchange.  The quickest method is to implement a scripted data source, which involves writing some Java or JavaScript to retrieve values.  This post illustrates retrieving a Google BigQuery using the scripted data source approach.   For this report we will use the births by year query described here.    The query is defined as "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year"The first thing we will need to do is to create a Java class that will call BigQuery.  The class for this example is shown below.  More details on the BigQuery Java API are available here. packagebirt.big.query.sample; import java.io.File; import java.io.IOException; importjava.security.GeneralSecurityException; import java.util.List; importcom.google.api.client.googleapis.auth.oauth2.GoogleCredential; importcom.google.api.client.http.HttpTransport; importcom.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.JsonFactory; importcom.google.api.client.json.jackson.JacksonFactory; importcom.google.api.services.bigquery.Bigquery; importcom.google.api.services.bigquery.model.QueryRequest; importcom.google.api.services.bigquery.model.QueryResponse; importcom.google.api.services.bigquery.model.TableCell; importcom.google.api.services.bigquery.model.TableRow; public class GetBigQueryData {         private static final String SCOPE = "https://www.googleapis.com/auth/bigquery";         private static final HttpTransport TRANSPORT = new NetHttpTransport();         private static final JsonFactory JSON_FACTORY = new JacksonFactory();         private Bigquery bigquery;         private static String QUERY = "SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year";         public void setupConnection() throwsGeneralSecurityException, IOException{                GoogleCredential credential = new GoogleCredential.Builder().setTransport(TRANSPORT)                               .setJsonFactory(JSON_FACTORY)                                .setServiceAccountId("[email protected]")                               .setServiceAccountScopes(SCOPE)                               .setServiceAccountPrivateKeyFromP12File(                                              new File("locationofyourkey.p12")).build();                bigquery = new Bigquery.Builder(TRANSPORT, JSON_FACTORY, credential)                .setApplicationName("BigQuery-Service-Accounts/0.1")                .setHttpRequestInitializer(credential).build();         }         public Listthrows IOException{                QueryRequest queryInfo = new QueryRequest().setQuery(query);                Bigquery.Jobs.Query queryRequest = bigquery.jobs().query("yourprojectid", queryInfo);                QueryResponse queryResponse = queryRequest.execute();                returnqueryResponse.getRows();         }         public static void main(String[] args) throws IOException, GeneralSecurityException {                GetBigQueryData bq = new GetBigQueryData();                bq.setupConnection();                ListQUERY);                if( rows != null ){                        for (TableRow row : rows) {                              for (TableCell field: row.getF()) {                               System.out.printf("%s--", field.getV());                              }                        }                }         } }   This is a simple class based on the Google BigQuery examples.  Note that this project was configured to use a service account.  If you plan to use the above code make sure to do the same for your project and enter the correct service account id and private key.  You will also need to specify your project id when creating the query request.  This example has two methods that will be called from BIRT setupConnection and executeQuery.  The setupConnection method just builds our GoogleCredentials object and the executeQuery method executes the passed in query string and returns the query response.  To make this class available to BIRT you can either add it to BIRT as a jar or create a Java project in the same workspace as the BIRT Report project.  For this post, a Java project was created (Named BirtGoogleBigQueryExample).  Using the BIRT Classpath preference, the Java project and the Google jars where added to the report project (Named BirtGoogleBigQueryReport). Select Configure Project Specific Settings and click on the BIRT report project (BirtGoogleBigQueryReport in this example).  Next click on the Add Projects button and add the Java project (BirtGoogleBigQueryExample in this example).  Finally select the Add JARs button and add the Google BigQuery API jars to the Report Project classpath. You can now create a Scripted Data Source in BIRT that calls this class.  First create a new report and in the data explorer view, right click on Data Sources and select New Data Source. Select Scripted Data Source and Click Finish.  Next right click on the Data Sets node and select New Data Set. Select the scripted data source you created and click on the next button.  Enter two columns, one for year and one for births.  Set their type to integer. For this example only two events need to be implemented.  Both are on the scripted data set.  An open and a fetch event must be implemented.  To do this select the scripted data set in the data explorer view and click on the script tab in the report editor.  Enter the following script for the open event. importPackage(Packages.birt.big.query.sample); importPackage(Packages.com.google.api.services.bigquery.model); importPackage(Packages.java.util); var bq = new GetBigQueryData(); bq.setupConnection(); var mrows = bq.executeQuery("SELECT year, SUM(record_weight) as births FROM publicdata:samples.natality GROUP BY year"); iter = mrows.iterator();This script imports the required packages and calls the setupConnection method from the class we created earlier.  The query is then executed by calling our executeQuery method.  An iterator is then created to use in the fetch method.  Select the fetch event and enter the following script. if( iter.hasNext()){  tr = iter.next();  myrow = tr.getF();  row["Year"] = parseFloat(myrow.get(0).getV());  row["Births"] = parseInt(myrow.get(1).getV());  return true; }else{  return false; }With BIRT scripted data sets the fetch method is continuously called until a false is returned.  In this example each of the BigQuery rows is iterated and the column values are set.  You can now use the data from the query in your report.   When deploying the report make sure to have the BigQuery jars and the custom class in the classpath of your runtime.  In most cases you will just copy the jars to the WEB-INF/lib of the viewer.  The example Java Project and Report Project are available at Birt-Exchange.  [Less]

Actuate is sponsoring a customer day in San Francisco on November 8 and in New York on November 14.  This one day event is free to all and will focus on Big Data and Data Visualization.  Shaku Atre will be doing a keynote on Big Data entitled “Big ... [More] Data in Motion and Humongous Data at Rest”.   Stephen Few will be doing a keynote on Data Visualization entitled “Telling Compelling Stories with Numbers” in San Francisco and Geoff McGhee will be presenting “Telling Stories with Data” in New York.  There will also be breakout sessions focused on Dashboards and Scorecards, Tips and Tricks, and Big Data Visualizations.   I will be doing a talk in the Tip and Tricks breakout entitled “BIRT Essentials: Tips and Tricks Every Developer Should Know”.  In this talk I will cover some of the most effective but least understood features of BIRT including how to improve performance, scripting, accessing Big Data sources, debugging reports, and making your reports more dynamic.If you are interested in attending, check out the registrationpage for more details. [Less]