Concatenate Data Items in a List Column

Concatenating in Cognos Analytics allows you to combine 2 data items in a list column.

For example, if you have First Name and Last Name data items, you might want them to appear together in a single "Name" column .

There are 2 methods to concatenate data items in a list column. We’ll use the name example above to demonstrate these methods.

  1. Use a query calculation

  2. Unlock page layout and drag multiple data items to a list column

Method 1: Use a Query Calculation

Use the screenshot to follow the steps below.

  1. From Tools, drag a Query Calculation to your list. 

  2. In the Name field, give your data item a name.
    Since we are creating a concatenation of first and last name, we’ll call it 'Name' for this example. 

  3. Into the Expression Definition field:

    1. Drag First Name.

    2. Type + ‘ ‘ + 
      That's: plus, single quote, space, single quote, plus. The plus sign combines data items, and the single quotes before and after a single space adds a space between first and last name values.
      TIP: Don’t copy and paste from this document. Characters from other applications don't always copy in the same format Cognos Analytics expects.

    3. Drag Last Name.

  4. Validate your expression, and then click OK

  5. Run the report to test your results.

Method 2: Unlock Page Layout and Drag Multiple Data Items to a List Column

In the reporting tool on a report page, in order to insert multiple objects in a single list column, you must unlock the page objects. The page is locked by default to prevent you from accidentally inserting multiple data items to a column. 

  1. Click the Lock Page Objects icon.
    The icon appears unlocked.

  2. Drag the First Name data item to your list.
    NOTE: If you want to arrange your data items in a table (for example to format a mailing address), insert a table into the list column, and then insert data items into the table.

  3. To add a space after First Name, from the Tools, drag a Text item to the First Name column and drop it to the right of the First Name data item, and then type a space.
    NOTE: As an alternative, you can add a space by adding padding to the right of the First Name data item.

  4. Drag the Last Name data item to the First Name column being mindful to drop it to the right of the space you added in the previous step.

  5. Select the First Name column heading, and then in the Properties pane, in the Label field, type Name.
    The column heading is renamed.

  6. Click the Lock Page Objects icon.
    The page is locked. 

  7. Run your report to test the results of your concatenation.





Comments (0)


Brown Community members, log in to submit a comment.

Top