Create a Multivalue Drop Down in SSRS for an Amount Range

Question

I'm trying to get a multi-value drop-down range setup in SSRS. What I need is for the drop-down range to be one of three options the user selects:

Option 1: 1000 to 4999
Option 2: 5000 to 2499
Option 3: 25000+

[P.S. Don't need the text "Option.." in the drop-down. Just for reference]

enter image description here

So, the user would select one of the above. It's just so an end-user can pull a report based on the values of one of those 3 options. Option 1, would pull anything in the $1000 to $4999 range, Option 2, etc.

I have tried updating and changing parameters. Tried specifying Available Values, e.g.

Amount1 =Parameters!AMOUNT>1000 AND Parameters!AMOUNT> 4999
Amount2 =Parameters!AMOUNT>5000 AND Parameters!AMOUNT> 25000
Amount3 =Parameters!AMOUNT>=5000 AND Parameters!AMOUNT> 25000

But, my drop down is always blank, or I get error messages. I've also set my available values to "Get values from query:" I created a new dataset, and use that DS_Amount for my parameter.

My SQL code for my Dataset is simple, it's just:

SELECT AMT AS AMOUNT
FROM FINANCE
WHERE AMOUNT = @AMOUNT

I've also tried adding in:

WHERE AMOUNT BETWEEN 0.00 AND 4999.99 OR AMOUNT BETWEEN 5000.00 AND 24999.99 OR AMOUNT > 25000.00

...in my dataset. I'm either getting blank drop-downs or errors.

enter image description here

Alan Schofield Alan Schofield · Accepted Answer · 2020-04-01T08:58:11

Parameters are simple key value pairs, so they can have a label and a value only. Typically you use the label to show the user and the value to process in your report.

The way I would approach this is create a table with the value ranges in your database (It sounds like you may have already done this?).

Something like

CREATE TABLE ValueRanges (RangeID INT IDENTITY(1,1), RangeName varchar(20), RangeMin int, RangeMax int)

INSERT INTO ValueRanges (RangeName, RangeMin, RangeMax) VALUES
('1000 to 4999', 1000, 4999),
('5000 to 24999', 5000, 24999),
('25000+', 25000, 999999999)

In your report, create a dataset called dsRange and set the dataset query to

SELECT RangeID, RangeName FROM ValueRanges

Create a parameter in your report called prmRange, set the available values to "Get Values From a query" and select dsRange. Set RangeID as the Value and RangeName as the Label

If you want the user to be able to select more than one range from the drop down then set the parameter to "Allow multiple Values" but do not confuse this with wanting to get multiple values back from your report. So, if the user will only ever choose one range from the list of three then turn this option off.

Assuming that you have only setup a single select parameter then your main dataset query (lets call it dsMain), the one that returns your report's actual data, would simply be something like ...

DECLARE @rngMin int
DECLARE @rngMax int

SELECT @rngMin = RangeMin, @rngMax = RangeMax FROM ValueRanges WHERE RangeID = @prmRange

SELECT AMT AS AMOUNT
    FROM FINANCE f
    WHERE AMOUNT BETWEEN @rngMin AND @rngMax

When you run the report, the user will see three options. They will see the 'RangeName' from ValueRanges table. If they chose for example the second option "5000 to 24999" then they are setting the prmRange parameter value to 2 as this is the RangeID for this row and that's the column we said would be our parameter's value earlier.

Then in the main dataset (dsMain) we have specified @prmRange but not declared it so SSRS knows this must be passed in, as the name is the same as the parameter name, SSRS knows to pass this parameter value in. In our example, it would pass in the value of 2.

We then get the min and max values from our ValueRanges table for this ID and use them to grab the correct data.

Hope this made sense?

Create a Multivalue Drop Down in SSRS for an Amount Range

2 Answers