Solved

Crystal Report Fields

Posted on 2012-03-31
2
233 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
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

809 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