Reports to Convert Multiple Rows to Multiple Columns 1

[SteveRemington]

Hello All,

I am hoping the collective wisdom of the forum can help me solve this problem.

In short I need to write a Crystal Report that will convert data in the Input format (as stored in the databse) to the Output format shown below.

The problem looks a little like a simple cross tab but not quite.

I would appreciate any suggestions as to how I might be able to achieve this outcome.

Thanks in advance to all those reply.

Regards,
Steve

[tt]
Input Data
----------
Equip_Id Obs_Point Obs_Date Obs_Desc Measurement
11111 AAAAA 01/01/01 Desc 1 123.456
11111 AAAAA 01/01/01 Desc 2 0.1234
11111 AAAAA 01/01/01 Desc 3 12.23
11111 BBBBB 01/01/01 Desc 1 234.567
11111 BBBBB 01/01/01 Desc 2 0.2345
11111 BBBBB 01/01/01 Desc 3 23.45
22222 XXXXX 03/02/01 Desc 1 23.456
22222 XXXXX 03/02/01 Desc 2 1.234
22222 XXXXX 03/02/01 Desc 3 12.23
22222 YYYYY 03/02/01 Desc 1 534.567
22222 YYYYY 03/02/01 Desc 2 0.7345
22222 YYYYY 03/02/01 Desc 3 53.45

Desired Output
--------------
Equip_Id Obs_Point Obs_Date Desc 1 Desc 2 Desc 3
11111 AAAAA 01/01/01 123.456 0.1234 12.23
11111 BBBBB 01/01/01 234.567 0.2345 23.45
22222 XXXXX 03/02/01 23.456 1.234 12.23
22222 YYYYY 03/02/01 534.567 0.7345 53.45[/tt]

 

[FoxG]

Hi Steve,

You can do this by creating formulae

What is your email. I could send you an example in CR7

Geoff

 

[Guest_imported]

Geoff,

I have a similar problem in which I want to take several records and combine them into one line. That is:

Customer ID# Nov-1 Nov-3 Nov-5
Cust1 111 X
Cust1 111 X
Cust1 111 X

I want:

Customer ID# Nov-1 Nov-3 Nov-5
Cust1 111 X X X

Can u help?

 

[chelseatech]

Same solution to both problems
You will need a group, and a variable for each of your consolidated values.
Accumulate values into the variables
Suppress the details/Group header sections
Display the values/variables in formulas in the Group Footer section

Solution for first example
Formula in Group Header
WhilePrintingRecords; numbervar d1:=0;
numbervar d2:=0; numbervar d3:=0;

Formula in Details;
WhilePrintingRecords; numbervar d1;
numbervar d2; numbervar d3;
if {table.ObjDesc}="Desc 1" then d1:= {table.Measurement};
if {table.ObjDesc}="Desc 2" then d2:= {table.Measurement};
if {table.ObjDesc}="Desc 3" then d3:= {table.Measurement};

Formula in Group Footer:
WhilePrintingRecords; numbervar d1;

(and similar formulas for D2 and D3)

http://www.chelseatech.co.nz

Editor of Crystal Clear