Text input through SSRS parameter including a Field name

Question

I have a SSRS "statement" type report that has general layout of text boxes and tables. For the main text box I want to let the user supply the value as a parameter so the text can be customized, i.e.

Parameters!MainText.Value = "Dear Mr.Doe, Here is your statement."

then I can set the text box value to be the value of the parameter:

=Parameters!MainText.Value

However, I need to be able to allow the incoming parameter value to include a dataset field, like so:

Parameters!MainText.Value = "Dear Mr.Doe, Here is your [Fields!RunDate.Value] statement"  

so that my report output would look like:

"Dear Mr.Doe, Here is your November statement."

I know that you can define it to do this in the text box by supplying the static text and the field request, but I need SSRS to recognize that inside the parameter string there is a field request that needs to be escaped and bound.

Does anyone have any ideas for this? I am using SSRS 2008R2

Answer 1

Have you tried concatenating?

Parameters!MainText.Value = "Dear Mr.Doe, Here is your" & [Fields!RunDate.Value] & "statement"

Answer 2

There are a few dramatically different approaches. To know which is best for you will require more information:

1. Embedded code in the report. Probably the quickest to implement would be embedded code in the report that returned the parameter, but called String.Replace() appropriately to substitute in dynamic values. You'll need to establish some code for the user for which strings will be replaced. Embedded code will get you access to many objects in the report. For example:

   Public Function TestGlobals(ByVal s As String) As String
      Return Report.Globals.ExecutionTime.ToString
   End Function
   

   will return the execution time. Other methods of accessing parameters for the report are shown here.

   1.5 If this function is getting very large, look at using a custom assembly. Then you can have a better authoring experience with Visual Studio

2. Modify the XML. Depending on where you use this, you could directly modify the .rdl/.rdlc XML.

3. Consider other tools, such as ReportBuilder. IF you need to give the user more flexibility over report authoring, there are many tools built specifically for this purpose, such as SSRS's Report Builder.

Answer 3

Here's another approach: Display the parameter string with the dataset value already filled in.

To do so: create a parameter named RunDate for example and set Default value to "get values from a query" and select the first dataset and value field (RunDate). Now the parameter will hold the RunDate field and you can use it elsewhere. Make this parameter hidden or internal and set the correct data type. e.g. Date/Time so you can format its value later.

Now create the second parameter which will hold the default text you want:

Parameters!MainText.Value = "Dear Mr.Doe, Here is your [Parameters!RunDate.Value] statement"

Not sure if this syntax works but you get the idea. You can also do formatting here e.g. only the month of a Datetime:

="Dear Mr.Doe, Here is your " & Format(Parameters!RunDate.Value, "MMMM") & " statement"

This approach uses only built-in methods and avoids the need for a parser so the user doesn't have to learn the syntax for it.

There is of course one drawback: the user has complete control over the parameter contents and can supply a value that doesn't match the report content - but that is also the case with the String Replace method.

And just for the sake of completeness there's also the simplistic option: append multiple parameters: create 2 parameters named MainTextBeforeRunDate and MainTextAfterRunDate.

The Textbox value expression becomes:

=Parameters!MainTextBeforeRunDate.Value & Fields!RunDate.Value & Parameters!MainTextAfterRunDate.Value.

This should explain itself. The simplest solution is often the best, but in this case I have my doubts. At least this makes sure your RunDate ends up in the final report text.