| Author |
Topic |
|
darkdusky
Aged Yak Warrior
591 Posts |
 Posted - 2009-02-09 : 09:35:12
|
| I have a column with an expression on the colour property:=IIF((Parameters!Year.Value(0)<>Fields!Year.Value) AND (Fields!PercentOccupancy.Value <>Nothing),"Red","Black")This is working for most cases except:Some of the cells have a value of Nothing - but as I also have a Round function on the cell - they display a zero rather than blank.My problem is I want the zero to be similarly formatted as it would if it contained a real value.The above expression works except for zeros placed where value=Nothing. These "Nothing zeros" are presently Black while the rest of the column is Red.Before I added the (Fields!PercentOccupancy.Value <>Nothing) part I got the opposite problem - Red "Nothing zeros" in the "Black" column. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 10:04:40
|
| [code]=IIF(Me.Value>=0,"Red","Black")[/code] |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-09 : 11:51:24
|
| Thanks for suggestion but what ever is done to the "Nothing zero" in one column it does the same in the other column. I have tried a lot of different combinations, e.g.=IIF(Parameters!Year.Value(0)=Fields!Year.Value ,"Black",IIf (Me.Value >=0,"Red","Black"))--this one gives Red zeroes in both columns.Do I need to use InScope rather than Parameters!Year.Value(0)=Fields!Year.Value. I basically want the column relating to highest year selected in dropdown to be Black. The column Grouping for this is matrix1_YearGroup which uses the =Fields!Year.Value |
|
|
|
Rajesh Jonnalagadda
Starting Member
45 Posts |
Posted - 2009-02-11 : 07:46:51
|
| Try this,=IIF(Parameters!Year.Value(0)= Fields!Year.Value AND ISNOTHING(Fields!PercentOccupancy.Value),"Black","Red")Rajesh Jonnalagadda[url="http://www.ggktech.com"]GGK TECH[/url] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-11 : 08:53:21
|
quote:
Originally posted by darkdusky
Thanks for suggestion but what ever is done to the "Nothing zero" in one column it does the same in the other column. I have tried a lot of different combinations, e.g.=IIF(Parameters!Year.Value(0)=Fields!Year.Value ,"Black",IIf (Me.Value >=0,"Red","Black"))--this one gives Red zeroes in both columns.Do I need to use InScope rather than Parameters!Year.Value(0)=Fields!Year.Value. I basically want the column relating to highest year selected in dropdown to be Black. The column Grouping for this is matrix1_YearGroup which uses the =Fields!Year.Value
does this nothing zeroes come in subtotal column? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-11 : 10:13:31
|
| visakh - no this is not a sub-total it is normal cell retrieving from dataset - Each column relates to a parameter chosen from a muli-select dropdown.rajesh - thanks for your suggestion but it made both entire columns go Red (all values not just zeroes).I changed to:=IIF(Parameters!Year.Value(0)=Fields!Year.Value AND NOT ISNOTHING(Fields!PercentOccupancy.Value),"Black","Red") - the zero in black column shows as red=IIF(Parameters!Year.Value(0)=Fields!Year.Value ,"Black",IIF(ISNOTHING(Fields!PercentOccupancy.Value),"Black","Red")) - the zero in red column is blackI will look at stored procedure and see if I can output a zero rather than a null so normal conditional formatting should work. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-11 : 10:19:00
|
| is Parameters!Year.Value a multivalued parameter? then wont Parameters!Year.Value(0) always take only first selected value? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-11 : 10:34:52
|
| It is a multivalued parameter but Year.Value(0) <> first selected, it is equal to the first in the the array of selected - i.e. the one nearest top of dropdown - regardless of the order selected.I looked at placing a zero from stored proc - but this blank value doesn't comes from a NULL value in a particular field. When the matrix is built it leaves a blank cell where there was no row in stored procedure to match the column / row combination in Matrix. Can I set a default value in Matrix cell to be zero rather than blank? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-11 : 11:07:46
|
| I've put zero in if blank =IIF(Fields!PercentOccupancy.Value IS NOTHING,"0",Fields!PercentOccupancy.Value)But I still have problem because the colour expression is checking the Year.value of the cell - which is blank for these cells even though the whole column is in the Year group. Can I access the group value inside the cell? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-11 : 11:55:05
|
| I found a way to get the Year value of the column. The Year column's title is inside a textbox (automatically generated when I addded the Year Group). I can access this title:=ReportItems!textbox4.Value in the blank cells so I can make comparison.Thanks for all your help. |
|
|
|
|
format cell with value=Nothing but zero displayed