Need help building a Data Item in Cognos Report Studio

Question

So I have a table with employee name, job and shift. Each employee (Row) has a unique name, a non unique job and one of 4 shifts (A, B, ,C or D). I want to create a Data Item called "Shift A Employees" that that for each job it spits out one string that contains all the employees who work that ob on that shift for example:

I have this

And want to be able to have this

How would I go about writing my expression definition for my "Shift A Employees" data item?

Answer 1

Creating a data item that does that could be rather involved. Why not drag a repeater table onto your list? You will need to add two data items to the query. One is running-count(1 for [Job]) (I think. I don't have my example in front of me right now.) The other compares that value to the maximum value for the job. If the values match it returns nothing, if it doesn't it returns a comma. Here's the pattern:

case
when [rownum] < [maxrownum] then ','
else ''
end

Name it comma. Then you just drag [Name] and [comma] to the repeater table. Make the repeater table 1 row high and more columns wide than you think you'll need. You'll need to define a master-detail relationship for the repeater table.