Features within BIRT Reports

1.Types of aggregate calculations
BIRT provides a wide range of functions in the Total class that support aggregate calculations over sets of data rows. These functions are custom JavaScript functions that BIRT defines. As with native JavaScript functions, you must use the correct capitalization when you type the function name, as shown here . For detailed information about each aggregate function, see Scripting Reference in BIRT's online help.
Aggregate function	Description
Total.count( )	Counts the number of data rows in the set.
Total.countDistinct( )	Counts the number of unique values for a specified field.
Total.first( )	Returns the value of a specified field in the first data row.
Total.isBottomN( )	Returns a boolean value that indicates if the value of a specified numeric field is one of the bottom n values.
Total.isBottomNPercent( )	Returns a boolean value that indicates if the value of a specified numeric field is one of the bottom n percent values.
Total.isTopN( )	Returns a boolean value that indicates if the value of a specified numeric field is one of the top n values.
Total.isTopNPercent( )	Returns a boolean value that indicates if the value of a specified numeric field is one of the top n percent values.
Total.last( )	Returns the value of a specified field in the last data row.
Total.max( )	Returns the highest value of a specified field in the set of data rows.
Total.median( )	Returns the median, or mid-point, value for a set of values in a specified numeric field.
Total.min( )	Returns the lowest value of a specified field in the set of data rows.
Total.mode( )	Returns the mode, which is the value that occurs most often for a specified field.
Total.movingAve( )	Returns the moving average of a specified numeric field over a specified number of values. This type of calculation is typically used for analyzing trends of stock prices.
Total.percentile( )	Returns the percentile value for a set of values in a specified numeric field, given a specified percent rank.
Total.percentRank( )	Returns the rank of a number, string, or date-time value of a specified field as a percentage of the data set. The return value ranges from 0 to 1.
Total.percentSum( )	Returns the percentage of a total of a specified numeric field.
Total.quartile( )	Returns the quartile value for a set of values in a specified numeric field.
Total.rank( )	Returns the rank of a number, string, or date-time value of a specified field.
Total.runningCount( )	Returns the row number, up to a given point,in the report.
Total.runningSum( )	Returns the total, up to a given point, in the report.
Total.stdDev( )	Returns the standard deviation of a specified numeric field over a set of data rows. Standard deviation is a statistic that shows how closely values are clustered around the mean of a set of data.
Total.sum( )	Sums the values of a specified numeric field in the set of data rows.
Total.variance( )	Returns the variance of a specified numeric field over a set of data rows. Variance is a statistical measure of the spread of data.
Total.weightedAve( )	Returns the weighted average of a specified numeric field over a set of data rows. In a weighted average, some numbers carry more importance (weight) than others.
Total.ave( )	Returns the average value of a specified numeric field over all data rows.

---

2.Specifying alternate values for display

Data in a data source can sometimes be cryptic or appear in abbreviated form. For example, gender values may be M or F rather than male or female. Credit rankings may be 1 to 5 rather than excellent, good, average, fair, or poor.BIRT Report Designer enables you to specify alternate values to display if you do not want to use the original values in your report. You use a data element's map property to create rules for mapping data values. You create one map rule for each data value that you want to replace. For example, to map M and F to Male and Female, respectively, you create two map rules.How to map data values to different display values

Select the data element for which you want to replace values. The property editor displays the properties for the dataelement. Choose the Map tab at the bottom of the propertyeditor. Map List appears Choose Add to create a map rule. On New Map Rule, in the first field, specify the expression that refers to the data set field for which you want to replace values. If you need help specifying the expression, choose the ellipsis (...) button to use Expression Builder. The following is an example of an expression:row["creditrank"] In the second field, select an operator from the list. For example:Equal In the third field, specify the value to replace. For example:"A" and You must enclose string values in quotation marks (" "). In the last field, specify the value that you want to display. For example:Excellent Choose OK. The rule that you created appears in Map List. When you select the rule, the display value appears in the box at the right Repeat steps 3 through 4 to create additional rules, one for each data value that you want to replace. follwing Figure shows an example of three map rules that were created for the selected data element. Preview the report to check the results of your map rules.

---

Resort Columns After Report Has Been Run

BIRT strong suit is in the realm of online reporting. One of the features that is common with online applications that display information in a grid format is the ability to click on a column header and have the information resort based on that column. In this article I will show you how to do just that with BIRT.

The report that we will do this on is a report that lists customers and their payment information. It will be based off of the Classic Cars databases. These instructions are for BIRT 2.2 and will not work on previous versions.

1. Create a new report in an BIRT reporting project. Call this new report “customerPayment.rptdesign”.
2. Create a new Data Source from the Classic Cars Inc. Sample Database.
3. Create a new dataset using the following query:

select
 *
from
 CUSTOMERS,
 PAYMENTS
where
 CUSTOMERS.CUSTOMERNUMBER = PAYMENTS.CUSTOMERNUMBER
and customers.customernumber = ?

·         a. Name the parameter dsprmCustomerID

·         b. Under the dialog for the data set parameter, create and link to a report parameter called rptprmCustomerID. Set it as a text box entry.

4. Drag and drop the newly created data set over to the report design pane. Delete all columns except for the following

·         a. Customer Number

·         b. Customer Name

·         c. Payment Date

·         d. Amount

Figure 1. Report will Look Like after step 4

5. Create a new report parameter called rptprmSortOrder. Set it to allow null values and hidden.
6. Add the following script to the OnPrepare event of the table.

//We only want to add this into our code when the value is not null for the
//parameter sort
if ( params["paramSortOrder"].value != null )
{
 //Bring in the BIRT Report Model API and for CONSTANTS
 importPackage( Packages.org.eclipse.birt.report.engine.api.script.element );

 //Create a dynamic sort condition
 var sortCondition = StructureScriptAPIFactory.createSortCondition();

 //Based on the value of the sort parameter, set the appropriate key value for sorting
 switch (params["paramSortOrder"].value)
 {
     //Remember that for the key, we need to use the fully qualified row and field name as a string, not as a value
     case "date" :
         sortCondition.setKey("row[\"PAYMENTDATE\"]");
         break;
     case "price" :
         sortCondition.setKey("row[\"AMOUNT\"]");
         break;
 }

 //set condition to ascending order
 sortCondition.setDirection("asc");

 //Add to the table
 this.addSortCondition(sortCondition);
}    

7. In the header row, we need to create hyperlinks that will call this report, and pass in parameters to tell which column to sort by. So save the report as it is, othehrwise the parameters will not show up in the drill down dialog. Select the PaymentDate column header, and create a hyperlink like so:

·         Select drill down

·         Link to the customerPayment.rptdesign file

·         Select the rptprmCustomerID field, and set the value to params["rptprmCustomerID"]

·         Select the rptprmSortOrder parameter, and set the value to “date” with the quotation marks.

·         Set to open in the same window.

·         Do the same thing for the Amount column, except set the value of rptprmSortorder to “price”.

With that done, now you can reorder the report in view time when the user clicks on the date or the amount columns. With a little more logic developed in you can have the report do both ascending and descending sorts, and even have it refresh
the report without having to refresh the viewing page.