grids in reports?

[Samalia]

I've been trying to create a report that can show data from multiple tables. I have a header that has only one set of data and only prints once on each page, but it had about three table that all have multiples sets of data that all correspond to the same header. I can get the tables to show all the data, but if i group them, they repeat in the order of 1,2,3,1,2,3,1,2,3, if there is more than one set of data for each, i want them to print out 1,1,1,2,2,2,3,3,3. Is this possible? And how?

 

[Stella740pl]

It could be possible with a different way of grouping and ordering. You can probably SELECT-SQL them in a cursor almost any way you want.

Are you showing date from 3 related tables or select them all in one cursor for the report? Can you post a little more detail of your data?

 

[Samalia]

there's actually one view and three tables, all are linked with the same field. and i'm working in foxpro 6.0 if that makes a difference.

 

[Stella740pl]

Sorry, I am having a hard time trying to imagine what your data looks like from your few lines of description.

Can you elaborate on this passage: "I can get the tables to show all the data, but if i group them, they repeat in the order of 1,2,3,1,2,3,1,2,3, if there is more than one set of data for each, i want them to print out 1,1,1,2,2,2,3,3,3." ?

Do you show fields from different related tables across one detail line? What are you calling "data set"? What is the primary report table? How it is ordered? How do you group it that it doesn't work?

Can you please be more specific? I don't know if someone can figure it out from what you said, but I personally lack imagination.

 

[Samalia]

Ok, I have three tables, Parts Used, Parts Ordered, and Work Done
On the report I have the fields grouped according to their tables.
For the Parts Used header there is the Used IHPN, Used Qauntity and Used Description,
on the Parts Ordered header I put the Ordered IHPN, Ordered Quantity and Ordered Description,
on the Work header I have the Work Done, Work Date and Hours Spent.

This is what it sort of looks like

Header
Used IHPN Used Quantity
Used Description
Used Header (arrow points up)
Ordered IHPN Ordered Quantity
Ordered Description
Ordered Header (arrow points up)
Work Date Hours Spent
Work Done
Work Header (arrow points up)
(then all the footers are here)

I have it so that the fields repeat when there is more than one IHPN per report number.
When the fields do repeat it looks like this;
Used fields
Ordered Fields
Work Fields
Used Fields
Ordered Fields
Work Fields

I would like the Used fields to be grouped together on the page and so on so that they look like this

Used Fields
Used Fields
Ordered Fields
Ordered Fields
Work Fields
Work Fields

Hope this is a better description

 

[Stella740pl]

Well, I guess I got it. They all related on IHPN field (right?), and, fairly enough, they are running in the order of IHPN, and grouping by Used-Ordered-Work Fields within each IHPN. Is it so?

What I would probably do is this (at least what I can think of at the moment):

SELECT	1 AS Extra, ;
		IHPN, ;
   		Quantity, ;
		Description, ;
		"" AS Done, ;
		{//} AS WDate, ;
		00 AS WHours ;
	FROM Used ;
		UNION ;
SELECT 	2 AS Extra, ;
		IHPN, ;
   		Quantity, ;
		Description, ;
		"" AS Done, ;
		{//} AS WDate, ;
		00 AS Hours ;
	FROM Ordered ;
		UNION ;
SELECT 	3 AS Extra, ;
		IHPN, ;
   		0 AS Quantity, ;
		"" AS Description, ;
		Done, ;
		WDate, ;
		Hours ;
	FROM Work ;
	GROUP BY 1,2 ;
	ORDER BY 1,2 ;
	INTO CURSOR ForReport

Then I would create an outermost grouping in the report on the field Extra, and use ForReport cursor instead of all 3 tables.

 

[Samalia]

I've tried using the code that you've given me, I've even tried changing a few things, but foxpro keeps telling me that I'm missing a phrase or keyword, and points to the first line. I have no idea what to do with this.

 

[Stella740pl]

Of course you should change a few things; names of your fields and files in the first place.

As for pointing to the 1st line, in VFP6 it almost always points to the first line of the whole statement. I can't test it without setting up the tables, and have no time to set them up now.

Can you post your resulting code to see what it looks like?
(By the way, Description is not a valid field name in VFP6, it's over 10 symbols long.)