Is There a Way to do a Spanish/English MailMerge?

Question

I have an SSRS report that has Spanish and English text boxes. If the dataset row is a Spanish speaking person, an expression in each Spanish textbox shows that and hides the English textbox. These textboxes are exactly placed over each other.

My boss wants me to use SSRS to generate an Excel spreadsheet from the dataset(this is not hard) and use Word template for a mail merge. However, I am having trouble trying to figure out if I can hide all English when row is a Spanish row and vice versa. These are health clients of Spanish and English nationality.

I can do mail merges attached to a Recordset, I can do one in English, one in Spanish. I am trying to avoid this and have it all in one Mail Merge.

Areas marked in red will change to Spanish translation and/or date format. The dates are a no-brainer I can use a conditional IIF, however the formatted body I have no solution for, based on value in Field "CL_Language" which is either "Spanish" or "English".

====================================

The merge fields for dates and greeting are easy. There is no merge field for the text. And yes, only option might be for 2 separate reports with different Recordsets.

score 1 · Answer 1 · answered Apr 27 '18 at 22:39

1

It's not clear what the actual issue is but... Instead of hiding textboxes, which could cause problems when exporting etc., why not set a single textbox to the correct language text using an expression?

Something along the lines of

=IIF(Fields!Language.Value = "English", Fields!MyEnglishText.Value, FieldsMySpanishText.Value)

answered Apr 27 '18 at 22:39

Alan Schofield

19,839
3
22
35

Isn’t Word just a bunch of text with merge fields inserted? A lot of the document is just formatted text with address name etc inserted. – JustJohn Apr 28 '18 at 03:31
For example, down below the letterhead and person's name/address is a 1/3 of a page of text, and below that Case Managers's name. This can be created in 2 languages on top of each other in SSRS. Not sure how to keep that fancy and long a "letter body" in a merge field. Not saying you can't, but some of these reports are humungus. – JustJohn Apr 30 '18 at 22:02
Screenshot of report design would be helpful, plus a sample of the final output highlighting which bits are static, and which are from the database, plus your database table structure with some data data. Add all this to your question and somebody may be able to help. – Alan Schofield May 01 '18 at 12:31
I put screenshots in Question. – JustJohn May 01 '18 at 17:03

score 0 · Accepted Answer · answered May 03 '18 at 23:14

I found a solution. But it could be very difficult for the client to create. It involves hitting Ctrl + F9 which will create curly brackets {}. Inside those curly brackets an IF statement is placed and I just pasted the whole Spanish formatted body in the true area, and the whole English body in the false area.

{IF "CL_Language" = "Spanish" "spanish body text here" "english body text here"}

Very strange syntax and you need to right click on the area to see choices like "Toggle Field Codes" (IF statement get's hidden), "Edit Field", and "Update Field". With Edit Field and Update Field you get a popup with the fields in your recordset. If you saw the examples in my question, you can see that is some big clunky text AND . . .inside of it is a merge field that works! The Excel recordset comes already with the month name in correct language for each row.

Since it is not smart to include links that might expire, I am including the Google text I used to find this solution. Then I took a chance on a huge formatted chunk of text with a merge field inside of it. Google this: "If Merge Field then"

Now is this a viable solution for the client versus just having a Word template for each language?

I think this is too difficult and I even duck when running it. Also, once it's working, if I look at the toggled code, the Conditional field no longer says the field name, but the value in the field, go figure. {IF "Spanish" = "Spanish" or {IF "English" = "Spanish" instead of {IF "CL_Language" = "Spanish" or {IF "CL_Language" = "English"

Here is how to access the fields using right click. (remember, your curly brackets HAVE to be created with Control + F9).

Is There a Way to do a Spanish/English MailMerge?

2 Answers2