Posted by: R Manimaran | April 20, 2009

Comma separated Column value in Crystal Report

I have come across a situation in Crystal Report. Suppose for example I have two tables Employees and Departments.

An Employee can be belonged to two departments. In the report I need some thing like this

 

Report Output

Report Output

 

 I don’t want to insert any group. Just a list of employees with their corresponding Departments

 

Solution:

To achieve this in crystal report we need to write some formula.

 

Formula 1: @Department

 

WhilePrintingRecords;

Shared StringVar Result;

if(NextIsNull({Employees.EMPID})=true or

  {Employees.EMPID }<>Next({Employees.EMPID })) then

   Result:={Departments.Name}

else

   Result:={Departments.Name }& “,” & chr(13)& Next({Departments.Name })

 

Place the @Departments formula in the details section. Now you will get the output like this.

 

 

 

 

Report Output

Report Output

 

We need to eliminate the second row and fourth row.  For that we need to do

  1. Right click the EMP.ID field in the details section and select Suppress if duplicated option.
  2. Now select the EMP.Name and @Departments fields. Right click and select Format Field . In the common tab check Suppress.
  3. In the X2 formula section of the Suppress write this formula.

WhilePrintingRecords;

if(PreviousIsNull({Employees.EMPID})=true or {Employees.EMPID}<>

Previous({Employees.EMPID})) then

   false

else

   true

  1. Now whole second row and fourth row are appearing blank.
  2. To eliminate that space, Right click details section and select Section Expert.
  3. Check the Suppress blank section option.

If anybody founds any other way achieve this, can post the things here.

Happy Coding!!

 

Advertisements

Responses

  1. […] Report; Business Objects – Get Crystal Report 2008 Prompts using InfoStore from CMS Repository; Comma separated Column value in Crystal Report; Useful Links: Excel-like Web-based Java Reporting Software; Freezea’s Report Lesson; […]

  2. […] Report; Business Objects – Get Crystal Report 2008 Prompts using InfoStore from CMS Repository; Comma separated Column value in Crystal Report; Useful Links: Excel-like Web-based Java Reporting Software; Freezea’s Report Lesson; […]

  3. How will be if you have MANY departments. In another word, not only 2, for X.

    Thank you.

  4. How can i able to do the reverse of upper task.Please help any one

  5. please help

  6. Thanx. It worked. 😀


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: