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.
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:
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.
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: 1
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.
stuff command basically is a fancy
concatenate function for SQL results. A basic
stuff example would be: 2
select stuff('abcdef', 2, 3, 'ijklmn');
This would return the result of:
stuff function has 4 arguments:
stuff ( character_expression , start , length , replaceWith_expression )
- character_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
- 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.
- 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.
- 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
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.
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:
|32563||RHODEJ01, ROMANN01, STARKT01|
|32646||BARTOC01, ROGERS01, BARNEB01|
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
for xml path functions to allow your select statement to transpose multiple records into a single line.