How to select NULL values in SSRS
Hello, I am using below SQL query, SELECT DISTINCT AdvType FROM Status The above query that i am using for the Parameter "Para", there are 3 distinct AdvTypes in database, "Internal", "External", and the third one left blank. I did check "Allow blank values" and I did select MultiValue as well. When i preview the report, the dropdown showing "Internal", "External", and Checked box with no value. when i select checked box, the report returns no records, but there is records. Could please help. Thanks.
November 9th, 2011 4:10am
Hi, To accomplish this wat you can do is create a temperary table. For eg : DECLARE @Test TABLE (id int,Region VARCHAR(50)) Then insert the values from the table having null values in the temp table INSERT INTO @Test SELECT [ID] ,ISNULL([Region],'') FROM [MasterRegion] and then select values from that table SELECT * FROM @Test where Region = @Region parameter @Region values being 'East','West',null Hope this helps. Regards, Anish Shenoy.
November 9th, 2011 5:32am
What do you mean with 'blank'? NULL? Because SELECT ... WHERE AdvType = NULL will never return any row. (NULL is never equal to anything.)
November 9th, 2011 8:44am
Hello, Thanks for reply. In 'AdvType' column there is few empty records ("Blank", nothing in the cell) and 'NULL' records."AdvType" contains date and time, i don't know how to pick these rows which has "Blank cells", "NULL". could please help me. Thanks.
November 9th, 2011 9:43am
... WHERE AdvType IS NULL would return all records without an AdvType information. For your reports, however, you had rather go as follow ... First, try to avoid SELECT DISTINCT queries to get the available values, if you can. You certainly do not want to go through 10 GB of data just to get 3 values, which will probably only change once in a while. Prefer a table on the server, or hard code them into a query, or enter them in the report definition. This being said, try to use the ISNULL function, as suggested by P. Anesh. If you have the values 'Internal', 'External' , '' in @parameters, then you can use the ISNULL function like that: WHERE ISNULL(AdvType,'') IN( @parameters) Please note your current SELECT DISTINCT query may currently return NULL values and not empty strings ( '' ). If you stick to your SELECT DISTINCT, make sure you use the ISNULL function there too.
November 11th, 2011 12:54pm