Solved

Crystal Report Fields

Posted on 2012-03-31
2
235 Views
Last Modified: 2012-05-03
I have designed a crystal report and connected to a database. When the report launches a sql statement runs on the calling form and generates a dataset in which to populate the report. See below.

I need to know how to create 3 fields or parameters within crystal so I can assign them to the dataset fields for population? The fields are called 'name' but the query renames the fields accordingly.

SELECT c.JobNo,c.Contract_Name,c.Address1,c.Address2,c.Address3,c.Town,c.County,c.PostCode,
s1.Name AS 'Contract Manager', s2.Name AS 'Surveyor', s3.Name as 'Site Manager',
s1.Mobile, s2.Mobile, s3.Mobile
FROM tbl_ContractDetails c LEFT OUTER JOIN tbl_Staff s1 ON c.ContractManagerID = s1.ID
LEFT OUTER JOIN tbl_Staff s2 ON c.SurveyorID = s2.ID
LEFT OUTER JOIN tbl_Staff s3 ON c.SiteManagerID = s3.ID
ORDER BY c.JobNo ASC

Thanks
0
Comment
Question by:SCOTT78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
OCDan earned 75 total points
ID: 37790656
Create Procedure Sp_Whatever
(
 @name1 varchar(50),
@name2 varchar(50),
@name3 varchar(50)
)
AS
SELECT c.JobNo,c.Contract_Name,c.Address1,c.Address2,c.Address3,c.Town,c.County,c.PostCode,
s1.Name AS 'Contract Manager', s2.Name AS 'Surveyor', s3.Name as 'Site Manager',
s1.Mobile, s2.Mobile, s3.Mobile
FROM tbl_ContractDetails c LEFT OUTER JOIN tbl_Staff s1 ON c.ContractManagerID = s1.ID
LEFT OUTER JOIN tbl_Staff s2 ON c.SurveyorID = s2.ID
LEFT OUTER JOIN tbl_Staff s3 ON c.SiteManagerID = s3.ID
where s1.name = @name1
and s2.name = @name2
and s3.name = @name3
ORDER BY c.JobNo ASC

Base the crystal report on this procedure and it automatically pulls through the parameters to be populated.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37791721
Is the report built to run from a dataset?

Can you change the SQL to do the filtering rather than relying on the report?

How are you passing the recordset to the report?

Does the report show the expected data?

mlmcc
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question