Hello,
I have multiple rows from a table and want the report to display in a single line for rows with a similar date..

 This is how the data looks in on a report i had generated using standard report:

Date               Grade Weight1   Weight2    Sum
1/10/2011         A          22           33           65
1/10/2011         B           20           30           60
2/10/2011          A          10         21             31
2/10/2011          B          10         22             32


THIS IS HOW I WANT THE REPORT TO LOOK LIKE:

 

DATE	A			B
	Weight1	Weight2	Sum	Weight1	Weight2	Sum
1/10/2011	22	33	65	20	30	60
2/10/2011	10	21	31	10	22	32

 
Your help will be highly appreciated...

you could probably use a cross tab style report...but cross tabs only deal with summaries so it will want to add or count or something like that (I don't use cross tabs at this job, so they're a bit fuzzy)

Thanks for you guide Jones,not yet there though..

Grade name in table 2 and 3 is picked from table 1 as a using a FK..
Sum in the report should sum Weight1 and Weight2 fields in each grade daily..
This is the ultimate format of the report i want to generate:

DATE	A			B			C			D
	WGHT1	WGHT2	SUM	WGHT1	WGHT2	SUM	WGHT1	WGHT2	SUM	WGHT1	WGHT2	SUM
1/10/2011	20	19	39	24	20	44	0	0	0	0	0	0
2/10/2011	18	22	40	21	0	21	0	0	0	0	0	0
3/10/2011	0	0	0	0	0	0	20	25	45	0	0	0
4/10/2011	0	0	0	0	0	0	0	0	0	0	24	21
5/10/2011	22	0	22	0	18	18	0	0	0	21	0	21
6/10/2011	0	0	0	0	0	0	0	20	20	0	0	0

 
Please HELP!!

 

 

I have run into this issue in the past. Crystal wants to display everything is row format, but you want things in be in a column format.

Sadly, crystal is a real pain if you try and deviate from this.

I got around it by developing the report in a SQL command object with sub queries, so that I then could display the report how I wanted to.

The SQL would look something like this.

SELECT
TABLE1.DATE,
TABLE2.DATE,
Weight_A.wieght_for_a

FROM

TABLE 1 tbl1

LEFT JOIN TABLE2 tbl2 ON tbl1.SOMEID= tbl2.SOMEID

LEFT JOIN

--here is the subquery

(SELECT
   tbl1_2.SOMEID
   tbl1_2.weight AS wieght_for_a
   FROM TABLE1 tbl1_2
   WHERE tbl_2.GRADE = A
   GROUP BY
   tbl1_2.SOMEID
   tbl1_2.weight
   ) Weight_A ON tbl1.SOMEID = tbl1_2.SOMEID

--you will need a sub-query for each grade, then you should be able to get the report to display how you want

WHERE

--Here is where you put your normal where statement

GROUP BY
TABLE1.DATE,
TABLE2.DATE,
Weight_A.wieght_for_a



Edited by sgtjim - 13 Oct 2011 at 12:00pm

here is a sample of how you plae it in footers to mimic the crosstab (each row is a group footer)