Using restriction values in a case statment.

[ismael2314]

I was trying to create a report that uses the restriction for CASE and calculates the result, but I don't want the restriction to be part of the where clause.

The query looks like this:

select name, age, case when hiredDate < restrictionValue then salary else ' ' end from employee where age > 30

the above query is just a sample of what I wanted to do using seal.

[ariacom]

For this, you have to code some C# in a task...
Create an InputValue of type Numeric and Prompt It
Then add a task that will override the SQL for the column selected in the model like:

@using System.Data
@using System.Data.Common
@{
    ReportTask task = Model;
    Report report = task.Report;
    //Take the first input value that has been defined in the report
    var inputValue = report.InputValues[0];
    var model = report.Models.First();
    var element = model.Elements.FirstOrDefault(i => i.SQLColumn == "[Order Details].Quantity");
    if (element != null) element.SQL = $"CASE WHEN [Order Details].Quantity < {inputValue.DoubleValue} THEN -1 ELSE [Order Details].Quantity END";    
}

In attachment is a report sample based on Northwind,

Check also sample: 301-Input Values - Columns to display and Factor
Test restrictions input value.zip