Duplicate PARAMETER values in SSRS Report RRS feed

  • Question

  • Hi All,

    I created a report in SSRS and the report has over 2000 records and i'm trying to set a paramter based on a CUSTOMER NAME. There are only 12 customers and i want to see these 12 customers only in the drop down of the parameters but for some reason i'm seeing these values being repeated multiple times in the PARAMETER drop down box. Please need help as how to get only these 12 names without repeat in the paramters drop down .



    Friday, October 19, 2012 2:31 PM


All replies

  • have you used distinct CutomerName as your dataset for your parameter?


    Friday, October 19, 2012 2:36 PM
  • SELECT DISTINCT customername, 
    FROM   tablename 

    Yes they are distinct


    Friday, October 19, 2012 2:53 PM
  • DISTINCT applies to the entire select. If any of the values are different then the row is distinct. You will need to create a separate dataset to drive the parameter that gets only DISTINCT customer names and applies the same filters as your primary dataset so the 2 will always be in sync. Use that to set available values for the parameter.

    Friday, October 19, 2012 3:11 PM
  • Thanks Tim that worked great, but here is another problem i'm facing. I created a different dataset and used it under the available values, then i went to the PREVIEW and saw the only 12 names that i looking for and then i select 2 customer names and cclicked on view the report and Alas! the report is showing all the records even those customer names that i didn't select. :-(


    Friday, October 19, 2012 3:20 PM
  • This should probably be a separate post since the problem statement has changed. That will help people find the answer to similar questions when they search the forums. When you post the new question please answer

    • Are you filtering the data in the tablix/chart or in the dataset?
    • What does the filter statement look like?
    • Edited by Tim Pacl Friday, October 19, 2012 3:30 PM
    Friday, October 19, 2012 3:27 PM
  • I'm not filtering the data, for data set 1 i used a query as mentioned in my first post, for dataset 2 i used the following query.



    I didn't quite uunder stand about filtering (filtering what?) can you please explain.



    Friday, October 19, 2012 4:45 PM
  • The purpose of the parameter is to filter the dataset or table to show only the data that applies to the selected values from the parameters, correct? So you need to implement that filter. If you haven't set any filters using this parameter value then it won't impact the report.

    As I said, you can implement the filter in your dataset or in the tablix/chart that the data is displayed in. I tend to favor the latter, filtering the tablix/chart. If you open the tablix or chart properties, you will see a filters tab where you can set your filter(s). Remember that in multivalue parameters the values are in an array, not a comma delimited set or string. You can use Join to create a comma-delimited string from the array and use Instr to find whether a value is in the parameter values:

    =IIf(InStr(","+Join(Parameters!Multi.Value,",")+",", ","+Fields!CustomerName+",")>0, True, False)

    Something similar to this should filter the results in the display.

    Friday, October 19, 2012 4:59 PM
  • Hi saivenkat77,

    Just adding to Tim’s answer, if the parameter “CUSTOMERNAME” is a multi-value parameter, we can simply add a filter to the dataset/data region/group as follows:

    Expression: [CUSTOMERNAME]
    Operator: In
    Value: [@CUSTOMERNAME]

    If it is a single value parameter, we can use the “=” operator to replace the “In” operator in the above filter example.




    Welcome to Cross Product & Scenario Based Support Forum(Chinese Edition)

    • Proposed as answer by JamesYi Tuesday, October 23, 2012 8:30 AM
    • Marked as answer by JamesYi Monday, October 29, 2012 3:10 AM
    Tuesday, October 23, 2012 8:30 AM