Is there a way I can use a calculated expression as a parameter in SSRS?

Question

I'm not able to write the parameter in the data set like I have with two non-calculated parameters. If I'm going about this the wrong way any help is greatly appreciated to get me on the right track.

Data Set Query

SELECT 
    Inmast.fpartno
    , inmast.fdescript
    , inmast.fonhand
    , inmast.fnonnetqty
    , inmast.fcstscode
    , inmast.fsource
    , inmast.fprodcl
    , inprod.fpc_desc
    , inmast.fsafety
    , inmast.fbook
    , inmast.fonorder
    , inmast.fproqty
    , inmast.freordqty  
From inmast  
    inner join inprod 
        on inmast.fac + inmast.fprodcl = inprod.fac + inprod.fpc_number  
Where inmast.fcstscode = @Code and inmast.fsource = @Source 

Calculated Expression

(inmast.fonhand 
    + inmast.fonorder 
    + inmast.fproqty 
    - inmast.fbook 
    - inmast.fnonnetqty 
    - inmast.fsafety < 0
) = @CalculatedExpression

Answer 1

You cannot use values from the DataSet to create a Parameter value. The Parameter is used to create the DataSet and therefore the values would not be available for the parameter.

I believe this will accomplish what you're trying to do. First, your new query is:

SELECT 
Inmast.fpartno
, inmast.fdescript
, inmast.fonhand
, inmast.fnonnetqty
, inmast.fcstscode
, inmast.fsource
, inmast.fprodcl
, inprod.fpc_desc
, inmast.fsafety
, inmast.fbook
, inmast.fonorder
, inmast.fproqty
, inmast.freordqty
From inmast  
inner join inprod 
    on inmast.fac + inmast.fprodcl = inprod.fac + inprod.fpc_number  
Where inmast.fcstscode = @Code and inmast.fsource = @Source
and  CASE WHEN 
(inmast.fonhand 
+ inmast.fonorder 
+ inmast.fproqty 
- inmast.fbook 
- inmast.fnonnetqty 
- inmast.fsafety
) < 0 THEN 0 ELSE 1 END = @CalculatedValue 

Next for your new Parameter named @CalculatedValue you will create two default values.

1. label "Less than 0" and value 0
2. label "More than 0" and value 1

What is happening is the case statement will return 0 if the sum of your calculation is less than 0. It will return 1 if it is >= 0. Your @CalculatedValue will just match to that value to filter your results appropriately.