Hello fellow cognites!
I am trying to build a report which includes a field with firstnames from people, the customer only wants to see the first name in the field, and not the rest. So I am looking for the following:
Field contents: "Bob Larry Smith the 3rd" should show "John"
Field contents: "Bob Warren Rose" should show "Bob"
Field contents: "Bob Smith" should show "Bob"
Field contents: "Warren Angle James" should show "Warren"
Field contents: "James" should show "James"
Basically anything before the first space, or when there is only one word then that whole field.
Is there something like a function like First_Word([Model].[Subject].[Field])????
Any advice appreciated, oh I can do this in Framework manager too however would rather not.
Ronald
ps Cognos 8BI Report Studio MR1
Something like:
substring(dataitem,1,(position(' ',[dataitem])))
You may need to adjust with -1 somewhere to get the proper cut-off, but you'll get the idea..
The code Blom suggested will work fine, but add -1 to it to get the exact string without a white space
substring([Name],1,position(' ',[Name])-1)
Thanks fella's I'll give that a go, would this also work for fields without a space in it? so for instance where its only "Bob" or do I have to IF a rround this?
Found it out, I changed it to the following and it works great:
IF (position(' ',[Interface Files].[Employee].[FirstNames]) > 0 )
THEN (substring([Interface Files].[Employee].[FirstNames],1,position(' ',[Interface Files].[Employee].[FirstNames])-1))
ELSE ([Interface Files].[Employee].[FirstNames])
Ronald