Crystal report question

[rlangi05]

So far I've done simple reports using crystal reports with a repeating section and all. Now I need to make a report that has two repeating sections with data taken from two tables, they are related of course-1 to many.

First table is Invoice table and second table is InvoiceItems. The Invoice table has the general information of an invoice, and the InvoiceItems table lists the Items associated with a particular invoice.

In the report, I want to be able to list all the Invoice as well as all the items of each Invoice.

I guess to get the data I use an SQL statement, but how do I design the report in the report designer?

I'm using Visual basic 2005 by the way.

Please help.

Thanks

 

[kulrom]

i would suggest subreport in this case as it is very confusing if you put few invoices (including invoice items) on the same page. For that you can 1st list all the invoices and then within subreport to show ONE invoice per page including the items as well.

 

[ManicCW]

you can use subreport or create query with that two tables and bind controls to that query.

 

[DavidT_macktool]

Here is another option:
Drive the report from the InvoiceItems table, Group by Invoice, link the two tables together by the keys.
On your detail line include information from the invoice table, play around with 'Suppress if Duplicated' for the fields of the invoice table.
This will show that information on the first row and hide it for the rest of the Invoice items - a cheater drop down like report.
Invoice Fields on the left - Invoice item fields on the right - Looks like a real report!

 

[kulrom]

Invoice Fields on the left - Invoice item fields on the right - Looks like a real report!

Are you sure it looks like you described above?
To be honest i haven't seen sucha invoice yet. Don't take offense but i am very curious to see that invoice ... maybe i've missunderstood your statement sorry if it's so

 

[DavidT_macktool]

no problem kulrom, here is a quick example of a 3 tier report done that way.

First tier is Part Number.
Second tier is Shop Number.
Third tier is Operation Number.

Detail line is based on Operation number like described above. Very basic example...
He still has to dress it up...

 

[kulrom]

ohhh sorry then, i missunderstood you buddy my apologies. I thought you want to say that an invoice should look like the one from the attached image below. My bad

 

[DavidT_macktool]

No worries,
I wasn't very clear... I was just trying to provide an easy solution because he admitted
"So far I've done simple reports using crystal reports with a repeating section and all".
I kind of thought Subreports would raise more questions and frustrations.

rlangi05, if you format the group section and check new page after, only one invoice will be on each page.

Hope this helps,

 

[rlangi05]

I made a subreport for the InvoiceItems table and linked the common field (InvoiceNumber) and I got what I want...
Im also going to try DavidT suggestion, it might come useful in the future with larger reports.

Thanks guys for the help... really thank you...
This forum thing is great!!