Getting results with CR6

[jaisol]

We have these tables:

Invntory Table
--------------------
Invt_ID Invt_Desc
1 Product 1

ListPrice Table
---------------------
Invt_ID Price Type
1 10.50 A
1 11.00 B
1 10.75 C

We created a report to show the List_Price comparation where the result is:
Invt_ID Invt_Desc List_A List_B List_C
1 Product 1 10.50 0.00 0.00
1 Product 1 0.00 11.00 0.00
1 Product 1 0.00 0.00 10.75

We inserted 3 formulas: List_A, List_B and List_C into the report where
List_A : IF {ListPrice.Type} = "A" THEN {ListPrice.Price}
List_B : IF {ListPrice.Type} = "B" THEN {ListPrice.Price}
List_C : IF {ListPrice.Type} = "C" THEN {ListPrice.Price}

... but we would like to get the next result:
Invt_ID Invt_Desc List_A List_B List_C
1 Product 1 10.50 11.00 10.75

Is it possible? How can we do that?

We really appreciate any response. THANKS!

 

[MalcolmW]

Group by Invt_ID, and summarize the 3 formulas (using Sum or Max), and place the summaries in the group footer, along with the Invt_ID and Invt_Desc. Suppress the detail section. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[jaisol]

I suppose your sugestion will be work with only one register into the Invntory table but I don't know if it will work with various registers (products) associated to various prices and types of ListPrice table. For example purposes I included just 2 records (Invntory) but could be many registers.

Invntory Table
--------------------
Invt_ID Invt_Desc
1 Product 1
2 Product 2

ListPrice Table
---------------------
Invt_ID Price Type
1 10.50 A
1 11.00 B
1 10.75 C
2 54.35 B
2 54.80 C

With 2 products the result is
Invt_ID Invt_Desc List_A List_B List_C
1 Product 1 10.50 0.00 0.00
1 Product 1 0.00 11.00 0.00
1 Product 1 0.00 0.00 10.75
2 Product 2 0.00 54.35 0.00
2 Product 2 0.00 0.00 54.80

... but we would like to get the next result:
Invt_ID Invt_Desc List_A List_B List_C
1 Product 1 10.50 11.00 10.75
2 Product 2 0.00 54.35 54.80

Are you sure that your response will be generate the above result? or we need to do some else: Please What?

... ahh! THANKS for your prompt response.

 

[kenhamady]

That is why Malcolm suggested that you group by ID. You have it print the maximum for EACH group and it will do exactly as you describe. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jaisol]

THANKS for all!