CODEWITHSUNDEEP
reporting-servicesssrs-2012
NotADog
NotADog

Reputation: 159

SSRS Report Field with a Dynamic Data Type

I'm amalgamating several manual reports into one SSRS one by changing the select statements to follow a set format of:

 select
 'DepartmentName'
 ,'StatName'
 ,'DataType' --(Int, percentage,date,time, etc)
 ,'Stat'
 from TablesNeeded

Now this unions together fine so I end up with a list of statistic names and their values. After this I want to have the cell showing "Stat" to change it's formatting based on the result of "DataType"

I attempted to use an IIF to determine the format, which behaved with a single IIF statement, however after nesting to accomodate different data taypes it appears to "false" every IIF result and return just the default/value:

Working

 =iif(
 Fields!DataType.Value="Percentage"
 ,Format(Fields!Stat.Value,"0%")
 ,Fields!Stat.Value
 )

"Falsing"

 =iif(
 Fields!DataType.Value="Percentage"
 ,Format(Fields!Stat.Value,"0%")
 ,iif(
 Fields!DataType.Value="Date"
 ,Format(Fields!Stat.Value,"y")
 ,iif(
 Fields!DataType.Value="int"
 ,Fields!Stat.Value
 ,Fields!Stat.Value
 )))

and using switch similarily also "Falsed"

 =Switch(
 Fields!DataType.Value="Percentage",Format(Fields!Stat.Value,"0%")
 ,Fields!DataType.Value="int",Format(Fields!Stat.Value,"#,#0")
 ,Fields!DataType.Value="Date",Format(Fields!Stat.Value,"y")
 )

I've asked a colleague and we're both stumped. Any ideas on proper expression formats?

Upvotes: 0

Views: 886

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

Assuming all your datatypes are correct then you just need to use a simplified version of your switch statement in the Format property of the cell(s).

Something like..

 =SWITCH(
 Fields!DataType.Value="Percentage","0%"
 ,Fields!DataType.Value="int","#,#0"
 ,Fields!DataType.Value="Date","y")
 )

Note You could just use "p0" for you percentage type, "n0" for your int.

Upvotes: 1

Related Questions