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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics