Technical Questions: Transpose Column and Row in Crystal

Transpose Column and Row in Crystal

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16519
Printed Date: 02 May 2024 at 1:14pm

Topic: Transpose Column and Row in Crystal

Posted By: bigwang916
Subject: Transpose Column and Row in Crystal
Date Posted: 22 May 2012 at 12:07pm

Hi everyone,

I am having problem with this Crystal Report, I believe the database table structure also plays a role in this. Is it possible to rotate columns to rows and vice versa? For example, I have the following in a report.

SubID

100 Rating 9

100 Comment Good

200 Rating 5

200 Comment Okay

300 Rating 1

300 Comment Horrible

I would like to manipulate the report to look like this:

SubID Rating Comment

100 9 Good

200 5 Okay

300 1 Horrible

I would like to do this without having to use any cross-tabs. Does anyone know if I can accomplish this?

Thank you.

Replies:

Posted By: yggdrasil
Date Posted: 22 May 2012 at 11:04pm

Yes you can, with formulae.

whileprintingrecords;

stringvar rating;

if isnull({Rating}) then rating:=rating else rating := {Rating}

Do the same for {Comments} and put them in the Details line.

Then group on Sub_Id.

Make two more formulae like

whileprintingrecords;

stringvar rating;

and put {Sub_ID} and the latter two formulae in the group footer.

Then suppress the group header and detail line.

Posted By: bigwang916
Date Posted: 23 May 2012 at 5:26am

Thanks YGG. It seems like I only could get the Comments to show. The result is that Comment and Rating shows duplicates of the Comment Text (i.e, the service was great!, good!, Bad, etc)

SubID Rating Comment

100 Good Good

200 Okay Okay

300 Horrible Horrible

This is what i have in the details section:

@Comment Formula:

whileprintingrecords;
stringvar comment;
if isnull({fieldcommentrating})then comment:=comment else comment := {fieldcommentrating}

@Rating Formula:

whileprintingrecords;
stringvar rating;
if isnull({fieldcommentrating})then rating:=rating else rating := {fieldcommentrating}

As a note:

fieldcommentrating contains data such as: 9, good, 8 OK, 1, bad etc

fieldname contains data such as : "Rating" and "Comment"

Then I grouped by the SUBID.

The following formulas were created to put in Group Footer Section (along with SubID field):

@Rating2

whileprintingrecords;
stringvar rating;

@Comment2

whileprintingrecords;
stringvar comment;

Posted By: bigwang916
Date Posted: 24 May 2012 at 8:50am

Problem solved! I created a Running Total Field to summarize my @rating (Maximum). Also, under "Reset" check "On change of group and place this field in the Group Footer. This pretty much did the trick since the max is 1 rating per subID.

Posted By: yggdrasil
Date Posted: 28 May 2012 at 4:53am

if isnull({fieldcommentrating})then comment:=comment else comment := {fieldcommentrating}

if isnull({fieldcommentrating})then rating:=rating else rating := {fieldcommentrating}

You would get the same for both Comment and Rating because you used

{fieldcommentrating} for both. I had assumed these were two different fields. If you separated '9, good' into rating 9, comment 'good', you would have got them both into the footer as you wanted

Posted By: bigwang916
Date Posted: 29 May 2012 at 1:31pm

Thanks for your help YGG!