Duplicate PARAMETER values in SSRS Report

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 .

Thanks

---

SV

Answer 1

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

---

:)

Answer 2

SELECT DISTINCT customername, 
                group_name, 
                rec_date, 
                rec_ful_nam, 
                rec_status, 
                attribute_value_readonly 
FROM   tablename 

Yes they are distinct

---

SV

Answer 3

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.

Answer 4

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. :-(

---

SV

Answer 5

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?
  

Answer 6

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.

SELECT DISTINCT CUSTOMERNAME

FROM TABLENAME

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

Thanks

---

SV

Answer 7

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.

Answer 8

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.

Reference:

• How to: Add a Filter
• Tutorial: Add a Parameter to Your Report

Regards,

James

---

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