BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Realtime CMS Reporting from Infolytik (Opens a new window)  

General Notice: No events within the next 45 days.

How to change case?


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Jamesp
Principal Member
Principal Member



Joined: 12 Oct 2004

Posts: 160


flag
PostPosted: Tue Apr 26, 2005 12:07 pm 
Post subject: How to change case?

I am developing a universe and the source data is not consistant some are in upper case and some are in lower case. The reports are developed by the users using webi. So I would like to make all changes at the universe level.
Do any know how to change case to proper case at the universe level?

Thanks,

james

_________________
Born Curious


Last edited by Jamesp on Tue Apr 26, 2005 12:17 pm, edited 1 time in total
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21977
Location: Dallas, Texas


flag
PostPosted: Tue Apr 26, 2005 12:17 pm 
Post subject: Re: How to change case?

Most databases have a function called upper() or ucase() or toupper() or something like that. You would have to do that on all of your objects, I guess, if the data is not consistent.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2014-08-22 Yes, Virginia, You Can Refresh One Data Provider At A Time
• 2014-07-08 Airlines Could Save Millions in Fuel Costs By Providing Everyone An iPad
• 2014-06-24 Did Florida State Win National Championship By Using Big Data?
Back to top
Jamesp
Principal Member
Principal Member



Joined: 12 Oct 2004

Posts: 160


flag
PostPosted: Tue Apr 26, 2005 2:51 pm 
Post subject: Re: How to change case?

Does anyone know syntax for proper case??? That will be a great help.

Thanks,

jamesp

_________________
Born Curious
Back to top
David Smith
Principal Member
Principal Member



Joined: 10 Jul 2002

Posts: 136
Location: Toms River, New Jersey


flag
PostPosted: Tue Apr 26, 2005 2:57 pm 
Post subject: Re: How to change case?

James,
Which database are you using? Then just do a google search that info.. real straight forward...then you would add the command to the syntax in the object in the Universe.. e.g. upper(warehouse.last_name)

Or.. you can have your users rekey all the info with the Caps Lock on to ensure the data is in upper case. icon_biggrin.gif

-Dave

_________________
-Dave Smith
Business Objects Developer
Business Objects Certified Professional - BOE
Back to top
Jamesp
Principal Member
Principal Member



Joined: 12 Oct 2004

Posts: 160


flag
PostPosted: Tue Apr 26, 2005 3:10 pm 
Post subject: Re: How to change case?

I am using Teradata

james

_________________
Born Curious
Back to top
HenkK
Forum Associate
Forum Associate



Joined: 02 Mar 2004

Posts: 852
Location: Zwolle, The Netherlands


flag
PostPosted: Wed Apr 27, 2005 3:09 am 
Post subject: Re: How to change case?

To use strings in the WHERE condition the easiest way is to convert either both values to lower of upper case. A disadvantage is that sometimes index cannot beused because a function is used on the indexed column.

If you want to display the data in a uniform way in the report you can use the InitCap function (converts "JAnsEN" into "Jansen") in reporter or a database function that does the same.

HenkK

_________________
BO 4.1.6 ~ XI, Cognos 8 BI icon_wink.gif, Microsoft BI 2005/2008/2012 icon_wink.gif, Oracle/SQL Server
Back to top
Sridharan
Forum Enthusiast
Forum Enthusiast



Joined: 08 Nov 2002

Posts: 1833
Location: India (MA, US)


flag
PostPosted: Wed Apr 27, 2005 1:09 pm 
Post subject: Re: How to change case?

Jamesp wrote:
I am using Teradata

james

There is no functions readily available to convert a string to Proper Case in Teradata... One way is as suggested by Henkk you can do it at the Reporter level... Or create a user defined function which takes a string and converts it to a Proper Case string literal...

_________________
~Sri~
"An eXpert in anything was once a beginner."
Back to top
vparikh1
Senior Member
Senior Member



Joined: 20 Feb 2003

Posts: 50
Location: Mumbai


flag
PostPosted: Wed Apr 27, 2005 3:22 pm 
Post subject: Re: How to change case?

Upper(), Lower() works fine with Teradata. Use it in Designer to change the case for the objects that you want.

Vivek
Back to top
Sridharan
Forum Enthusiast
Forum Enthusiast



Joined: 08 Nov 2002

Posts: 1833
Location: India (MA, US)


flag
PostPosted: Wed Apr 27, 2005 3:41 pm 
Post subject: Re: How to change case?

vparikh1 wrote:
Upper(), Lower() works fine with Teradata. Use it in Designer to change the case for the objects that you want.

Vivek

James was looking for way to convert a string to ProperCase...

_________________
~Sri~
"An eXpert in anything was once a beginner."
Back to top
Anil Kumar
Principal Member
Principal Member



Joined: 19 Apr 2005

Posts: 259


flag
PostPosted: Thu Apr 28, 2005 3:00 am 
Post subject: Re: How to change case?

Do you have something like SubStr in Teradata to extract strings. In that case try this,

If it is a single word (A string without any space)
Extract the first character and covert this to upper and extract the remaining character and convert to lower case and then add them back.

Bu if you have more than one word in the string, you have to go for something similer to userdefined functions in ORacle. Is it there in TeraData?

Then you have to loop through the string and extract each word and then apply the above logic for each word then concatenate.

Hope this will help.

Anil
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11551
Location: London


flag
PostPosted: Thu Apr 28, 2005 5:50 am 
Post subject: Re: How to change case?

It should be norted that 'cleaning' the data at a universe level can cause any indexes on your database to not be used.
Can this data not be cleaned initially and then put into the correct case as part of an ETL process?
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0494 seconds using 17 queries. (SQL 0.0023 Parse 0.0362 Other 0.0109)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo