0

I'm working on create a multilingual SSRS report for SQL Server 2008R2. To do that without external code and only get translation from DB, I need to use Lookup() built-in function in the section code of the report.
I have the following expression for textbox: 

=LOOKUP("Rpt_0_Hello", Fields!Token.Value, Fields!Translation.Value, "DS_Translation")

The goal is to reduce the complexity of expression for the textbox translation. I would like to get to the expression:

=Code.TrasT("Rpt_0_Hello")

I try to write a VB function like this:

Public Function TransT( Token as String )
   Lookup( Token
          ,Report.Fields!Token.Value
          ,Report.Fields!Translation.Value
          ,"DS_Translation")
End Function

This code generate an error of "[BC30451]'Lookup' is not declared.". I found on the web to use "Report" object to get Report element like Fields.
Is there a way to reference "Lookup()"?

GLNebiacolombo
  • 1
  • 2
  • This is not VBScript. Is it VBA perhaps? Please use the appropriate tags. – Geert Bellekens Nov 27 '18 at 12:06
  • This is not VBA because there's not based on a strong Object model like excel, but it's a simple VB interpreter with syntax not well defined. – GLNebiacolombo Nov 27 '18 at 13:11
  • something like `Token as String` is not valid VBScript syntax, so if it works I guess it must be VBA, or VB.Net or just plain VB or.... VBA still seems like the most likely candidate though. – Geert Bellekens Nov 27 '18 at 13:59
  • The Lookup function (https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-2017) can be used used in an expression. See this article for some examples - https://www.red-gate.com/simple-talk/sql/reporting-services/sql-server-reporting-services-basics-customizing-ssrs-reports/ Try seeing expression of a cell in table to this =Lookup( Token ,Report.Fields!Token.Value, Report.Fields!Translation.Value, ,"DS_Translation") – SQLBobScot Nov 27 '18 at 19:20
  • I know the normal use of Lookup on Expression but I ask for to use them on Code section to reduce the complexity of expression for the textbox translation – GLNebiacolombo Nov 28 '18 at 08:11

3 Answers3

0

I think you are referring to the SSRS lookup function. You can;t use this directly in a Report Code function (as far as I know).

However, what you probably want to do is just use the lookup function to get your translation from a dataset called 'DSTranslation'. If this is correct them simply set the expression of the textbox (or whatever) to the lookup function.

So your textbox expression would just be 

=LOOKUP(Fields!Token.Value, Fields!Token.Value, Fields!Translation.Value, "DS_Translation")

This assumes both datasets have a field called Token

If I've misunderstood then edit your question and explain what you are trying to do in a bit more detail and what data you have in your datasets.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • I understand that my goal it's not evident and modified my question. My starting expression to translate a textbox it's just what you wrote. – GLNebiacolombo Nov 28 '18 at 08:16
  • OK, I understand now. I'm 99% certain you can't do this. When I've faced a similar problem I've either setup the report as you have now or I've done all the translation in the dataset query. – Alan Schofield Nov 28 '18 at 09:46
0

Its not possible to call Lookup() function in the custom code.

Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
0

I found an answer to the goal of complexity reduction in the translation. This not resolve the question but it's a workaround for minimize the work on multilingual report preparation. You can use report variable as intermediate expression for the element to translate. If you define a variable for any element to translate like this: 

V_Hello=LOOKUP("Rpt_0_Hello", Fields!Token.Value, Fields!Translation.Value, "DS_Translation")

You now can use the following expression on textbox: 

=Variables!V_Hello.Value

It's not direct and short like the solution of the question, but if you respect naming you can automate the insertion of this variables into the report XML file .rpt (this is a future problem).
With this you kill two birds with one stone because you simplify expression and evaluate the expression once. This may be useful on complex report.

GLNebiacolombo
  • 1
  • 2