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

Can you change that Data Set Query into a stored procedure call? It looks like you've got a typo in your calculated expression, btw. — Brian, Aug 21 '18 at 20:25
I haven't tried that. Still fairly new to SSRS but will give it a try. — David, Aug 21 '18 at 20:42
Okay - let us know how converting that to a stored procedure works out. — Brian, Aug 21 '18 at 20:48
It's not at all clear to me what you're trying to do, or what your question is. — Tab Alleman, Aug 22 '18 at 13:09
I have a report that I'm making and I want to use the calculated field as a parameter for the report.I'm not able to create the parameter using the expression. — David, Aug 22 '18 at 13:55

score 0 · Accepted Answer · answered Aug 22 '18 at 17:26

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.

label "Less than 0" and value 0

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.

Thank you so much for the help. Been stuck on this for days at work. This will help me a lot for future reports. — David, Aug 22 '18 at 19:59

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

1 Answers1