«Conga Composer Microsoft Excel Templates Conga Composer 7 - Winter ‘13 Release Welcome Conga Composer lets you generate documents and spreadsheets ...»
Microsoft Excel Templates
Conga Composer 7 - Winter ‘13 Release
Conga Composer lets you generate documents and spreadsheets by gathering data from
Salesforce and merging to a template. The resulting file(s) can be printed or emailed and you
can log the actions you’ve taken in Salesforce.
Conga Composer solutions let you create individual documents by clicking a button from a
record in Salesforce.
Contacting Conga Conga is a leading provider of software solutions to enrich your Salesforce experience, specializing in document creation tools that integrate data from Salesforce. Founded in 2006, Conga has since built a substantial market presence with thousands of customers worldwide.
Technical Support At Conga, we are committed to providing you with timely answers to your technical support questions. We provide support to system administrators for our products via email or
telephone. Generally, we’re available weekdays, during the following hours:
North America Support Hours: 8 a.m. – 8 p.m. Eastern Time Tech Support Email: email@example.com Telephone: +1 303-465-1616 or (866) 502-3334 Europe, Middle East, Africa (EMEA) Support Hours: 9 a.m. – 5 p.m. British Time Tech Support Email: firstname.lastname@example.org Telephone: +44 20 3608 0165 Asia / Pacific (APAC) Support Hours: 9 a.m. – 5 p.m. Eastern Time Tech Support Email: email@example.com Telephone: +61 28 417 2399 © 2014 Conga. All Rights Reserved. Conga, Conga Suite and Conga Composer are all trademarks or registered trademarks of Conga, as are other names and marks. All other trademarks or registered trademarks are the properties of their respective owners.
For information on other products from Conga, please contact our New Business team:
Sales Information Email: firstname.lastname@example.org Telephone: +1 303-465-1616 Resources
For detailed examples, including sample templates, report designs and sample output, please download the Conga Composer Examples file.
Articles and Videos:
Conga provides an online knowledgebase and YouTube channel that are available 24X7 to help you answer questions.
Want to get started right away? Our Quick Start Guides will help you quickly generate your first Conga Composer solutions.
To learn more, go to our Conga QuickStart Library.
Page 2 Table of Contents General Disclaimer
Designing Microsoft Excel Merge Templates
What Merge Fields are Available?
Create Merge Fields
Creating Excel Merge Fields
Merging Detail Data
Refine Merge Fields with Parameters
Special Merge Fields
Today’s Date (Text)
Today’s Date (Date/Time Value)
Microsoft Excel FAQs
How can I use a merge field within a formula?
How do I set the Printable Area of the Spreadsheet
How can I determine which sheets to include in an output PDF?
What’s the best way to format text paragraphs?
How can I group data in Excel?
Know Your Limits
General Disclaimer Conga Composer solutions depend on and leverage technology from a multitude of software and hardware vendors including but not limited to salesforce.com, Microsoft, Google, and the providers of various Internet browsers (“Third Party Technology”). Output from Conga Composer solutions can be impacted by these and numerous other factors beyond the control
of Conga, including:
Template design errors in Microsoft Word, Excel, PowerPoint templates, PDF Forms or Conga HTML email templates The nature and scope of data and/or images being retrieved for merging into templates Individual user’s browser, network, ISP and hardware settings The availability and performance of the salesforce.com service and other software-as-aservice solutions such as e-signature, e-commerce and other AppExchange products, and periodic updates, enhancements or other changes to such services made by their respective vendors Salesforce.com security settings including User Profiles and Permissions, folder and object access and field-level security Internet performance, regional and even global outages For these reasons, Conga cannot guarantee that your particular implementation of Third Party and Conga technologies – even if configured based on Conga documentation and/or guidance offered by Conga Support personnel - will perform consistent with your expectations or requirements, or provide the output you require. Accordingly, Conga disclaims any and all liability resulting from, or related to, performance issues associated with Conga products to the extent arising from your implementation, configuration, or the behavior of Third Party Technology.
508 Compliance Conga Composer is an installable package from the Salesforce AppExchange, which integrates with an individual customer's existing implementation of Salesforce. When properly configured, Conga Composer will run within an existing Salesforce instance without the need to view, manipulate or configure the application outside of the Salesforce user interface.
The use of Conga solutions is entirely represented by standard Salesforce user interface components when properly configured. Therefore, customers seeking the 508 Compliance VPAT documentation are referred to Salesforce.
Page 4 Designing Microsoft Excel Merge Templates Conga Composer can merge data into an Excel template. (Excel templates only work with Conga Composer, not Conga Mail Merge.) What exactly is an Excel template? It’s an Excel file (.xls or.xlsx) that contains one or more merge fields. In this chapter we’ll examine how to create and edit Excel fields.
Conga Composer supports Excel templates saved in the Excel 2003 (“.xls”), or Excel 2007/2010 (“.xlsx”) format. However, we recommend the Excel 2003 format (“.xls”) to ensure file format compatibility across a broad audience.
The first step in creating a template is to determine which fields are available, which we’ll explore in the next section.
What Merge Fields are Available?
An essential tool in the development of Conga Composer solutions is the Template Builder.
The Template Builder is located at the lower-left corner of the Conga Composer page.
Tip! You’ll use the Template Builder frequently in the upcoming sections.
The Template Builder exposes the data retrieved by Conga Composer from the Master Object, and from any Salesforce Reports or Conga Queries referenced in the Conga Composer URL. (The use of Salesforce Reports and Conga Queries is described later in this chapter.) Select the desired dataset to view the available merge field names. The field names listed in the left column indicate the field names you must use in your templates. Filter the list of fields by entering search criteria in the Live Search box. You may search the field names or the sample data (drawn from the first row of data available).
Click the field name to copy it to the clipboard, then switch to your template (in Word, Excel, PowerPoint or a PDF Form) and paste the field name into the appropriate location (see the template-specific guides for information on how to create a merge field).
See the next page for several tips on how to best use the Template Builder.
Page 6 Tip! If an Opportunity is your Master Object, Conga Composer will automatically make the Opportunity Line Items available for merging in the OppLineItems dataset.
Tip! If a Quote is your Master Object, Conga Composer will automatically make the Quote Line Items available for merging in the QuoteLineItems dataset.
Create Merge Fields With the Template Builder window close at hand, you’re ready to begin creating merge fields.
What exactly is an Excel merge field? It’s an Excel formula that begins with &= followed by the View Data workbook sheet name, a period and then the field name. The format looks like: &=DatasetName.FieldName Creating Excel Merge Fields
1. In Excel, create or open a workbook to use as your template
3. Switch to the Excel template and move to the location where you’d like the merge field
4. Create the field by
following this convention:
Say we want to merge several fields from a Contact record, including the LastName field, into Excel. Assuming the Contact is the Master Object, the LastName field would appear on the Master sheet. The formula for this field would be &=Master.LastName.
The Contact also has a field called CONTACT_MAILINGCITY. The Excel merge field formula would be &=Master.CONTACT_MAILINGCITY.
Say we also have a field name on the ReportData dataset entitled LastActivityDate. The corresponding Excel merge field would be &=ReportData.LastActivityDate.
Finally, let’s say we also want to include the current user’s first name, which is visible on the User dataset on the Template Builder window as USER_FIRSTNAME. The corresponding Excel merge field would be &=User.USER_FIRSTNAME.
In each instance, notice that the sheet names (Master, ReportData and User) are case-sensitive.
Warning! Merge field formulas must stand-alone in a cell and cannot be used as part of a more complex formula. To use a merge field in a formula, put the merge field in a non-printing region (or a different sheet) and then refer to that cell in the formula.
Merging Detail Data Conga Composer has the unique ability to use data from a Salesforce Report as detail data.
Since you can retrieve multiple rows of data for each master object (e.g., invoice line items for each invoice), your Excel template needs to dynamically expand to accommodate an unlimited number of detail rows.
Fortunately, Conga Composer will automatically expand your template by adding rows (or columns if merging horizontally), for detail data.
Consider this data from a Template Builder window.
Here, we have several Contacts related to an Account in the ReportData
In our Excel template, say we lay out the merge fields like
When Conga Composer merges with this template, it will automatically insert rows to accommodate the number of rows on the ReportData sheet. The first Contact’s data will be inserted into cell B3, then a new row will be inserted into the sheet and the second Contact’s data will be inserted in B4, and so on.
The final output would look
Page 10 Refine Merge Fields with Parameters You can tune Conga Composer’s behavior when merging to Excel with field parameters. Each of the following parameters may be appended to the end of a merge field within parentheses, separating each with a comma.
Say we want to merge Opportunity Line Items on an Excel template. To merge them
vertically (the typical method), your template would look like this:
Assuming we have three items from the OppLineItems sheet, the output would look like this:
To write data left-to-write, include the Horizontal parameter. In this example, the Product Names from an opportunity are written left-to-right.
The output after adding the Horizontal parameter would look like:
Extending the previous example, say we want Opportunity Line Items (and Sales Price) to appear from left-to-right across a page. We’ve also included headers each product column (Product 1, Product 2 and Product 3). To prevent Conga Composer from inserting new
columns, we’ve added the Noadd parameter:
&=ReportData. OPPORTUNITY_LINEITEM_PRODUCTNAME(Horizontal, Noadd)
The output from this horizontal merge would look like:
The Skip:n parameter can be helpful when you want to insert alternating data on every other row (from fields containing multiple values).
In this example, we’d like to have an Opportunity Line Item Product appear, then the Sales Price below the product, followed by another product and price, and so on.
Usually, the Noadd parameter accompanies the Skip:n parameter.
The output from this template would look like:
Page 14 Special Merge Fields Conga Composer offers several additional merge fields for even more flexibility in your merge templates.
Today’s Date (Text) By creating a merge field with the name &=Date.Today, Conga Composer will insert the current date as a text string. Although this field is easy to use, you won’t be able to apply a date-format to this value because it’s text.
Today’s Date (Date/Time Value) By creating a merge field with the name &=Date.TodayDT, Conga Composer will insert the current date as a date/time value. The benefit of this field is that you can format the resulting date with any of Excel’s date-formatting options.
Dynamic Formulas Dynamic Formulas allow you to insert Excel's formulas into cells even when the formula must reference rows that will be inserted later during the merge process. They can repeat for each inserted row or use only the cell where the field is placed.
Dynamic Formulas begin with &== and are followed by an Excel formula.
Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula.
You may use any of Excel’s functions in a Dynamic Formula.