One of the first rules I learned when creating reports was to get your report's select statement to do as much of the work for you. When the select statement is properly formatted, the statement can do a lot of the functional work you'd have to setup in BIRT. This can be a more foolproof way to ensure you get consistent reporting results because if the raw data looks correct in you SQL query tool, you can be assured it will look good in your report too.

A common select statement for a report would be for the data to have a parent record and multiple child records.

select workorder.wonum, wplabor.laborcode 
from workorder
join wplabor on workorder.wonum=wplabor.wonum and workorder.siteid=wplabor.siteid

In this example statement, normally when you have multiple child records to a parent record, SQL will return the results in individual rows. So if you had information about a WO number and the planned labor on the job, the results would like this:

Pulling that information into a BIRT report would cause every LABORCODE record to show up like this:

• 32563
  • RHODESJ01
  • ROMANN01
  • STARKT01
• 32646
  • BARTOC01
  • ROGERS01
  • BARNEB01

But for information like planned labor lists, most supervisors want to see the information as a single list, not multiple rows. There are some BIRT report tricks you can get records to show inline, but it's always better to get the raw data in the format you want first.

stuff function

SQL Server has a function called stuff that can be used to pull multiple records into a single field. The formal definition of the function from Microsoft is: ¹

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

The stuff command basically is a fancy concatenate function for SQL results. A basic stuff example would be: ²

select stuff('abcdef', 2, 3, 'ijklmn');

This would return the result of:

aijklmnef

The stuff function has 4 arguments:

stuff ( character_expression , start , length , replaceWith_expression )

1. character_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
2. start: Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.
3. length: Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.
4. replaceWith_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.

Making the magic

While the stuff function gets things rolling, it's a second function - for xml path - that makes some real magic. Using this function, with a sub-select statement, can transpose multiple records into a single result. The second function is actually two parts - the for xml function and the path mode modifier. The for xml is used to transpose a queries result into XML format. The path modifier can be used to modify a wrapper in the generated XML.

The stuff function is iterative, but in conjunction with the for xml path, when multiple records are returned from a selection, the sub-select statement will continue to concatenate the results. Pulling all of this together gives us a select statement like this:

select workorder.wonum, stuff((select ', ' + laborcode from wplabor 
    where wplabor.wonum=workorder.wonum and wplabor.siteid=workorder.siteid 
    for xml path('')), 1, 1, '') as [labor_code]
from workorder
where workorder.historyflag=0 and workorder.istask=0

In the statement above, the entire sub-select statement (including the for xml path) is the first part of the character_expression section. Setting the start and length to 1 allows the concatenate to appear to string itself together. The final replaceWith_expression is set to '' so the iterative process won't rewrite over itself.

The previous select statement will return the following results:

So now using the stuff modifier in your report will keep all of the planned labor names inline.

• 32563 | RHODEJ01, ROMANN01, STARKT01
• 32646 | BARTOC01, ROGERS01, BARNEB01

So now you can use the stuff and for xml path functions to allow your select statement to transpose multiple records into a single line.