Select All option for a parameter works in Visual Studio but not when deployed to the Report Server
-
I am experiencing a strange issue with a particular report. The report itself has three parameters: @StartDate, @EndDate, and #LicenseTypeID. To get the list of values for my License Type, I am using a stored procedure that looks like:
ALTER PROCEDURE [dbo].[rpt_DataSet_LicenseTypeID]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @localDate DATETIME = dbo.locGetDate();
SELECT LT.[id] AS [LicenseTypeID] ,
LT.[description] + ' - ' + Res.[description] + ' - ' + Terms.[description] AS [LicenseType]
FROM dbo.LicenseTypes LT
INNER JOIN dbo.LicenseTermTypes Terms ON LT.licenseTermTypeId = Terms.[id] AND Terms.active = 1
INNER JOIN dbo.LicenseResidentTypes Res ON LT.licenseResidentTypeId = Res.[id] AND Res.active = 1
WHERE ( LT.inactiveDate IS NULL OR LT.inactiveDate > @localDate )
--AND LT.id <> 195
ORDER BY LicenseType;
END;I pass in the @StartDate, @EndDate, and @LicenseTypeID parameters to another stored procedure that generates my dataset:
ALTER PROCEDURE [dbo].[rpt_LicenseTransactionsReport]@StartDate DATE ,
@EndDate DATE ,
@LicenseTypeID NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;DECLARE @localDate datetime = dbo.locGetDate()
SELECT C.[id] AS [CustomerID] ,
C.firstName AS [FirstName] ,
C.middleName AS [MiddleName] ,
C.lastName AS [LastName] ,
CASE WHEN CA.street2 IS NULL THEN CA.street1
ELSE CA.street1 + CHAR(13) + CA.street2
END AS [Address] ,
CA.city AS [City] ,
CA.stateAndTerritoriesID AS [State] ,
CA.zipCode5 AS [Zip] ,
OI.ItemEndDate AS [ExpirationDate] ,
'' AS [Rep] ,
O.orderDate AS [PurchaseDate] ,
CASE WHEN CL.isVoided = 1 THEN 'Voided'
WHEN CL.isInvalidated = 1 THEN 'Invalid'
WHEN CL.[expireDate] <= @localDate THEN 'Expired'
ELSE 'Active'
END AS [Status] ,
LT.[description] + ' - ' + Res.[description] + ' - ' + Terms.[description] AS [LicenseType]
FROM dbo.Customers C
INNER JOIN dbo.Common_OrderItems OI ON OI.CustomerID = C.id
INNER JOIN dbo.Common_Orders O ON O.OrderID = OI.OrderID
LEFT OUTER JOIN dbo.CustomerLicenses CL ON CL.orderID = OI.OrderID AND CL.orderItemNumber = OI.OrderItemID
INNER JOIN dbo.LicenseTypes LT ON LT.id = CL.licenseTypeId
INNER JOIN dbo.LicenseTermTypes Terms ON LT.licenseTermTypeId = Terms.[id]
INNER JOIN dbo.LicenseResidentTypes Res ON LT.licenseResidentTypeId = Res.[id]
LEFT OUTER JOIN dbo.CustomerAddresses CA ON CA.customerID = C.[id] AND addressType = 'P'
WHERE CL.licenseTypeId IN ( SELECT Number FROM [dbo].[fn_SplitInt](@LicenseTypeID, ',') )
AND CAST(O.orderDate AS DATE) >= @StartDate
AND CAST(O.orderDate AS DATE) <= @EndDate
AND ( CL.isVoided <> 1 OR CL.isVoided IS NULL )
AND ( CL.isInvalidated <> 1 OR CL.isInvalidated IS NULL )
AND O.orderStatusTypeId = 1
ORDER BY PurchaseDate ASC ,
C.lastName ASC;END
And the report runs as expected in Visual Studio and when I deploy it to the report server.
But in my License Type parameters, there is a specific parameter that I would like to exclude for the list. And in the License Type procedure, you can see I have this particular line commented out:
--AND LT.id <> 195.
The issue that I cannot resolve and do not understand is that if I updated the stored procedure to exclude LT.id = 195, the report will still run in Visual Studio 2013 but once I deploy it to the report server - the report no longer runs.
Here you can see in Visual Studio that strange license type has been filtered out:
And when I run the report in Visual Studio 2013, I get a valid report:
So I deploy the report to the report server and attempt to run the report:
Nothing appears to happen - the report is blank and the only thing that I have changed is to filter out the license type 195 in the stored procedure that generates a listing of my license type. And what makes this even more confounding, I can select multiple license types with the ID of 195 filtered out - and the report will run with no issues:
Would anyone have a suggestion or idea as to why filtering out one specific ID would cause the "Select All" option to no longer work on this report?
-
Have you tried tracing the SQL calls coming from the report run? and then trying to debug the stored procedure? It would be interesting to understand what was being passed to the procedure parameters.
-
In your screen shot it looks like the report that came up blank in SSRS was for "1 Day Fishing - All Species" licenses. Are you sure there is data in the date range for that license type?
-
Hey Myles - I've tried to run a Profiler trace on the database to see what parameters are being passed - but I am not a member of of the sysadmin or have ALTER TRACE permissions to do so. I will have to get permissions from a DBA.
And doug - yes, I am sure there is data. When I run the report in Visual Studio using the same exact parameters, I get data back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply