COGNOiSe.com - The IBM Cognos Community

Hi,

This is a question similar to one raised recently but I'm hoping that there might be another answer  ::)

I'm trying to amalgamate the values from multiple rows into one footer...eg

If a shop wanted a report showing a summary of what countries they has sold their stock to and the data was held in a table like this:

Bat                Canada
Bat                Denmark
Bat                Spain
Moose            Finland
Moose            Spain
Snake            Finland
Snake            Greece
Snake            Italy
Snake            Spain
etc


The report that I want to create would show

Bat            Canada  Denmark  Spain
Moose        Finland Spain
Snake        Finland Greece Italy Spain
etc   

I've tried all sorts of combinations of concatenations, grouping and associations and have failed to stumble on the correct way of doing this.

Hopefully someone can point me in the right direction (if one exists)

Thanks

Lee

If there is a finite list of countries and you know what they are (and there aren't too many), create a calculation for each one in your report like this:

Finland = if (country = "Finland") then ("Finland") else (" ")
Canada = if (country = "Canada") then ("Canada") else (" ")

Group on the Bat/Moose etc column in your example

Create a further set of calculations, one for each country:

FinlandMax = Maximum(Finland)
CanadaMax = Maximum(Canada)

Associate each of these with the first grouped column.

Create another calculation which is the concatenation of each of the 'Max' calculations:

CountryList = trim-trailing(FinlandMax) + " " + trim-trailing(CanadaMax) + " " + ......

Again, associate this with the first grouped column.

Include the Bat/Moose column and CountryList in the report output.

It will still work if there are a lot of countries, it'll just be a pain to do. And any time that a new country is added to the data, you'll need to amend the report.

So it's not pretty, but if the country list is fairly fixed then it'll do the job ok. 

Thanks ianderson,

That method crossed my mind but unfortunately my report is actually for which ranges manufactured Components are being used,  and the ranges change monthly, weekly (sometimes it feels like hourly).

I'll have to find another way of skinning this cat.

Thanks for your help, 

Lee