Problems with "ANY" data types in Birt 2.5.1

We recently had a performance issue with the Aim application that was attributed to "ANY" data types in some of our Birt reports. Apparently, Birt prefers data types other than "ANY" such as string, integer, etc. A review of all 73 of our custom reports indicated that "ANY" data types occurred in computer data set columns and parameters. Best way to track these down is to look at your data sets in the output columns screen and in the parameter set up. As a final check, do a text search for "ANY" in the xml. We found cases where the parameter was set to string, but was any in the xml. Thanks to Greg Williams for going through all 73 of the reports.

Cascading Date Range Parameters

I've written about date range parameters in an earlier post. In that post I wrote about using a group-by month sql query to dynamically create parameter lists. As I used the technique for a while, I noticed an irritating flaw. Because the two parameters were independent of each other, it was possible to request an end date that was before the start date, which generated 0 records when the report was run. Had to be a better way. I knew about cascading parameters, but found that their use was too limiting for the reports I like to create. But maybe I could use them to make a better date range parameter.

So using the technique described in the earlier post, I made two data sets, one called begin date and one called end date.

Begin date has a query like:

select distinct convert(datetime, convert(varchar,dateadd(day,-datepart(day,ae_s_fnd_a.tranx_date)+1,ae_s_fnd_a.tranx_date), 101)) as starting_date,

right(convert(varchar, ae_s_fnd_a.tranx_date, 106), 8) as display_name

from ae_s_fnd_a

end date has a query like:

Select distinct dateadd(ss,-1, convert(datetime, convert(varchar,(dateadd (mm,1,ae_s_fnd_a.tranx_date - day (ae_s_fnd_a.tranx_date)+2)-1), 101))) as ending_date,

right(convert(varchar, ae_s_fnd_a.tranx_date, 106), 8) as display_name

from ae_s_fnd_a

where ae_s_fnd_a.tranx_date > ?

Then make a cascading parameter, with the first parameter called begin date. Make it a list box, dynamically created, and reference the begin date data set. Set it to order descending by starting date.

Then add another parameter called end date. Reference the end date data set. Everything else same as above.

Then go back to your end date data set. Create a data set parameter referencing the begin date report parameter.

End result is that your end date parameter will be filtered by dates AFTER your start date. So you just cant pick a date BEFORE your start date.

Hyperlink to a new email off Birt Report

I made a series of contact list reports off the Human Resources module for use as a phone/email directory and I wanted to make the email addresses hyperlinked so that the default email application would start an email when the address was clicked off the report.

Make a text field in the email column of your table. Set type to html.

I tried to punch the code into the body of my post, but the browser is reading it as html and not showing all the code, so check out the image for the code.

Creating Birt Sub-Reports Using Dataset Parameter Binding

I'm going to write about a feature that I only recently discovered and wished I knew about a long time ago. The concept is how to do sub-reporting and neatly tie the master value with sub-reports using something called dataset parameter binding.

For example, let's say I have a master record like a service contract, and I want to report all the associated invoices associated to the service contract.

I have a primary dataset of service contracts, and a secondary dataset of invoices.

Make a table for service contract. Inside the detail row of the service contract table, place another table for invoice data.

Create a dataset parameter in the invoice dataset. It is not tied to a report parameter, so you are forced to choose a default value. Go ahead and do this even though it seems weird.

Then go to your invoice table. Select binding and click on dataset parameter binding. Edit the existing binding by identifying the corresponding dataset row from the contract table. This ties the outer table to the inner table.

Once you get this simple example you can expand the concept. I made a dashboard for shop supervisors a combination of 5 charts and tables all using dataset parameter binding that works great in the Aim Workdesk.

Alternative Row Highlighting in Birt

To improve readability of a list report, alternative row highlighting can be applied to the detail row of a table. Select the entire row, and then click on the Highlighting Tab in the Property Editor. Click Add and then paste the following text

row.__rownum %2

into the "if the following condition is true field". Set the next field to equal to and then put a 0 into the last field. Then down at the bottom, select "background color" and pick out a LIGHT grey color. You'll be presented with basic colors, but I like to pick a custom lighter grey because it prints better.

Nice looking Percents on Mouse-Over Chart Interactivity in Birt

If you're incorporating a mouse-over interactive feature in a Birt Chart and your data point is a percent, the resulting data flash can look sort of ugly (like 0.021200). You can control this by editing the format of the tooltip text.

a = row["PERCENT_CLOSED"]
a = a.toPrecision(2)*100
a = a+'%'

This should result in the percentage being displayed at as 21% as opposed to 0.021200. Occasionally I get some strange results like 21.0000004% that I can't really explain.

Interactive Birt Chart Output Formats - Which one do you use?

If you're thinking about making some cool charts for your Birt reports, one of the first decisions you'll have to make is which output format to use. The choices are PNG, JPG, BMP, SVG. If the report doesn't need to feature any interactive features, you could pick JPG or BMP. To take advantage of the interactive features of Birt, you'll need to choose between PNG or SVG. And that's where things get a little more complicated, because different browsers render the interactive features of PNG and SVG differently. Also, some interactive features work differently in SVG than PNG.

First, read up on PNG and SVG on Wikipedia to get a little background. One of the things you'll find out is that SVG is not supported by IE 8 and only marginally by IE 9. However, I've noticed that some Birt interactive chart features don't work even in browsers that 'support' SVG. There is an adobe plug-in for IE that allows it to support SVG, but I'd like to avoid plug-ins if possible. Read more about browser support for SVG here, where some saint is documenting the results of performing 280 different tests on every known web browser.

For the reasons described above, I started using PNG. There were a couple cool interactive features that were supported by PNG, but it seemed like the best fit.

So here are the features I like best. I like to show tooltips on Mouse Over. This will allow some additional data to show up in a little window when the user hovers the mouse pointer over a data point. I find this useful because it's always a challenge to fit everything on the chart and this is a way to make the chart as big as possible while still allowing the user to get some more contextual information. As an example, let's say the y-axis is ae_s_bld_c.bldg, which is a building number or address. Because not all your users can quickly perform the mental translation from building number/address to building description, you could flash the more commonly known building description as a tool tip.

Another application for the tooltips would be when a chart has been made that normalizes the data based on percentage and you could use the tool tip to show the actual count of work orders, for example. You could also stitch a couple of these together into the same tool tip.

The other feature that I really like is hyperlink. You can drill-through from the chart report to another chart report or text report or right into a browse or individual record in the Aim application. You can see another article that I've written on that topic here.

I'll get some pictures up when I get a chance, plus some examples of the tooltips and hyperlinks code.