Filter the report data based on user define

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
  2. 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 )

  3. Define the @Condition with predefined value and @Value under parameters
  4. Predefined Value for @Condition parameters:

  5. 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