Removing Hyperlinks from null values in a Birt Cross Tab

When you use dynamic hyperlinks from a cross tab that has some null values in the cells, with a slight javascript modification to your hyperlink, you can hide all the little blue underscores in the cells with no values.

if (data["count_Building/bldg_Work Code/craft_code"]!= null) {"https://gwuapp.assetworks.com/fmax/screen/SC_BROWSE?aeSCtrE.contractor="+data["contractor"]+"&aeSCtrE.statusCode="+data["status_code"]"}

or

if (summary data row!= null) {"hyperlink"}

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.

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.

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.

Great Article on SQL grouping by time periods

This is a great article on grouping my month and has been the foundation for developing the base queries that I use for many BIRT reports that often have a date component. I wanted to give the author the credit he is due.

http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

Birt Reports on Aim Desktop - Trailing 13 months

One of the great features of Aim is the ability to display Birt reports on the workdesk. This gives report designers the ability to create a reporting dashboard showing one or more charts in a single BIRT report.

I like to show the trailing 13 complete months in chart reports that auto-load on the work desk so that the user can see the annual trend and the same month from the year prior. I riffed of the last second of the month sql discussed in my "nice looking date parameters" article to come up with the where clause for the chart dataset.

First, read up on the dateadd function here. http://www.w3schools.com/sql/func_dateadd.asp

Maybe there's a cleaner way to do this, but it works.

Here's a simple application:

select ph_workorder, ph_date_created
from phase
where ph_date_created > dateadd (m, -13, dateadd
(ss,-1, convert(datetime, convert(varchar,(dateadd (mm,1,getdate() - day (getdate())+2)-1), 101))) )
order by ph_date_created