Valid Values of report Parameter " "

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Valid Values of report Parameter " "

Author

Topic

jamarc1
Starting Member

10 Posts

Posted - 2008-11-19 : 11:13:15

This is the error message I'm getting
Valid Values of report Parameter " " doesn't have the expected type...

I have this stored proc.. I am returning dates to the report server and its saying that my parameter doesnt have the expected type. I have my datatype in the parameters box also set to the datatype of datetime.. An suggestions

alter PROCEDURE dbo.s_Msm_Personel_Summary_Account
@prmsof int,
@prmdept int,
@prmStart_Date datetime,
@prmEnd_Date datetime

AS

set @prmStart_Date = null
set @prmEnd_Date = null

select distinct employee_name, left(ssn,4) as ssn, [dept_no],
[pos_ctl_no] + ' - ' + [job_title] as pos,
[pos_ctl_no],
dept_name,
[acct_number],
[source_of_funds],
[account_description],
[object_code],
[grant_type],
grant_description,
[job_title],
[dept_no] + ' (' + [dept_name] + ')' as dept_name_no,
[appcategory_sub_description],
[objectcode_description],
start_date,
end_date,
round( a.fte,2,0) as fte,
a.monthlyamount,
round(salary,2,0) as salary,
[total_budgeted]
from
[v_SalDistEmp_Pct_by_GrantType] a,employeepositiondetails c, employee d
where a.position_id = c.position_id
and c.employee_id = d.employee_id
--and position_status_id 7
and (source_of_funds =@prmsof or @prmsof is null or dept_no=@prmdept or @prmdept is null
--and dept_no=@prmdept
and (start_date >= @prmStart_Date or @prmStart_Date is null)
and end_date <= @prmEnd_Date or @prmEnd_Date is null

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 11:18:49

whats the purpose of seeting parameters @prmStart_Date & @prmEnd_Date explicitly to null in procedure?
also nothing related, but you're missing brackets in last parameter filter condition. it should be

(and end_date <= @prmEnd_Date or @prmEnd_Date is null)

jamarc1
Starting Member

10 Posts

Posted - 2008-11-19 : 11:22:02

I removed that and corrected the changes.. Thanks for the heads up..

Can you think of any reason that the error message would be happening?

The property 'Valid Values' of report parameters 'prmStart_Date' doesnt have the expected type. ??

jamarc1
Starting Member

10 Posts

Posted - 2008-11-19 : 11:24:46

The report runs when I set the parameter to data type 'String' under Report Parameters, but returning all the data. Therefore looking like its ignoring the dates..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 11:35:16

is start_date & end_date fields datetime?

jamarc1
Starting Member

10 Posts

Posted - 2008-11-19 : 11:35:45

yes they are

jamarc1
Starting Member

10 Posts

Posted - 2008-11-19 : 11:41:10

thanks.. that is fixed.. the underlying date had a function that actually formatted the date. I removed the function from the date portion and it worked.. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 11:43:00

quote:
Originally posted by jamarc1

thanks.. that is fixed.. the underlying date had a function that actually formatted the date. I removed the function from the date portion and it worked.. :)

Ok. i guessed it should some convertion happening somewhere. Glad that you sorted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources