SSRS Matrix Dynamic Formatting of Cell Issue

hi - have problem that's bit beyond me :

i have following result set putting ssrs 2012 matrix:

rownum rowlabel val title
1.00 advance 10000.0000 testtitle1

4.00 list price 18.0000 testtitle1

5.00 units shipped 20000 testtitle1

6.00 return units -8125 testtitle1

7.00 net sales units 11875 testtitle1 8.00 return % 45.0%/10.0% testtitle1

*note: data in val for 'return %' text field - informational , necessary.

when setting such columns title, , rows rowlabel, folowing:

	testtitle1
advance	10000.0000
list price	18.0000
units shipped	20000
return units	-8125
net sales units	11875
return %	45.0%/10.0%

what is, basically, format cell based upon rownum. rownums 1 through 7 specific format each, , want rownum = 8 left alone , display text contents.

	testtitle1
advance	$10,000
list price	$18.00
units shipped	20,000
return units	(8,125)
net sales units	11,875
return %	45.0%/10.0%

when try set conditional formatting on cell, 'val', #error 'return %' row: 

"warning 1 [rsruntimeerrorinexpression] value expression textrun ‘textbox2.paragraphs[0].textruns[0]’ contains error: input string not in correct format."

i tried using cstr , cdec in different combinations in 'expression' setting cell, no avail - latest attempt: 

= iif( fields!sortorder.value *1 = 5 
     or fields!sortorder.value *1 = 6
     or fields!sortorder.value *1 = 7
         ,"#,0;(#,0)"
         ,(iif (fields!sortorder.value *1 = 4
                 ,"'$'0.00;('$'0.00)"
                ,(iif (fields!sortorder.value *1 = 8
                      ,"text"
                      ,"'$'#,0;('$'#,0)")
                 )                  )
           )
       )

any appreciated!

thanks!

--jim

hi devastator,

according description, trying use different format in different rows, right?

based on research, issue related data type of val column. in ssrs, format used numeric value. however, 45.0%/10.0% string value. avoid issue, need convert value integer value. have tested on local environment. step below reference.

1. add calculated field.
   field name:val2
   field source: =iif(isnumeric(fields!val.value),cint(iif(isnumeric(fields!val.value),fields!val.value,0)),fields!val.value)
2. use val2 column instead of val in report.
3. format text using expression below.
   = iif( fields!rownum.value= 5.00
        or fields!rownum.value= 6.00
        or fields!rownum.value= 7.00
            ,"#,0;(#,0)"
            ,(iif (fields!rownum.value= 4.00
           or
           fields!rownum.value= 1.00
                    ,"'$'0.00;('$'0.00)"
                   ,(iif (fields!rownum.value= 8.00
                         ,"text"
                         ,"'$'#,0;('$'#,0)")
                    )
                    )
              )
          )

the report looks below.

if have questions, please feel free ask.

regards,

---

charlie liao
technet community support

SQL Server  >  SQL Server Reporting Services, Power View