Help with labels using multiple tables as their source!

[ausmoran]

I am using CR version 9 built over a SQL 2000 database and am having a difficult time trying to figure out how to best handle to following issue.

I have Ethics and Professional Standards Cases that included people in five different tables (all related by CaseID). I need to create a single mailing labels report that generates mailing labels to each person from each of these five separate tables BY CASE. I am not sure how to do this in Crystal.

Here is the breakdown of the tables:

tbl_CASE (pk= CaseID)
tbl_CasePanel (fk=CaseID pk=CasePanelID)
tbl_CaseLitigants (fk=CaseID pk=CaseLitID)
tbl_CaseGrievance (fk=CaseID pk=CaseGrievanceID)
tbl_CaseAttorneys (fk-CaseID pk=CaseAttorneysID)

Thanks for any assistance that you might be able to offer. I am a relative novice when it comes to CR.

- Austin

 

[lbass]

Where do the addresses fit in? Are they present in each table? Are the fields in each table consistently set up the same, e.g., a separate first and last name field in each?

-LB

 

[ausmoran]

Thanks!
Above each of these "case" tables there is another parent table (e.g. tbl_mediators, tbl_litigants, tbl_attorneys, etc where the pk=mediatorID or pk=attorneyID etc).

The name address layout, unfortunately, is similar but not identical in all tables. That would have made too much sense.

 

[lbass]

So each parent table contains the addresses? I was going to suggest using a union statement in a command, which still might work, but I would have to guess at the fields. Please show fields necessary for links and for the label fields for one set of tables, e.g., tbl_Case, tbl_litigants, tbl_caselitigants. Also show some sample data that shows how the label fields vary in the way they are set up.

-LB

 

[ausmoran]

Thank you so much for taking the time to help me out. I have been playing around with the same idea. However, in some cases, I may not have data in one of the tables, and that seems to be throwing an error, so I am not sure what to do in that event.

SELECT dbo.tbl_Case.CaseID AS CaseID, dbo.tbl_PanelMembers.Lname, dbo.tbl_PanelMembers.Fname, dbo.tbl_PanelMembers.Middle, 
               dbo.tbl_PanelMembers.OfficeName, dbo.tbl_PanelMembers.Address1, dbo.tbl_PanelMembers.Address2, dbo.tbl_PanelMembers.City, 
               dbo.tbl_PanelMembers.State, dbo.tbl_PanelMembers.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.TblCasePanel ON dbo.tbl_Case.CaseID = dbo.TblCasePanel.CaseID AND dbo.tbl_Case.UserID = dbo.TblCasePanel.UserID INNER JOIN
               dbo.tbl_PanelMembers ON dbo.TblCasePanel.PanelID = dbo.tbl_PanelMembers.PanelID
UNION
SELECT dbo.tbl_Case.CaseID AS CaseID, dbo.tbl_Litigants.Lname, dbo.tbl_Litigants.Fname, dbo.tbl_Litigants.Middle, dbo.tbl_Litigants.OfficeName, 
               dbo.tbl_Litigants.Address1, dbo.tbl_Litigants.Address2, dbo.tbl_Litigants.City, dbo.tbl_Litigants.State, dbo.tbl_Litigants.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.tblCaseLit ON dbo.tbl_Case.CaseID = dbo.tblCaseLit.CaseID AND dbo.tbl_Case.UserID = dbo.tblCaseLit.UserID INNER JOIN
               dbo.tbl_Litigants ON dbo.tblCaseLit.LIT_ID = dbo.tbl_Litigants.LIT_ID
UNION
SELECT dbo.tbl_Case.UserID, dbo.tbl_GrievanceCommittee.Lname, dbo.tbl_GrievanceCommittee.Fname, dbo.tbl_GrievanceCommittee.Middle, 
               dbo.tbl_GrievanceCommittee.OfficeName, dbo.tbl_GrievanceCommittee.OfficeStreetAddress1 AS Address1, 
               dbo.tbl_GrievanceCommittee.OfficeStreetAddress2 AS Address2, dbo.tbl_GrievanceCommittee.City, dbo.tbl_GrievanceCommittee.State, 
               dbo.tbl_GrievanceCommittee.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.TblCaseGrievance ON dbo.tbl_Case.CaseID = dbo.TblCaseGrievance.CaseID AND 
               dbo.tbl_Case.UserID = dbo.TblCaseGrievance.UserID INNER JOIN
               dbo.tbl_GrievanceCommittee ON dbo.TblCaseGrievance.GrievanceID = dbo.tbl_GrievanceCommittee.GrievanceID
UNION
SELECT dbo.tbl_Case.CaseID, dbo.tblMediators.Lname, dbo.tblMediators.Fname, dbo.tblMediators.Middle, dbo.tblMediators.OfficeName, 
               dbo.tblMediators.Address1, dbo.tblMediators.Address2, dbo.tblMediators.City, dbo.tblMediators.State, dbo.tblMediators.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.tblCaseMediators ON dbo.tbl_Case.CaseID = dbo.tblCaseMediators.CASEID AND dbo.tbl_Case.UserID = dbo.tblCaseMediators.UserID INNER JOIN
               dbo.tblMediators ON dbo.tblCaseMediators.MEDIATORID = dbo.tblMediators.MediatorID
UNION
SELECT dbo.tbl_Case.CaseID, dbo.tblAttorneys.Lname, dbo.tblAttorneys.Fname, dbo.tblAttorneys.Mname, dbo.tblAttorneys.OfficeName, 
               dbo.tblAttorneys.Address1, dbo.tblAttorneys.Address2, dbo.tblAttorneys.City, dbo.tblAttorneys.State, dbo.tblAttorneys.Zip
FROM  dbo.tbl_Case INNER JOIN
               dbo.tblCaseAttorneys ON dbo.tbl_Case.CaseID = dbo.tblCaseAttorneys.CaseID AND dbo.tbl_Case.UserID = dbo.tblCaseAttorneys.UserID INNER JOIN
               dbo.tblAttorneys ON dbo.tblCaseAttorneys.UserID = dbo.tblAttorneys.UserID

I have set up this group of select statements so that the results all produce the same column names.

Thanks again for your terrific helpfulness!

- Austin

 

[Turkbear]

Hi,

and that seems to be throwing an error

What is the error message?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ausmoran]

Here's the error that I am getting when I attept to run it in the .NET application that it exists in:

Server Error in '/' Application.
Unknown Query Engine Error
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: CrystalDecisions.CrystalReports.Engine.DataSourceException: Unknown Query Engine Error

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[DataSourceException: Unknown Query Engine Error]
   .F(String 	, EngineExceptionErrorID 
) +540
   .A(Int16 
, Int32 ) +537
   CrystalDecisions.CrystalReports.Engine.FormatEngine.Export(ExportRequestContext reqContext) +469
   CrystalDecisions.CrystalReports.Engine.FormatEngine.Export() +107
   CrystalDecisions.CrystalReports.Engine.ReportDocument.Export() +67
   easyethics.reportlitigants.createpdf(String strCaseID)
   easyethics.reportlitigants.Page_Load(Object sender, EventArgs e)
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +750


Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300

THANKS again for your terrific assistance. I must admit, I am completely at a loss here.

- Austin

 

[Turkbear]

Hi,
Try creating a view in the database
( if SQLServer 2000 has views, I'm an Oracle person)
using that code and see if you can select from it.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

As Turk suggests, go into the SQL Server Enterprise Manager, right clikc Views, select New, and paste in your SQL.

Hit the data view and the sql will execute.

Turk: I do both Oracle and SQL Server, and SQL Server kicks butt for View creation, it's a graphical interface with drag and drop linking.

-k

 

[Turkbear]

Hi,
Yep - SqlServer does have , out-of-the-box, more user-friendly features than Oracle...Oracle, however, (as you probably know), kicks butt in performance with large systems and with transaction control ( readers never block writers and vise-versa, 2 Phase commits, etc for instance)

The 3rd party tools ( and even some newer Oracle ones) available can make Oracle friendlier, at the expense of some flexibility..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ausmoran]

I pasted this into a view and it seems to work okay! Can I simply run a view from CR? And then set up a parameter in CR?

Thanks to you all for your kind helpfulness.

 

[synapsevampire]

Yep, that will work fine in Crystal, and it's the better way to go for reusability and maintenance.

-k

 

[synapsevampire]

TurK: I agree with Oracles superior performance and scalability, although DB2 and Teradata are eating into their top end market share these days, while MS continues to bottom feed...

-k

 

[ausmoran]

When I attempt to save the QUERY, I get a message that says

UNABLE TO PARSE QUERY TEXT

The SQL pane is out of sync with the other panes. The query cannot be represented graphically in the Diagram Pane and the Grid Grid Pane. Do you want to Continue? Click YES to edit the query in the SQL Pane. Click NO to undo your changes.

Any ideas??????

Thanks again, - Austin

 

[ausmoran]

apparently MS SQL doesn't like UNIONS in a VIEW for some reason. urgghhhhhh. Not sure where I go from here. Unless I do it in a stored procedure.

 

[Turkbear]

Hi,
Perhaps using a temporary table and inserting rows into it using your 3 queries, one after the other - A Stored Proc is the route to go I expect..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ausmoran]

I'll try a SP next...and will let you guys know. Once again, thanks to you all for your help and support. You're the best!

-Austin

 

[synapsevampire]

Unions work fine in Views.

What happened?

It sounds like soemthing is wrong in the syntax.

Start with the first SQL, then add the next, etc., until you find the offending part.

-k

 

[ausmoran]

okay...I'll try it. THANKS