BIRT: Dynamic column visibility / Grouping on visible columns

Recently I needed to implement reporting for a project our company is involved in. Eclipse Birt was chosen as the underlying reporting framework and I got started. The standard Birt reporting tool allows you to quickly make nice looking reports by simply dragging and dropping variables from any data source on a page, selecting your sorting and grouping preferences and subsequently deploying them on a webserver using a servlet provided by the project. My reporting requirements, however, were a little complicated and in the end I ended up spending quite some time getting things working the way I wanted them to.

My situation was as follows:

• The datasource is a remote webservice which outputs csv data: the first row containing the titles/names of each property, all subsequent rows containing the actual data.
• All items have a length property
• The report should achieve a number of tasks
  • The toplevel grouping property must be user selectable (ie: using a report parameter)
  • At the end of each group, a sum must be made of all lengths of items within a group
  • The user must be able to choose the visible columns (again using a report parameter)
  • Within each group, there may be no duplicate lines: an additional grouping step is to be performed on all visible properties, adding the similar items lengths in the process

Let me provide you with a simple example to make things a little more clear. Suppose the datasource provides us with information regarding cabling. Cables have a number of properties, including a length. Assume the original data is as follows:

Manufacturer;Material;Color;Location;PricePerMeter;Length;ThicknessRadius
FactoryA;Plastic;White;Antwerp;2.5;14.9;2
FactoryA;Copper;White;Ghent;2;200;1.1
FactoryA;Copper;Red-Blue;Brussels;4;9;2
FactoryB;FiberOptic;White;Antwerp;12;25;0.9
FactoryB;FiberOptic;Green;Brussels;12;140;0.9
FactoryB;FiberOptic;Blue;Antwerp;12;14;0.9

Suppose the user selects Location as his primary grouping parameter, the default look of the report should be something like this:

Antwerp
    Factory A     Plastic     White      2.5     14.9     2
    Factory B     FiberOptic  White     12.0     25.0     0.9
    Factory B     FiberOptic  Blue      12.0     14.0     0.9

                                         Total Length:   53.9

Ghent
    Factory A     Copper      White      2.0    200.0     1.1

                                         Total Length   200.0

Brussels
    FactoryA      Copper      Red-Blue   4.0      9.0     2.0
    FactoryB      FiberOptic  Green     12.0    140.0     0.9

                                         Total Length   149.0

Now, if the user decides, however, that he’s only interested in the material and the length, he can hide the other columns. In this case, however, I want the report to automatically merge existing items based on visible data. The result would look as follows:

Antwerp
    Plastic     14.9
    FiberOptic  39.0     

                                         Total Length:   53.9

Ghent
    Copper      200.0     

                                         Total Length   200.0

Brussels
    Copper      9.0
    FiberOptic  140.0   

                                         Total Length   149.0

Notice that the two fiberoptic cables in Antwerp are now reduced to a single line: the total length of fiberoptic cables in Antwerp is 39.

Now, how can we do this?

Importing an external CSV source into our report

In order to access a remote csv source, I implemented my own datasource. Implementing a datasource in itself is relatively well documented by the birt project, and few significant problems occured in this step.

My datasource uses a single Java-class: csvparser

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URL;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;

/**
 * Generic CSV date input parser for reporting services.
 *
 * @author Yves Vandewoude
 */
public class CsvParser {

 List<String> items;
 int count = 0;
 HashMap<String, Integer> columnMapping = new HashMap<String, Integer>();
 String[] current;

 /**
 * Load all elements from the original csv datasource.
 *
 * @param path
 */
 public void loadData(String path) {
 items = textRead(path);
 if (!items.isEmpty()) {
 String[] parts = items.remove(0).split(";");
 for (int i = 0; i < parts.length; i++) {
 columnMapping.put(parts[i], i);
 }
 }
 }

 /**
 * Retrieve all columns
 */
 public String[] getColumnNames()
 {
 return columnMapping.keySet().toArray(new String[columnMapping.size()]);
 }

 /**
 * Allows caller to determine the currently active element
 * @param number the number of the record to set as the current
 */
 public void setCurrent(int number) {
 if (number > items.size() - 1) {
 number = items.size() - 1;
 }
 current = items.get(number).split(";", -1);
 }

 /**
 * The number of elements in the dataset offered by this csvparser
 * @return the the number of elements
 */
 public int numberOfItems() {
 return items.size();
 }

 /**
 * Returns the value of the column with the given name at the currently active row.
 * @param column the name of the column
 * @return the value of the given column in the active row
 */
 public String getValue(String column) {
 if (current == null)
 return null;
 else {
 String result = current[columnMapping.get(column)];
 return result;
 }
 }

 /**
 * Private method, does the actual parsing.
 * @param path the path of the input-url
 * @return a list of strings, each string representing a single (unsplit) row of csv data
 */
 private List<String> textRead(String path) {
 BufferedReader br = null;
 List<String> result = new ArrayList<String>();
 try {
 URL url = new URL(path);
 br = new BufferedReader(new InputStreamReader(url.openStream()));
 String current = br.readLine();
 while (current != null) {
 result.add(current);
 current = br.readLine();
 }
 br.close();
 } catch (IOException ioe) {
 }
 return result;
 }
}
}

The class is relatively straightforward: it offers a method that retrieves the value of a certain column for the current row and exports all columns. Nothing special here.

The actual datasource is a scripted source which calls the above class (when testing, make sure the class is in your classpath). Only the open method of the script must be implemented:

// This will track your current row later on
count = 0;

// Create instance of the Controller class
controller = new Packages.be.vlaanderen.awv.CsvParser();

// Load the JSON Source
controller.loadData(params["URL"]);

// Calculate the total rows we will have
totalCount = controller.numberOfItems();

Note that you can perform additional parameterparsing here (such as parsing additional filter information that you wish to pass on to the underlying service by manipulating the url. I prefered to keep the above codesnippet short, so I did not do this.

Now we have our datasource, we need to create a dataset with our own source as its source. We add one output column for each field of our datasource. We also create one addition outputcolumn of type string, which I called filter. We then need to implement the fetch method of this dataset so that it uses our java csv parser for fetching its records.

if(count < totalCount){
 controller.setCurrent(count);
 allNames = controller.getColumnNames();
 var filter = "";
 for (var i = 0; i < allNames.length; i++){
 row[allNames[i]] = controller.getValue(allNames[i]);
 if (params["Hide"].value.indexOf(allNames[i]) == -1 & params["UnusedFields"].value.indexOf(allNames[i]) == -1)
 {
 filter = filter + "_" + row[allNames[i]];
 }
 }
row["filter"] = filter;
 count++;
 return true;
}
return false;

Don’t worry about that filterpart just yet. The essential part right now is that we simply execute

row[allNames[i]] = controller.getValue(allNames[i]);

for each column. Note that the fetch method must either return true or false, depending on whether another record is present in the datasource.

At this point, we have a valid datasource and dataset and can start building our report. We make a table, drag in our items and are ready to go. It’s a good idea to test your report at this point.

Hiding columns on demand

Hiding columns in a table is easy: we simply use the visibility feature of each column and assign an expression to it. In our case, we chose to use a single reportvariable of type string: “Hide”, which includes a comma seperated value list of all columns that should be hidden. Add this reportvariable to your report and then go to the layout window of your table, select Visibility and check for the presence of the name of your datacolumn in the hide parameter:

params["Hide"].value.indexOf("TypeI;") != -1 |
params["Hide"].value.lastIndexOf("TypeI") == params["Hide"].value.length-5

Basically, I here check if the TypeI is present in the csv of Hide. Do this for each column (evidently choosing the relevant variable  present in your dataset).

You can now try to see if the columns are being hidden correctly simply by manipulating the Hide parameter of the report.

Merging on visible fields

So far so good. Now we need to hide duplicate rows and make a sum of their lengths. Basically what we want to do is make an additional group on all visible properties. And that is where filter comes in: basically, upon loading of the report, I create an additional dataset value (filter) which simply concatenates all visible properties except for length. The visible properties are simply all properties in the dataset except for those that are hidden and those that are simply not used in the table. For the latter, I created another report variable which I just statically set to a csv list of all properties present in the dataset but that are not used. The creation of the filter property is done in the fetch method for which the code is shown above.

The actual table then is a bit tricky: you create two groups. The toplevel group, is a group where you place a small expression based on a report parameter in the groupon field:

if (params["AggrTop"].value.toLowerCase() == "color")
{
row["color"];
}
else if  (params["AggrTop"].value.toLowerCase() == "location")
{
row["location"];
}

....

You get the idea. The innergroup is a just a group on filter. The trick is now not to place the information with all columns in the detailrow, but in the footer of the innergroup, since you want to add the lengths of all items in that group. You can do that last thing by simply creating an aggregation with the inner group as its scope.

Voila, here we go. I hope my explanation was helpful.