how to display null values in the filter


  • Hi All;

    below is my procedure

    ALTER PROCEDURE [dbo].[TrackingApplicationEvidenceForOutputProgressStageCaseload]
            @Status               nvarchar(2000),  
    		@Programme ntext,
    		@StartDate datetime=null,
            @ProgrammeContract    nvarchar(2000),
            @OwnerName nvarchar(4000),
            @ClientStage nvarchar(4000), 
            --@Application nvarchar(4000),
    		@EndDate datetime=null
    		--@ClaimPeriod nvarchar(4000)
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	insert into testtable
    	values (@Programme)
    	insert into testtable
    	values (@ClaimPeriod)
    declare @addNullProg int, @addNullCont int,@addNullOwner int, @addNullCStage int
        select @addNullProg = 0, @addNullCont = 0, @addNullOwner = 0,@addNullCStage = 0
    if ( charindex('NONE SPECIFIED', @Status) > 0)
           select @addNullCont = 1
    if ( charindex('NOT SPECIFIED',@Programme) > 0)
           set @addNullProg = 1
    if ( charindex('NONE SPECIFIED', @ProgrammeContract) > 0)
           select @addNullCont = 1
    if ( charindex('ALL STAFF', @OwnerName) > 0)
           set @addNullOwner = 1         
    if ( charindex('NOT SPECIFIED', @ClientStage) > 0)
           set @addNullCStage = 1  
        if (@StartDate is null)
          select @StartDate = convert(datetime,'20000101')
    --select @StartDate = convert(varchar,getdate(),23)
        if (@EndDate is null)
           select @EndDate = dateadd(year,1,getdate())  
    select a.new_programmecontractidname as 'new_programmecontractidname',
     a.new_programmeappliedforidname as 'new_programmeappliedforidname', 
    a.new_applicantidname as 'new_applicantidname', 
    a.new_businessidname as 'new_businessidname', 
    a.new_glhhours as 'new_glhhours', 
    a.new_laareaidname as 'new_laareaidname', aa.new_relatedapplicationid,a.new_expectedtrainingcompleteddate,
    a.new_dateappformcompleted, a.new_totalhours,
    a.new_applicationformid as 'new_applicationformid' 
    from FilteredNew_applicationform as a 
    full outer join FilteredNew_evidence as aa on (a.new_applicationformid  =  aa.new_relatedapplicationid) 
    left join FilteredNew_outputlookup ap on (aa.new_evidenceforoutputid  =  ap.new_outputlookupid)
    left join FilteredContact ct on (ct.contactid = a.new_applicantid)
               left join FilteredLead l on (l.leadid = ct.originatingleadid)
     (( a.statuscodename in ( select Value from dbo.fnBO_SplitString(@Status,','))
             or (a.statuscodename is null and @addNullCont=1))
    and    ( a.new_programmeappliedforidname in ( select Value from dbo.fnBO_SplitString(@Programme,','))
               or (a.new_programmeappliedforidname is null and @addNullProg=1))
    and    ( a.new_programmecontractidname in ( select Value from dbo.fnBO_SplitString(@ProgrammeContract,','))
             or (a.new_programmecontractidname is null and @addNullCont=1))
    and    ( a.new_applicationformid in ( select Value from dbo.fnBO_SplitString(@Application,','))
             or (a.new_applicationformid is null and @addNullCont=1))
    and   ( a.owneridname in (select Value from dbo.fnBO_SplitString(@OwnerName,','))
                 or (a.owneridname is null and @addNullOwner=1))
    and    ( a.new_clientstageappnidname in ( select Value from dbo.fnBO_SplitString(@ClientStage,',')))
            -- or (a.new_clientstageappnidname is null and @addNullCStage=1))
    and    isnull(a.new_dateappformcompleted,a.createdonutc) between @StartDate and @EndDate)

    I need to display null values for
    @ClientStage associated with a.new_clientstageappnidname in my report

    currently it dont display any null values
    Any help much appreciated


    Monday, April 29, 2013 2:17 PM

All replies

  • Hello,

    Does your query return the expected value in SSMS? Null value is not displayed in reports (SSRS). If you need to display Null as a value in report then you need to make it a string value

    Monday, April 29, 2013 2:29 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Wow! You have names that are four thousand letters long! What is the magical, generic “status”? Marriage? Employment? Graduation? You invite garbage data and it will come. A good schema seldom uses OUTER JOINs and you have more of them in one procedure than I use in entire applications. We do not use local variables. Competent SQL programmers do not use UDFs; they do not port or optimize. Even worse, using a string splitter tell the world that the programmer does not even know what First Normal Form is. 

    Did you read the BOL about FMTONLY? :
    Do not use this feature. This feature has been replaced by sp_describe_first_result_SET (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL),  sys.dm_exec_describe_first_result_SET (Transact-SQL), andsys.dm_exec_describe_first_result_SET_for_object (Transact-SQL). 

    You have no idea how to design a schema or how to write declarative code. Throw out what you have, get help and start over. When I say that you have done nothing right, I think I speak with some authority and you might want to listen.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi NModerator Monday, April 29, 2013 6:34 PM
    • Unproposed as answer by Simran08 Tuesday, April 30, 2013 7:41 AM
    Monday, April 29, 2013 3:13 PM
  • not clear about what you want, can u explain?
    Wednesday, May 01, 2013 9:48 AM
  • Hi ,

    In the above procedure when i run just the select statement, in which

    field a.new_clientstageappnidname displays null values with associated records

    but when i bring it in SSRS report i cannot display those null values with associated record

    how do i display null values in the ssrs report?

    Any help much appreciated



    Wednesday, May 01, 2013 9:53 AM
  • Hello Prad,

    See my comment above. Nulls are not displayed in SSRS. If you must display the value 'Null' then you can make it a string. e.g ISNULL(a.new_clientstageappnidname,'NULL')

    Wednesday, May 01, 2013 9:56 AM
  • Hi Mayorlaq

    I did replace a.new_clientstageappnidname to ISNULL(a.new_clientstageappnidname,'NULL')

    and in the SSRS filter replace the filter for Client Stage query to below query to display Null

    but it didnt work

    select distinct new_clientstageappnidname from filteredNew_applicationform
    where  new_clientstageappnidname  is not null or
    new_clientstageappnidname  <>''

    statuscodename in (@Status) and new_programmeappliedforidname in (@Programme) and new_programmecontractidname in (@ProgrammeContract)

    SELECT     '(NULL)' AS Expr1
    from filteredNew_applicationform
    new_clientstageappnidname  is null or
    new_clientstageappnidname  =''
    statuscodename in (@Status) and new_programmeappliedforidname in (@Programme) and new_programmecontractidname in (@ProgrammeContract)
    ORDER BY [new_clientstageappnidname]


    Wednesday, May 01, 2013 10:19 AM
  • Hello Prad,

    Although I don't completely understand what you are trying to achieve but I think your query need some adjustment. See updated query below. Does it make any difference?

    Select Distinct new_clientstageappnidname
    From filteredNew_applicationform
    Where  Isnull(new_clientstageappnidname,'') <>''
    And statuscodename in (@Status)
    And new_programmeappliedforidname in (@Programme)
    And new_programmecontractidname in (@ProgrammeContract)


    Select Distinct 'NULL' AS Expr1
    From filteredNew_applicationform
    Where Isnull(new_clientstageappnidname,'') =''
    And statuscodename in (@Status) and new_programmeappliedforidname in (@Programme)
    And new_programmecontractidname in (@ProgrammeContract)
    ORDER BY [new_clientstageappnidname]

    Wednesday, May 01, 2013 11:18 AM
  • Simran,

    Why are you asking a question about Null and show than an long code page where your use of null is hidden.

    Simply create a small piece of code which shows the goal you want to achieve. 

    Then it is easy to help you.

    Be aware that a Null allowed column in SQL server does not contain a Null value. It can be if wished simply not be used at all.


    Wednesday, May 01, 2013 11:23 AM
  • Any progress?

    Can you display ALPHAKAPPA where value is NULL?

    Kalman Toth Database & OLAP Architect
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Wednesday, May 08, 2013 10:30 PM