locked
Problem allowing null values in parameter ~ [Report Builder 3.0] RRS feed

  • Question

  • Hello everyone. My name is Diana De La Torre, I'm a computer systems' engineer working on the mining company Fresnillo PLC.

    I have a trouble with Report Builder 3.0

    I have three parameters that can allow "null" values. Like this:

     

    Then, When I run the report, this is shown:

     

    Does anyone have any idea of why is this happening?

    I really need some help with this because everything I read about how to allow null values on a parameter explains that the only way of doing it is selecting that option in the parameter properties like I did in the first picture.


    :3 ~ Minera Fresnillo S.A. de C.V. ~ >:3

    Wednesday, October 1, 2014 2:09 PM

Answers

  • Hi,

    Based on your description, you are experiencing the problem when you are creating the parameter by Get values from a query, the parameter have the null values which should be allowed to select in the report to filter, right?
    I have tested it on my local environment and reproduced your problem, I have token reference of Patrick Hurst’s answer and SqlCraze’s.

    We have two methods: if you Get values from a query for the available values and there is some cascading between each parameters, you can do some modification in the query to allow the query Conditions include the null values. If we just specify values for the available values of the parameters we can just add a null values in the available values list which already provided by Patrick Hurst.

    Please follow the steps below to modify the query to allow the null values:

    1. Right Click the main dataset1 and select the “Dataset Properties” to edit the existing query like this:

    Select * from Table  where (Libro=(@ParametroBook) OR  Libro is NULL ) and (Proceso=(@Parameter2name)  OR  Proceso  is NULL ) and (Turno=(@Parameter3name) OR  Turno   is NULL )

    2. Right click the dataset2  which include the query for the “Proceso” and select the “Dataset Properties” to edit the existing query like this(If the  parameter “Proceso” have any cascading with the “Libro”):

    Select distinct Proceso  from Tablewhere Libro =(@ParametroBook) OR Libro is NULL

    3. Right click the dataset3  which include the query for the “Turno” and select the “Dataset Properties” to edit the existing query like this(If the parameter “Turno” have any cascading with the Libro):

    Select distinct Turno  from Tablewhere Libro =(@ParametroBook) OR Libro is NULL

    Below links is for your reference about the similar issue:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dfd4efd2-fc0f-4ba2-b225-ce5f7f1e2a96/ssrs-dropdown-should-allow-null-value?forum=sqlreportingservices

    If you still have any question, please feel free to ask.

    Regards
    Vicky Liu

    • Proposed as answer by Vicky_Liu Tuesday, October 14, 2014 12:54 AM
    • Marked as answer by Charlie Liao Tuesday, October 14, 2014 1:47 AM
    Monday, October 6, 2014 9:03 AM

All replies

  • Hi ,

    What type of data you are using ? is there any cascading ?

    There are many alternative way for this .

    but first make sure you check the selection box that is asking for Null Value.

    One is as below,

    The query for the dataset that populates your dropdown should look something like;

    Select Distinct EmployeeNames FROM Employee_Info
    Union
    Select 'NULL' FROM Employee_Info

    This dataset should be used to populate a dropdown with a parameter say @EmployeeName

    Now for the dataset parameter set the value "NULL" as the default value.

    Finally  the main dataset query  should look like;

    Select * From Employee_Information WHERE (EmployeeName = @EmployeeName or @EmployeeName = 'NULL')

    The outcome of this approach is that, the value "NULL" would be selected when you render the form by default instead of "Select a Value".

    Second is as below ,

    you can set the default value for this parameter.

    Thanks


    Please Mark This As Answer or vote for Helpful Post if this helps you to solve your question/problem.



    • Edited by BI_Support Wednesday, October 1, 2014 2:59 PM
    Wednesday, October 1, 2014 2:53 PM
  • If you look a little closer at the parameters selection, you'll notice a check box which you need to check to pass a null.

    You are getting that message, because you are passing a BLANK value, which you have not set to be allowed.

    HTH.

    Wednesday, October 1, 2014 2:56 PM
  • Hi, thank you so much for taking the time of Reading this.

    Yes, I've tried to do it by query too (The type of data I'm using is varchar). I have that in my main dataset query.

    The question in here is that I have the option of using the values obtained from a dataset or don't select anything at all; If the user doesn't select a parameter, it means that it will be a general Select.

    I don't know if I explained myself well (sorry, I'm still improving my english :( ).

    I tried the second option you showed me but it didn't work. I tried to put -> "" <- as a default value but maybe I'm doing it wrong. What should I put in there? (I also tried just leaving it in (Null) and tried typing ->null<- but it didnot work either)

    And sorry if I am too slow with this, this is the first time I use Reporting Services and this is a new world for me, I just finished my Career so I'm new on all these.... :)


    ~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞



    Wednesday, October 1, 2014 3:27 PM
  • See, if this link would help.

    http://geekswithblogs.net/NatasaG/archive/2012/03/28/ssrs-optional-parameters-settings.aspx

    You have changed the properties of the parameter on SSRS side. But the source query or the stored procedure would need to handle the NULL values too.

    Wednesday, October 1, 2014 3:32 PM
  • If you look a little closer at the parameters selection, you'll notice a check box which you need to check to pass a null.

    You are getting that message, because you are passing a BLANK value, which you have not set to be allowed.

    HTH.


    Hey, thank you so much for Reading this.

    Well I've tried everything to make this work; I also allowed "BLANK" values and it didn't work either.

    :( I am a Little bit lost with this, sorry. This is my first time using SSRS.


    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Wednesday, October 1, 2014 3:33 PM
  • See, if this link would help.

    http://geekswithblogs.net/NatasaG/archive/2012/03/28/ssrs-optional-parameters-settings.aspx

    You have changed the properties of the parameter on SSRS side. But the source query or the stored procedure would need to handle the NULL values too.

    I am not using a store procedure because the user they asigned me in this company doesn't have enough permissions to create, modify or delete. (I really wanted a stored procedure because it makes things easier but I had to do everything in a really big query).

    But thank you so very much for taking the time of Reading this, I think this will work for sure for someone who is able to use stored procedures.


    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Wednesday, October 1, 2014 3:38 PM
  • I did this

    And this:

    And didn't work either....


    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Wednesday, October 1, 2014 3:51 PM
  • If you want to leave the parameter blank (not type anything) you need to check the "Allow Blank Value" check box in your first screen shot.

    If you want to submit a NULL you need to check the box next to the parameter when you run it:

    Wednesday, October 1, 2014 7:06 PM
  • In your SQL SOURCE query, did you try doing something like this?

     SELECT * FROM Table
    WHERE
    (Libro = @Libro OR @Libro IS NULL)

    Then, in the "dataset" to populate the @Libro parameter do something like

    SELECT DISTINCT Libro FROM Table
    UNION
    SELECT NULL
    ORDER BY 1

    Wednesday, October 1, 2014 7:45 PM
  • In your SQL SOURCE query, did you try doing something like this?

     SELECT * FROM Table
    WHERE
    (Libro = @Libro OR @Libro IS NULL)

    Then, in the "dataset" to populate the @Libro parameter do something like

    SELECT DISTINCT Libro FROM Table
    UNION
    SELECT NULL
    ORDER BY 1

    Yep, I tried that and didn't work.... :( Sorry.....

    (And thank you so much for your patience)


    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Wednesday, October 1, 2014 9:00 PM
  • @Patrick Hurst:

    Oh, in my reports I don't have any checkbox next to my paremeters. Look:

    How do I do that?


    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Wednesday, October 1, 2014 9:05 PM
  • Libro is set to allow multiple values in that screenshot. SSRS will not allow you to put null values into a multiple value parameter.

    Set it to single, and to allow nulls, and you should then see the check box.

    Wednesday, October 1, 2014 10:09 PM
  • Libro is set to allow multiple values in that screenshot. SSRS will not allow you to put null values into a multiple value parameter.

    Set it to single, and to allow nulls, and you should then see the check box.

    Multiple Value? You mean like I can select two or more fields from various Datasets in that combo, right? (When I called to Mexico's Microsoft support center they told me that.... Anyway those guys aren't good at their Jobs, that's why I am here... :( )

    But if that's what you mean I think I don't have my parameters in multiple values, I mean, all I did is just to fill the combobox with a field of a dataset.

    Well... I think I won't be able to do this in this way anyway....

    I don't know if you can help me with this, but let me explain you more less what I try to do with my Project:

    I have the parameters, right? And then the final user will be able to select the fields he wants from them to make filters for his results. But then I have this other option (that is what my boss wants me to do) and is that if the final user doesn't select the book, shift and process parameters, then the only filters I will have in there will be just the date and the rest of the "Select" will be taken like a general query... I mean like taking all the processes of all the shifts of all the books beween these dates....

    I'm sorry if this sounds confusing, I'm trying to be specific (and it's been a while since I don't practice my english with americans :'< )

    Do you have any idea of how can I do this please? I'm really lost in this part  and this is the only thing left so I can finish this Project.... :(


    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Wednesday, October 1, 2014 10:22 PM
  • (Look this is how my parameters are filled:)


    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Wednesday, October 1, 2014 10:24 PM
  • Have you ticked Allow Blank Values ("") ??

    Wednesday, October 1, 2014 11:53 PM
  • Yes, I did and did not work.... :(

    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Thursday, October 2, 2014 1:25 PM
  • Is this what you're trying to achieve? A null value in a specified value?



    Thursday, October 2, 2014 2:41 PM
  • Yes, that's it. That's how they want me to make this Project work.

    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Thursday, October 2, 2014 3:24 PM
  • So you're problem is solved, then?

    Thursday, October 2, 2014 3:39 PM
  • So you're problem is solved, then?


    No... This is not working yet..... :(

    💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞~ Minera Fresnillo S.A. de C.V. ~ World's first place in silver production ~ Mexico's second place in gold production 💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞💞

    Thursday, October 2, 2014 3:48 PM
  • Hi,

    Based on your description, you are experiencing the problem when you are creating the parameter by Get values from a query, the parameter have the null values which should be allowed to select in the report to filter, right?
    I have tested it on my local environment and reproduced your problem, I have token reference of Patrick Hurst’s answer and SqlCraze’s.

    We have two methods: if you Get values from a query for the available values and there is some cascading between each parameters, you can do some modification in the query to allow the query Conditions include the null values. If we just specify values for the available values of the parameters we can just add a null values in the available values list which already provided by Patrick Hurst.

    Please follow the steps below to modify the query to allow the null values:

    1. Right Click the main dataset1 and select the “Dataset Properties” to edit the existing query like this:

    Select * from Table  where (Libro=(@ParametroBook) OR  Libro is NULL ) and (Proceso=(@Parameter2name)  OR  Proceso  is NULL ) and (Turno=(@Parameter3name) OR  Turno   is NULL )

    2. Right click the dataset2  which include the query for the “Proceso” and select the “Dataset Properties” to edit the existing query like this(If the  parameter “Proceso” have any cascading with the “Libro”):

    Select distinct Proceso  from Tablewhere Libro =(@ParametroBook) OR Libro is NULL

    3. Right click the dataset3  which include the query for the “Turno” and select the “Dataset Properties” to edit the existing query like this(If the parameter “Turno” have any cascading with the Libro):

    Select distinct Turno  from Tablewhere Libro =(@ParametroBook) OR Libro is NULL

    Below links is for your reference about the similar issue:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dfd4efd2-fc0f-4ba2-b225-ce5f7f1e2a96/ssrs-dropdown-should-allow-null-value?forum=sqlreportingservices

    If you still have any question, please feel free to ask.

    Regards
    Vicky Liu

    • Proposed as answer by Vicky_Liu Tuesday, October 14, 2014 12:54 AM
    • Marked as answer by Charlie Liao Tuesday, October 14, 2014 1:47 AM
    Monday, October 6, 2014 9:03 AM
  • Where you have value:

    (NULL)

    try this:

    =System.DBNull.Value

    Wednesday, June 7, 2017 5:42 PM