SQLTeam.com | Weblogs | Forums

Ssrs 2012 iif issue

#1

In an ssrs 2012 report, I am getting the error message of 'The Value Expression for the 'Textbox80.paragraphs[0].TextRuns[0]' contains an error [BC305156].
Overload resolution dailed becuase no accessible 'IIf' accepts this number of agruments.

The problem is caused from the following iif

=IIF(Fields!homeLanguage.Value = "Spanish"," en "Approved Technology". La lista tiene tres categorías:", " on the "Approved Technology" icon. The list has three categories:")

when i change the iif to the following it works:

=IIF(Fields!homeLanguage.Value = "Spanish",' en 'Approved Technology'. La lista tiene tres categorías:", " on the 'Approved Technology' icon. The list has three categories:")

The user wants me to place "Approved Technology" to be in double quotes instead of 'Approved Technology'.

Thus can you show me what I can do to make the "Approved Technology" show up?

#2

I am surprised that your second expression works, because it has mismatched set of quotes. So my guess might be completely off. Regardless, I would try one of these

=IIF
(  
   Fields!homeLanguage.Value = "Spanish",
   " en ""Approved Technology"". La lista tiene tres categorías:", 
   " on the ""Approved Technology"" icon. The list has three categories:"
)

=IIF
(
   Fields!homeLanguage.Value = "Spanish",
   ' en "Approved Technology". La lista tiene tres categorías:', 
   ' on the "Approved Technology" icon. The list has three categories:'
)
#3

I would highly recommend you do this data driven not embedded in the report itself. That way if manana you have nuevo languages, you do not have to sweat the details. Something like this?

create table languages(languageId int, LanguageCode char(5), 
LanguageDescr varchar(50))
insert into languages
select 1, 'en-US', 'English (United States)' union
select 2, 'es-DO',	'Spanish (Dominican Republic)' union
select 3, 'es-EC',	'Spanish (Ecuador)' union
select 4, 'es-ES',	'Spanish (Castilian)' union
select 5, 'es-ES',	'Spanish (Spain)' 

create table terms(termid int, term nvarchar(2500), defaultlanguageId int)
insert into terms
select 1, 'on the ''Approved Technology'' icon. The list has three categories:', 1

create table translations(translationId int, translation nvarchar(1500), 
termid int, languageId int)
insert into translations
select 1 , 
'en ''Approved Technology''. La lista tiene tres categorías:', 1, 5 union
1 Like
#4

Then use a stored procedure to get your data as follows

create proc report_sp(@homeLanguage char(5))
as
begin

select *
  from terms t
  join translations tr on t.termid = t.termid
  join languages l on l.languageId = tr.languageId

  where LanguageCode = @homeLanguage
end

go

report_sp 'es-ES';
go