In SSRS 2008 R2 - How to use relative references to reference fields (columns)

In SSRS 2008 R2 I have a report with a bunch of columns evenly spaced 3 columns apart.  I want to set some conditional formatting so that if the date in column 6 is before the date in column 3 the cell turns red. ...and if the date in column 9 is before the date in column 6 it turns red, etc. for about 16 date fields.

If the current cell can be referenced by "me.value" how would I reference the field -3 columns from "me?"

I want to turn this:
=IIF(Me.Value > 2, "red", "green")

into something like:
=IIF(Me.Value < Me(-3).Value "red", "green")

So that I can just past the same expression in the Fill area of all the columns without having to reference each field by name.

Thanks.  :-)
LVL 1
megninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nico BontenbalCommented:
I'm quite sure you cant' use relative references in SSRS. What you could do is naming your text boxes 'date01', 'date02' etc. Then you can use Me.ToString() to get a string that contains the name of the textbox calling the expression. Based on the name of the textbox you can determine what values to compare and what background color to use. Doing this in one expression will be rather complex. You are probably better of with some custom code. But it will take a lot of time to get the details right. It will be hard to debug and might also cost you a bit of performance. So I think you are better of with 16 different formulas then with a very complex solution with one formula.
Entering the 16 different expressions might be easier when you first set the same expression for every text box and then edit the code (xml) of the report directly to make the changes in the expression for the other 15 text boxes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
Yep.  I agree.  That's just what I ended up doing.  It took less time to just edit 16 expressions than to develop a complex expression that would work on every column.

I was hoping that SSRS had an expression that would allow you to do relative column references similar to how Excel does. ... IIF A1 > A1(-3)  to compare a column 3 columns away from the first.  It sure would be a handy feature.  ;-)

Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.