Print Page | Close Window

Displaying Multiple Rows as 1

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3743
Printed Date: 05 May 2024 at 5:10pm


Topic: Displaying Multiple Rows as 1
Posted By: bcgoh
Subject: Displaying Multiple Rows as 1
Date Posted: 18 Jul 2008 at 5:58am
Hi,
I've got a request to format a report in a way that's confounding me, please help:

CER table has multiple records for each primary id, CERNO. This is to be coalesced into 1 row per CERNO. The no of records per CERNO is not fixed and is expected to range from anywhere between 1 to 50 as a guide. Anyone has any idea how this can be done?

Scenario
CER Table
CERNO State Date
1 1 12/7/08
1 2 12/7/08
1 3 14/7/08
1 1 15/7/08
1 2 16/7/08
2 1 17/7/08
2 2 18/7/08
2 3 19/7/08
3 1 18/7/08

Expected formating as below:
CER View
CERNO State Date State Date State Date State Date State Date
1 1 12/7/08 2 12/7/08 3 14/7/08 1 15/7/08 2 16/7/08
2 1 17/7/08 2 18/7/08 3 19/7/08
3 1 18/7/08

My main problem is that the number of records/rows per CERNO is dynamic. I thought of using crosstabs, but then crosstabs is used for summaries and doesn't display record detail.

Thanks in advance for you help.


Replies:
Posted By: venkatesha
Date Posted: 18 Jul 2008 at 9:17am
Kindly try

Detail Section -> Format with multiple columns you will get Layout Tab appropriately give the size.

If its not helpfull

please try this

Create a Group for CERNO

Report -> Group Expert -> 

SELECT FIELD CERNO

Create formula
CERROWINIT

Place it inside formula

WhilePrintingRecords;
Global stringVar a:="";

Place this formula in Group Header

Create one more formula

CERROWINIT


WhilePrintingRecords;

global stringVar a;
a:=a+ ToText({ State}) +" " + ToText({DATE})


Place the above formula in the Detail section and suppress the detail section.

For Group Header section enable Underlay following section.

Hope this will solve your problem

Cheers

Venkatesh
Place the same formula in the Group footer also. And enable the can grow property and also increase the width for the formula.

Posted By: bcgoh
Date Posted: 20 Jul 2008 at 6:45pm

Hi http://www.crystalreportsbook.com/Forum/member_profile.asp?PF=4787&FID=5 - venkatesha ,

Thanks for responding, but I'm afraid this does not address the requirements. But thanks anyway.

Posted By: venkatesha
Date Posted: 21 Jul 2008 at 1:09am
Have you tried ?

Cheers

Venkatesh

Posted By: bcgoh
Date Posted: 22 Jul 2008 at 12:37am
Yes, I did. It did not give the expected results.
 
But then, I experimented later, and finally got the expected results. I guess your concept was sound, but perhaps the steps were not complete. Anyway, was just gonna come back and add an update. So thank you. =)
 
BTW, another question. Now that I've got the output, the next step is to get it published for the users. I'm using "~" as the column delimiter. Right now, I can only export the results to EXCEL. From there, I saved it in txt format. When I opened the txt file and applied the Delimited Separator to convert the data text string into data columns, I got some data rows that refuse to convert. Any experience with this scenario?
 
 


Print Page | Close Window