Summing up values in a Single Merge Field

[Beowolf]

Hi All,

I have a situation currently which has totally stumped me.



I have a requirement to generate a report out of Salesforce.com and then merge it in the word format. In my report I am getting an extract of all the opportunities created in this year and then the revenue associated to those opportunities.

My word format should include the the total count of opportunities and then sum total of the revenue. For the total number of Opportunities I was able to do that by using the RowCount in merge field. However I am not able to do the sum of the revenue. I have tried the below syntax but somehow it always gives the Syntax error

{=SUM({MERGEFIELD Opportunity_Revenue})}.

I have tried this by using Ctrl-F9 for inserting the curly brackets and then typing the field manually or even by inserting the field by using Quick parts and then inserting the merge field. However word always says syntax error after doing F9.

I wanted to understand if that is even possible at all? or if there is anything wrong in my syntax.

[macropod]

Originally Posted by Beowolf

I have tried the below syntax but somehow it always gives the Syntax error

{=SUM({MERGEFIELD Opportunity_Revenue})}.

I have tried this by using Ctrl-F9 for inserting the curly brackets and then typing the field manually or even by inserting the field by using Quick parts and then inserting the merge field. However word always says syntax error after doing F9.

I wanted to understand if that is even possible at all? or if there is anything wrong in my syntax.

There's nothing wrong with the field construction - it'll display the syntax error until you Preview or Finish the merge, as all it's trying to do till then is SUM a text string - which isn't possible.

As for

In my report I am getting an extract of all the opportunities created in this year and then the revenue associated to those opportunities.

My word format should include the the total count of opportunities and then sum total of the revenue.

Your {=SUM({MERGEFIELD Opportunity_Revenue})} field will never do that. All it will do is output the final record's Opportunity_Revenue value.

However, it's not apparent to me that you even need a mailmerge for what you're doing. If all you need is a single table in a Word document, you could achieve that by copying the Excel range and pasting it into Word. If you use the Paste Special>Paste Link method, the Word table will be linked to the Excel workbook, so any changes in Excel will be reflected in the Word document.

You'd only need a mailmerge if you're generating separate reports for individuals, regions, etc. In that case, you could use Word's Catalogue/Directory Mailmerge facility for the task (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://windowssecrets.com/forums/sho...merge-Tutorial
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

For some worked examples, see the attachments to the posts at:
https://www.msofficeforums.com/mail-...html#post23345
https://www.msofficeforums.com/mail-...html#post30327
http://windowssecrets.com/forums/sho...l=1#post928391

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or
Doug Robbins at https://skydrive.live.com/?cid=5aedc...615E886B%21566

In addition to a 'Many to One' merge, the latter handles:
• Merge with Charts
• Duplex Merge
• Merge with FormFields
• Merge with Attachments
• Merge to Individual Documents
• Merge, Print and Staple