Scripted Field Value

Hello,

What is the easiest way to take field values returned in multiple rows of a SQL dataset and concatenate them into a single string field (comma separated)?

Let me explain using a Classic Cars example... Customers make many orders. I can create an inner join on Customer table and Orders table that will return a record for every order a customer has made (provided I pass a parameter for customer number into my Order dataset).

What I want is one record per customer, with a single field that contains all of the order numbers for that customer.

I thought I would be able to use an aggregate function but I couldn't get that to work. The best I can do at this point is create a sub-table for the many side of the relationship and make it 'look' like one dataset.

Any suggestions would be appreciated.

Thanks in advance,

Rob

Find more posts tagged with

Comments

bhanley

You can certainly achieve this in a script as the table is rendered.<br />
<br />
A more efficient path would be to leverage Information Objects, where a more agile query syntax can be leveraged. Something like this:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
DECLARE @List VARCHAR(5000)
SELECT @List = COALESCE(@List + ', ' + OrderID, OrderID)
FROM Orders
Order By OrderID</pre>
<br />
That is not tested SQL, but it is pretty close to what you would need to get a comma-separated list like you are looking for.

rtruban

Thanks for the quick response!

Can this go directly in the Dataset Query somehow? BIRT doesn't like the '@' sign.

Or do I need to put this in a script on the afterOpen/beforeClose of a dataset?

Sorry for the newby questions, I haven't playing with scripting much yet.

Thanks in advance,

Rob

bhanley

To implement the entire solution in BIRT, you will need to loop through the data as the table is rendered and concatenate the values manually (the onRender method of the table could be one place, or the onCreate method for the data set). If you have access to the Information Object designer, you will be able to use the more complex SQL I pasted in my initial post.

I am not sure what the eventual data source is that you are targeting, but you may also want to consider a scripted data source. This is a data source that can be target to exactly meet your needs. There are a lot of pointers and help on BIRT Exchange to get you going if you want to go that route.

BIRT is EXTREMELY versatile in how it can provide access to your data. The question is not generally if BIRT can access your data (It almost always can), but instead deciding which of the available methods you want to use. It comes down to the needs of your report and how that report needs to implement the data elements.

rtruban

Thanks again for the quick response. I believe a scripted data source is the way to go for me proved I can figure it out.

I've looked at the examples on BIRT Exchange and ran through the tutorial in the "Integrating and Extending BIRT 2nd edition" book.

If I can do this in script and not Java, it will be easier on me I think... But what is not jumping out at me is the way I connect a script to an existing data set or data source. The examples illustrate how to create data with counters and do not show how to call/use other report objects inside the script. Can I reference the Orders dataset inside the scripted data source, and iterate through the rows of the Orders dataset? Or will the scripted dataset have to connect directly to the underlying Classic Cars database? Not sure if that makes sense or not the way I worded it...

Any 'more-sophisticated' scripted data source examples you could point me to would be much appreciated.

Thanks again,

Rob