using Replace function with concatenated data items

[kswaringen]

Hi,

I'm new to Cognos (and I should also add that my company is using a cloud-based project management application that features Cognos Report Studio as a reporting tool--we do not have any Cognos admin rights).

I am attempting to get a concatenated string of values from 5 data fields (Author1, Author2, Author3, Author4, Author5).  I'd like the result to be along the lines of "AuthorAAA; AuthorBBB; AuthorCCC; AuthorDDD; AuthorEEE."

One or more of the fields can contain a null value.  Since this messes up the concatenation, I've set the data items to display the @ symbol if the value is null.  This allows me to concatenate the values--e.g., AuthorAAA; AuthorBBB; AuthorCCC,; @; @.

I then tried using the Replace function to replace the instances of @ with a blank string ('').  This causes Cognos to give an error: "An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'" ("UDA-SQL-0219 The function "replace" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042").

The Replace function does work when I apply it directly to fields in my report.  It just doesn't seem to be able to process the results of another function (in this case the concatenation).  Is there any way to resolve this?  I'm stumped.

Thanks,
Kristen

[BigChris]

Hi Kristen,

This might be a bit of overkill, but I'd be tempted to use a couple of case statements. Obviously it depends on what your data looks like in the first place, but you could come up with something along the lines of:

Code Select Expand


Case
  when [YourAuthorField] is missing then ''
  else
  Case [YourAuthorField]
    when 'Author1' then 'AuthorAAA'
    when 'Author2' then 'AuthorBBB'
    when 'Author3' then 'AuthorCCC'
    Else 'Undefined'
  End
End

There's probably a more elegant way of doing that, but I haven't had any coffee yet  :D

[kswaringen]

Hi BigChris,

Your example got me trying out different case statements, and I think I finally cracked it!  Here's a bit more context to explain what I did.

The table containing the author information has three fields:

• AuthorNumber (an auto-incremented ID number--e.g. 1, 2, 3)
• Author Name
• Credentials

I then created a couple of data items:

AuthCred

Code Select Expand


CASE
WHEN ([Credentials] is null) THEN ([Author Name])
WHEN ([Credentials] is missing) THEN ([Author Name])
WHEN ([Credentials] = '') THEN ([Author Name])
ELSE ([Author Name] || ', ' || [Credentials])
END


FirstAuthRecord

Code Select Expand


minimum ([AuthorNumber])


LastAuthRecord

Code Select Expand


maximum ([AuthorNumber])


Author1

Code Select Expand


CASE
WHEN ([AuthorNumber] = [FirstAuthRecord]) and ([LastAuthRecord] = [AuthorNumber])
   THEN ([AuthCred])
WHEN ([AuthorNumber] = [FirstAuthRecord]) and ([LastAuthRecord] <> [AuthorNumber])
   THEN ([AuthCred] || '; ')
ELSE ('')
END


Author2 (Author3, Author4, and Author5 fields follow this model, but instead of +1, they use + 2, + 3, and + 4, respectively)

Code Select Expand


CASE
WHEN ([AuthorNumber] = [FirstAuthRecord] + 1) and ([LastAuthRecord] = [AuthorNumber])
   THEN ([AuthCred])
WHEN ([AuthorNumber] = [FirstAuthRecord] + 1) and ([LastAuthRecord] <> [AuthorNumber])
   THEN ([AuthCred] || '; ')
ELSE ('')
END


AllAuthors

Code Select Expand


([Author1] || [Author2] || [Author3] || [Author4] || [Author5])


In addition to the issues I noted in my original post (dealing with null values and removing unneeded delimiters), I had also found myself running into problems where records had been deleted, causing the values in the AuthorNumber field to no longer begin with 1 and/or to no longer be incremented by 1.  This coding appears to fix that.

So, as an example, if we originally had 5 author records for a project, and records 1 and 4 were subsequently deleted, the AllAuthors field now yields:

AuthorBBB, CPA; AuthorCCC, CPA; AuthorEEE, CPA

As a bonus, I find that the AllAuthors field exports to Excel as a single cell (instead of broken out like I had been seeing with my other attempted solutions).

This is probably much clunkier than it needs to be, but at least it's working.

Thanks again for your help!
Kristen