How to pass parameter value to the Dataset


hello,

i did create new parameter,  "start_date_category" (which highlighted in blue color) , hardcoded 3 values ( date project logged, date advertised, on hold start date) (label , value same) "available values" in report parameter properties. above mentioned values column names in report. whenver select column name in parameter dropdown, should retrive data accordingly.these 3 values ( column names ) contains date.
till did (as per online articles), did right click on main dataset , selected dataset properties , selected query tab, click on "fx"( expression) next query. and  inserted following expression:


="select [job title], [task grade], [group], department, [business unit], [line manager], [date project logged], [date advertised], [closing date], [team leader], practitioner,
                      [project status], comments, [ciboodle ref no/ reference number], on_hold_start_date, on_hold_end_date, [advert type], [on-hold comment],
                      [successful candidate], dbo.fngetbusinessdays([date project logged], [closing date], [project status]) [days fill], dbo.fngetbusinessdayshold(on_hold_start_date, on_hold_end_date) [days on hold], attachments from  hr_recruitment_status              where  practitioner=" & "'"& join(parameters!practitioner.value,",")&"' , [group]="& "'"& join(parameters!group.value,",")&"' , [team leader]="& "'"& join(parameters!team_leader.value,",")&"' , [advert type]="& "'"& join(parameters!advert_type.value,",")&"' , [project status]="& "'"& join(parameters!status.value,",")&"'and [" &parameters!start_date_category.value &"] between " &"'" &parameters!date_range_start.value & "' , '" & parameters!date_range_end.value &"'"

i need multi select, that's why did use "join" ( not sure functionality), think there mistake in expression.

could please me, thanks.

hello,

sorry, code snipplet isn't readable.

anyway, shouldn't create sql statement dynamically; should use real sql parameter @para here:

  select ... from ... where column = @para       and ...

wizard recognize sql parameter , can assign report paramater sql parameter.

olaf helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
wenn ich denke, ist das ein fehler und das beweise ich täglich
blog xing


SQL Server  >  SQL Server Reporting Services, Power View



Comments

Post a Comment

Popular posts from this blog

SQL server replication error Cannot find the dbo or user defined function........

hi there, i ran problem while deploying sql 2008 transactional replication between 2 servers lets assume server , b. actually created  both distributor , publisher on server , after created subscriber pointed server b, launch replication monitor , found big red cross. when click on tab "distributor subscriber history" error says "cannot find either column "dbo" or user defined-function or aggregate "dbo.ufnleadingzeros", or name ambiguous". can body regarding issue. regards, ishuv hi, you need compare data , others reseved, index size, unused should not compared. differs. here server - data - 87896 kb server b - data - 78152 kb server data size includes publication tables, can't compare directly. need check article article know exact data size. SQL Server  >  ...
Read more

BIT Version

  hi,     knows how find out ther bit version in sql  , windows os (32 or 64)   regards, s.balavenkatesh   microsoft sql server 2005 - 9.00.1399.06 (intel x86) oct 14 2005 00:33:37 copyright (c) 1988-2005 microsoft corporation standard edition on windows nt 5.2 (build 3790: service pack 2)     intel x86)   -- says sql server 32 bit   madhu SQL Server  >  SQL Server Tools
Read more

Admin Permissions

hi need create new 'role' following permissions, 1. access sql server logs. 2. access maintenance plans ( can avoid access) 3. access activity monitor 4. normal access product databases , not system db´s need guidance on how it, can point me in right direction please? thanks 1.grant  execute on xp_readerrorlog [domainname\ad user or group]; 2. http://technet.microsoft.com/en-us/library/ms191002.aspx 3.view server state permission 4. make user member of dbo_owner database role. best regards,uri dimant sql server mvp, http://sqlblog.com/blogs/uri_dimant/ ms sql optimization: ms sql development , optimization ms sql consulting: large scale of database , data cleansing remote dba services: improves ms sql database performance sql server integration services: business intelligence SQL Server  >  ...
Read more