on 11-11-2013 2:10 PM
I need to be able to Select a value based on the earlisst date that an employee worked in a specific State. For example:
Our employess travel from state to state. I need to create a report that says for year 2013 show me the first state the the employee worked in. The employee may have worked in 5 state but I only want to see the first one for all employees in 2013.
Can this be done? I am faqirly new to Crystal, so I am not great at formulas yet.
Thanks,
Rick
Hi Richard,
You'll be spoilt for choice when it comes to this requirement! You have many ways to do this:
Using Groups and Group Selection formula:
1) Create a Group on the Employee ID field
2) Go to Report > Selection Formula > Group and use this code:
Minimum({Date_field}, {Employee ID}) = {Date_field}
3) Suppress the details section and move everything to the Group Header or Group Footer section
4) Add a Record Selection Formula to bring back records only for year 2013.
Using SQL Expressions:
1) Under Field Explorer, look for an option called 'SQL Expressions'. Right-click and select New > Give it a name (First Date) and use this code:
(
Select Min("Date_field") from Table
where "Employee ID" = "Table.Employee ID"
)
2) Drag and drop it on the details section
3) Go to Report > Selection Formula > Record and use this code:
{Date_field} = {%First Date}
AND
year({Date_field}) = 2013
Hope this helps.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
May you try this.
The firstly, you create a new store procedure.
CREATE PROCEDURE [dbo].[FirstStateForEmployee]
@Year INT
AS
BEGIN
SET NOCOUNT ON;
SELECT T2.empID, T2.firstName + ' ' + T2.lastName AS EmployeeName, T2.startDate, T2.workState
FROM dbo.OCST T1 WITH(NOLOCK)
INNER JOIN
(
SELECT TOP 1 T2.empID, T2.workState, T2.firstName, T2.lastName, T2.startDate
FROM dbo.OHEM T2 WITH(NOLOCK)
WHERE YEAR(ISNULL(T2.startDate, 0)) = @Year
ORDER BY T2.StartDate ASC
)T2 ON T2.workState = T1.Code
--WHERE T2.empID = @EmployeeCode
END
GO
GO
The secondly, you create a new crystal report and set datasouce by the store procedure.
The thirdly, you create a new group section, set empID value in this group and then drop and drap values to show in detail section.
I hope to help you.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.