?
Solved

Crystal Report Fields

Posted on 2012-03-31
2
Medium Priority
?
237 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 300 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 101

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

752 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