July 27, 2016 at 9:09 am
Hi,
I am having a problem with SSRS giving me the following error.
The 'startdate' parameter is missing a value
This param in SSRS is set to accepting NULL and hidden.
In the Stored Proc I set this as shown below.
ALTER PROCEDURE [dbo].[USP_Report_Vacancy_Monitoring_Wards] @month VARCHAR(MAX), @year VARCHAR(MAX), @startdate DATE, @enddate DATE, @fp VARCHAR(MAX)
AS
SET @fp = (SELECT MAX(FYMonthNumber) FROM dbo.DmDate WHERE [MonthName] = @month AND [Year] = @year)
SET @startdate = (SELECT MAX(FirstDayOfMonth) FROM dbo.DmDate WHERE [MonthName] = @month AND [Year] = @year)
SET @enddate = (SELECT MAX(LastDayOfMonth) FROM dbo.DmDate WHERE [MonthName] = @month AND [Year] = @year)
Does anyone know why this might be happening?
Thanks,
Paul 😀
July 27, 2016 at 9:14 am
You need to set @startdate, @enddate and @fp as OUTPUT parameters in your stored procedure, don't you?
John
July 27, 2016 at 9:18 am
I have changed the SP to include OUTPUT and no luck.
ALTER PROCEDURE [dbo].[USP_Report_Vacancy_Monitoring_Wards] @month VARCHAR(MAX), @year VARCHAR(MAX), @startdate DATE OUTPUT, @enddate DATE OUTPUT, @fp VARCHAR(MAX) OUTPUT
AS
SET @fp = (SELECT MAX(FYMonthNumber) FROM dbo.DmDate WHERE [MonthName] = @month AND [Year] = @year)
SET @startdate = (SELECT MAX(FirstDayOfMonth) FROM dbo.DmDate WHERE [MonthName] = @month AND [Year] = @year)
SET @enddate = (SELECT MAX(LastDayOfMonth) FROM dbo.DmDate WHERE [MonthName] = @month AND [Year] = @year)
July 27, 2016 at 9:23 am
I can't see your report, so I don't really know what's happening. Does this stored procedure produce the main report, or does it just supply the parameters for the main report? If the latter, consider whether you need output parameters from the stored procedure, or a result set.
John
July 27, 2016 at 9:29 am
This is the SP for the main dataset. What happens is month and year are entered through SSRS to the SP at this point 3 more parameters are based on this and used through out the script.
For example last day of month (end date) and finance period (fp).
July 27, 2016 at 9:38 am
That stored procedure isn't going to return a result set, just some output parameters, so I'm a little confused. And given that startdate is an output parameter for the stored procedure, it shouldn't appear as a parameter in the report, so you should remove it from the list of parameters in the report. I'm guessing, really, because I can't see your report.
John
July 27, 2016 at 9:42 am
The SP is 220 lines long and uses all the params that are set at the top which I showed before.
July 27, 2016 at 9:43 am
For example here;
SELECT * INTO ##BankStaffTemp
FROM (
SELECT
RIGHT([Cost Centre],5) AS 'Cost Centre'
,SUM([WTE Worked]) AS 'Total WTE Worked'
FROM [MCT-BIT-LIVE01].[MCT_STG].[dbo].[Staging_Finance_Extra_Staffing]
WHERE FinancialYear = YEAR(@startdate)
AND FinancialPeriod = @fp
GROUP BY RIGHT([Cost Centre],5) ) T2
July 27, 2016 at 9:44 am
You don't need outputs on these if they are inputs, but your start date should be defined as being optional. In your sp at the moment you have it as a compulsory parameter.
For example, an SP where all parameters are needed:
Create proc AllParamsNeeds_sp @StartDate Date, @EndDate Date, @CustomerID int as
Select OrderDate,
CustomerID,
OrderID,
OrderValue
from Orders
where OrderDate between @StartDate and @EndDate
and (CustomerID = @CustomerID or @CustomerID = NULL)
Note that although i have catered for a NULL on CustomerID, it has to be supplied, so having it is pointless.
To make it optional, I would as = NULL as below:
Create proc AllParamsNeeds_sp @StartDate Date, @EndDate Date, @CustomerID int = NULL as
Select OrderDate,
CustomerID,
OrderID,
OrderValue
from Orders
where OrderDate between @StartDate and @EndDate
and (CustomerID = @CustomerID or @CustomerID = NULL)
This will allow the SP to not have a value for CustomerID passed, as it defaults its values.
Edit:
Question, if you are setting these values in the SP, why are they in your sp definition??
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply