User`s Manual - Sage ACT! add-ons
Contents
1. Count No calculation L Ignore default formatting 12 If you want to apply special formatting to the total of a specific column to make it stand out while the field column is selected in the dropdown check Ignore default formatting and define the desired formatting by clicking the Font button 4 Summary The summary section includes totals for the data included in the whole report The options are the same as for the sub totals 5 Example Here is an example of a report with a 1 section based on the Stage field with a section Title in red a Sub total in Brown and a Summary in blue k 2 3 4 5 6 T 8 ua B c Opportunities by Stage Record Manager Compa 1 Initial Communication Sales Chris Huffman Brandee s Bakery Sarah Whiting KKQS Radio 10 Total Sales 11 Total W ted Total 647 35 309 10 1 456 45 1 456 45 1 456 45 1 456 45 Advanced Sales Lookup Export and Reports User s Manual 13 Formatting the Template Excel Report only Each Excel report is associated with a template The use of a template allows to define the look of your report once and for all so that you can generate formatted reports without reformatting the report each time after the data is imported A blank template is automatically created when creating a new report To edit it right click the report name and choose Edit Template in the popup menu Or choose Edit Template from the Report m2. Advanced Sales Lookup Export and Reports Options Help General 1st Condition 2nd Condition Sales status Include data for contacts managed by pen 9 All users Closed Won Selected users Closed Lost Inactive Creation date Include data for sales managed by All dates 5 All users Selected dates 2 Selected users From 4 amp 2005 To 4 6 2005 gt Defining conditions Y ou may also define one or two conditions that the opportunities will meet For this check the Apply a condition checkbox B Advanced Sales Lookup Export and Reports Options Help General Fiter Ist Condition 2nd Condition First condition description Then pick an opportunity field Apply a first condition as described below First condition description Actual Close Date Estimated Close Date Open Date Opportunity Field 1 Opportunity Field 2 Opportunity Field 3 Advanced Sales Lookup Export and Reports User s Manual Then define the condition Apply a first condition as described below First condition description Actual Close Date Between 1 1 2005 Finally press the Run button to create the lookup Creating an opportunity lookup If you follow the same procedure as described above but from the Opportunity List screen you will obtain an opportunity lookup instead of a contact lookup NOTE To better explain the difference between the two lookups here is an exa
3. Advanced Sales Lookup Export and Reports v 5 0 for ACT 2006 Another efficient and affordable ACT Add On by e ponenciel http www exponenciel com Advanced Sales Lookup Export and Reports User s Manual Table of content lluvia mE M 3 Mstalafon proceduren c C A 3 Upgrading from previous versiOns ecce eee e eere ee eene eee eee tette e eese s eaae tees esee ess eaae etae ee 3 ha inversion s 3 Tm portant K own ISSUE 3 CP EAU a TOOKU Doc ei rer e aviso eere deba EERS a dado a P D dv PP Tees Iiis 4 creatino a CONIC JOORUD rresten dean fu tubus 4 Crieatine du OP POLITY NOOK Desie e a E AER 6 Generatne a TrepOr irera nE E 6 CPE ATG A FOPONG sisien 8 Naming the report CSV or Excel Report occu suicide dee ee eee 8 Defining the report settings CSV or Excel Report cc ceccceccccccceeeeeeeeeeeeeeeseenseseeeeeeees 9 LU AVA AD TER 9 Dh Soo P RUN 10 35 5eCLIOTIS TID coo ME PNEU DM MEN EI DM I MED D SE 10 e SEIS UR AEN LUC 12 SM be 18 Ooae a a D OT E LI PERLES 12 Formatting the Template Excel Report only eeeeeesesseeeeeeeennnneeeee n 13 I MRE AUIS tie e ddet i oeibosatupe ondes tis a teoria UD ovas sepa iSt Sas ooI 13 2 AOU ACEI te co UTR ooi ete ioa casts Meine ie eet debes sto Aided ica e etis d ome todas 13 3 Colaps ne COMMING oo oa
4. adsheet and more NOTE The look of an Excel report except for the sub total and total sections and format of the cells is controlled by the template of the report Naming the report CSV or Excel Report To create a new report make sure Sales Report 1s selected under Type of output and that the type of report you want to create is selected under Send output to Then go to the Report menu and choose New You will be prompted to enter a name for the report New Excel Report Choose a name for the new Excel report Sales figures by Salesperson Once you click OK the name of the report will be shown in the Select a report template list Select a report template Opportunities by Company Opportunities by Contact E Opportunities by Estimated Clase De Opportunities by Product Opportunities by Record Manager Opportunities by Stage Opportunities by Status Sales figures by Salesperson lt Ill Advanced Sales Lookup Export and Reports User s Manual 9 Defining the report settings CSV or Excel Report Right click the name of the report in the list and choose Edit Report Settings or go to the Report menu again and choose Edit Settings to open the Report Settings window This window contains several tabs The first tab called Data allows to define the header and the top left cell of the data Report Settings Report Name Sales figures by Salesperson Data Fields l Ist Section 2nd Section 3rd Sect
5. dit mode Advanced Sales Lookup Export and Reports User s Manual 15 Changing the template folder Sharing your templates over a network By default the template folder is located on your hard drive under the Exponenciel Advanced Sales Lookup Export and Reports folder located under the ACT Plugins folder by default at c Program Files ACT ACT for Win 7 Plugins If you wish to change this location to share the templates over a network for instance go to the Options Template folder menu and choose the new location Note that all the files under the template folders are necessary So if you want to move your templates to another location you need to copy all the files 1e Report Name XLT and Report Name WXL for Excel Reports and Report Name WCS for CSV reports Advanced Techniques Explanation of the Commission Report In some cases you might want to have calculations to be run once the data 1s imported into Excel For instance you might want to calculate a 2096 commission based on the Total sales amount That s what the Commission Report does see below the commission figures in blue Total Prob W ted Total Commission 150 00 9 750 31 25 243750 280 00 3 750 31 25 243750 19 500 62 487515 At this stage you might want to open the Commission Report template select the report then do a right click and choose Edit template As you can see we duplicated the Opportunities by company report and added an extra colum
6. enu The add on will open the template in Excel 1 Creating the header The first thing you will want to do is create the header rows 1 to 3 in the example above Remember that data is inserted in the template starting at the cell specified in the Report Settings window under the Data tab and that this Top Left Cell should be below the last row of your header 2 Formatting the columns Then format each column as required for instance if a column is to contain dates you will need to select the column right click it and choose Format cells ij Copy JR Paste Paste Special Insert Delete Clear Contents Format Cells Column Width BS Hide Unhide In the Format Cells window select Date under Category Then pick the format you prefer under Type Format Cells General Number Currency Accounting 3 14 2001 Wednesday March 14 2001 3 14 3 14 01 03 14 01 iar 14 Mar 01 Locale location English United States Date formats display date and time serial numbers as date values Except for items that have an asterisk applied formats do not switch date orders with the operating system Cancel Advanced Sales Lookup Export and Reports User s Manual 14 You will want to repeat the same process for each column containing dates or numbers or currencies so that proper formatting 1s used when rendering the report NOTE Once again the formatting of the title sub total and summary sections is not co
7. ion i Summary Header C Use field names for top row of data range Top left cell You can define where you want the data imported in your Excel spreadsheet Column amp How 1 1 Data tab Header You have the option to export the field names with your data in your Excel spreadsheet Most likely you will not want to use this option but rather create your own header in the Excel template you will be using for the report see below The Top Left Cell If you decide to create your own header in the Excel template you will want to keep this header from being overwritten by your sales data when the data is merged into the document This is when the Top Left Cell settings comes into play If your header spans over 3 rows as shown below you will want to have the Top Left Cell to be at least A4 if not A5 to allow for a blank line A B Opportunities by Esti 2 Compam Advanced Sales Lookup Export and Reports User s Manual 10 2 Fields tab Under the Fields tab you simply select the fields you want included in the report You may include Contact fields Opportunities or Products fields Report Settings Report Name Sales Figures by Salesperson Pu T Data Fields Ist Section 2nd Section 3rd Section Summary Fields not included Opportunities aha Fields included in the report Private Opportunity Probability of Close Process Reason Record Creator Record Manage
8. kup Export and Reports User s Manual 4 Creating a lookup With Advanced Sales Lookup Export and Reports you may create either a lookup of contacts if launched from a Contact screen or a lookup of opportunities if launched from the Opportunity List screen Creating a contact lookup Advanced Sales Lookup Export and Reports allows to create lookups of contacts which sales opportunities meet certain criterias Note that the filters 1 and 2 conditions apply to the sales opportunities not to the contacts For instance you may create a lookup of contacts which opportunities close date is between the 1 of January and the 31 of March The resulting lookup will list all contacts who have such an opportunity but who might also have opportunities not meeting the condition as well To create a contact lookup launch the add on from either the Contact Detail or Contact List screen As Type of output select Lookup b Advanced Sales Lookup Export and Reports Options Help General Filter 1st Condition 2nd Condition Type of output Send output to Wor Replace Lookup C Add to Lookup Select a repart template 2 Narrow Lookup O Sales Report Then choose between the 3 traditional ACT lookup options Replace Lookup Add to Lookup or Narrow Lookup Advanced Sales Lookup Export and Reports User s Manual 5 Applying a filter Under the Filter tab you have several options allowing to filter the opportunities
9. license code right click one of the controls of the pack in your layout and select About Once the About screen 1s open type the registration code in the appropriate box and click OK You might need to close and restart ACT to complete the registration process Support For support contact support 2 exponenciel com Rev September 20 2006 v 5 0
10. mple Let s say that you want to lookup the opportunities that were closed today and there are 3 in your database 2 are associated with ClientA and 1 with ClientB If you do a Contact lookup the add on will create a contact lookup containing ClientA and ClientB If you do an Opportunity lookup the add on will create a lookup of 3 opportunities Generating a report To create a report select Sales Report as Type of output Advanced Sales Lookup Export and Reports Report Options Help General Filter 1st Condition 2nd Condition Type of output Send output ta O Lookup Excel Report Select a report template Commission Report Opportunities by Company Opportunities by Contact f Opportunities by Estimated Close Date gii Opportunities by Product Current contact Opportunities by Record Manager Opportunities by Stage Current lookup Opportunities by Status 9 All contacts TT Advanced Sales Lookup Export and Reports User s Manual Then choose between Excel Report and CSV File Export in the Send output to dropdown list General Fiter 1st Condition 2nd Condition Type of output Send output to Lookup Excel Report Then select the report template you wish to base the report on To filter the opportunities and apply one or two conditions follow the instructions given in Applying a filter and Defining conditions under Creating a contact lookup above Then pres
11. n Caption lt value gt Font Do not repeat value in section Empty line before title Add a border below the title row Empty line after title Create a subtotal for this section 3 Below the section You may add a title before each section Usually you will want the title to be the value of the field That way every time that the value of the field changes you new section will clearly be identified with the new value The value keyword will be replaced by the real value of the field You may use it with other characters as in value s territory for instance To apply formatting to the titles click the Font button Each section may have a sub total as well The sub total may be below the section or above the section this latest option is not compatible with a title To define your sub total click the Options button to open the Sub Total Options window To define which column should have a calculation select the column in the Calculation for each column dropdown list then choose either Total Count Average or No calculation You may have one calculation per column Advanced Sales Lookup Export and Reports User s Manual Sub Total Options Caption Total for lt value gt Write this caption in column 5 Default formatting for this subtotal section L Border above Empty lines Empty line before the total Empty line after the total Calculation for each column Total C Average
12. n in the template called Commission If you look into cell L5 which corresponds to the row where the data will be inserted If you look at the report settings you will see that the Top Left Cell is A5 you will see the formula IF I5 T5 20 e E fe IF b s 152096 ed Total Commission Advanced Sales Lookup Export and Reports User s Manual 16 The reason the final report displays the commission at the end of each row is that this cell is automatically copied at the end of each data row That s because this cell is 1 onthe row where the data is inserted the row of the Top Left Cell setting 2 to the right of where the data will be inserted Any cell meeting these 2 conditions will automatically be copied at the end of each data row as well So you can have more than one additional column to do after import calculations NOTES e We used the if function to only calculate the commission when the I column the Total amount in contains a value This way we avoided created cells with zeros as in 150 00 9 750 31 25 243758 280 00 9 750 31 2556 243758576 19 500 62 487515 e To ensure that the report still looks great borders are automatically extended to the extra columns Registering your product To continue using this product passed the trial period please buy a license at www exponenciel com Licenses are issued for one ACT username and will only work for that username Once you get a
13. ntrolled by the template but defined in the Report Settings window see above 3 Collapsing columns If you look at the reports supplied with the add on you will see that we sometimes collapsed the first column to pixel so that it does not take any space but allows the content ie the section titles to spread over the next cell This way the title which is in the first column appears to be on top of the second column 4 Other document settings You may also create a footer define print areas and any other Excel document settings Duplicating an existing report To duplicate an existing report select the report you want to duplicate then do a right click and select Duplicate Report or go to the Report menu and choose Duplicate report Editing an existing report There are 2 levels of editing possible To edit the content of the report itself and the formatting of titles sub totals and summary select the report right click and choose Edit Settings or go to the Report menu and choose Edit Settings For explanations on the different screens of the Report Settings window see Defining the report settings under Generating report previously in this document To edit the columns formatting header footer or any settings associated with the Excel document itself select the report right click and choose Edit Template or go to the Report menu and choose Edit Template The add on will then open the template in Excel in e
14. ode e ex bove ote adn odor eae ge Damas dvd ne es iU code CU ee Ead 14 4 Otherdocuraent SCLIN OS cuoio teie ete eni a eoe teta Rose telae die niu te nio ocv Me enun ciem oe cd 14 Duphcatins an existms report isesi IE er SEY ERAS YR ER Vasa NE DESEE EPVAE EE NERA PEE MOVE en Cau e Ev v Eee uS 14 Editing an EXISTING FepOLEL i o eeeieveeeeest eoeecee eu esee ce Pee ee ve aae e cupi vae Ee ee EEOSE VAISE ERU EVE ES EEE ISSN EIE VE ese Lei EEA 14 Changing the template folder Sharing your templates over a network 15 Advanced Techniques Explanation of the Commission Report ee eeere 15 Registering riga didis C 16 Vlll ge T vende evaded cdas cue caideesasenavaceas 16 Advanced Sales Lookup Export and Reports User s Manual 3 Purpose of the add on Advanced Sales Lookup Export and Reports allows to e Create lookups of contacts who are associated with opportunities meeting certain criteria e Create lookups of opportunities meeting certain criteria e Export opportunities meeting certain criteria to a CSV file e Generate pre formatted reports in Excel of opportunities meeting certain criteria Installation procedure Download the program file from our download page and double click it to start the installation Note that contrarily to versions supporting previous ACT versions and because of the changes in ACT add on
15. r Refemed By Stage Status Total Weighted Total e 3 C Include number with Stage Add Empty Column Use the Up and Down arrow on the left to change the order in which the fields will appear in the report The top field will be the first column to be displayed and so on Include number with Stage If you include the Stage opportunity field in your report you might want to check the Include number with Stage checkbox The stage name will then be preceded by the stage number ex 1 Initial Communication 2 Needs assessment so that if you sort the report by stage it will sort them chronologically instead of alphabetically 3 Sections tab The following tab or 3 tabs for an excel Report allow to define sections A section is defined by a field which is also the reference for the sort order For instance if you want your report to be sorted by Salesperson then by Product then by Company you will have to select Record Manager in the Field and sort for this section dropdown list of the 1 section Product in the same list of the 2 section and Company in the same list of 3 section Advanced Sales Lookup Export and Reports User s Manual 11 Report Settings Heport Mame Sales figures by Salesperson See rT 1 h Data Fields Ist Section 2nd Section 3rd Section Summary Field and sort for this section Record Manager Ie Ascending C2 Descending Add a title above this sectio
16. s are now DLL files and not executable files This means that they are automatically initialized by ACT when placed in ACT plugin folder a subfolder of the main ACT directory and cannot be started from the Windows Start menu anymore This is why you are not given a choice of installation directory during the installation process Upgrading from previous versions Only your report templates XLT files from previous versions are reusable with the new version The report settings have to be completely recreated DO NOT ATTEMPT TO USE THE OLD FILES EVEN IF THEY HAVE THE SAME EXTENSIONS WXL AND WCS New in version 4 0 e Since with ACT 7 0 you may have more than one product under an opportunity Advanced Sales Lookup Export and Report now allows to create reports either at the opportunity level or at the product level To create a product report simply include a product field in the report definition e You may now add calculations for each row of data in your Excel template see Advanced Techniques explanation of the commission report e tis now possible to insert blank columns in your reports to give you even more control over the look of your report in Excel Important Known Issue ACT 8 0 1 has a bug that prevents us from accessing the reasons lists Sage has assured us that this will be fixed in the next release In the meantime this opportunity fields may not be used under the 1 and 2 condition tabs Advanced Sales Loo
17. s the Run button e If you chose the Excel Report option Excel will open and display the data using the appropriate template e If you chose the CSV File Export option a Save dialog box will open to allow you to name the file in which you want the data stored NOTE Make sure your Excel security settings Tools gt Macros gt Security are not set to High otherwise none of the macros necessary to create the report will be activated If you choose to set them to Medium when prompted click Enable Macros Microsoft Excel C Program Files ACT ACT for Win 7 Plugins Exponencel Advanced Sales Lookup Export and Reports Launch XLS contains macros Macros may contain viruses It is usually safe to disable macros but if the macros are legitimate you might lose some functionality Disable Macros able Macro More Info If you want to avoid having to enable macros every time set your security settings to Low Advanced Sales Lookup Export and Reports User s Manual Creating a report Since the add on comes with a number of pre formatted report it might be easier to duplicate and modify an existing report see Duplicating a report then Modifying an existing report below The settings of the report are defined through the Report Settings window and they include The fields that ought to be included in the report The sort order The sub total and total sections The upper left cell of the data section in your Excel spre
Download Pdf Manuals
Related Search