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

Nice Looking Date Parameters in Birt

When making a Birt report with parameters for a date range, I like to build data set(s) for the date parameter(s) from the same Aim table as the eventual results set. For example, if I'm reporting from the phase status table, (ae_p_pst_e), I'll select the dates for the parameter data set from that table. That way you don't have to build a bunch of static dates and your parameters always will match the data available in the table from which you are reporting.

We generally look at results on a monthly basis, so to make sure I get all records, I have my start date with a datetime of the first second of the month and the ending date with the last second of the month. And because in Birt you can display one column as the display value and have another column as the data value, I also use a shortened date format that is a little more visually pleasing for the display.

In short, create a data set called start date. Here's your query.

select distinct convert(datetime, convert(varchar,dateadd(day,-datepart(day,ae_p_pst_e.status_date)+1,ae_p_pst_e.status_date), 101)) as starting_date,
right(convert(varchar, status_date, 106), 8) as display_name
from ae_p_pst_e
order by date desc

Then create a data set called end date. Here's that query.

Select distinct dateadd(ss,-1, convert(datetime, convert(varchar,(dateadd (mm,1,status_date - day (status_date)+2)-1), 101))) as ending_date,
right(convert(varchar, status_date, 106), 8) as display_name
from ae_p_pst_e

Then create two report parameters called start date and end date. Within the config for each parameter, reference the respective data sets. Set the value column to the data set column with the last/first seconds format, and the display column to the more visually pleasing column. Set the sort to the display column and order descending to show the most recent dates at the top.

Then create your main data set. Maybe its something like

select * from ae_p_pst_e where status_date > ? and status_date < ?.

Create your two dataset parameters referencing the report parameters and let it rip. You'll have nice looking parameters that underneath are very specific in setting the limits of the month.

Shortening long string fields and removing carriage returns

Inevitably, your users will want a work order list report. In AiM, the work order and phase description fields are up to 255 chars long, which can eat up a lot of space in the limited space available on your report. To avoid killing tons of trees every time a report is printed, there are a couple options to shorten the text passed to the report.

Starting in Birt 2.5, you can control in the report editor whether wrapping is allowed or not. This can be found in data or column advanced properties/text/whitespace. Set the value to "no wrapping". I've found this to be of limited value because it doesn't control carriage returns in the data and will continue to wrap on the carriage returns.

see here for more details: http://www.eclipse.org/birt/phoenix/project/notable2.5.php#jump_15

Another option is to control the text formatting in the sql query.

Use the sql command SUBSTRING to limit the number of characters retrieved from the database.

select
substring (ph_desc, 1, 60) as ph_desc
from phase

which means that 60 chars, starting with the first character, of the ph_desc field will be retrieved.

Then, AiM allows users to enter carriage returns and line feeds, so I do a couple more modifications to remove those since they can also eat up space.

--to remove carriage returns
select
replace (ph_desc, CHAR (13), ' ') as ph_desc
from phase

and/or

--to remove line feeds
select
replace (ph_desc, CHAR (10), ' ') as ph_desc
from phase

All of these can be combined in the following way.

select
replace (replace (substring(ph_desc,1,60), CHAR (13), ' '), CHAR(10), ' ') as ph_desc
from phase

Report Item Visibility based on parameter

Birt allows users to hide data elements based on the parameter selection. For example, maybe you have a report with a chart at the top with a corresponding table at the bottom. You can allow the user to dictate whether the table would be visible.

Create a report parameter with static values of Y and N. Then select the report item (a table in this example) and select visibility in the properties editor.

In the check the hide element box and select the radio button "for all outputs". Click on the formula editor and paste in:

BirtComp.equalTo(params["Parameter_name"].value, 'N')

where Parameter_name is the name of your report parameter.

When the report is run, the user will be presented with a parameter that allows the user to hide or display the report item.

Multiple, Multi-Select Parameters

One limitation in Birt is the lack of a simple way to create multiple, multi-select parameters. What I mean by that is when you want to have parameters where the user can select more than one value for a parameter. This can be overcome by writing scripts that modify the query at runtime. There are two ways to do this, but one of them is much more powerful than the other, especially when you want to have multiple parameters.

Here's the first way and I think it is is an inferior solution compared to the second one described below. It's utility seems to be limited to reports with a single parameter. In this method, you write a query with no where clause. Create a report parameter. Make sure to click the Allow Multiple Values checkbox. In the screen shot below, I built the parameter off a data set from the database as opposed to hard-coding in the values.

Next, go to the dataset and click on the scripts tab. Set the drop-down box to 'Before Open'. Enter the following script with the appropriate modifications to match what you've named your parameter.

var endOfQuery = " WHERE workorder.proposal IN ('" + params["proposal"].replace(/,/g,"','") + "')";
this.queryText += endOfQuery;

Another way to view this would be:

var endOfQuery = " WHERE tablename.columnname IN ('" + params["parametername"].replace(/,/g,"','") + "')";this.queryText += endOfQuery;

This will append the where clause onto the end of the query. It may be possible to modify this script to accomodate additional where clauses. It's been a while since I used this method and I may have tried and failed or maybe I never tried it.

My current preferred method is to write the query, but to include the where clause. A script is still required, but write string variables in the where clause. The script replaces these strings with selected parameter values.

The where clause may be something like this:

where tablename1.columname1 in ('xxx') and tablename2.columname2 in ('yyy')

Here's the script.

this.queryText = this.queryText.replace("xxx",params["parameter1"].value.join("','" ))
this.queryText = this.queryText.replace("yyy",params["parameter2"].value.join("','" ))

where parameter1 refers to tablename1.columnname1 and parameter2 refers to tablename2.columname2

I like this for its simplicity and there doesn't seem to be a limitation on the number of parameters that you can have.

A note: no dataset parameter is required when this is used.

Hyperlinks from a Birt Report to Aim

Since many, if not all, records in Aim have a unique URL, you can create hyperlinks from a Birt Report back to the Aim application. This avoids the tedious searches by typing in the work order number, for example. BIRT provides a Hyperlink Editor for the creation of links to other BIRT reports, external content, and bookmarks within the same report. In this section we see how to use hyperlinks to link to AIM application.

In Birt, build your report. Then select the particular field for which the hyperlink is to be applied. Click on the properties editor and choose hyperlink. Select Link to and the hyperlink editor is displayed. In the hyperlink editor, select URI. Click the Fx button on the right to open the Expression builder. Type the URL of the AIM application surrounded by double quotes and end by referring the unique value that will direct the hyperlink to the corresponding Aim record.

A couple examples:
"https://gwuapp.assetworks.com/fmax/screen/WO_VIEW?proposal=" + row ["PH_WORKORDER"]

"https://gwuapp.assetworks.com/fmax/screen/SC_INVOICE_VIEW?tranxNo=" + row ["tranx_no_con"]

To find the exact URL, you can see it Aim, by doing a search and then selecting a record. The URL that is displayed is the unique url associated with that record. From that you'll understand the basic format, and how you need to modify it as a dynamically constructed hyperlink in BIRT.