Sometime you will found that you need to filter the return data from user interaction such as x value greater or less than y value.

Here is the a simple way to define that not require “case” (Expression), “if-else” (control-of-flow) statement and others. The statement will make use of “OR” and “AND” logical operator to filter out the unwanted records.

Here is the sample report that will filter out the record based on sales or onhand value that define by user.
1. Paste the following code under the created datasource

IF NOT object_id(‘tempdb..#Stock’) IS NULL
DROP TABLE #Stock

CREATE TABLE #Stock
( SKU VARCHAR(10) NOT NULL,
sales DECIMAL(12,0),
onhand DECIMAL(12,0)
)

INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU001′, 100, 2000);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU002′, 200, 1900);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU003′, 300, 1800);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU004′, 400, 1700);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU005′, 500, 1600);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU006′, 600, 1500);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU007′, 700, 1400);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU008′, 800, 1300);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU009′, 900, 1200);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU010′, 1000, 1100);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU011′, 1100, 1000);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU012′, 1200, 900);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU013′, 1300, 800);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU014′, 1400, 700);
INSERT INTO #Stock (SKU, sales, onhand) VALUES (‘SKU015′, 1500, 600);

SELECT * FROM #Stock
WHERE (@Condition = 1 AND sales > @Value) OR
(@Condition = 2 AND sales < @Value) OR
(@Condition = 3 AND onhand > @Value) OR
(@Condition = 4 AND onhand < @Value) OR
(@Condition = 5 )

2. Define the @Condition with predefined value and @Value under parameters

Predefined Value for @Condition parameters:


3. Create your report screen design shown in image

Now you should able to preview the screen by click on “Preview” Tab.

Download the source file