BIRT Exchange Forum: BIRT Exchange Forum -> Michael's BIRT Blog

The usability of a product is a very important factor in the productivity of its users. With enhanced usability features such as derived cube measures, filters that don't impact totals, an enhanced expression builder, and more, Actuate 11 SP4 is doing its part to keep productivity high. In this post, I'm going to shed some light on these features and demonstrate their value.

Gone are the days when BIRT Data Analyzer users and BIRT report authors are forced to define derived measures on their own. In Actuate 11 SP4, the data cube author can define the needed derived measures based on other measures from the cube, allowing the cube consumer to use them as if they are normal, non-derived measures. They won't be able to notice a difference.

On top of that, the new derived measures don't increase the size or generation time of the cube. Unlike a normal cube measure, derived measures are only computed when they are used, allowing you to save some processing time, when a measure is not used. The learning curve on this is very small, as well. The only difference between creating a standard cube measure and a derived cube measure is that the "Is Derived Measure" checkbox must be checked, as seen in the image below.



Have you ever filtered a table or crosstab, but then wanted to do a calculation based on the entire result set? For example, say you had a table with sales by country, but wanted to only show the top 10, so you put a filter on the table. Now, you want to figure out the percent of total sales for each of these countries. In past versions, with the filter, you could no longer do a calculation like this, over the entire, original result set. Well, now you can.

In 11 SP4, BIRT Designer Pro table and crosstab filter dialogs now allow for totals to not be recalculated to exclude the filtered values. In BIRT Data Analyzer, the filter dialogs also have the option to control whether the totals should be recalculated or not, when a crosstab is filtered.



Another enhancement made is to the expression builder for creating new computed columns in both the Interactive Viewer and BIRT Data Analyzer. The provided functions are now categorized into different groups, like Math, Financial, Text, etc. Additional functions have also been added to both products. Some of the new functions included are:

• %OF - calculates the percentage for the selected column value in the scope of the selected base group. For the selected column value, the base group can be other column values, Aggregations like Group Total, Grand Total etc.
• %OF DIFFERENCE - calculates the percentage of the difference between values in two numbers
• RUNNINGSUM - Calculates the running total for the selected Column at the specified base group level
• RANK - Calculates the rank for the Column value at the specified level and in specified direction

Creating filters in BIRT Studio also gets easier. The BIRT Studio Table Builder Wizard adds functionality to help users the option to easily define complex, composite filters. Related to this is a performance enhancement for BIRT Studio. Filters defined in the Table Builder Wizard are pushed to the underlying database, in all possible cases, allowing for data to be more efficiently filtered at the source.



With Actuate 11 SP4, BIRT Designer Pro also adds the capability to define which table groups start expanded or collapsed, depending on the granularity needed by creating a Named Expression called “__ac_group_collapse_level” on the containing table. The value is a comma separated list of the groups that are to start collapsed. 1 is starting value for the most outer group. The expand/collapse operations are also more efficient than ever, as they are handled completely on the client side, now, with no calls to the server.



Last, but not least, report authors using BDPro can now use aggregation fields in a table's sort key expression.

Thanks for reading. Next time, I'll talk about the feature upgrades for data connectivity and the iHub. In the meantime, download a free trial version of Actuate BIRT iServer and BIRT Designer Pro, to check it out for yourself.

This blog series takes a look back at the past week in the forums, recalling how active they were and highlights some of the questions asked that seem to frequent the forums or other interesting topics. We're in the last week of the year! The BIRT Exchange forums have been quiet in the past week because of the holidays. I don't expect that they'll pick back up again until the beginning of next year! Many great questions and answers are being posted every day. As always, thanks to those users who help answer questions! It is a great help to the community!

The unanswered post from last week is about a coloring issue in an area chart. There isn't much explanation to go on for this issue. Two charts are shown in an attached image with different coloring. One showing the correct color and one showing different colors. The complaint is that the colors change depending on data order. If you've had an issue like this before, feel free to comment in the thread.

The second topic from last week that I'll cover is making all charts in a report have the same colors for shared categories. The series palette determines the color of the data points in your chart. The data points are colored in order based on the palette. If you have multiple charts with the same categories, but the charts are sorted in a way in which the categories aren't in the same order, the data points for each category will have different colors. An example in this thread will show how to use a simple script to provide custom coloring for each data point and legend item to match across all charts.

The last topic for this week is merging data in a joint dataSet. When you join two dataSets together, many times you'll end up with incomplete columns or two fields that you'd like to merge. To handle this issue, all you need to do is create a computed column in your new joint dataSet. In this computed column, you can fill in a default value for null values or if you want to merge two columns, you can check which column has data of the two columns you want to merge, and use that value as the output for the column.

Again, this is just a small sample of what went through the forums this past week. For more questions and answers that have been posted, check out the forums. As always, if you have a question, feel free to ask it, and if you see a question you know the answer to or have a similar experience to, feel free to post an answer or comment. I hope everyone has a safe New Year! See you in 2012!

This blog series takes a look back at the past week in the forums, recalling how active they were and highlights some of the questions asked that seem to frequent the forums or other interesting topics. The forums have been busy all year. Quite a bit busier than last year, it seems, though I don't have the numbers on this in front of me. :) Luckily, many members are contributing answers to help with the extra traffic. Many great questions and answers are being posted every day. As always, thanks to those users who help answer questions! It is a great help to the community!

The first topic for this week is how to get today's string day (Monday, Tuesday, etc.) into your chart title. The poster wanted to grab the day of week from today's date and create their chart title with this value. This can be done by creating a date format that only shows the string day of week and then using the value in your chart script to set the title of the chart. An example is shown in the forum thread that shows how to do this. If you're not interested in setting the day of week in the title, but just want to dynamically set your chart title, this example will also be helpful.

The second topic for this week is about passing data between queries in a report. Sometimes you need to use data from one dataSet to get the appropriate data from another for your report. One way to do this is to create a joint dataSet to join all of the data, but if you have large amounts of data, this can cause processing to be very slow. The ideal way to do this is to take the data from the first dataSet and pass it as a parameter in the second query. There are at least a couple ways to do this in BIRT. One is to use a dataSet parameter in the second dataSet, embed a table for this dataSet into a table for the first dataSet, and use the dataSet parameter binding feature to pass the value from the outer table to the inner table's query to grab only the appropriate data. Another way, that is described in the forum thread is to embed the second dataSet's table into the first dataSet's table and in the onCreate script of the outer table, you'd set a global variable to have the value you want to limit your inner query by, and then in the beforeOpen of the inner dataSet, you can use this variable to set your where clause in your queryText.

The last topic for this week is how to create a rolling sum in your dataSet. There are standard aggregations to create a sum for an entire column or to keep the running sum, but a rolling sum for a specified period is not a standard aggregation. The poster wanted to be able to keep a sum of their data fro the last 12 months. When they reached the 13th month, they wanted to disclude the first month from the total. To do this, you can create a computed column in your dataSet that uses an array to keep track of the previous values so you can create your rolling sum. An example is included in the forum thread.

Again, this is just a small sample of what went through the forums this past week. For more questions and answers that have been posted, check out the forums. As always, if you have a question, feel free to ask it, and if you see a question you know the answer to or have a similar experience to, feel free to post an answer or comment.