Create comma separated list from table column data

I made a report to show assets that weren't on the appropriate PM template and to make it easy to add the exact assets on the PM template phase screen, I wanted to convert column data into a single array or comma separated list. I did some research and cobbled together a couple online examples to solve the problem. 

On the data set beforeOpen script:

footlist = "";

On the data set beforeOpen script:

foolist +=row['asset_tag'] + ", ";

On the layout, I added a dynamic text following the table:

foolist=foolist.replace(/,\s*$/, "");
foolist;

Here is how it looks in the report designer.

Here is how it looks on the report. That list can be copied and pasted into the template phase to search for assets. This is especially helpful if you have a template requiring multiple asset classifications in multiple buildings as it defines the exact list of assets. 

This approach could also be used for building lists of shop people to search for daily assignments to compose a url. 

Concatenating rows of data into a single row

I wanted to create a report that would feature a hyperlink to AiM daily assignment browse screen that required a "in" operator with comma seperated values. I made a separate data set that looked up all the shop people for a particular shop and then used the following script to concatenated the rows into a single row, separated by the URL encode for a comma so that I could make a dynamic hyperlink to the daily assignment browse. 

Here's the script:
DECLARE @Txt1 VARCHAR(MAX)
DECLARE @shop varchar (20)
SET @Txt1=''
SET @shop='WC03' 
SELECT  @Txt1 = @Txt1 + shop_person +'%2C'
FROM    ae_l_shp_d  
where shop=@shop and date_to is null
SELECT  @shop as shop, LEFT(@Txt1,LEN(@Txt1)-3) AS shop_person

So instead of getting data like this:

 I get data that looks like this:

I used the hyperlink editor to dynamically build my hyperlink with these values.

Similarly, I converted sql datetime to URL date time using this script. 

select convert(varchar, GW_OPENS_DONES.dates, 23) + '%2000%3A00%3A00.0' as url_date

to make values like this:2015-11-11%2000%3A00%3A00.0

Table Visibility

When you want to hide a whole table if there is no data in the dataset, use this in visibility:

if(Total.count() <1){true}

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"}

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.