0

I am getting value from the single column of a database like this in the report

Name: Raghu Age: 23 blood group: o+ Address: Bangalore

But I need it to appear like this

Name: Raghu
Age: 23

blood group: o+
Address: Bangalore

I mean each thing in a new line

Raghu
  • 817
  • 1
  • 7
  • 21

1 Answers1

0

First, obligatory advice: Create a new table with Name/Age/Address/Bloodtype etc. fields and parse this data out before loading into Cognos.

However, text parsing in cognos is possible in a pinch.

Lets assume your field with all data in it is called [field1].

Create a Query Calculation field for each data point. I will use name as an example:

[Employee]

SUBSTRING([field1];POSITION('Name:';[field1])+5);POSITION('Age:';[field1])-POSITION('Name:';[field1])+5)

Substring parses out data from field1, starting 5 characters after where it finds 'Name:' and ending where 'Age:' begins (subtracting the start position to get a length).

[Age]

SUBSTRING([field1];POSITION('Age:';[field1])+4);POSITION('blood group:';[field1])-POSITION('Age:';[field1])+4)

Age is similar in format. You subtract 4 instead of five because 'Age:' is four characters.

Use that method to grab all but the last column. Lets say the last column is 'Address:', capture it like this:

SUBSTRING([field1];POSITION('Address:';[field1])+8);-1)

The -1 will tell the SUBSTRING command to continue to the end of the line of data.

Damienknight
  • 1,876
  • 2
  • 18
  • 34
  • You still have to add the line feed/new line characters to obtain the desired result. – Marcus Rickert Apr 21 '15 at 04:30
  • His data is a single 'column', indicating it already ends each line. Cognos reports automatically put each row of data on a new line in a 'List', so no addition of New Lines will be required when parsing this in Cognos. – Damienknight Apr 23 '15 at 21:36