2 table on single report

[rommeltm00]

hi guys

is there a possibility to make a report came from two table?

all i want is to print all data on the first table then
print all data on the second one, is this possible? the two table structure is not identical.

any suggestions, thanks.

 

[markros]

Try to use two detail bands.

 

[Mike Lewis]

all i want is to print all data on the first table then
print all data on the second one,

If you mean that literally (that is, the whole of one report followed by the whole of the next report), then print then as separate reports, but use NOPAGEEJECT on the first one. That way, the combined reports will be a single print job, and the page numbering will follow in sequence.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

If the data in the two tables is dramatically different, then Mike's suggestion of 2 separate reports printed sequentially would be best.

An alternative to this would be to over-lay report 'pages' with some criteria within the data table itself added (example: field REPORT_NO) to be used within the Print When... expression of the various Report Form objects.

Lastly, if the data were the same in the 2 tables you could always do a JOIN and merge the two tables into one comprehensive table for report purposes and then send it all to the Report Form as one print job. Again if you wanted to differentiate which data appeared when, you might need to add some 'control' field as a differentiator.

Good Luck,
JRB-Bldr

 

[rommeltm00]

guys,

lets say there are 5 records in the first table
let say its name
print the 5 names

then lets say there are 10 records in the second table
then print it immediately after the 5 names

name1
name2
name3
name4
name5

second table data 1
second table data 2
and so on

in same page

can this be possible
thanks

 

[Mike Lewis]

I think we've already answered that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Stella740pl]

Lastly, if the data were the same in the 2 tables you could always do a JOIN...

Did you mean UNION?

I like Mike suggestion of two separate reports printed as one print job, but I do remember getting requests once in a while to show the two reports in the same preview.

If this is the case, I would try an artificial construct similar to this:

SELECT name, "" AS second_table_data ;
FROM first_table ;
UNION ;
SELECT "" AS name, second_table_data ;
FROM second_table;
INTO CURSOR all_data

Then, use this cursor as a table for your report.

I would do this ONLY if both of the tables are not too big, both, field-wise and record-wise; and ONLY if you need them in the same preview.

 

[JRB-Bldr]

Thanks Stella - Yes UNION Opps!

rommeltm00 - what you have not told us is if the values from Table1 need to be clearly identified as separate from the values from Table2

If that were to be the case then you need to do as I suggested and add a new field which can be used to differentiate the values from Table1 from Table2 and that new field can be used in the Report Form.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

.... add a new field which can be used to differentiate the values from Table1 from Table2 and that new field can be used in the Report Form.

That's right. Furthermore, if he adopts Stella's suggestion of using a UNION, he would need that new field to sort the cursor on. Otherwise, there's no guarantee that all the second table's records would appear after the first.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Stella740pl]

Mike, you are right.

I kind of thought about that, then decided that the cursor could be sorted on the combination of name, which is empty for the second table, and second_table_data, which is empty for the first one - assuming the tables have completely different structure.

But since all I know about the structures is "name" and "second_table_data", I left this question to the OP to figure out.

 

[TamarGranor]

Check out the section titled "Multiple unrelated siblings" in this paper from my website:

http://www.tomorrowssolutionsllc.com/Materials/building_queries_in_visual_foxpr.htm

Once you've built the query, in the report, using the added field that identifies the record's source in Print When conditions or IIF()s to have each data item appear only when it should.

Tamar

 

[rommeltm00]

guys take a look at this report, this is what i want to accomplished. thanks. God Bless.

 

[TamarGranor]

The key to any complex report is getting the data into a cursor in the order you want to report on it. Please take a look at the paper I pointed you to for that.

Tamar

 

[Olaf Doschke]

Looked at the XLS, the last portion of the xls has some totally different layout. I wonder if you can do that with a secondary detail band, perhaps. I'd rather use report concatenation like advised by Mike Lewis right at the beginning. print the first part with NOPAGEEJECT, than print a second report with another cursor for that last portion of the report.

Tamar is also right, you can always print a two part report with one cursor, for example if you layout your cursor like this:

a1,   a2,   a3,   b1,   b2,   part
data  data  data  NULL  NULL  1
data  data  data  NULL  NULL  1
data  data  data  NULL  NULL  1
data  data  data  NULL  NULL  1
NULL  NULL  NULL  data  data  2
NULL  NULL  NULL  data  data  2
NULL  NULL  NULL  data  data  2

Fields a1,a2,a3 are used for the first part of the report, all report controls are set to "print when" part=1, the other part of the controls is set to "print when" part=2 and are printing data from b1 and b2 fields.

I haven't followed Tamars link to see if that is the idea of her article, I find it easier, tough, to have two seperate cursors and print the first report with NOPAGEEJECT and the second report afterwards. One advantage of the single cursor combining two cursors this way is, that you could also have overlap of common fields, you can also switch forth and back between part 1 and part 2 with any record.

Bye, Olaf.

 

[rommeltm00]

SELECT * FROM firearms WHERE agency='M' INTO CURSOR x
rEPORT FORM firearms NOCONSOLE TO PRINTER NOPAGEEJECT
SELECT calivgauge,coun(calivgauge) FROM firearms WHERE agency='M' GROUP BY calivgauge INTO CURSOR xx
rEPORT FORM firearm1 NOCONSOLE TO PRINTER

i try the above command but the first report ejected and the second one print on the next paper

any suggestion guys

 

[Olaf Doschke]

Did you try from the Command window? NOPAGEEJECT is valid only during program execution. It is disregarded when issued in the Command window.

Take a look at the printer spooling, NOPAGEEJECT should keep a print job open and the next REPORT should add to it.

If it doesn't work then there's perhaps something wrong with windows or the printer driver(s).

Bye, Olaf.

 

[rommeltm00]

i compiled it into executable file and the printer said it has 2 pages in que

 

[Olaf Doschke]

Well, I can't tell you more, what fails and why. Perhaps take a look at windows log, eg event log. Maybe it's even just the printer not supporting this option. You may use Process Monitor to gain more info on what is happening, eg what DLLs are used while printing. See here:

https://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

Bye, Olaf.

 

[Mike Lewis]

the first report ejected and the second one print on the next paper

Does the second report have different page settings than the first -- for example, a different page size or orientation? If so, that would explain what you are seeing.

Also, is it possible that the first report completely filled the page? Sorry if that sounds obvious, but it too would explain what you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[rommeltm00]

i used hpd2560 and canon ip1880 same page layout only the contents are changed, it ejects the first page then it prints the last page which is on the second page, btw i used vfp9 sp2 version 09.00.0000.5815