Data Connectivity
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Data Connectivity
Message Icon Topic: Reporting on Select from multiple tables? Post Reply Post New Topic
Author Message
GrahamL
Newbie
Newbie
Avatar

Joined: 20 Nov 2006
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote GrahamL Replybullet Topic: Reporting on Select from multiple tables?
    Posted: 20 Nov 2006 at 4:06am
Hi, Not sure if this is the right place, but here goes....
 
I am writing a Windows app in VB.2005 and have several reports to generate. Some, I have already worked out using references to Text Objects (as they are really mail-merge type letters), others I have done with references to typed datasets based on a single database table, as described in examples I have found all over the net.
 
However, the one I am stuck on (for several days!) is as follows:
 
I have to report a guest list for an event. The data comes from three tables related by two inner joins. My SQL works fine as I can see the correct results of this query in the server explorer's 'new query' window, but when I set the report's datasource, all I get is an empty page.
 
I have tried creating the report by referencing to a dataset and to the tables themselves, but the catch always seems to be that I cannot get CR to see the fact that there is data coming from three places.
 
In the end, my workaround was to create a temporary table, create a dataset from it, then populate the temp table from the "inner join" query and create and subsequently run the report from the dataset created to mirror it.
 
This all seems a long-winded way round doing something that really should be easy, but I guess I am missing something obvious as I can't see how to do this.
 
Can post full code if needed.
 
If there is anyone out there (Brian?) who can point me in the right direction, please please help!
 
Also, if there is anyone out there who wants to see how i did the mailmerge type letters with textobjects, including multi-page letters, let me know and I'll post an example.
 
Cheers
 
Graham, London UK.   
One day I'll understand it all....then there'll be trouble
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 20 Nov 2006 at 11:34am
Hmmm, it's kind of tough to answer a question like this when there are so many ways that it could have been implemented (and so many ways for something to get missed). If you are pulling from a Sql View, then this should be just like running a report from a single table (CR doesn't know the difference). If that's the case, then something basic is missing from your report.

An easy way would be to creat the report and reference each of the tables within the report and create the inner joins using Links tab of the Report Expert dialog box.

I don't know why CR wouldn't know that there was data coming from three different places. This is standard stuff for CR. Maybe the joins are correct and thus it returns no data? Strange...

Re the mailmerge with text objects, I would be interested in seeing how you did it to see if it would be a useful tip for people.  :-)

Brian
IP IP Logged
GrahamL
Newbie
Newbie
Avatar

Joined: 20 Nov 2006
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote GrahamL Replybullet Posted: 21 Nov 2006 at 1:25am

Hi Brian,

Thanks for the reply.

I did think of using a view (This is an Access .MDB, by the way), but as the query contains a program variable, I couldn't see how to save it correctly. The actual SQL I ended up trying to use pulled from only two tables, but still the same issue arose, as follows:

"SELECT * FROM partybookings INNER JOIN customers ON partybookings.cust_ref = customers.cust_ref WHERE partybookings.party_ref = '" & pRef & "'"

(pRef is a class-level variable that holds a string like "071206RP")

Maybe I am just being dumb as regards how to store/use views in VB. Confused
 
Also, I wanted to keep as much "intelligence" away from the reports as possible - The reason for this is that if I come back to this code in six months' time, I don't want to have to dig around all over the place to remember how the reports got generated.
 
Looking back at this I think I ended up going around in circles trying to achieve something that could have been done with a view and a dataset, but I didn't (and still don't) know how to implement the view.
 
Oh well....:-)
 
 
As for the mail-merge stuff, here's a really simple example....
 
1. Create a blank report
2. Insert a text object
3. Rename the text object, ie "toAddr1"
4. Format and position the text object as required
5. Note which section the text object appears in (I tend to rename them away from 'Section1', 'Section2' etc to more memorable strings like 'Header' and 'Body')
6. In code, populate a datareader with the record you want to merge into the template
7. Reference the objects in the section that the textobject is in via the ReportObjects collection (I use the renamed section 'Header' for this example):
 
dim txtobjs as ReportObjects = cr.ReportDefinition.Sections("Header").ReportObjects
 
8. Set the text you want to display. NB: As the ReportObjects collection returns objects, you need to cast to the correct type:
 
Ctype(txtObjs("toAddr1"), TextObject).Text = drdr.item("addr1").ToString
 
In this example, I use the addr1 database field as populated in the datareader
 
9. Print the report: cr.PrintToPrinter(1, true, 1, 1)
 
For a full mail-merge, all I did was to enclose the whole lot in a While .Read loop. That will print a page for each record in the reader.
 
For multi-page documents, I found that creating several new sections in the report and making each one approx 16380 in height gave a A4 page per section that can be formatted and populated with text objects in the same way as above. All that is needed then is to reference the sections in turn (as in step 7) and then refer to each textobject as required (as in step 8). Finally, remember to set the number of pages in the PrintToPrinter call to the same as the number of pages that the report generates.
 
Cheers
 
Graham
 
 
One day I'll understand it all....then there'll be trouble
IP IP Logged
PaulAkh
Newbie
Newbie


Joined: 01 Nov 2007
Location: United Kingdom
Online Status: Offline
Posts: 1
Quote PaulAkh Replybullet Posted: 01 Nov 2007 at 12:55pm

Hi Graham

An organisation I work for has recently acquired Crystal XI and I was asked today if it is possible to do create mail merge reports . . . so . . I'd be really interested to have a look at the 'mailmerge type letters with text objects' you've offered to share.
With thanks - in anticipation
Paul
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.