SSRS NULL value Query

Question

SSRS NULL value Query

pprender 1

Hi,

I am running a SSRS report which shows Active Directory users that have 4 specified properties with specified values. Problem i am trying to overcome is "If one of the properties I supply is not defined for a user, that user will not appear in the report. I can see from ssms query that a user may have NULL where the property should be, ie title = NULL.

Is it possible for me to return all user rows even if one of the properties(title) i supply is null for a user

user Title Company Manager

John NULL Contoso me

Tom tom tom tom

="
CREATE TABLE #TempMacro_PV (mask VARCHAR(8000))
CREATE TABLE #TempMacro_PV2 (mask VARCHAR(8000))
CREATE TABLE #TempMacro_PV3 (mask VARCHAR(8000))
CREATE TABLE #TempMacro_PV4 (mask VARCHAR(8000))

        DECLARE @FirstStr NVARCHAR(4000)
        DECLARE @i INT, @j INT

        SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList1_Values.Value) &"' AS NVARCHAR(4000))
        SET @i = - 1

        WHILE @i <> 0
        BEGIN
            IF @i < 0
                SET @i = 0 

            SET @j = CHARINDEX(';', @FirstStr, @i + 1)
            SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j END 

            INSERT INTO #TempMacro_PV VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))

            SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END
            SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList2_Values.Value) &"' AS NVARCHAR(4000))
            SET @i = - 1

            WHILE @i <> 0
            BEGIN
                IF @i < 0
                    SET @i = 0

                SET @j = CHARINDEX(';', @FirstStr, @i + 1)
                SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j
            END 

            INSERT INTO #TempMacro_PV2 VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))

            SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END 
        SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList3_Values.Value) &"' AS NVARCHAR(4000))
            SET @i = - 1

            WHILE @i <> 0
            BEGIN
            IF @i < 0
                SET @i = 0 

            SET @j = CHARINDEX(';', @FirstStr, @i + 1)
            SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j END 

            INSERT INTO #TempMacro_PV3 VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))

            SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END
        SET @FirstStr = CAST(N'"& Code.Parameter_GetCodedAttrValues(Parameters!Parameter_AttrbitesList4_Values.Value) &"' AS NVARCHAR(4000))
            SET @i = - 1

            WHILE @i <> 0
            BEGIN
            IF @i < 0
                SET @i = 0 

            SET @j = CHARINDEX(';', @FirstStr, @i + 1)
            SET @j = CASE WHEN @j = 0 THEN LEN(@FirstStr) + 1 ELSE @j END 

            INSERT INTO #TempMacro_PV4 VALUES (REPLACE(REPLACE(LTRIM (RTRIM (SUBSTRING (@FirstStr, @i + 1, @j - @i - 1))), '*', '%'), '?', '_'))

            SET @i = CASE WHEN @j = LEN(@FirstStr) + 1 THEN 0 ELSE @j END END


        SELECT DISTINCT
            CASE WHEN Parent.ObjectPath collate database_default LIKE '%/' collate database_default THEN SUBSTRING (Parent.ObjectPath, 1, len(Parent.ObjectPath) - 1) ELSE Parent.ObjectPath END  collate database_default AS [ParentName],
            Object.ObjectName AS [ObjectName],
            CAST(
                CASE
                    WHEN Object.objectpath collate database_default LIKE '%[^\]/%' THEN SUBSTRING(Object.objectpath, 1, len(Object.objectpath) - patindex('%/[^\]%', reverse(Object.objectpath))) collate database_default
                    WHEN Object.objectpath collate database_default LIKE '%,ou=%' THEN SUBSTRING(Object.objectpath, charindex(',', Object.objectpath) + 1, LEN(Object.objectpath)) collate database_default
                    ELSE ''
                END
                AS NVARCHAR(2000)
            ) AS [Container],
            pd.Type AS [Type],
            pd2.Type AS [Type2],
        pd3.Type AS [Type3],
        pd4.Type AS [Type4],
            pd.pk AS [PropertyID],
            pd2.pk AS [PropertyID2],
        pd3.pk AS [PropertyID3],
        pd4.pk AS [PropertyID4],
            Object.pk AS [pk]
        INTO
            #TempMacro_V1
        FROM
            ObjectsObjects AS Object   
    INNER JOIN ObjectsPropertyValues AS pv ON Object.pk = pv.objectid
            INNER JOIN PropertyDescriptions AS pd ON pd.pk = pv.PropertyID AND pd.PropertyName = N'"&Parameters!Parameter_AttrbitesList1.Value &"'
            INNER JOIN(SELECT mask FROM #TempMacro_PV AS pf) AS t1 ON
                pv.Value_String collate database_default LIKE t1.mask collate database_default
                OR CAST(pv.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t1.mask collate database_default
                OR CONVERT(NVARCHAR(255), pv.Value_Time, 120) collate database_default LIKE t1.mask collate database_default
                OR CAST(pv.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t1.mask collate database_default   
    INNER JOIN ObjectsPropertyValues AS pv2 ON Object.pk = pv2.objectid
            INNER JOIN PropertyDescriptions AS pd2 ON pd2.pk = pv2.PropertyID AND pd2.PropertyName = N'"&Parameters!Parameter_AttrbitesList2.Value &"'
            INNER JOIN(SELECT mask FROM #TempMacro_PV2 AS pf) AS t2 ON
                pv2.Value_String collate database_default LIKE t2.mask collate database_default
                OR CAST(pv2.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t2.mask collate database_default
                OR CONVERT(NVARCHAR(255), pv2.Value_Time, 120) collate database_default LIKE t2.mask collate database_default
                OR CAST(pv2.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t2.mask collate database_default
    INNER JOIN ObjectsPropertyValues AS pv3 ON Object.pk = pv3.objectid
            INNER JOIN PropertyDescriptions AS pd3 ON pd3.pk = pv3.PropertyID AND pd3.PropertyName = N'"&Parameters!Parameter_AttrbitesList3.Value &"'
            INNER JOIN(SELECT mask FROM #TempMacro_PV3 AS pf) AS t3 ON
                pv3.Value_String collate database_default LIKE t3.mask collate database_default
                OR CAST(pv3.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t3.mask collate database_default
                OR CONVERT(NVARCHAR(255), pv3.Value_Time, 120) collate database_default LIKE t3.mask collate database_default
                OR CAST(pv3.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t3.mask collate database_default  
    INNER JOIN ObjectsPropertyValues AS pv4 ON Object.pk = pv4.objectid
            INNER JOIN PropertyDescriptions AS pd4 ON pd4.pk = pv4.PropertyID AND pd4.PropertyName = N'"&Parameters!Parameter_AttrbitesList4.Value &"'
            INNER JOIN(SELECT mask FROM #TempMacro_PV4 AS pf) AS t4 ON
                pv4.Value_String collate database_default LIKE t4.mask collate database_default
                OR CAST(pv4.Value_Integer AS NVARCHAR(1024)) collate database_default LIKE t4.mask collate database_default
                OR CONVERT(NVARCHAR(255), pv4.Value_Time, 120) collate database_default LIKE t4.mask collate database_default
                OR CAST(pv4.Value_Double AS NVARCHAR(1024)) collate database_default LIKE t4.mask collate database_default             
        INNER JOIN ObjectsObjects AS Parent ON Object.ObjectParent = Parent.pk
        WHERE
            Object.Tag = 1 AND ISNULL(Object.ObjectPath, '') <> ''
            AND Object.ObjectType = 'User' AND pd.Type IN('VARCHAR','INTEGER','DATETIME','DOUBLE') AND pd2.Type IN('VARCHAR','INTEGER','DATETIME','DOUBLE') AND pd3.Type IN('VARCHAR','INTEGER','DATETIME','DOUBLE')
            AND
            (("& Code.ParamSQL_Parameter_OUObject_Like(Parameters!Parameter_OUObject_Like.Value) &") AND (NOT ("& Code.ParamSQL_Parameter_OUObject_NotLike(Parameters!Parameter_OUObject_NotLike.Value) &")))
            AND
            (("& Code.ParamSQL_Parameter_UserObject_Like(Parameters!Parameter_UserObject_Like.Value) &") AND (NOT("& Code.ParamSQL_Parameter_UserObject_NotLike(Parameters!Parameter_UserObject_NotLike.Value) &")))

        SELECT Object.ParentName AS [ParentName],
            Object.ObjectName AS [ObjectName],
            Object.Container AS [Container],
            CASE Object.Type WHEN 'VARCHAR' THEN pv.Value_String
                            WHEN 'INTEGER' THEN CAST(pv.Value_Integer AS NVARCHAR(1024))
                            WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv.Value_Time, 120)
                            WHEN 'DOUBLE' THEN CAST(pv.Value_Double AS NVARCHAR(1024))
                            ELSE '' END AS [Value],
            pv.ValueNumber AS [ValueNumber],
            CASE Object.Type2 WHEN 'VARCHAR' THEN pv2.Value_String
                            WHEN 'INTEGER' THEN CAST(pv2.Value_Integer AS NVARCHAR(1024))
                            WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv2.Value_Time, 120)
                            WHEN 'DOUBLE' THEN CAST(pv2.Value_Double AS NVARCHAR(1024))
                            ELSE '' END AS [Value2],
            pv2.ValueNumber AS [ValueNumber2],
        CASE Object.Type2 WHEN 'VARCHAR' THEN pv3.Value_String
                            WHEN 'INTEGER' THEN CAST(pv3.Value_Integer AS NVARCHAR(1024))
                            WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv3.Value_Time, 120)
                            WHEN 'DOUBLE' THEN CAST(pv3.Value_Double AS NVARCHAR(1024))
                            ELSE '' END AS [Value3],
        pv3.ValueNumber AS [ValueNumber3],
        CASE Object.Type4 WHEN 'VARCHAR' THEN pv4.Value_String
                            WHEN 'INTEGER' THEN CAST(pv4.Value_Integer AS NVARCHAR(1024))
                            WHEN 'DATETIME' THEN CONVERT(NVARCHAR(255), pv4.Value_Time, 120)
                            WHEN 'DOUBLE' THEN CAST(pv4.Value_Double AS NVARCHAR(1024))
                            ELSE '' END AS [Value4],
        pv4.ValueNumber AS [ValueNumber4],
            Object.pk AS [pk]
        INTO #TempMacro_V
        FROM
            #TempMacro_V1 AS Object
            INNER JOIN ObjectsPropertyValues AS pv ON Object.pk = pv.objectid AND Object.PropertyID = pv.PropertyID
            INNER JOIN ObjectsPropertyValues AS pv2 ON Object.pk = pv2.objectid AND Object.PropertyID2 = pv2.PropertyID
    INNER JOIN ObjectsPropertyValues AS pv3 ON Object.pk = pv3.objectid AND Object.PropertyID3 = pv3.PropertyID
    INNER JOIN ObjectsPropertyValues AS pv4 ON Object.pk = pv4.objectid AND Object.PropertyID4 = pv4.PropertyID

        SELECT PK, [Value], ValueNumber, Value2, ValueNumber2, Value3, ValueNumber3, Value4, ValueNumber4
        INTO #TempMacro_VV
        FROM #TempMacro_V

        CREATE CLUSTERED INDEX idv ON #TempMacro_VV (PK)

        /*Insertion of 08.02.2010 - beginning*/
        DECLARE @CountVal INT, @ValLen INT
        SELECT @CountVal = MAX(ValueNumber) FROM #TempMacro_VV
        SET @ValLen = 1500

        SELECT DISTINCT
            P.PK AS PK,
            CAST(P.[Value] AS NVARCHAR(1500)) AS PValue
        INTO #TempMacro_P1
        FROM #TempMacro_VV AS P
        WHERE P.ValueNumber = 0

        CREATE CLUSTERED INDEX id1 ON #TempMacro_P1 (PK)

        SET @I = 1
        WHILE @I <= @CountVal
        BEGIN
            UPDATE #TempMacro_P1
            SET PValue = CASE WHEN (LEN(PValue) + LEN(P.[Value])) < (@ValLen - 2) THEN PValue + '; ' + P.[Value] ELSE PValue END
            FROM #TempMacro_P1 AS P1
            INNER JOIN #TempMacro_VV AS P ON P1.PK = P.PK
            WHERE P.ValueNumber = @I

            SET @I = @I + 1
        END

        DECLARE @CountVal2 INT
        SELECT @CountVal2 = MAX(ValueNumber2) FROM #TempMacro_VV

        SELECT DISTINCT
            P.PK AS PK,
            CAST(P.[Value2] AS NVARCHAR(1500)) AS PValue
        INTO #TempMacro_P2
        FROM #TempMacro_VV AS P
        WHERE P.ValueNumber2 = 0

        CREATE CLUSTERED INDEX id2 ON #TempMacro_P2 (pk)

        SET @I = 1
        WHILE @I <= @CountVal2
        BEGIN
            UPDATE #TempMacro_P2
            SET PValue = CASE WHEN (LEN(PValue) + LEN(P.Value2)) < (@ValLen - 2) THEN PValue + '; ' + P.Value2 ELSE PValue END
            FROM #TempMacro_P2 AS P2
            INNER JOIN #TempMacro_VV AS P ON P2.PK = P.PK
            WHERE P.ValueNumber2 = @I

            SET @I = @I + 1
        END
    DECLARE @CountVal3 INT
        SELECT @CountVal3 = MAX(ValueNumber3) FROM #TempMacro_VV
        SET @ValLen = 1500

        SELECT DISTINCT
            P.PK AS PK,
            CAST(P.[Value3] AS NVARCHAR(1500)) AS PValue
        INTO #TempMacro_P3
        FROM #TempMacro_VV AS P
        WHERE P.ValueNumber3 = 0

        CREATE CLUSTERED INDEX id3 ON #TempMacro_P3 (PK)

        SET @I = 1
        WHILE @I <= @CountVal
        BEGIN
            UPDATE #TempMacro_P3
            SET PValue = CASE WHEN (LEN(PValue) + LEN(P.[Value3])) < (@ValLen - 2) THEN PValue + '; ' + P.Value3 ELSE PValue END
            FROM #TempMacro_P3 AS P3
            INNER JOIN #TempMacro_VV AS P ON P3.PK = P.PK
            WHERE P.ValueNumber3 = @I

            SET @I = @I + 1
        END
    DECLARE @CountVal4 INT
        SELECT @CountVal4 = MAX(ValueNumber4) FROM #TempMacro_VV
        SET @ValLen = 1500

        SELECT DISTINCT
            P.PK AS PK,
            CAST(P.[Value4] AS NVARCHAR(1500)) AS PValue
        INTO #TempMacro_P4
        FROM #TempMacro_VV AS P
        WHERE P.ValueNumber4 = 0

        CREATE CLUSTERED INDEX id4 ON #TempMacro_P4 (PK)

        SET @I = 1
        WHILE @I <= @CountVal
        BEGIN
            UPDATE #TempMacro_P4
            SET PValue = CASE WHEN (LEN(PValue) + LEN(P.[Value4])) < (@ValLen - 2) THEN PValue + '; ' + P.Value4 ELSE PValue END
            FROM #TempMacro_P4 AS P4
            INNER JOIN #TempMacro_VV AS P ON P4.PK = P.PK
            WHERE P.ValueNumber4 = @I

            SET @I = @I + 1
        END
        /*Insertion of 08.02.2010 - end*/

        SELECT
            Objects.ParentName AS [ParentName],
            Objects.ObjectName AS [ObjectName],
            P1.PValue AS [Value],
            N'"&Parameters!Parameter_AttrbitesList1.Value &"' AS [PropertyName],
            P2.PValue AS Value2,
            N'"&Parameters!Parameter_AttrbitesList2.Value &"' AS [PropertyName2],
        P3.PValue AS Value3,
            N'"&Parameters!Parameter_AttrbitesList3.Value &"' AS [PropertyName3],
        P4.PValue AS Value4,
            N'"&Parameters!Parameter_AttrbitesList4.Value &"' AS [PropertyName4],
            1 AS [S],
            Objects.pk AS [pk]
        FROM #TempMacro_P1 AS P1
        INNER JOIN #TempMacro_P2 AS P2 ON P1.PK = P2.PK
    INNER JOIN #TempMacro_P3 AS P3 ON P1.PK = P3.PK
    INNER JOIN #TempMacro_P4 AS P4 ON P1.PK = P4.PK
        LEFT JOIN #TempMacro_V AS Objects ON Objects.PK = P1.PK AND Objects.PK = P2.PK AND Objects.PK = P3.PK AND Objects.PK = P4.PK AND Objects.ValueNumber = 0 AND Objects.ValueNumber2 = 0 AND Objects.ValueNumber3 = 0 AND Objects.ValueNumber4 = 0
        WHERE (" & Code.ParamSQL_Parameter_Domains_In_5_1_Item1(Parameters!Parameter_Domains_In_5_1.Value) & ")
        ORDER BY 1, "& Parameters!Parameter_Sorting.Value &" ASC, 2 ASC 

        DROP TABLE #TempMacro_P1
        DROP TABLE #TempMacro_P2
    DROP TABLE #TempMacro_P3
        DROP TABLE #TempMacro_PV
        DROP TABLE #TempMacro_PV2
    DROP TABLE #TempMacro_PV3
    DROP TABLE #TempMacro_PV4
        DROP TABLE #TempMacro_VV
      "

5 answers

Your answer

Answer 1

Michael Taylor 60,161

In SQL if a value is NULL then any additions to that value result in NULL.

   Column + '*'  -- NULL if Column is NULL

To handle nulls you generally use a coalesce or isnull.

   ISNULL(Column, '(empty)' + '*'  -- Will not be null

In general having NULL just means you'll see it in the output that way. If you are seeing them filtered out then that means you have a WHERE clause (or perhaps GROUP BY) that is detecting the NULL and removing it. Adjust the clause to take NULL into account.

   Column = ''   -- False if NULL  
   ISNULL(Column, '') = ''  -- True if NULL

pprender 1 Reputation point

2021-01-11T11:53:39.947+00:00

Thank you for the suggestion, i was able to find this line in the query

WHERE
Object.Tag = 1 AND ISNULL(Object.ObjectPath, '') <> ''

replaced it with

WHERE
Object.Tag = 1 AND ISNULL(Object.ObjectPath, '') = '' -- True if NULL

Result
Query Runs but No Rows are retrieved
Michael Taylor 60,161 Reputation points

2021-01-11T17:27:20.707+00:00

Take that entire query you're running in SSRS, after adjustments and run it in SSMS. Do you get any results back? If so then the query is fine and the issue is how you are displaying your data in SSRS. If the query doesn't return results in SSMS then the problem is still with the query. I tend to break complex queries down into the first SELECT...FROM and then add the WHERE statement clause by clause until I get unexpected results. If all that works then I add in the GROUP BY clauses until it breaks.

Answer 2

Joyzhao-MSFT 15,631

Hi @pprender ,
I’m not sure if you want to set a parameter that allows null values?
I am sorry that I am not good at TSQL statements, if you allow null value parameters in SSRS, you could refer to: https://www.mssqltips.com/sqlservertip/3502/allow-null-value-in-multi-value -report-parameter-in-sql-server-reporting-services/

I added the tag of TSQL, I hope more experts could help you.
Best Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Joyzhao-MSFT 15,631 Reputation points

2021-01-11T09:14:39.6+00:00

When I tried to execute the SQL statement, a lot of errors were displayed. I asked my colleagues about TSQL. The errors were extensive and the data was incomplete. Because there is no more data available, the test cannot be performed.

Answer 3

Hi @pprender ，
You’ll use INNER JOIN when you want to return only records having pair on both sides, and you’ll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not. If you’ll need all records from both tables, no matter if they have pair, you’ll need to use CROSS JOIN (or simulate it using LEFT JOINs and UNION).
If the parameter allows null values, you can set the parameter attribute to allow null values after adding the parameter in SSRS. As shown below:

To set parameters in the report, you can refer to: Tutorial: Add a Parameter to Your Report (Report Builder)
Report Parameters (Report Builder and Report Designer)
Add, Change, or Delete a Report Parameter (Report Builder and SSRS)
Hope this helps.
Best Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Could this be where my issue is , perhaps missing a ISNULL statement

SELECT
Objects.ParentName AS [ParentName],
Objects.ObjectName AS [ObjectName],
P1.PValue AS [Value],
N'"&Parameters!Parameter_AttrbitesList1.Value &"' AS [PropertyName],
P2.PValue AS Value2,
N'"&Parameters!Parameter_AttrbitesList2.Value &"' AS [PropertyName2],
P3.PValue AS Value3,
N'"&Parameters!Parameter_AttrbitesList3.Value &"' AS [PropertyName3],
P4.PValue AS Value4,
N'"&Parameters!Parameter_AttrbitesList4.Value &"' AS [PropertyName4],
1 AS [S],
Objects.pk AS [pk]
FROM #TempMacro_P1 AS P1
LEFT JOIN #TempMacro_P2 AS P2 ON P1.PK = P2.PK
LEFT JOIN #TempMacro_P3 AS P3 ON P1.PK = P3.PK
LEFT JOIN #TempMacro_P4 AS P4 ON P1.PK = P4.PK
LEFT JOIN #TempMacro_V AS Objects ON Objects.PK = P1.PK AND Objects.PK = P2.PK AND Objects.PK = P3.PK AND Objects.PK = P4.PK AND Objects.ValueNumber = 0 AND Objects.ValueNumber2 = 0 AND Objects.ValueNumber3 = 0 AND Objects.ValueNumber4 = 0
WHERE (" & Code.ParamSQL_Parameter_Domains_In_5_1_Item1(Parameters!Parameter_Domains_In_5_1.Value) & ")
ORDER BY 1, "& Parameters!Parameter_Sorting.Value &" ASC, 2 ASC

Joyzhao-MSFT 15,631 Reputation points

2021-01-15T08:04:09.07+00:00

If you think it is a query statement problem, I suggest that you provide the complete test data to TSQL, just add the TSQL tag to your newly created issue, and experts will troubleshoot your statement to see if there is a problem with the statement.

Answer 5

PJP 1

thank you for your responses

closing issue

Share via

SSRS NULL value Query

5 answers

Your answer