Solved

Crystal Report Fields

Posted on 2012-03-31
2
232 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
2 Comments
 
LVL 9

Accepted Solution

by:
OCDan earned 75 total points
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now