locked
Not returning results if "NULL" is checked in a parameter value RRS feed

  • Question

  • hi, newbie to SSRS and am running into some problems.  Using SQL Server 2012 and Microsoft SQL Server Reporting Services Designers Version 11.0.3000.0.

    I did the tutorial for just creating a basic report (using the AdventureWorks db).  My report just shows the order date, order #, product description, quantity, and order total.  I added 4 parameters to the report:

    Order Date From, Order Date To, Order Total (>= to), and quantity (= to).  All 4 are set to "Allow null value", with no default values.  However, if the "null" checkbox is checked, meaning I don't want to use that parameter, I do not get any results back from the query!  All 4 boxes are optional and thus the reason I checked the "allow null values".  The only way I can get results back is if I put in a from/to date range, and a value in the  quantity  parameter.  If I don't put a date range in (which to me should mean get all records), or if I DO have a date range but NOT a value in the quantity parameter, I get 0 records back!  I have to have values in the date range and also the quantity parameter or it won't return any records.  I can't figure out why it's behaving this way.  If the parameters are optional, then why is nothing coming back?

    to clarify:  if I leave all 4 NULL, no results; if I put in a date range but leave the quantity and order total NULL, no results; if I put in a date range and also the order total, but leave the quantity NULL, no records;  if I put in a date range and the quantity parameter, I get results; if I put in all four, I get results.

    any help is very much appreciated!


    Monday, September 30, 2013 9:09 PM

Answers

  • hi, newbie to SSRS and am running into some problems.  Using SQL Server 2012 and Microsoft SQL Server Reporting Services Designers Version 11.0.3000.0.

    I did the tutorial for just creating a basic report (using the AdventureWorks db).  My report just shows the order date, order #, product description, quantity, and order total.  I added 4 parameters to the report:

    Order Date From, Order Date To, Order Total (>= to), and quantity (= to).  All 4 are set to "Allow null value", with no default values.  However, if the "null" checkbox is checked, meaning I don't want to use that parameter, I do not get any results back from the query!  All 4 boxes are optional and thus the reason I checked the "allow null values".  The only way I can get results back is if I put in a from/to date range, and a value in the  quantity  parameter.  If I don't put a date range in (which to me should mean get all records), or if I DO have a date range but NOT a value in the quantity parameter, I get 0 records back!  I have to have values in the date range and also the quantity parameter or it won't return any records.  I can't figure out why it's behaving this way.  If the parameters are optional, then why is nothing coming back?

    to clarify:  if I leave all 4 NULL, no results; if I put in a date range but leave the quantity and order total NULL, no results; if I put in a date range and also the order total, but leave the quantity NULL, no records;  if I put in a date range and the quantity parameter, I get results; if I put in all four, I get results.

    any help is very much appreciated!


    Hi ,

    In your dataset query filter (WHERE clause) handle for NULL input parameter values , like

    WHERE Order = ISNULL(@Order , Order )

    using ISNULL or handle your queryWHERE clause  if it gets NULL input parameter values accordingly .

    Related reference link :

    http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/do-you-use-column-param-or-param-is-null


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Charlie Liao Friday, October 4, 2013 7:35 AM
    • Marked as answer by Charlie Liao Tuesday, October 8, 2013 2:28 PM
    Tuesday, October 1, 2013 3:22 AM