locked
I need to avoid "Please select a value for the parameter ''ParameterName" message comes. Also retrun all data. RRS feed

  • Question

  • Hi All,

    I need to avoid "Please select a value for the parameter ''ParameterName" message comes in while clicking View report. Also i need to retrun all data from query.

    Below is my sql dataset query: Having 4 parameters all are Multivalue Parameter.

    My client dont want to set any Default values to parameter, as well they want to avoid "Please select a value for the parameter ''ParameterName" message comes while clicking View report. Also i need to retrun all data from query without selecting any parameter value.

    How to do this, Please reply back to me.

    Sql query:

    select

    distinct(tMML.new_metricversion),tMML.new_mhpoints,tMML.new_mhmin,tMML.new_ehpoints,tMML.new_ehmin,

    tMML

    .new_name,tMML.new_metricdescription,tMML.new_riskareaidname,tMML.new_guidance,tMML.new_formatname,

    CASE

    WHEN tMML.new_formatname = 'Integer (##)' THEN '' --Integer (##)"

    WHEN tMML.new_formatname = 'Decimal (##.##)' THEN '' -- Decimal (##.##)

    WHEN tMML.new_formatname = 'Percentage (%)' THEN '%' --Percentage (%)

    WHEN tMML.new_formatname = 'Money ($$.$$)' THEN '$'--Money ($$.$$)

    WHEN tMML.new_formatname = 'String (Abc123)' THEN '' --String (Abc123)

    WHEN tMML.new_formatname is null THEN ''END AS Symbol,

    tMML

    .new_ehoperatorname,tMML.new_mhoperatorname,

    CASE

    WHEN tMML.new_formatname = 'Integer (##)' THEN convert(int,tMS.new_data) --Integer (##)

    WHEN tMML.new_formatname = 'Decimal (##.##)' THEN convert(decimal(20,2),tMS.new_data) -- Decimal (##.##)

    WHEN tMML.new_formatname = 'Percentage (%)' THEN convert(decimal(10,2),LEFT(tMS.new_data, LEN(tMS.new_data) - 1)) --Percentage (%)

    WHEN tMML.new_formatname = 'Money ($$.$$)' THEN convert(decimal(20,2),RIGHT(tMS.new_data, LEN(tMS.new_data) - 1))--Money ($$.$$)

    WHEN tMML.new_formatname = 'String (Abc123)' THEN convert(decimal(38,10),tMS.new_data) --String (Abc123)

    WHEN tMML.new_formatname is null THEN convert(decimal(38,10),tMS.new_data)

    END

    AS Data,tContact.fullname

    from

    FilteredNew_metricmasterlist tMML

    Join

    FilteredNew_metricscore tMS on tMML.new_metricmasterlistid=tMS.new_metricid

    Join

    FilteredContact tContact on tMS.new_contactid=tContact.contactid

    where

    tMML

    .statecodename='Active' and

    tMS

    .statecodename ='Active' and

    --tMS.new_monthname in('Jan','Feb','Mar') and

    --tMS.new_yearname in('2011','2012') and

    --tMML.new_metricversion+'('+tMML.new_name+')' in ('MCB_001_V2_1(Number of customers with clawback transactions)','MAM_001_V2_1(Margin loan customers (where not accredited))') and

    --tContact.new_brandname in('WBC')

    --Parameteres

    tMS

    .new_monthname in(@pMonth) and

    tMS

    .new_yearname in(@pYear) and

    tMML

    .new_metricversion+'('+tMML.new_name+')' in(@pMetric) and

    tContact

    .new_brandname in(@pBrand)


    Natarajan.V


    Monday, June 18, 2012 7:10 AM

Answers

  • Hi Natarajan.V,

    From your description, you want the users to get all the report data without selecting any values for the parameters. Besides, you don’t want specify any default values for the parameters. If I have something misunderstood, please feel free to correct me.

    In Reporting Services, each parameter must have a value which can be a blank value and null value when we try to run a report. This is by design. Based on your scenario, the closet approach is to add a blank value to the parameter available values and set the blank value as the default value. Please note that a blank value will be added to the dropdown list the parameter after doing so. To do this, please refer to the steps below:

    1. Modify the dataset which the parameter “pYear” get values from as follows:

    SELECT DISTINCT Year
    FROM         Your_Table
    UNION
    SELECT     ‘’ AS Year
    ORDER BY Year

    2. Open the Parameter Properties window of the “pYear” parameter, and check the “Allow blank value (“”)” option.

    3. Click “Default Values”, select “Specify values”, click “Add”, delete the “(null)” value an click “OK”.

    3. Modify the query of the main dataset like below:

    WHERE (‘’ IN (@pYear) or Year IN (@pYear))

    Reference:
    How to Default to ALL in an SSRS Multi-select Parameter

    Regards,
    Mike Yin

    • Marked as answer by Mike Yin Sunday, June 24, 2012 2:46 PM
    Tuesday, June 19, 2012 9:20 AM

All replies

  • Hi,

    You can assign default value for your report parameter. 

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Monday, June 18, 2012 7:56 AM
  • Hi there

    Janos is right but in his post he mentioned

    "My client don’t want to set any Default values to parameter, as well they want to avoid "Please select a value for the parameter ''ParameterName" message comes while clicking View report. Also i need to return all data from query without selecting any parameter value"

    To me if you are using multivalued parameter and you don’t want to select any value you could not run the report until you have some value which behave as blank value and you allow multivalued parameter to accept blank value.

    So my recommendation is please put your dataset query like this

    Select yourcolumn as parametervalue ,yourcoulmn as parameterlabel

    From yourtable

    Union all

    Select

    ‘ ‘ as parametervalue,’any’ as parameterlabel

    Select yourcolumn as parametervalue ,yourcoulmn as parameterlabel
    From yourtable
    Union all
    Select 
    ‘ ‘ as parametervalue,’any’ as parameterlabel

    And allow your parameter to have blank value and default set to blank string and your any label will selcted

    I hope this will help

    Many thanks

    Syed

    Monday, June 18, 2012 8:07 AM
  • 1. Use "Allow null value" as parameter setting

    2. Remove references to parameters in your SQL (eg tMS.new_monthname in(@pMonth) ), because we're going to bring back a full resultset (provided this doesn't bring back so many records that your report is slow or times out) and filter at report level.

    3. On your report dataset, right click and choose dataset properties, then Filters (choose "In") and do what you are now doing in SQL in your report filter.

    If this helps, please use the relevant reply option.

    Monday, June 18, 2012 1:11 PM
  • 1. Use "Allow null value" as parameter setting

    2. Remove references to parameters in your SQL (eg tMS.new_monthname in(@pMonth) ), because we're going to bring back a full resultset (provided this doesn't bring back so many records that your report is slow or times out) and filter at report level.

    3. On your report dataset, right click and choose dataset properties, then Filters (choose "In") and do what you are now doing in SQL in your report filter.

    If this helps, please use the relevant reply option.

     

    Hi Matt

    Thanks for your posting. As far as I know in multivalued parameter you would not be able to allow null. Am I right? Please update

    Many thanks

    Syed Qazafi Anjum



    Monday, June 18, 2012 9:11 PM
  • Hi Natarajan.V,

    From your description, you want the users to get all the report data without selecting any values for the parameters. Besides, you don’t want specify any default values for the parameters. If I have something misunderstood, please feel free to correct me.

    In Reporting Services, each parameter must have a value which can be a blank value and null value when we try to run a report. This is by design. Based on your scenario, the closet approach is to add a blank value to the parameter available values and set the blank value as the default value. Please note that a blank value will be added to the dropdown list the parameter after doing so. To do this, please refer to the steps below:

    1. Modify the dataset which the parameter “pYear” get values from as follows:

    SELECT DISTINCT Year
    FROM         Your_Table
    UNION
    SELECT     ‘’ AS Year
    ORDER BY Year

    2. Open the Parameter Properties window of the “pYear” parameter, and check the “Allow blank value (“”)” option.

    3. Click “Default Values”, select “Specify values”, click “Add”, delete the “(null)” value an click “OK”.

    3. Modify the query of the main dataset like below:

    WHERE (‘’ IN (@pYear) or Year IN (@pYear))

    Reference:
    How to Default to ALL in an SSRS Multi-select Parameter

    Regards,
    Mike Yin

    • Marked as answer by Mike Yin Sunday, June 24, 2012 2:46 PM
    Tuesday, June 19, 2012 9:20 AM