how to pass the bit value in parameters in ssrs2008

• sambireddy chirra

  SSC-Addicted

  Points: 458

  More actions

  April 29, 2011 at 3:02 am

  #239383

  Hi All,

  i am facing problem in my report, bellow sp working fine in SSMS but inmy report side

  i am using below query to pass the parameter values in the report.

  select distinct case when IsActive=1 then 'Active' Else 'NotActive' end as ActiveStatus

  from OfficeUserRole_Dim

  Below Error Is comeing:convertion failed when converting varchar to bit.

  ALTER PROCEDURE [dbo].[SP_GrantMaker]

  (

  @ActiveStatus Varchar(Max),

  )

  AS

  -- exec [SP_GrantMaker]

  BEGIN

  select

  OfficeUserRoleId as RoleID,

  Officename as EntityName,

  OfficeRole as EntityType,

  OUR.OfficeId as EntityID,

  case when OUR.IsActive=1 then 'Active' Else 'NotActive' end as Activestatus

  from OfficeUserRole_Dim OUR

  inner join costcenter_Dim CCD on CCD.CostCenterName=OUR.OfficeName

  where OUR.IsActive IN (SELECT DISTINCT ITEMS FROM dbo.Split(@ActiveStatus, ','))

  order by Officename, OfficeRole

  END

  Plese slow my problem

  thanks in advance

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 29, 2011 at 7:59 am

  #1317808

  With no ddl or sample data it is a stab in the dark but I guessing that your problem is in

  where OUR.IsActive IN (SELECT DISTINCT ITEMS FROM dbo.Split(@ActiveStatus, ','))

  My guess is this select is returning a value that can't be implicitly converted to a bit datatype.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 29, 2011 at 8:12 am

  #1317811

  Plz don't cross post, I had already answered that question in that thread.

  Plz finish the discussion there.

  http://www.sqlservercentral.com/Forums/Topic1100743-8-1.aspx