Solved

Crystal Report Fields

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
VB.Net/LINQ: ".Contains" condition not finding existing result in MSSQL 16 41
Possibly a grouping issue? 7 28
No Data for DropDown List 2 28
System.net.Sockets Error 5 26
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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