Need text in Tital Case

Hey Friends...

In database I have description field in Uppar case but I want to display it in Title Case ( I tried ProperCase but its not working in Crystal report 8.5)

Like :

In database I have description like : TAKE BACK CONTROL OF YOUR CHANGE

But in report I want like : Take Back Control Of Your Change

and I am using crystal report 8.5 so can anybody help me please

Thanks

-Jayesh Mendpara
jmendpara@.gmail.comyou didn't mentioned what database u r using

oracle supports INITCAP funtion

im sure equivalent, available in SQL Server.

so u can handle that at database level.|||Hello sraheem...

I have the same situation as this initial post. However my situation is not really connected to a db. The initial dollar amount in the example does come from a db however the conversion just takes place within the report itself. I have converted the numbers to text and need to get this in title case. Example:

($583.00 converted to - five hundred eighty-three and xx/100)

I need the conversion to read: Five Hundred Eighty-Three And xx/100

Any assistance you could provide would be greatly appreciated. I thank you in advance for your time!|||Create a formula for the field you want to display as such and use something like this for the formula:

ProperCase ({MyTableNameGoesHere.MyFieldNameGoesHere})|||Will this work in 8.5?|||I think we already established that it won't.
Relatively simple to implement in a formula though.|||Check out this link for info on how to do it in 8.5.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do;jsessionid=B649460555EE48B11E10F040D88394B5?cmd=displayKC&docType=kc&externalId=c2011637&sliceId=&dialogID=360384&stateId=1%200%20356275

Need replacement for sybase list aggregate

Sybase has an aggregate function called list that concatenates the
results into a scalar.
sybase eg:
select dept,list(description) from parts group by dept
This query would return one row for each department. Each row would
contain all the description text for that department.
Is there any way to do the same in TSQL without using a cursor? I'd
like to do this in a subquery.
TIAThere is none. There are certain workarounds though; see:
http://groups.google.com/group/micr...3e?dmode=source
In SQL 2005 you can use some complex methods using XML with CROSS APPLY, but
the most reliable way is to use the server to return the tabular resultset
and use a client side programming language to get concatenate/ format the
way you need. In certain situations, you might be able to use the PIVOT
operator as well.
Anith|||Consider 'type' is your department and 'name' is the descriptions then this
should do what you want...
SQL Server 2005 only...
select type,
(
select name + ',' as [text()]
from sys.objects soi
where soi.type = t.type
order by name
for xml path( '' ), root( 'sysobjects' ), type
)
from ( select distinct type from sys.objects ) as t
Will give output like this...
D <sysobjects>DF__spt_value__statu__436BFEE3,</sysobjects>
IT
<sysobjects> queue_messages_1003150619,queue_messages
_1035150733,queue_messag
es_1067150847,</sysobjects>
P <sysobjects> sp_MSrepl_startup,sp_MScleanupmergepubli
sher,</sysobjects>
S
<sysobjects> sysrowsetcolumns,sysrowsets,sysallocunit
s,sysfiles1,syshobtcolum
ns,</sysobjects>
SQ
<sysobjects> QueryNotificationErrorsQueue,EventNotifi
cationErrorsQueue,Servic
eBrokerQueue,</sysobjects>
U
<sysobjects> spt_fallback_db,spt_fallback_dev,spt_fal
lback_usg,spt_monitor,sp
t_values,</sysobjects>
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Darren" <darren@.nospam.com> wrote in message
news:uGD%23BWc%23FHA.2608@.tk2msftngp13.phx.gbl...
> Sybase has an aggregate function called list that concatenates the results
> into a scalar.
> sybase eg:
> select dept,list(description) from parts group by dept
> This query would return one row for each department. Each row would
> contain all the description text for that department.
> Is there any way to do the same in TSQL without using a cursor? I'd like
> to do this in a subquery.
> TIA|||Have you seen the FOR XML extension that will allow you to do this in the
engine without the need of sending more data to the client.
Its one of the examples I now use of when, for scalability and complexity
reduction this should be done in engine rather than client side.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:O8YqVbc%23FHA.3608@.TK2MSFTNGP09.phx.gbl...
> There is none. There are certain workarounds though; see:
> http://groups.google.com/group/micr...3e?dmode=source
> In SQL 2005 you can use some complex methods using XML with CROSS APPLY,
> but the most reliable way is to use the server to return the tabular
> resultset and use a client side programming language to get concatenate/
> format the way you need. In certain situations, you might be able to use
> the PIVOT operator as well.
> --
> Anith
>|||I know, but I am not sure if the OP is using SQL 2005 or not.
Anith

Need help writing a trigger

Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post.

Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may be different in nature, project phases are defined in tblPhaseType and tblPhase tables. The table used to keep track of what's going on is the tblProduction table.

Here's what I need to do. When a project is completed -- meaning it has gone through all the phases that it needs to go through -- I want a trigger to fire up and change the contract status in the tblDeals table to "Completed" whose value is 1. When a new contract gets entered into the table, the Contract Status is set to 5 by default which means "In Progress" -- as defined in tblContractStatus. The tricky part is that because, each project is different and has different number of phases, the trigger has to make sure that all the phases have been submitted into the tblProduction table for that particular deal.

I'd really appreciate some help here. Thanks in advance for all your help.

------------
Here's the script
------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblContractStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblContractStatus
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContractStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContractStatus]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblCompany] (
 [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblContractStatus] (
 [StatusID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
 [PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblDeals] (
 [DealID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyID] [int] NOT NULL ,
 [DealDate] [smalldatetime] NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [CashAmount] [smallmoney] NOT NULL ,
 [StatusID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblPhase] (
 [PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblProduction] (
 [TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
 [DealID] [int] NOT NULL ,
 [PhaseID] [tinyint] NOT NULL ,
 [TransactionTimeStamp] [smalldatetime] NOT NULL ,
 [Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
 CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
 (
 [CompanyID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContractStatus] WITH NOCHECK ADD
 CONSTRAINT [PK_tblContractStatus] PRIMARY KEY CLUSTERED
 (
 [StatusID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] WITH NOCHECK ADD
 CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
 (
 [PhaseTypeID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] WITH NOCHECK ADD
 CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
 (
 [DealID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] WITH NOCHECK ADD
 CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
 (
 [PhaseID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
 CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
 (
 [TransactionID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [DF_tblDeals_StatusID] DEFAULT (5) FOR [StatusID]
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR [TransactionTimeStamp]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
 (
 [CompanyID]
 ) REFERENCES [dbo].[tblCompany] (
 [CompanyID]
 ),
 CONSTRAINT [FK_tblDeals_tblContractStatus] FOREIGN KEY
 (
 [StatusID]
 ) REFERENCES [dbo].[tblContractStatus] (
 [StatusID]
 ),
 CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
 (
 [DealID]
 ) REFERENCES [dbo].[tblDeals] (
 [DealID]
 ),
 CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
 (
 [PhaseID]
 ) REFERENCES [dbo].[tblPhase] (
 [PhaseID]
 )
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Description', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'Status'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of phase structure this deal will go through', N'user', N'dbo', N'table', N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifies the current status of deal', N'user', N'dbo', N'table', N'tblDeals', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column', N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO

------------
And here's some data
------------

INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer Info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write script',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit commercial',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write script',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print ad',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000000000000000e-001)
INSERT INTO [tblContractStatus] ([Status])VALUES('Completed')
INSERT INTO [tblContractStatus] ([Status])VALUES('Hold')
INSERT INTO [tblContractStatus] ([Status])VALUES('Collections')
INSERT INTO [tblContractStatus] ([Status])VALUES('Legal')
INSERT INTO [tblContractStatus] ([Status])VALUES('In Progress')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Try-It-Again, Inc.')
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(1,'Aug 5 2003 12:00:00:000AM',1,120.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(2,'Sep 9 2003 12:00:00:000AM',2,150.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(3,'Sep 10 2003 12:00:00:000AM',2,130.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(4,'Nov 20 2003 12:00:00:000AM',1,190.0000,5)
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10 2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10 2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10 2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10 2003 10:24:00:000AM','Finished designing ad copy')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,3,'Nov 20 2003 11:29:00:000AM','Did more work')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,4,'Nov 20 2003 11:29:00:000AM','Finally finished the job')

OK,
The trigger should be placed in the table TransactionTimeStamp.
The Trigger Fires on Insert

The SQL should look Like This

Declare
 @.phasesDone int,
 @.phasesNeeded int,
 @.dealid int,
 @.phaseid int,
 @.phasetypeid int
Select @.dealid = dealid, @.phaseid = phaseid from inserted
Select @.phasesdone = count(*) from tblProduction where dealid = @.dealid and phaseid = @.phaseid
Select @.phasesneeded = count(*) from tblphase INNER JOIN tbldeals on tblphase.phasetypeid = tbldeals.phasetypeid where tbldeals.id = @.dealid
If @.phasesdone = @.phasesneeded
begin
 update tbldeals set statusid = 1 where id = @.dealid
end

that shoul work i'm quite sure.
hope this helps you.|||Hi Misiu,

Thanks for the help. I'm getting an error. I think it has somehting to do with getting the PhaseID and the DealID from the "inserted". For some reason I've never been able to get this to work for me. Is there anything I need to do i.e. activate, some kind of setting or something -- so that I can get data from the inserted?|||What error do you get?
As I know you don't have to activate any setting.