Parameter Expression - SSRS
I have a fairly simple report where I'm taking YEAR as a parameter (user input) and displaying the results of that year in a table. The issue is calculation in the table...calculations are based on a static number for that year. I want to say
that when the YEAR is = "2010" AND INCOME is greater than 30000 THEN 30000 * .02 , ELSE INCOME * .02. Then I can add each year (2011, 2012, 2013...) as a expression as the years go by with a static number...
=Iif(Parameters!YEAR.Value = 2010 AND Fields!INCOME.Value >= 30000, Fields!INCOME.Value = 30000 * .02, Fields!INCOME.Value * 0.02)
Here is what I have so far and I thought it shoud work? Can anyone point me in the right direction. I'm using VS 2005 to create this report. THANKS!
June 17th, 2011 3:20pm
You’re very close.
For the second expression in your Iif statement remove the “Fields!INCOME.Value = “ since you can’t set a value here.
It’s simply IIF, THEN, ELSE :
=Iif(Parameters!YEAR.Value = 2010 AND Fields!INCOME.Value >= 30000, 30000 * .02, Fields!INCOME.Value * 0.02)
Also, be sure your Parameter has a data type of Integer in the Report Parameter Properties since you are not using quotation marks in your expression.
Martina
http://dataqueen.unlimitedviz.comMartina White
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2011 3:41pm
The right solution would be to create a table "StaticNumbers" and store year and your static numbers in it.Remember to mark as an answer if this post has helped you.
June 17th, 2011 3:43pm
That works! I thought I had tried that before, must have overlooked something. Thank you so much!
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2011 3:52pm
I totally agree Igor, but I can't get permission to create a lookup table (or any table for that matter). in the database I'm working with.
June 17th, 2011 4:25pm
If it's only a few values you could create your "table" like this, and join to your query :
SELECT
T1.YEAR,
T1.INCOME,
Case when T1.INCOME
>= T2.Limit THEN T2.LIMIT* T2.VALUE
ELSE T1.INCOME
* T2.VALUE END as New_Income
FROM your_table AS T1
INNER JOIN
(SELECT 2010
as Year, 20000 as Limit, .02
as Value
UNION
SELECT 2011
as Year,
30000 as Limit, .03 as Value
UNION
SELECT 2012
as Year, 40000 as Limit, .04
as Value) AS T2
ON
T1.YEAR = T2.YEAR
Martina White
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2011 4:56pm