Crystal Reports Same Data different result

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Same Data different result

Author	Message
boldon mackem Newbie Joined: 26 May 2009 Online Status: Offline Posts: 9	Topic: Same Data different result Posted: 04 Jun 2009 at 11:00pm
	I have a list which looks like this: Mr A 123456 Mr B 123456 Mr C 789101 Mr D 789101 Is there anyway I can get it too look like this? Mr A 123456 Mr B Mr C 789101 Mr D ???? Thanks, Edited by boldon mackem - 04 Jun 2009 at 11:02pm
	IP Logged


rahulwalawalkar Senior Member Joined: 08 Jun 2007 Location: United Kingdom Online Status: Offline Posts: 731	Posted: 05 Jun 2009 at 2:34am
	Hi The solution assuming Col1 :-MR ABC ...col2:-123456 ... Solution - Create the function below in SQL CREATE FUNCTION [dbo].[ufn_empnamesdata] (@col2 varchar(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @col1 VARCHAR(8000) SELECT @col1 = ISNULL(@col1 + ' ', '') + [col1] + ' ' +[col2] FROM TABLENAME WHERE col2 = @col2 RETURN @col1 END Then Create a view to access the data or use command object in crystal the SQL is SELECT distinct [dbo].[ufn_empnamesdata] (COL2) as 'Final Data' FROM TABLE NAME once the report data is generated you will get the row data as below MR A 123456 MR B 123456 Then create a formula in crystal Frm1 to get the exact display syntax left({Command.Final Data},Instrrev(replace({Command.Final Data}," ","\|"),"\|")) the above formula first replaces the spaces with \| ,the uses the Instrrev function to get the last \|, the uses the Left function to get the data before the Last \| Result MR A 123456 MR B Cheers Rahul Edited by rahulwalawalkar - 05 Jun 2009 at 2:34am
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 05 Jun 2009 at 6:21am
	You could also use formulas in Crystal to create a concatenated string if there happens to be more than 2 occurrances of the linking number. You would need to display the desired results in a Group Footer line instead of a detail, so it will depend on what other information is needed for your report. I prefer to solve issues like this in SQL if possible. Rahul's solution will give multiple lines of 2 names strings if there are more than 2 names alike...just a caveat...so depending on your data, his solution may not be ideal either. Converting multiple rows into 1 row is always a tricky business. HTH
	IP Logged

boldon mackem Newbie Joined: 26 May 2009 Online Status: Offline Posts: 9	Posted: 08 Jun 2009 at 3:23pm
	Forgive my ignorance, but how do I create the formula in SQL ? Thanks
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 09 Jun 2009 at 6:12am
	right click on Formulas...under Database in the Report Explorer and select New. Give it a name, and it will open the Formula Editor OOPS, that is create a formula in Crystal. There isn't a 'formula' in SQL. What we are speaking of in SQL is either a function or a stored procedure or a view. A view and a stored procedure are similar, they usually return something that looks like a table. A function is something that can be called by a select statement and may return a value or a table. The web is full of sites to assist in creating these / the syntax. Rahul's post is how to create a function and how to call it.
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum