changing case in cognos

[jaymoore1756]

I need to change data in a file that is all in upper case to uppercase and lower case. Does anyone have any code to do this in Cognos ? Thanks

[BigChris]

You can convert a string to be all upper case using Upper([string]), or to all lower case using Lower([string]), but I don't know of a function that will convert it to sentence case. If you just want to set the first character of the string to be upper case you could construct a field to do that...let me know if you can't make that happen.

[blom0344]

I need to change data in a file that is all in upper case to uppercase and lower case. Does anyone have any code to do this in Cognos ? Thanks

You mean you need every first character of a word in upper case and the rest in lower?  Or just the first one of a sentence?  Blurry to me   ;D 

[bdbits]

If you want "proper" capitalization for example with names, all I can say is ... good luck! It can be surprisingly complex. This sort of thing is best captured at the time of entry, otherwise I can not encourage messing with it in code. But if you must, I recommend doing it at ETL time, or at least putting the expressions into table attributes within the package so you have one place to fix it when it does not work as expected.

[jaymoore1756]

Yes it is blurry as mentioned above and also complex.. But what the company is looking for is data in the fields. the soft ware does everything in CAPS so they are looking for a way to make CAPS Caps. Never tried this before and have not been successful as of yet.

[BigChris]

Ok - if you want to turn "TELEPHONE" into "Telephone" you'll need something like trim(UPPER(substring([Field],1,1)) + LOWER(substring([Field],2,99))

I've been lazy and used 99 as a large number to cater for the length of the field - you could be more elegant and calculate it properly if you wanted to, in which case you wouldn't need the trim function.

If you want full sentence case, or even making the first letter of each word a capital letter, that would be MUCH more difficult, and definitely beyond my humble capabilities.

[blom0344]

Yes it is blurry as mentioned above and also complex.. But what the company is looking for is data in the fields. the soft ware does everything in CAPS so they are looking for a way to make CAPS Caps. Never tried this before and have not been successful as of yet.

I think it is a bit far-fetched / naive to expect Cognos to correct this. A reporting solution using for data-cleansing will get you only  so far..

[Lynn]

I agree with Blom!

There is an Oracle INITCAP function which would translate "HELLO WORLD. IT IS A BEAUTIFUL DAY" to "Hello World. It Is A Beautiful Day". I'm not aware of a similar function in any other DBMS although creating a user defined function would be possible. Otherwise string manipulation like BigChris suggests is an option.

All of these ideas are BAD in terms of best practice, but if you can't alter the database content for whatever reason, then consider creating a view in the database that performs these manipulations. Reporting off the view would be less bad than putting this sort of cleansing in every report.