I have a situation where the report doesn't work, but the SQL does. It works in the designer when I just run the query and manually put in the variable values. It does not work when run in the preview mode; I get: incorrect syntax near ')'.
I am attaching the .rdl as an XML file and the query definition; thank you for your help!
/****** Object: StoredProcedure [dbo].[RptPayTypeLimitsRep
ort] Script Date: 04/19/2011 15:08:42 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CO 24915: We want to alter the Pay Type Overage Report to have extra functionality. The original specifications can be found below. Please make the following changes:
Name Change: Pay Type Limits Report
Added Filter: Drop-down Box with no label to the immediate left of the Hours/Amount Text Entry field. The box would have two Options (Over and Under.)
Currently the report returns any values for a selected pay type where the hours or amount is greater than or equal to the entered filter amount.
I would like to expand the report to include the option to do 'greater than or equal to' AND 'less than or equal to.'
Expanded Option: Make the Pay Type field multiple-select in addition to having the following options listed at the top:
All Regular Pay Types, All Overtime Pay Types, All Expense Pay Types, and All Premium Pay Types.
The Over/Under would calculate all pay types together rather than one pay type at a time.
The 'All' option would allow the user to select all Pay Types of a particular type (Regular, Overtime, Premium, or Expense) at one time rather than individually.
New TLS report requested: Users would like to see employees who went over a certain amount of hours for a selected pay type for a selected date range.
Title: Pay Type Overage (Daily) Report
Pay Type: Drop-down field with a pay type list from the selected Org
Start Date: Calendar Field
End Date: Calendar Field
Filter Validations: Start Date cannot be earlier than End Date
Employee Last Name, First Name
Project Abbreviation: PA used for charged pay type
Total: Hours or Amount charged to pay type for the given day
The report should return any time the employee went over filter limit for the selected pay type. The report listings should be grouped by Employee ID (sorted in descending alphanumeric order.) Entries within the Employee ID order should be sorted by date.
The report header should include the report name and the pay type and date filters used.
-- DROP PROC [dbo].[rptPayTypeLimits];
CREATE PROC [dbo].[rptPayTypeLimits]
DECLARE @PayTypesList nvarchar(max) = (dbo.udf_GetPayTypesByCate
PRINT '@PayTypesList = ' + @PayTypesList;
DECLARE @nvcOrgID nvarchar(10) = CONVERT(nvarchar, @OrgID );
PRINT '@nvcOrgID = ' + @nvcOrgID;
PRINT '@OverUnder = ' + @OverUnder;
DECLARE @TSQL nvarchar(max) =
emp.LastName + '', '' + emp.FirstName as [EmployeeName],
td.Code07 as [ProjectAbbreviation],
sum(td.Amount) as [Total]
FROM vwTimesheetDetail As td WITH (NOLOCK)
INNER JOIN vwEmployeeInfo As emp WITH (NOLOCK)
ON (emp.EmployeeNumber = td.EmployeeNumber)
WHERE td.StartDate < td.EndDate
AND td.isDeleted = 0
AND emp.OrgID = ' + @nvcOrgID + '
AND td.Paytype IN ( ' + @PayTypesList + ' )
AND td.HoursDate BETWEEN ''' + CONVERT(varchar, @StartDate) +
''' AND ''' + CONVERT(varchar, @EndDate ) + '''
GROUP BY emp.OrgID,
IF (@OverUnder = 'OVER')
SET @TSQL = @TSQL + 'HAVING sum(td.Amount) >= ' + LTRIM(RTRIM(STR(@Hours))) + ';';
SET @TSQL = @TSQL + 'HAVING sum(td.Amount) <= ' + LTRIM(RTRIM(STR(@Hours))) + ';';
SET NOCOUNT ON
GRANT EXECUTE ON [dbo].[rptPayTypeLimits] TO PUBLIC;
@OrgID = 228,
@StartDate = '2011-01-01',
@EndDate = '2011-05-02',
@Hours = 5,
@OverUnder = 'OVER',
@PayType = 'REGULAR'