none
SSRS 2008 setting default values on paramters

    Question

  • In a SSRS 2008 r2 existing report, I am trying to add a new paramerter to the report called @GRADE. Currently the
    report runs based upon @Course. Now I want to run the report based upon if the @Course or  @GRADE parameter
    is selected. Depending which parameter is selected, I want to generate the report in that manner. The following is
    a small snapshot of the code in the dataset.

    IF @Course IS NOT NULL
        WITH Ctudent AS (

      SELECT
        BCourseSK
       
           
            FROM
       DataStore.dbo.vwBr
      WHERE
       BCourseSK = @Course
      
    )
    ELSE IF  @GRADE IS NOT NULL

     WITH Btudent AS (

             SELECT              [grade]
                                 
           
            FROM
        [DataStore].[dbo].[vwBr]

      WHERE
       grade = @GRADE

      
    )

    Basically either  @Course or  @GRADE parameter is selected but not both. For this to occur,
    I have set the default value for these paramters to be null. From what I have
     seen on the internet, setting the default paramter value to null is the same as setting the parameter as
    an optional value.

    My problem is the way I set the default parameter values to NULL. The query will not pick any values
    when the report is executed.
    (When   run the sql in SSIS manager, the sql is working).

    Thus can you tell me what you recommend I do so that the query runs and picks records?

    Tuesday, October 15, 2013 10:09 PM

Answers

  • You need to first decide what should be the starting condition for the report. As per your explanation you need to pass a value for either of the two parmeters. Otherwise you'll end up with empty report. Or make the report in such a way that it gives all data initially and then you can select a value for grade or course or both to apply the required filtering. In that case logic should be to bypass the filter under default conditions (NULL value)

    Wednesday, October 16, 2013 5:56 AM
  • SSRS will run a report automatically when all parameters have a default value, in this case both parameters have a default value of NULL.

    I'm assuming you can launch the report, it runs, but no results are returned. You can then enter a parameter value, rerun, and the report works (is this correct?)

    Currently, the query does not support this (both values are NULL). In this instance, the query doesn't have a logical way to process.

    You will likely either have to accept that the report will run and not return results or decide what the starting value should be for the report to have it initially run.

    Your other option is to split this into two separate reports.


    ----- This posting is provided "AS IS" with no warranties, and confers no rights

    Tuesday, October 15, 2013 11:34 PM
  • Hi Wendy,

    In Reporting Services, when the parameter was set to "Allow NULL value", then we cannot select any records using the query below on the dataset:
    WHERE CloumnName=@ParameterName

    If we want to get some default records when check the "Null" checkbox, we can use the query like:
    WHERE ColumnName=IsNull(@ParameterName,"Default values")

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 17, 2013 1:53 AM
    Moderator

All replies

  • SSRS will run a report automatically when all parameters have a default value, in this case both parameters have a default value of NULL.

    I'm assuming you can launch the report, it runs, but no results are returned. You can then enter a parameter value, rerun, and the report works (is this correct?)

    Currently, the query does not support this (both values are NULL). In this instance, the query doesn't have a logical way to process.

    You will likely either have to accept that the report will run and not return results or decide what the starting value should be for the report to have it initially run.

    Your other option is to split this into two separate reports.


    ----- This posting is provided "AS IS" with no warranties, and confers no rights

    Tuesday, October 15, 2013 11:34 PM
  • You need to first decide what should be the starting condition for the report. As per your explanation you need to pass a value for either of the two parmeters. Otherwise you'll end up with empty report. Or make the report in such a way that it gives all data initially and then you can select a value for grade or course or both to apply the required filtering. In that case logic should be to bypass the filter under default conditions (NULL value)

    Wednesday, October 16, 2013 5:56 AM
  • Hi Visakh

    I feel you answer is the perfect solution for this query. This requirement can be achieved only if the default values for the parameters are set to "NULL" or else we will not be able to see the report values.


    Please mark as answer if it is helpful. Thank You

    Wednesday, October 16, 2013 11:10 AM
  • So you are saying that the default values for both report parameters should be null? Then allow the user to select a value if they want t?
    Wednesday, October 16, 2013 1:17 PM
  • Is there a way to make certain the user has to select 1 parameter or the other parameter?
    Wednesday, October 16, 2013 1:18 PM
  • Hi Wendy,

    In Reporting Services, when the parameter was set to "Allow NULL value", then we cannot select any records using the query below on the dataset:
    WHERE CloumnName=@ParameterName

    If we want to get some default records when check the "Null" checkbox, we can use the query like:
    WHERE ColumnName=IsNull(@ParameterName,"Default values")

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 17, 2013 1:53 AM
    Moderator
  • Hi Wendy,

    Yes, there is a way, you can declare the value of parameter ISNULL in the query where you are importing you parameter. This can be done as follows:

    Where ColumnName=ISNULL(@ParamName, "DefaultValues")


    Please mark as answer if it is helpful. Thank You

    Thursday, October 17, 2013 6:03 AM