June 8, 2011 at 10:31 am
Hi,
I want to apply some conditional formatting (to the BackgroundColor). However, I want my expression to only apply to the first column in the matrix, but I can't find if it's possible to reference this. In other words, I want my expression to be like:
=IIf(ColumnPosition=1,"Red","Green")
I can't find anything in BOL and Googling has brought an occasional reference to Fields!ColumnPosition.Value, but this doesn't work.
Thanks,
Barry
June 8, 2011 at 10:35 am
How do you define first column?
The first column if each group of the matrix or really the 1st column period?
June 8, 2011 at 11:06 pm
The first column of the Column Group grouping, e.g. if I had person as the row group, sales date as the column group, and number of orders as the sum, person would be the first column, then the next columns would be the sales dates - it would be the first one of the sales dates columns that I'd be interested in.
Barry
June 9, 2011 at 2:29 am
Then why just changing that column's backgraound not enough? That will put the background for all of them as you want it.
June 9, 2011 at 2:54 am
Apologies, there's an extra element to the expression:
=IIf(ColumnPosition=1,IIf(Fields!FieldName.Value=0,"Red","Green"),Nothing)
In other words, if it's column 1, and if the value = 0 then red, else green - if it's any other column, irrespective of Fields!FieldName.Value, set backgroundcolor to nothing.
Barry
June 9, 2011 at 5:13 am
I just don't get why you need columnposition = 1.
Can you show me a screenshot of what you want in excel?
June 9, 2011 at 6:57 am
Hi,
You're right, a picture is worth a 1000 words so hopefully the attached will help.
Barry
June 9, 2011 at 7:00 am
Nothing attached!
June 9, 2011 at 7:09 am
Doh! It is now.
June 9, 2011 at 7:19 am
What's the difference between the 3 columns? So you basically only have 1 column group and 1 data column in there and you only want the first column to have formatting?
If it is the case you can add another tablix with 1 column and select MIN or MAX of that data column... whatever the order is in the matrix.
Rename the textbox to something meaningfull.
Hide the tablix once you're done testing.
Then use IIF(FIRST(Fields.DateCol.Value) = ReportItems!txtFirstColumnDate.Value...)
The rest of your expression was correct.
June 9, 2011 at 8:15 am
No difference between the date columns. Its just that color coding is only wanted on the most recent day, i.e. the first column.
I'm not sure your expression would work due to the underlying data. For some applications, there may be no records for one or more of the days (I've just used an expression to replace a null value with 0). Hence, my not looking at data values, but the column position instead.
In other words, I only want to color code the most recent day. I can't rely on checking the date in the field as for some applications it may be null.
The obvious solution, I know, is to ensure in the underlying data, that there are records for each day for each app, but I was hoping to avoid this by a simple reference to a column position. But if it can't be done...
Barry
June 9, 2011 at 8:19 am
Is the first column always for today... or the latest date with data?
Either waymy solution to find the max data from the dataset and compare that to the current group column should work.
June 9, 2011 at 8:48 am
I understand now (didn't appreciate that FIRST in SSRS is something completely different to First in T-SQL!).
As my first column will always be yesterday's date, I don't need the extra tablix, I can just do the following:
=IIf(FIRST(Fields!SessionStartDate.Value)=DateAdd("d",-1,Today()),IIf(IsNothing(Fields!NewSessionCount.Value),"Red","Green"),Nothing)
However, this isn't working where the NewSessionCount is null (works fine where there is a value, i.e. fills the cell green only on the first column). I've tried:
=IIf(FIRST(Fields!SessionStartDate.Value)=DateAdd("d",-1,Today()),IIf(Fields!NewSessionCount.Value=0,"Red","Green"),Nothing)
but still no joy.
What is the check for null. So close now...
June 9, 2011 at 8:56 am
tried is nothing or = nothing??
June 9, 2011 at 9:11 am
Apologies for giving false hope, just did more checking, and the part of the expression that tests for the first column isn't working. Well, it does, but only if there a value in Fields!SessionStartDate.Value - if it's null it just evaluates to false.
I've cut it down to just:
=IIf(FIRST(Fields!SessionStartDate.Value)=DateAdd("d",-1,Today()),"Green","Red")
This should make all cells in the first column green, and cells in all other columns red. It does where there is a record in the underlying data for this date, but not if there isn't a record.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply