Home

- MasterMine Software

1. Source MM Field Name SQLExpression dBase Expression Coalesce Convert float Nulllf ISDate CH DURATION 1 Convert float NullIf Char Index S CH SRECTYPE 1 1 convert float Convert Datetime CH DUR iif isdate CH DURATION timevalue CH CH Hours ATION 24 DURATION 24 0 Coalesce nullif ISNUMERIC Replace C H DURATION 1 1 nullif charindex S CH SRECTYPE 1 iif CH DURATION Like _ Or 1 Convert Money srectype lt gt S 0 iif CH DURATION Is CH Amount Replace CH DURATION Null O val CH DURATION Coalesce nullif ISNUMERIC CH UNITS 1 1 nullif charindex S CH SRECTYPE 1 CH Units 1 Convert Real CH UNITS if CH UNITS Is Null O val CH UNITS iif CH CREATEON Is CH CreatedOn Coalesce CH CREATEON NullDate Null NullDate CH CREATEON DateDiff day CH CREATEON Coalesce iif CH Ondate is null NullDate CH DaysForcst2Sale CH OnDate NullDate CH ondate CH CREATEON DateDiff day Coalesce C1 CREATEON iif CH Ondate is null NullDate NullDate Coalesce CH OnDate NullD CH ondate iif C1 CREATEON Is CH DaysLead2Sale ate Null NullDate C1 CREATEON CH Last User Rtrim CH LASTUSER CH LASTUSER iif CH lastdate Is CH Last Modified Coalesce CH LASTDATE NullDate Null NullDate CH LASTDATE CH CreatedBy Rtrim CH CREATEBY CH CREATEBY CH Linked OpRecID CH LOPRECID CH LOPR
2. 64 131 EXPOLE iia 70 Field Button Color esses 61 Field TOS iia 61 Fields ACCOUNT Oia teet 163 COMPANY 163 DURATION 220 802008024282 163 Linked to Contact 97 163 Mints Ss occisos erecto ce gente 164 ONDJATE unciataadiasdaad a 163 RECID stand 164 RECTYPE cunado 163 REF 163 Shortt Reference i250 duode 163 SUCCESS nannten 163 File Locations 20 26 27 51 lidad 51 Isilrssiet e 51 O sueco tette ite 52 Filters cette eee e ete ee n 93 Prestar star CROP 51 IT 40 Format Numeric Fields 94 Formatting PUTA ELS ai 80 PivotTables See PivotTable formatting Breeze Panes 65 129 GoldMine Administrator s Toolkit 45 GoldMine Filters 52 GoldMine Groups sse 52 69 GoldMine taskbar esses 69 GoldMine Taskbar esses 106 Grand Total iii 94 Grouping dat S e dama ctiain e ates 82 89 WEEMS is 65 131 CUM Seco deed ears 80 on content seatch sse 88 Headings eene 93 lef 69 Hidden Area 60 81 94 115 Hidden Items iii 91 hide items in a field 91 How to deploy a report 69 How to add fields to an existing query 112 add MasterMine usets sss 38 add remove subtotals ee 94 create a MasterMine repott 38 drill down for detail
3. sss 96 manage existing reports 106 122 refresh data in an existing report 63 92 106 123 troubleshoot reports sss 122 TfistallatiOBZ cete ce eet 16 20 Multiple nette tette 25 Integration of non GoldMine data 10 14 110 Items Per Biel diana gen 167 A A nennen ee 42 ECOS 114 123 L bel 2 n u oia 93 Large File Handling See Memory managing usage Licensing nensneenesnesnernesnesnennenunen 16 20 limit query size sese 53 Line Listin cis 96 Memoty managing usage nennen 46 123 MS Office iii 168 MS Office installation SIL MS Office sette 17 MS OUEN ett DIU 66 68 110 168 hp c M 94 159 Numeric data 23 30 Open MasterMine Report 38 132 Page Ata 60 91 Page PilberS mcr 81 Page Layos 92 PivotTable formatting nennen 65 91 123 128 functionality 25 2 aci ran 59 A 165 memory usage 18 165 preferences 91 Wizard tasar 111 PivotTable Options See PivotTable formatting Preferences u a 30 Preserve Formatting sse 91 Profile field AS Date zo amate het 30 ds NUM Einicio 30 judo M 126 Profiles tette tetendit 40 PULSE esee tenen as 63 A A eee UE Ru 63 Refresh on open 70 Refresh Query 10 55 63 82 92 123 A breres 92 123 Refreshing Repotts see 106 R pOttz iet teet
4. Workbook Name Description GM Tables No History Activity Contacts with no history no completed activity C1XX Sync Log Full log of synchronizations SL Last Sync History Report of most recent sync activity for each sync site GSGH Sync Report of sync activity by site GS Alerts GoldMine Alerts assigned to contacts in record properties AHALC1C2 Alerts by Date GoldMine Alerts sorted by date last modified AHALC1C2 Lookups All Lookup values Includes Zip code lookups LK All Lookup values except Zip codes Faster to load and refresh Lookups excluding Zip codes than Lookups report LK All group members shows all memberships by company or all Group Members companies with membership C1CGG2C2 Company Resources scheduled for usage typically in pending Resource Usage appointments RICA 130 Activity Analysis Toolkit MM Activities xls Activity Analysis templates are account centric reports that focus primarily on Most Recent Activity and Next Pending Activity These views into your database can be used for many purposes but we recommend using them at least to find neglected customers and opportunities missed assignments and well or poorly covered territories Workbook Name Description GM Tables For all contacts shows only most recent completed Last Completed Activity Any type activity or none if there is none C1CH For all contacts shows only most recent non e mail
5. a a Inner Joined missing do NOT appear at all C1C2 Forecast sales linked to contacts and any profile record Forecast Sales 8 Contact Counts one of EACH for every occurrence of a match so amp Details Profiles iltering must be done on the page CACSC1C2 Completed sales linked to contacts and any profile record Completed Sales amp Counts one of EACH for every occurrence of a match so Contact amp Details Profiles ffiltering must be done on the CHCSC1C2 Pending activities linked to contacts and any profile record Pending Activities amp Counts one of EACH for every occurrence of a match so Contact amp Details Profiles ffiltering must be done on the page CACSC1C2 Completed activities linked to contacts and any profile Completed Activities amp record Counts one of EACH for every occurrence of a Contact amp Details Profiles match so filtering must be done on the page CHCSC1C2 Supplementary info including user defined profiles referrals document links and other specialized GoldMine info Supplementary records profiles referrals doc links etc ContSupp amp Contact linked to primary contact info including user defined fields CSC1C2 All records of completed activities Analysis of ContHist log records generated if GoldMine s History Log feature is turned on Will result in No Records History Log Analysis message and aborts if feature is turned off CHC1C2 Supplementary Contacts Additional Cont
6. N A will appear in the Number of Matching Records box if the query is too complex to be pre calculated Remember that if you set criteria for more than one field a record must meet the criteria for BOTH fields to be included in the query If the count is 0 your criteria may be too restrictive That is you may have eliminated all records in the database from the query When you hit Finish to run such a report a warning message box appears and you must re select filtering criteria When dealing with large databases there are several reasons to hone your query in advance Faster loading and refreshing are obvious benefits Most importantly pre filtering allows you to handle limitations in Office 97 and 2000 on the number of unique items that may appear in a pivot field Filter on History Dates If your selected report includes a query of history completed activities the history filter is enabled so that you can limit the query by date This is the same First Date field that appears on the main MasterMine dialog screen If you have already set the First Date on the opening screen it will reflect the same date here However there are some additional considerations By default the Last Date is disabled unless you check the associated box DO NOT specify a last date unless you want your report to permanently exclude newer history items than those on the date you set Remember MasterMine reports ar
7. within it There are then 100 cubed 100 3 possible combinations or 1 000 000 intersections With Office 97 or Office 2000 Excel creates a memory location for each possible combination of these values because any given record might have any combination of these items in it In the Row area your pivot table is limited to 2 1 billion intersections In the column area the limit is 32 768 intersections With Office XP and later Excel holds these intersections in memory ONLY if there is actual data that would occupy them That is an intersection is used only if your query actually contains at least one row whose fields contain the item in each of the three fields represented by that intersection In practice your data probably will not have a representative of each of these combinations in it so beginning with Office XP your pivot table can accommodate vastly more fields in the row or column area without hitting the limit Example Assume you create a Pivot Table that contains five column fields The fields contain 10 5 2 40 and 3 items respectively The product of these values is 10 x 5 x 2 x 40 x 3 or 12 000 possible intersections 160 If you try to add one more field that contains three items the product would be 12 000 x 3 or 36 000 Because this number exceeds the maximum product of the items you receive the following error message Not enough memory to completely display Pivot Table Note that worksh
8. MasterMine automatically creates date groups when creating Pending or History reports The OnDate field is the one used even though activity records contain many dates from which date analysis might be made CreateOn LastModified etc When you group dates to make date ranges be sure to use the appropriate date field Ungroup Field Items Select the group item and click the ungroup button on the MasterMine toolbar If the selected item is the only group item in the field the grouped field will disappear Notes on Groups Because grouping is so powerful and such an important feature of the dynamic nature of MasterMine it is important to know some key things about groups e Groups affect all pivot tables in a workbook that rely on the same cache of data Therefore if your workbook contains multiple worksheets cloned from a single one re grouping will apply across ALL pivot tables in the workbook e When you name a group in the dialog box that comes up when you create the group the new name applies across all pivot tables using the same data cache If you re name a group after the fact by typing over the group name directly in the pivot table the name change will apply only to the current pivot table e Creating a groupaffectsthe shape of the pivot table Note that since grouping affects pivot tables across worksheets you might cause unintended changes to a pivot table on a worksheet other than the one that is currentl
9. Setting up for SQL Installations MasterMine includes an automatic function that sets up your system to use SQL databases For GoldMine Version 6 7 and below this function requires that the current Windows user have administrative access to the Borland Database Engine BDE folder If this setup does not finish successfully you can manually set up access to your SQL database by updating the parameters on the GMSQL tab of MMINI xls found in the Support folder within the MasterMine folder Note that you MUST obtain a working username password combination for the SQL database from your system administrator If your GoldMine installation uses MS SQL the first time you run MasterMine you will see this dialog br GoldMine InstallShield Wizard jw Save SA password in eara new epar fnr autemaic mfresh InstallShield Wizard Completed The InstallShield Wizard has successfully installed MasterMine 6 0 or GoHMine Click Anis1 to exit the wizard Completing installatior Please wait lt Back Frush Lancel 16 Enter a working SQL username and password High security environments may require the use of a SQL username and password that have read only capabilities in SQL For further security uncheck the box for Save SQL password in each new report for automatic refresh Unchecking this preference causes MasterMine to ask the user for a working SQL password every time the person refreshes
10. Yellow Pages Prospect Aviation Laura SE 2 22 2006 AD022423074 X X_Bro C 5 lan Flowers Service Plus Machines GA Customer Referral Prospect Aviation Laura SE 2 6 2006 AD020837472 xXPOK lWva A iflowers minds 6 Barbara Jones _Bartman Company NC Radio Advertising Past Client Aviation Laura SE 12 11 2005 99121340961 LOOTZBar A babs mci net 7 Brandon Bates Martinsen NC Magazine Ad Past Client Aviation Bill SE 12 12 2005 AD0120251918 amp V v B brandon martit 8 Kathy Woodworth Premdor Systems FL Customer Referral Past Client Aviation Tammy SE 5 20 2005 97111136296 QNMBGLau B kathy mci net 9 Patricia Phan Qualified Systesm NC Yellow Pages Engineering Tammy SE 4 19 2007 91040329159613033Dav B 10 Rachel Thompsin Ramtron Inc FL Trade Show Suspect Engineering Tammy SE 1 11 2006 A0011332047 3CREQRac_B rachel ramtror 11 Walter Wells Consumer Reclamation Project NC Customer Referral Prospect Engineering Bill SE 8 29 2005 990831006058 GCom C wwells consre 12 Randolph Williams R D Williams Inc GA Magazine Ad Prospect Engineering Tammy SE 10 21 2004 98102330724 P _J C 13 Marty Spier Landsource GA Radio Advertising Prospect Engineering Laura SE 7 31 2006 94041300000126900Mar C 14 Donna Ritter Speedy Solutions TN Trade Show Past Client Engineering Bill SE 10 27 2006 A0020160931 K_AV7 B 15 Lance Lawrence Trenton Solutions Group GA Trade Show Suspect Food Laura SE 11 22 2005 99112432398 1_ NGJoh C 1
11. 11 For Office 97 unsupported except by special arrangement Ensure that your Office 97 installation includes Excel 97 and the following Data Access selections Database Drivers DBase and MS FoxPro Drivers MS Excel Driver MS SQL Server Driver if using SQL version of MasterMine MS Query Data Access Objects for Visual Basic Some of these selections are zo part of the typical installation for Office 97 so you may have to install them using your Office 97 installation medium To install the necessary data access utilities for Office 97 run the Office 97 setup directly from CD ROM or from your network or click Start Settings Control Panel and select the Add Remove Progtams icon find Microsoft Office 97 in the list box and hit OK Click the Add Remove button and in the provided list box click once on Data Access and then click on the Change Option button on the right Hit Select All below the Change Option and click OK Finally click once on the Continue button at the bottom We recommend that you install the most current service release for Microsoft Office soon after it is released You can obtain it from the Microsoft support website at http support Microsoft com at no cost Required Hardware MasterMine software itself occupies very little space either on your hard drive or in memory during operation especially when compared with GoldMine Excel and Windows all of which must r
12. Last Completed Activity Excluding E mail activity or none if there is none C1CH For all contacts shows only most recent successful closed sale Contact information is included for ALL contacts Last Closed Sale including those with NO sales C1CH For all contacts shows most recent completed sale whether closed or lost Contact information is included for Last Completed Sale Closed OR Lost ALL contacts including those with NO sales C1CH Most recent call for each contact or none if there is none Contact information is included for ALL contacts including Last Completed Call those with NO calls C1CH Most recent appointment for each contact or none if there is none Contact information is included for ALL contacts Last Completed Appointment including those with NO appointments C1CH For all contacts shows only most recent completed call or Last Completed Appointment or Call appointment or none if there is none C1CH For all contacts shows all Pending activity for each Pending Activity for All Contacts account or Blank if none C1CA Pending Activity except E Mail for All For all contacts shows all Pending activity except e mails Contacts for each account or Blank if none C1CA Next Pending activity for each contact record or None if Next Pending Activity only All Contacts none exists C1CA Alarms for all pending items organized into a calendar Alarm Calendar format CAC1 All pending items organized by UserlD Report de
13. Note that dragging in the Industry field allows us now to see each Contact Type item broken down by both Industry and Territory The advantage of seeing this further detail is obvious we see a finer level of detail in our database The disadvantage is that our report now extends beyond the boundaries of the page Continuing to add detail will simultaneously expand the size of the pivot table eventually making the table very difficult to read Line Listing Reports To create a common list listing report simply drag all the fields you need into the Row Area of your report in the sequence you need them to appear This kind of report typically shows a single line per row in your query You DO NOT need to drag fields into the report that will have only a single value These fields can be left in the Page Area of the report and the specific value simply selected in the listbox to the right of the field This vastly improves the efficiency of your report as well as creating an explicit heading at the top of the report indicating what is shown below Once you have dragged all necessary fields into your report you can make a choice about which fields to subtotal Typically in this type of report only the first one or two fields in the row area will be left with subtotals Turn off subtotaling for each field by right clicking on one subtotal for each field and hitting Hide in the submenu You can do 70 this for all fields
14. PF PFRectype Rtrim PF RECTYPE PF RECTYPE PF Detail Item Rtrim PF FNAME PF FNAME PF Detail Reference Rtrim PF F VALUE PF FVALUE PF Detail Notes udfDBO udfMMStripHTML PF NOTES PF NOTES substring udfDBO udfMMStripHTML PF PF Detail Notes2 NOTES 254 253 mid PF NOTES 254 substring udfDBO udfMMStripHTML PF PF Detail Notes3 NOTES 507 253 mid PF NOTES 507 substring udfDBO udfMMStripHTML PF PF Detail Notes4 NOTES 760 253 mid PF NOTES 760 substring udfDBO udfMMStripHTML PF PF Detail Notes5 NOTES 1013 253 mid PF NOTES 1013 PF Sync substring PF LINKEDDOC 8 1 mid PF LINKEDDOC 8 1 iif isdate format mid PF LINKEDDOC 2 3 8 HHHEHHEHHE datevalue format mid convert datetime substring PF LINKEDD PF LINKEDDOC 23 8 PF Detail CreatedOn OC 23 8 NullDate PF Linked Doc substring PF LINKEDDOC 47 30 mid PF LINKEDDOC 47 PF Whole Link PF LINKEDDOC PF LINKEDDOC PF DetailRecords PF RECID PF RECID PT PTRectype Rtrim PT RECTYPE PT RECTYPE PT Team Member Rtrim PT CONTACT PT CONTACT PT Department Rtrim PT COMPANY PT COMPANY PT Title Rtrim PT NAME PT NAME PT Role Rtrim PT STATUS PT STATUS PT PTAccountNo PT ACCOUNTNO PT ACCOUNTNO PT TeamRecords PT RECID PT RECID PI PlRectype Rtrim PI RECTYPE PILRECTYPE PI Issue Rtrim Pl COMPANY PI COMPANY PI Issue Priority Rtrim PI STAGE PI STAGE PI Issue Status Rtrim PI STATUS PI STATUS PI Issue Start PI STARTDATE PI STARTDATE PI Issue User Rtri
15. SaveTo preference in MMINLxls A typical installation with a Common folder in the GoldMine folder will therefore include a MasterMine folder within the Common folder once the first MasterMine report has been built Once created MasterMine reports can be stored anywhere without losing their functionality This makes them easy to transport and share among GoldMine MasterMine users Terminal Services Installation MasterMine may be installed on Terminal Servers including those using such products as Citrix SQL parameters need be entered only once as these are stored in the central MMINI xls file Beyond this simply install MasterMine on the server machine as usual and do the following to make the software available to each user 1 Add launch items to the user s GoldMine View Menu This may be done manually by copying the FileMenu segment of one installed user s User ini file into those of other users Alternatively for each subsequent MasterMine user log into GoldMine as the additional user then double click on the file MMInstaller exe in the MasterMine Support folder 2 License each user in the way they ate logged in or do this in advance for them while logged into GoldMine in step 1 above Uninstalling MasterMine s uninstall will remove all MasterMine program files from the machine as they were during installation You may need to delete files or folders that have been created
16. t fit your company s prescribed policies or that are inaccurate simply because of data entry errors Some can be merely irritating while others like a sales follow up call scheduled in the wrong year or a missed appointment might cost you money As you work with your regular management reports you will often come across out liers odd records that stand alone in a schedule or that don t fit your regular data pattern Before changing the data check to make sure that the user who made the mistake is well informed about the correct way to enter data You may uncover similar mistakes in a pattern that suggests training issues Next to correct the errors you have uncovered MasterMine s built in Drilldown feature becomes very handy Drill down to the original GoldMine record to correct the data While there check around for other errors in the same record Each successive drilldowncreates new spreadsheets in your workbook Don t forget to delete these when the data have been corrected Then go back to the original Pivot Table and refresh the data to ensure that your corrections are made propertly Exception Reports You can also build reports specifically designed to uncover data exceptions For example by grouping Users according to function and running Pivot Tables that compare user groups against activity codes or date groups you can find records that indicate activities outside the normal process The pre fo
17. 1 1 1989 AS OnDate Datediff day getdate CA OnDate AS DaysTIll Rtrim CA USERID AS User ID Rtrim CA ACTYCODE AS Activity Code Rtrim CA REF AS Product substring CA Ref 1 20 AS Short Reference Coalesce CA OnDate 1 1 1989 AS OnWeek Convert float Coalesce CA Duration 0 100 AS Probability Coalesce CA Duration O AS Duration In general it is a good practice when moving a GoldMine database from one server to another to avoid changing the database name This will simplify updating existing MasterMine reports to use the new data location because you will need to change the reference only to the server here Query Text The entire Transact SQL statement Query that underlies the pivot table may be viewed and analyzed but not altered within the Query Text tab Users who know SQL expressions can see precisely what data transformation if any a field has undergone prior to being presented in the MasterMine report Most MasterMine fields include some type of formatting error trapping or other data transformation so it is helpful to be able to view the expression that underlies a field here Modify Query If you wish to alter the underlying query using MSQuery you can hit the Modify Query button beneath the query display OR hit the Query Wizard button ie on the Excel PivotTable toolbar while a field ON the pivot table is selected see Modify Existing Query Chapter 6 MasterM
18. Null NullDate CA LASTDATE CA AlarmTime Rtrim CA ALARMTIME CA ALARMTIME CA AlarmFlag Rtrim CA ALARMFLAG CA ALARMFLAG Rtrim isnull nullif CA APPTUSER Blue CA Color CA APPTUSER CA ContactFile Rtrim CA DIRCODE CA DIRCODE CA CAAccountNo CA ACCOUNTNO CA ACCOUNTNO CA CARecords CA RECID CA RECID CA Linked OpRecID CA LOPRECID CA LOPRECID iif CH Ondate is null NullDate CH OnDate Coalesce CH OnDate NullDate CH ondate substring datename weekday Coalesce CH OnDay CH OnDate NullDate 1 3 WeekDay CH OnDate CH OnTime CH OnTime CH OnTime CH OnHour substring CH OnTime 1 2 left CH OnTime 2 CH User ID Rtrim CH USERID CH USERID Datediff day Coalesce CH OnDate NullD date iif CH OnDate is null CH DaysSince ate getdate NullDate CH OnDate CH Activity Type CH SRECTYPE CH SRECTYPE CH Result Code Rtrim CH RESULTCODE CH RESULTCODE CH Activity Code Rtrim CH ACTVCODE CH ACTVCODE Rtrim substring Ltrim Stuff CH REF Char left iif CH REF Like index oc CH REF 1 200 Charindex oc left CH REF instr CH REF oc CH Short Reference oc CH REF 1 ShortRefLen 1 CH REF ShortRefLen Rtrim Itrim Stuff CH REF Charindex iif CH REF Like oc CH REF 1 200 Charindex 0c left CH REF instr CH REF oc CH Product oc CH REF 1 CH REF Rtrim Ltrim Stuff CH REF Charindex iif CH REF Like oc CH REF 1 200 Charindex oc left CH REF instr CH REF oc CH Reference oc CH RE
19. Start Run and in the command box enter c progra 1 master 1 mastermine exe NS The next time the user launches GoldMine the View menu will contain the MasterMine launch items and the MasterMine add in will be available in Excel File Locations The MasterMine program and support files should be installed locally on each workstation to be used by a MasterMine user MasterMine s main files typically reside in a Program Files sub tree named MasterMine and containing subfolders Support and Toolkits The installer also includes a number of standard Microsoft libraries dlls which automatically overwrite and self register if older versions of the same dlls are found MasterMine appends two Excel add ins that are invoked each time you load Excel These add ins include a MasterMine toolbar which provides a number of important functions in addition to the basic Pivot Tables toolbar native to Excel The add in files called MasterMineLib_v2 xla and MMupdater xla are copied by MasterMine into the Library folder of the active Microsoft Excel application folder tree typically on the local workstation Once standard Excel spreadsheet files with the extension xls have been created from the MasterMine templates these files reside for dBase users in a MasterMine subfolder 20 of the selected GoldMine contact set For SQL users the location of MasterMine reports is determined by the
20. but can be used for most other types of records Note that record here refers to a row of information in a GoldMine table typically corresponding for example to a single activity on the Pending or History tabs in GoldMine or to a profile record on the Details tab or an Opportunity record or sub record Test run do not update GoldMine By default every time you use MMUpdater this option is checked on If you leave it checked you WILL have another opportunity to commit changes to GoldMine after viewing the before after on a log sheet We recommend leaving this value checked when making important changes When you click the OK button MMUpdater passes through all selected records and if Test run is unchecked updates the data in GoldMine 94 MMUpdater Logsheet As it processes records MMUpdater creates a Logsheet that displays your data in before after form This allows you to double check that your intended changes are really the ones that will occur It also provides you a to keep in the workbook so you know what changes were made on this date If you did a test run you can now commit your changes to GoldMine from the Logsheet by clicking on the MMUpdater icon on the MasterMine toolbar again A dialog appears asking if you are sure you want to update GoldMine You may also update only a subset of the displayed records on your Logsheet simply by deleting the rows of any records you do not wish to updat
21. feature to operate is that each pivot table have an identically named field in the Page area Change a Field Button Name or Label Select the field button or item and type a name that reflects its meaning in the context of the current report You may need to select the button twice if the first selection highlights a row or column field s underlying data Do not use a name that is already used elsewhere in the Pivot Table If you do that field will be pulled to the location of the selected field The color of the field changes to black an undocumented Excel feature Drag any field in the table to change the color back Change an Item Name Select an item in a row or column of the table and type a name that reflects its meaning in your context Once the item name has changed you can identify the original name by 87 drilling down to the underlying data and finding it in the column for the source field Name changes like this remain in place even through data refreshes Retrieve Hidden Page Fields The easiest way to retrieve a field is to find it in the pivot table field list and drag it into your report However another trick can save you time If you know the name of the field you wish to retrieve you may select any page field and type the name of the field exactly as though you were changing the name on the field button Instead MasterMine recalls the hidden field and moves it down Re size Cells to Fit Data Cells s
22. it For example if we have been looking at data stored in Z Program Files Goldmine Common this workbook is at ZA Program Files Goldminel Common MasterMinel Sales Pending xls 126 Appendix C Pre formatted Reports Toolkits and Templates MasterMine s pre formatted report templates are grouped into job function or content related Toolkits to make them easier to find These Toolkits and the templates they contain are accessed by clicking use template on the upper right of the main create report dialog They can also be found listed by toolkit from the Templates menu The following pages document the contents of these toolkits which contain the bulk of templates available in MasterMine GoldMine Administrator Activity Analysis Sales Manager Toolkit Opportunities Linked to Contacts Projects Linked to Contacts Combined Pending amp History Workbooks by GM Table Name QuoteWerks onsupone To aid in report selection the following tables list the templates available in each toolkit Note on Custom Fields Contact2 fields which are custom user defined fields ARE available for any workbook that shows Contact2 in the GM Tables column Checking any C2 field on the Fields Used tab will add the Contact2 table to the query and change the join type to inner if it was not already MasterMine Aliases for Database Tables In MasterMine documentation as well as in MasterMine SQL queri
23. not available on GoldMine PE opens a new contact window to display the report record instead of switching away from the current record in GoldMine In rare instances GoldMine calls up the record with the account number nearest the one you ate seeking but not necessarily the same one MasterMine will check the account number against the target and alert you if it does not match This might happen if your report uses old data or data from a database other than the one currently being used by GoldMine If you receive this warning check in GoldMine under File Open to make sure your open GoldMine database is the same as the target of your MasterMine Pivot Table For some reports involving pending and completed activities there may be a Linked to Contact field in the PivotTable that you can set to Yes meaning include only records linked to the currently open GoldMine contact set Create and Use GoldMine Groups Built into GoldMine is an ability to process or update GoldMine records in groups from two to two hundred thousand as many account records as you have Some important functions that are enabled by creating GoldMine groups e E mailing campaigns can be managed by using GoldMine Groups together with GoldMine s e mail client e Records can be mass updated Contact level only that is Contactl and Contact2 fields by using GoldMine Groups together with GoldMine s Global Replace function e Calling
24. or link it to a page in the GoldMine Knowledge Base Such deployment follows the ordinary rules for linked documents in GoldMine in particular this Files are synchronized to other users NOT every time they are changed but rather each time they are LINKED to a GoldMine record or to the Knowledge Base Keep this in mind when planning an enterprise wide distribution scheme Opening Existing Reports MasterMine reports are saved in a way that requires them to be opened using the licensed MasterMine application Even though MasterMine reports are stored in Excel workbooks they are protected from viewing or manipulation by unauthorized users 99 Attempting to open a MasterMine report directly in MS Excel results in the report opening then immediately shutting down without displaying any data There are two typical ways to open an existing MasterMine report Open via Windows dialog Click Open MasterMine Report on your View GM versions 7 and lower or GoTo menu to open a standard Windows directory dialog and navigate to your report By default this dialog opens to the stored SaveAs folder designated in your MasterMine preferences You have all the usual file handling capabilities in this dialog so in addition to simply opening existing workbooks you can copy paste move rename or delete files as needed To open an existing workbook double click on the filename or select it and hit Open If you have Mas
25. processes by which your company operates We can t dictate to you what these are because your processes are unique but these guidelines can help you work your way toward them Creating Functional Workbooks By now you know that dynamic management reports are much easier to create and manipulate in MasterMine than static reports created with GoldMine s built in report generator or a third party software like Crystal Reports MasterMine reports also refresh much more quickly and are easier to copy and modify In addition since they allow drilling down back to your GoldMine database MasterMine reports can help you clean up your data instead of just showing it to you For a dBase installation of GoldMine a new folder called MasterMine is created in the contact set folder associated with each GoldMine contact set The folder contains a series of Excel workbooks extension xls one for each template you have opened The Toolkits that MasterMine providesgive you an excellent start in developing this set but you will quickly move beyond what mere templates can do for you Change the names of these workbooks to reflect the functions they serve Some like Calls Analysis and Sales are obvious Others might look at your Production Processes Customer Service Financials or other data and should be named accordingly as you build them Troubleshooting Reports While you are still new to data mining it
26. udfMMStripHTML C1 NOTES C1 NOTES substring udfDBO udfMMStripHTML C1 C1 C1Notes2 NOTES 254 253 mid C1 NOTES 254 substring udfDBO udfMMStripHTML C1 C1 C1Notes3 NOTES 507 253 mid C1 NOTES 507 substring udfDBO udfMMStripHTML C1 C1 C1Notes4 NOTES 760 253 mid C1 NOTES 760 substring udfDBO udfMMStripHTML C1 C1 C1Notes5 NOTES 1013 253 mid C1 NOTES 1013 C1 MergeCodes Rtrim C1 MERGECODES C1 MERGECODES C1 CreateBy Rtrim C1 CREATEBY C1 CREATEBY iif C1 CREATEON Is C1 CreateOn Coalesce C1 CREATEON NullDate Null NullDate C1 CREATEON Coalesce convert datetime nullif substrin g char ascii c1 accountno 17 isdate su bstring c1 accountno 1 6 iif c1 accountno is null 1 substring c1 accountno 2 5 1 isdate NullDate datevalue formattiif left c1 2 substring c1 accountno 2 5 6 N accountno 1 A 200 mid c1 accountn C1 CreateOn ullDate 0 2 5 left c1 accountno 6 C1 Owner Rtrim C1 OWNER C1 OWNER C1 Acct Last User Rtrim C1 LASTUSER C1 LASTUSER iif C1 LASTDATE Is C1 Acct Last Modified Coalesce C1 LASTDATE NullDate Null NullDate C1 LASTDATE C1 C1AccountNo C1 ACCOUNTNO C1 ACCOUNTNO isnumeric substring C2 ACCOUNTNO 2 C2 Linked To Contact2 1 C2 ACCOUNTNO Is Not Null C2 USERDEF01 C2 USERDEF01 C2 USERDEFO1 C2 USERDEFO2 C2 USERDEFO2 C2 USERDEFO2 C2 USERDEFO3 C2 USERDEFO3 C2 USERDEFO3 C2 USERDEF04 C2 USERDEF04 C2 USERDEF04 C2 USERDEFO5 C2 USERDEFO5 C2 USERDEFO5 C2 USERDEF0
27. C1 Product Int C1 Phone C1 Address3 E C ener sd Ci Ciy Premium Edition or higher Cases these can also lt appear in the list They can be turned on at installation time see Installation page 10 14 Report Name Prospect Analysis d at any n time using the Preferences SaveTo Path C MasterMine Reports 08 page 5 E Save To Template File Finish Cancel Your first choice specifies which GoldMine area you intend to report Once you ve selected the 33 area you can easily build a report from scratch but specifying more narrowly which subtypes of each data with which you wish to work Alternatively the following section discusses existing template selection once the GoldMine atea is specified Contacts You can report on only main contacts or by checking Additional you can include the contacts on the Contacts tab in GoldMine in parallel to the main contacts as though they were in one big table together Activities Select Pending History or both to report at the activity level One row will be returned for each activity record so these reports are activity centric E rests New MasterMine Workbook al If you select Activities you have a secondary choice File Toolkits SQL Query Preferences Help of the type of activities to include like Calls Which part of GoldMine s information do you want to see Appointments etc This list corresponds to the C Conta
28. Details tab Native GoldMine Preferences Toolkits Integration Details Plus Path to Application exe Enable V g Details Plus Browse QuoteWerks Path to Application exe Enable W ga uoteW erks Browse Quotewerks Database Demo Cancel Save and Close makes ten text type fields plus one notes field available in a Profile DetailsPlus makes available all 14 fields plus two notes fields in the ContSupp table Most importantly each field can be defined as a character numeric or date type field with full data entry validation on a user configurable screen layout MasterMine reads this profile configuration so that DetailsPlus fields are formatted automatically using the proper field types This allows you to take advantage of MasterMine s sophisticated numeric and date grouping and analysis capabilities correlating DetailsPlus profile records with main account information To set up the integration launch the Preferences menu in MasterMine s Create New Analysis Workbook dialog Click on the Integration tab Check the box to Enable DetailsPlus then browse to or enter the path to the DetailsPlus application Typically this is a folder named something like C Program Files Solica DetailsPlus containing the file DetailsPlus ini Once this integration is set up MasterMine presents DetailsPlus defined profiles in the Profile Type window and Details P
29. Record Type Data CH RECTYPE CH RECTYPE CH Full Activity Type Rtrim substring CH RECTYPE 1 2 trim left CH RECTYPE 2 sign Coalesce ascii substring CH RECT CH Success YPE 3 1 1 50 mid CH RECTYPE 3 1 lt gt U iif CH status Is Null CH HasNotes Coalesce substring CH STATUS 2 1 0 mid CH STATUS 2 CH CHNotes udfDBO udfMMStripHTML CH NOTES CH NOTES substring udfDBO udfMMStripHTML C CH CHNotes2 H NOTES 254 253 mid CH NOTES 254 substring udfDBO udfMMStripHTML C CH CHNotes3 H NOTES 507 253 mid CH NOTES 507 substring udfDBO udfMMStripHT ML C CH CHNotes4 H NOTES 760 253 mid CH NOTES 760 substring udfDBO udfMMStripHT ML C CH CHNotes5 H NOTES 1013 253 mid CH NOTES 1013 substring udfDBO udfMMStripHTML C CH CHNotes6 H NOTES 1266 253 mid CH NOTES 1266 substring udfDBO udfMMStripHTML C CH CHNotes7 H NOTES 1519 253 mid CH NOTES 1519 substring udfDBO udfMMStripHTML C CH CHNotes8 H NOTES 1772 253 mid CH NOTES 1772 substring udfDBO udfMMStripHTML C CH CHNotes9 H NOTES 2025 253 mid CH NOTES 2025 substring udfDBO udfMMStripHTML C CH CHNotes10 H NOTES 2278 253 mid CH NOTES 2278 CH Duration Rtrim CH DURATION CH DURATION Coalesce Convert float Nulllf ISDate CH DURATION 1 Convert float Nulllf Char Index S CH SRECTYPE 1 1 convert float Convert Datetime CH DUR iif isdate CH DURATION timevalue CH CH Minutes ATION 1440 DURATION 1440 0 143
30. Using these skills you can build a sophisticated Digital Dashboard that contains all the key metrics you need to manage your people and your business effectively Data Area Alternatives Your Pivot Table Data area generally shows a count of the number of records in GoldMine that meet the criteria given by the corresponding column and row headings and by filters set in the Page area More sophisticated reports can include summed numeric data such as sales amounts or even dates in the data area If you start with a MasterMine template you will generally not need to drag fields into or out of the Data Area MasterMine reports anticipate the best arrangement of fields to be included in the Data Area given the context of the report As you become a more sophisticated user of MasterMine you may find that experimenting with the Data Area is a rich source of sophisticated information Here our comments on the Data must remain general If a field in the data area contains character data the numbers displayed are counts of non null values in that field Because every record in GoldMine contains a value in both the RecID and AccountNo fields these fields ate most often used as the default field in 73 Suggestion When you first launch MasterMine you need only hit Create without selecting the Data Area However sometimes MasterMine uses numeric or date type data to create more interesting results Numeric Fields After
31. a report or uses MSQuery If you have multiple SQL GoldMine databases we recommend that you insert additional rows by hand into the GMSQL worksheet of the MMINI xls file to accommodate each database s SQL parameters This eliminates the need to re enter SQL parameters each time you access a different GoldMine database from inside GoldMine ServerName DBName UserName Password and BDEName should be well known to the SQL GoldMine administrator These settings are easily obtained using the Borland BDEAdministrator In GoldMine 5 x or above go to the Tools menu and select BDE Administrator In the left column find the BDE alias that matches your current GoldMine contact set as named in the GoldMine Open Database dialog and click on it once The parameters you need will be available on the right side of the screen SaveTo Indicates the default storage location of MasterMine reports generated for SQL contact sets Integration with GoldMine Add on Products MasterMine reporting and analysis extends your ability to work with some of the more popular GoldMine add on products If your GoldMine installation includes any of the products listed below you can integrate them by entering path information in Preferences Integrating Details Plus By Solica Consulting www solica com DetailsPlus is a very popular add on for GoldMine greatly extending the capability of one to many user defined Profiles on GoldMine s
32. by users or that have been created by the program itself If this is the case delete the application folder called MasterMine to remove any folders in the same tree To complete the uninstall you will need to perform some additional manual adjustments GoldMine View menu references to MasterMine must be removed from the file yourname ini in the GoldMine folder using any text editor If other custom menu items exist they would be listed under Filemenu in the ini be sure to leave them sequenced properly starting with Opt1 Opt etc After running the automatic MasterMine uninstall you will need to manually remove from Excel the reference to the MasterMine add in In Excel go to the Tools menu select Add ins de select MasterMineLib_v2 and hit OK Then exit Excel To 21 remove the MasterMine add in entirely locate the Library sub folder within the Microsoft Office application tree typically c program filesiMicrosoft Office Officexx Library and delete the file MasterMineLib_v2 xla In some versions of MasterMine there may also be an add in called MMUpdater Remove it the same way you removed MasterMineLib_v2 xla To remove the MasterMine toolbar from Excel go to the Tools menu select Customize and find MasterMine on the Toolbars tab Select it hit the Delete button and Close Uninstall will not remove xls files initiated and saved from MasterMine templ
33. ca eed text is efe Rtas e e e detta 63 ADD TOGOEDMINE TASKBAR 5 5 vacate esee eae leh e Rs eee gene se Ee e ER Ev NE PER YVES VR ag TET 63 SNAPSHOT AARCHIV Bi ne cert pete ti ties op eei estes 64 PRINESCHEDULER sti ahnen ehren Ee es epe ve elias 64 FORMATTING REPORTS 55 20 22382 Ve un eu erp oes esie pna e ESSES ona E ive aepo 67 MOVING FIELDS AROUND iain eee einsehen eR Ie x E Reed E de eese oues eee ded 67 REPORT TYPES e A ae 68 Summary REPO iii ep Ur e E RE I M oi 68 M trix ReDOTIS 2 essct te ehe de utere dee d te er A uel e rele 69 Multi dimensional Matrix ccccccccccccccccccessccccccccsvsssccescccesssssseeeeeccessssssseeeeeceesesssaeeeeeceesenssaaeeeeeeeees 69 Line Listing REPOTAS estie e d tereti de e hen e n e ret t e ee e rues 70 Drill down Report u a eee lt d bee eat e AA i Bat o Eve 71 Charts and Graphis ereng d ete du i t a bea 72 Multi table Reports and Digital Dashboards essen enne 73 Data Area Alternatives ove eee etie ee verte ide rer d eere tah rere e Ev 73 TOOLS amp SKILLS FOR FORMATTING AND ANALYZING eerte nere nennen entren 74 Combine Rollup Rolldown with Item Grouping esee 76 GROUPING FIELD ITEMS usa us he ees Sete ts 79 Grouping Techniques and Tricks esee enne eene enne nennen renes 61 PIVOT TABLE SETTINGS ans aa EU TRE esas 85 Format Options s te so E a OU e REI PR ERO QN tia 85 Grand Totals for Columns Rows Turns on off totaling for co
34. click on the header row row 1 Or from the pivot table Drilldown From PivotTable to Line Listing sheet click on the information button and go to the Do not format data or headers on Drilldown sheet Drilldown From Excel to GoldMine Drilldown tab Check Do not format data or headers on Drilldown sheet to ignore field or field item substitutions as well Show sample of source row fields inside GoldMine as field item formatting e g formatting of numeric Open new window in GoldMine For Found record items Drill Through to Pop GoldMine Records Note Drildown preferences affect only the currently active workbook j Double click on any cel in any record on the first Drilldown worksheet to Drill down to GoldMine If the record is linked to a particular GoldMine contact 90 that record will pop in GoldMine That is control switches to your active GoldMine application and MasterMine queries the record by account number Excel remains active in the background and you can safely switch back and forth between the two In the drilldown preferences mentioned above you can Show sample of source row fields inside GoldMine MasterMine displays a list of the first eight to ten fields from the drilled down row thereby giving you a reminder of key information that may help you find the related information within the GoldMine record Open new window in GoldMine for found record
35. deeper knowledge of one s own business that only the questioner has Knowledge Requirements This manual presumes that MasterMine users have at least the first level of knowledge described below Most GoldMine site managers and all GoldMine Solutions Partners should have this level of knowledge already MasterMine Software Inc provides training to achieve any desired level of user competence Some GoldMine Solutions Partners offer training for MasterMine data mining techniques as well Beginning MasterMine Users know the following e Comfortable with GoldMine usage e Know the names and functions of all key fields on the GoldMine contact screen e Familiar with lookups and customized codes for their business e Know or can learn basic Microsoft Excel functionality mouse functions manipulating data saving and retrieving spreadsheets and can do the following with MasterMine e Use pre formatted MasterMine reports to dynamically summarize and analyze sales calling faxing scheduling referral and custom profile information e Drill down to and correct data exceptions that may indicate serious business process or training issues or simply data entry errors e Manage individuals and groups by performance criteria e Manage and improve all GoldMine based business processes Intermediate MasterMine Users have the following additional knowledge e Familiar with standard Excel Pivot Table functionality and MasterMi
36. desirable If you DO want this to happen it is probably best to type in a hint to the user to the right of the Page area indicating the hidden values If you DO NOT want hidden values to affect the displayed report you can change the table attribute Subtotal hidden page items as desired e Hidden items can always be revealed by moving the field to the Row Area and viewing the dropdown list associated with it If you see unchecked field items there re checking them will return them to the report Without moving the field you can also see hidden items by double clicking on the field in the page area and viewing the highlighted items in the Hide dialog e If you have several Page area fields and are unsure whether some of them contain hidden items that might be affecting the data displayed in your report you can reveal these fields by clicking Show Page Fields with Hidden Items under the Format button on the MasterMine toolbar Date Ranges Filtering on a date range is very similar to filtering on any other Page Area item Do this by clicking on the Year Month Quarter Week or other date desired All Pending and History reports in MasterMine automatically contain the OnDate of each activity date the date when the activity is scheduled to occur or has actually occurred respectively grouped into Years Quarters Months and Weeks Selecting a date range requires of course that your report contain these fields
37. don t relate to the selected contact set It may sometimes be useful to use outer joins to view the entire schedule regardless of contact set MasterMine templates based on ContHist dbf use only outer joins by default except where one or three tables are involved To summarize Inner Join Includes only records with counterparts in both tables Outer Join Includes all records in one table and only records in the other that have a counterpart in the first Finishing Query Changes and Returning Data If you are happy with the result of your changed query you can return the data to your Pivot Table from the new query by clicking File Return Data to Microsoft Excel To cancel without returning new data click File Cancel and Return to Microsoft Excel Now from the wizard you must include any new fields in your Pivot Table by clicking Next to get to Step 3 of 4 and arranging the fields for the new table as desired Formatting After Query Modification e Your new fields will remain hidden if you do not explicitly place them in the Page Row Column or Data areas e Any changes to field names or dBase expressions invalidate previous aliases you may have assigned These fields must be re introduced to the Pivot Table active areas and given new aliases e When done click Finished to see your Pivot Table refreshed with the new data e You may wish to leave your user defined field buttons black or for consistenc
38. end none Be careful Excel has not yet worked out the details of combining autoformatting with Pivot Tables So when you select none your Pivot Table will no longer have borders We hope Microsoft will resolve this issue in the next version of Excel Charts amp Graphs One of Excel s most powerful features is its built in charting capability You can use your MasterMine reports as the basis for any chart Data must be arranged in a similar fashion when creating an Excel chatt MasterMine s toolbar Chart button in automatically selects the innermost data labels and all displayed data and turns off grand totals before starting the Excel Chart feature It s up to you to set the parameters for your chart just as you would with anExcel chart Automatic Refresh Setting a refresh time on your live reports gives you current information at any moment on key business processes without intervention on your part Among the many formatting features available on your Pivot Table Options panel right click on the table and select Table Options is Refresh every __ minutes on the bottom left This allows you to turn your dynamic report into a real time display Anyone familiar with 117 Microsoft s Digital Dashboard will appreciate how easy this makes building your own dashboard element Data Integrity Testing Finding Out liers One of the most powerful uses of MasterMine is finding data that don
39. have been in this field in your GoldMine database any time this report has been refreshed Uncheck any items that you would like to hide from your report This filtering effect follows the field from then on even if you drag the field to another area unless you check these hidden items on again It only stops filtering on the data shown if you drag the field off the pivot table entirely to the Pivot Table Field List Effects of Hiding Field Items Having hidden items in your fields can have several important implications e Hiding field items reduces counts and sums in the data area by the amounts associated with these items When you use the same report later your report may be misleading unless you account for your missing hidden items In addition to sheer long run convenience this is why we recommend using the grouping method of filtering described above wherever possible 75 e HINT New users tend to overuse this feature It can be very time consuming to check or uncheck large numbers of field items It is easier to create a group of items that you want to display or of the ones you want to hide Then you can simply turn the group on or off as desired either in the Row or Column area or even better in the Page Area Read more about data grouping starting on page 79 e If you move a field containing hidden items up to the Page area the missing items will still affect the displayed data This may or may not be
40. in the current GoldMine contact set Manipulate Table Layout 15 16 17 18 19 20 21 To view the data for a single user click the down arrow list box to the right of USERID and select that user You can do this for any of the criteria shown on the screen or any combination of them Try this Show the potential and probable sales for prospects from one source like Trade Shows for a given product and a particular salesperson If you keep additional prospect group information in indexed key fields on the main GoldMine screen the same information will appear here and be used to select criteria for your table Now change all page fields except Linked to Contact back to All in order to include all data Note that MasterMine automatically groups the OnDate field into years months and weeks the buttons OnYear OnMonth and OnWeek These buttons can be treated like any other field button by dragging and dropping them into the Row Column or Page areas Drag the field buttons OnMonth and OnYear up to the Page Area drop them and replace them with USERID by dragging the button down to the Row Area Watch the mouse icon to be sure you drop these buttons into the intended areas Now you see all potential sales broken out by user By the OnYear button select 2000 to see only sales projected for the year 2000 broken out by user presumably sales representative If
41. in the row atea at once by clicking Hide Row Field Subtotals under the Format menu on the MasterMine toolbar A typical line listing report appears below This report has five fields in the Row Area with all subtotals turned off except for the first row field and no column area fields Note that the right most column is really the Data Area of the report containing all 1 s because our report is at such a level of detail that each row of the underlying query appears as a unique row in the report Stage TAT y 11 Territor 12 13 C1AccountNo 14 Contact Type w Contact_______ w Company__________ v Industy fwjSource Taal 16 Ida Yates Jensen amp Sons Pharmaceuticals Word Of Mouth 1 17 18 Total 3 19 Suspect Connective Automation Engineering Trade Show 120 n Elite Systems Engineering 21 Brown Investment Systems fest Import 22 Praxis Consulting Inc Aviation Word Of Mouth 123 i Sandor Assembl Media Trade Show 24 Pharmaceuticals 25 Advanced Product Solutions Media Magazine Ad 26 i Collegiate Systems Aviation Customer Referral 27 Fenix Associates Aviation Web Import 28 29 Remton Inc Customer Referral 30 i Bluefield Banding Engineering Radio Advertising 31 Complete Solutions Engineering Radio Advertising 32 Generic Brands 33 i Kalvert amp Sons 34 Suspect Total 35 Prospect Annette Ambrose Blackhaw
42. latter are often inadequate with regard to information about Pivot Tables but you can supplement your knowledge by using Excel s built in Help screens Latest Release When significant additions or changes to this manual are made the latest release will be posted for free download at MasterMine s website www mastermine net Likewise current upgrades product fixes and enhancements can be retrieved at any time from our website Introduction Is MasterMine Right for You What MasterMine can do for you MasterMine data analysis and reporting tools are for users and managers of enterprises using GoldMine as their Customer Relationship Management CRM system Your GoldMine data is a mother lode of up to the minute information about your business its internal processes and its future prospects MasterMine tools allow you to easily and flexibly extract organize and analyze that information to vastly increase the value your company gets out of GoldMine Following is a brief explanation of some ways you can use MasterMine to help master every aspect of your business Any organization that tracks its interactions with a large number of outside contacts in short virtually any organization that uses GoldMine can benefit by analyzing the flow of activity in relation to those contacts with MasterMine Like GoldMine itself much of the functionality in MasterMine anticipates uses in a sales environment But remember that you can
43. lists can be created or pending activities scheduled for users by creating GoldMine groups e GoldMine s Export feature can be used with GoldMine Groups to export a targeted segment of records for say mailing or transferring to another database See the GoldMine user manual for help with these functions Note also that MasterMine reports can be based on either a GoldMine Filter or a GoldMine Group so you can use this feature to make other MasterMine reports easier to produce 91 Within GoldMine creating Groups requires either some technical knowledge the logic of building a SQL query or a great deal of patience clicking on individual records and adding them one by one to an existing group MasterMine vastly increases the power of these functions by allowing you in seconds to define a complicated target set of account records using the slicine and dicing capabilities of the pivot table then drill down to a line listing of the records from which you can pick a subset or send the entire set of records to be grouped Group Creation In your pivot table report select items in the Page area and include fields in the Row or Column areas to define segments graphically By showing the number of records that meet your criteria or many potential sets of criteria this replaces the work you would normally have to do creating many complex SQL queries to isolate your target records From there drill down to a line listing page showin
44. not update GM data if source cell is blank Empty target field For ALL GoldMine records before starting Delete records from GoldMine v Test Run do not update GoldMine OK Cancel ot RecID fields Update existing data in this GoldMine table Select the target GoldMine table to update Tables are labeled with both a user friendly name and the technical database table name Use this MasterMine column to match AccountNo or RecordID of target records A MasterMine report field containing the normally used match field for the selected table will be displayed in the second field In most cases the default selection here is the correct one to use when MMUpdater looks up your target record in GoldMine For the Contactl table the AccountNo is used For any other table a RecID is required MasterMine labels RecID fields as CXRecords where CX corresponds to the key table in the query behind the MasterMine report you are using In some rare instances you may have a variety of match fields to choose from if your MasterMine report includes multiple AccountNo 93 Replace the contents of this GoldMine field Select the target field in GoldMine to be updated The list of fields here is all fields in the table you selected above showing both user friendly labels and database field names with the contents of this MasterMine column Defaults to the name of the wotksheet column containing your highlighted data You may selec
45. once on a field item then move the mouse to the outer edge of the to same cell until the mouse icon changes to a 4 way arrow R Then click and drag the item to a new location anywhere within the same field The new order of items with the field will remain even if the field is dragged out of the pivot table Sort Ascending or Descending To change any field to another type of sorting double click on the field button itself click Advanced and see the sort settings on Manual you can drag items to rearrange them the left side of the dialog Ascending Descending Using field Select Ascending or Descending to cause the field to automatically sort all field items within it alphabetically The default medium of sorting is the contents of the field itself Contact Type You may also choose to sort by the values in the data area corresponding to each field item Thus for instance selecting Descending and then in the Using field list box below this selecting a field in the Data area to sort by will result in a report showing the number of occurrences of each item within the database in descending order When refreshing your report causes new field items to appear within the field these new items will appear in the field at a location determined by the type of sorting that is in effect for the field If the field is sorted Ascending or Descending new items will automatically sort into t
46. permanently exclude other types for instance if you Report Name Pending Activities Al want to create a calling report and know you will SaveTo Path C MasteMineRepots SCS esee Repot l not be using this report to compare calling and Save To Template Filter Finish Cancel appointments and other possible contacts with your prospects or customers Details sometimes referred to as Profiles If you select Details the right side of the MasterMine dialog displays a checklist of the user defined Profiles set up in your GoldMine This is the same list you should see when you right click in your Details tab in GoldMine select New and view the Lookups for the Detail field 34 El Create New MasterMine Workbook 3 Typically you will be reporting on one type of File E i Detail Profile rather than many so check the box next to the detail type you wish If only a single Which part of GoldMine s information do you want to see C Contacts Design New C Use Template Profile is checked the fields list below will reflect Main all the user defined field labels from the GoldMine er All except E mail amp Websites Additional ee setup for that Detail Profile C Activities Addons Sold 1 H Credit Card 5 BE dd CRM Used If you don t know how Details and particularly istory CRMs Sold user defined Profiles relate to the rest of Details Download D e P Dates E mail A
47. put on every report you create Not only will this protect your report from being overwritten by the next similar report that is created it also makes it much easier to reuse and modify useful reports Choosing a report name may seem trivial but you will find that if your name accurately but specifically reflects either the content of the data included i e Completed Calls for Prospects with Qualifying Info or the specific use to which it 44 will be put i e Friday Calls and Appointments Report you will be able to re use and branch off of your earlier work much more often Save To The default SaveTo location is stored in MasterMine preferences For dBase users the SaveTo path is automatically a MasterMine subfolder of the contact set to which they refer However if you wish to store them elsewhere you may specify another location in the Save To list box or use the Browse button Be aware that reports stored outside the default SaveTo folder will not appear when you use Open MasterMine Report from the GoldMine menu Limiting Record Selection or Filtering In addition to shaping your query at the field level you may also want to limit the quantity or type of records table rows included Although it is frequently easiest to simply query in a mass of data and set your filters within the report itself where they can be changed later as needed sometimes it helps to pre filter y
48. realize that seeing notes is very easy to do by drilling through a MasterMine report to pop the relevant GoldMine record If possible include notes only if you know you will need to see them in your report HTML code in Notes fields GoldMine includes HTML formatting codes in notes fields in its default setup SOL versions of MasterMine since 5 1 have automatically included an HTML filter to prevent these codes from appearing in reports This filter is a user defined SQL function of the form udfDBO udf MMStripHTML CS NOTES For users of dBase based GoldMine Standard edition no such filter is possible However we recommend turning off the HTML feature in GoldMine for any notes field you intend to use in reports Remember there are notes fields in several different tables Contactl ContHist Cal and ContSupp 153 To turn off HTML coding and thereby leave only text in your notes fields add the following lines as appropriate to your GM INI file in the GoldMine section HTML_CAL Notes 0 HTML_CONTHIST_Notes 0 HTML_CONTACT1_Notes 0 HTML_CONTSUPP_Notes 0 This will disable HTML notes for whichever tables you include in the list you need not do all four Remember this will affect only newly saved notes entered after you make this change When finished editing GM INI log into GoldMine again All future notes you create will be without HTML 154 Appendix F Special Fields In addition to fields loaded wi
49. report to correlate filter or sort on sales figures together with other demographic data A final example is a common real life GoldMine need updating codes in data to match a new coding schema for pending or completed activities Say your organization sets up new tules for coding completed calls including a new set of Activity Codes and Result Codes Does your existing data simply go to waste because records did not conform to the new way of doing things This could cause you to lose valuable historical information about the activities recorded in your database 95 With MMUpdater starting with the pivot table itself you can conveniently group or re group your old codes under labels corresponding to your new codes Some other information i e from the Reference or some Contact field may be needed in the pivot table to subdivide the data along the new lines When you have grouped and filtered in your pivot table as necessary to get your data appropriately re grouped drill down and use MMUpdater to mass change the old historical records to the new coding Now you can apply current metrics that go back in time beyond when you actually started using the codes possibly even to the earliest time your company used GoldMine 96 97 Deploying Sharing and Managing Reports Sharing reports is a vital part of any reporting solution Even a single MasterMine user needs to deploy frequently used reports for easy recall rather tha
50. scratch Sharing Reports Each MasterMine report contains a connection string pointing to the original source of the data This means that MasterMine reports can be stored anywhere albeit only refreshed when the data is available along the same path used to create the report The most common way to share MasterMine reports is to store them on the same network location or at least the same folder tree for all users The SaveAs location in your MasterMine preferences determines the default location of MasterMine reports The next most common is to use the Deploy button on the MasterMine toolbar The process is very simple With the report already created and formatted as desired click the button on the MasterMine toolbar in Excel and answer the prompts in the dialog See the full description of this dialog under Chapter 6 The MasterMine Toolbar on page 63 In GoldMine Corporate Edition version 7 0 and lower the GoldMine taskbar is built to mimic a taskbar in Microsoft Outlook If your taskbars are turned off you can activate them by going to the Window menu and clicking Taskbar You may also distribute reports by attaching them like any other Excel document to outgoing e mails Be aware though that recipients must be licensed MasterMine users or they will not be able to open the file Reports may also be distributed via GoldMine synchronization To do this attach a report as a Linked Document
51. the other users in the list Note that there are subtotals for each of the groups created Now you can treat the new grouped field button the same as any other field in the Pivot Table Click on the USERID2 button and type a new name such as Sales Groups Drag the USERID field up to the page atea to see your sales broken out by sales group Switch the USERID and Sales Groups fields then select a single group in Sales Groups to limit your table to one group or another Save Useful Report to New Sheet 24 Suppose you wish to use your sales forecast by sales group on an ongoing basis and you don t want to re create it from scratch every time Save this worksheet under a separate name so that you can continue to tweak the original sheet while preserving your work on this form Simply click the copy sheet button on the MasterMine toolbar to create a duplicate sheet Right click the tab at the bottom of the new sheet and select rename When the name Original 2 is highlighted type a new name for the tab such as Group Sales Forecast Then return to the Original tab to continue developing variations Save Variations at the Workbook Level 25 You may want to save various versions as separate workbooks especially if different users will be using these reports If so use the normal Save As function of Excel to save the report in vatious formats These can easily be shared with other licensed MasterM
52. to a report designer or a wizard In this chapter we ll give you the basics for organizing data on the page and an introduction to some tools added by MasterMine to the Excel interface that make using pivot tables together with GoldMine data and built in features a powerful combination Moving Fields Around A drag and drop involves simply left clicking on the field you wish to move and holding the clicker button down while you move the mouse to the target area for the field then dropping the field by releasing the clicker As you drag a field in a pivot table notice how the mouse icon changes to a schematic of the pivot table itself with the highlighted area reflecting the area of the pivot table where your mouse is hovering Letting go of the clicker will drop the field into the pivot table area corresponding to the highlighted area in the mouse icon The rest of this manual assumes you know the names of the areas in a pivot table If you are not familiar with the areas of a pivot table and how they relate to data layout please refer to Pivot Table Elements on page 53 In this example the Company field is being dragged and is hovering over the Page area of the pivot table Therefore the upper part of the mouse icon is highlighted in blue Notice also that a fuzzy line appears between the 57 4 67 Suspect State and Source fields indicating that the Company field would be loca
53. to improve management productivity and effectiveness in a variety of areas data mining simple daily reporting and data integrity testing You may find that your own best uses ate not even on this list Keep your eyes and mind open to new opportunities to learn about your business at all times Introduction to Data Mining Data Mining is a relatively new concept for most business managers but a very powerful one Simply put Data Mining is about knowledgeably digging through and arranging your data to elicit information about every aspect of your organization MasterMine gives you a tool to do just that Data Mining asks the same questions you ve been asking all along How are we doing What are we doing well Where ate the hidden weaknesses in our business processes What parts of our processes should be getting priority attention How can we use past experience to anticipate the future How can we do mote of what has worked in the past and stop doing what does not work Keep these questions in mind as you creatively explore the views and insights MasterMine gives you about your data Start with the familiar a sales projection report or calls analysis report for instance and see how you can slightly alter your viewpoint to identify a new opportunity or anticipate a problem Suddenly you ll realize that up until now you ve been flying blind and the static management reports you had been using seem woefully inadequate to your most imp
54. use MasterMine to create meaningful analyses for every part of your business that GoldMine touches Who should use MasterMine Every manager MasterMine software is designed for anyone managing GoldMine users Another way to put it is Who needs reports Answer Anyone who is responsible for the quality and quantity of workflows that are reflected in GoldMine data should have a licensed copy of MasterMine available Anyone responsible for producing measured output GoldMine is a workflow management tool and that makes MasterMine a tool for anyone using GoldMine to increase the efficiency of their daily activity For example sales representatives responsible for making a certain number of calls or appointments can record their results in GoldMine and can measure their success against standards using MasterMine Managers with MasterMine can measure and reward or punish ordinary users can do better by self managing to expectations because they know how they are doing at every step of the way Anyone needing questions answered Is MasterMine only for doing sophisticated analysis Hardly Anyone who has asked for information from GoldMine and waited hours days or weeks while someone else builds a report NEEDS MasterMine to get their own quick answer Yes sophisticated reports are possible but they are not the only reason to use MasterMine The person who asks questions knows what he or she needs for an answer and often this requires
55. way without clearly knowing what you want to achieve but knowing that it ought to reveal something together Suddenly you make an important discovery after the report is done As you ate exploring be sure to periodically save your workbook and copy the current sheet for later use This is very easy with the Copy Sheet to End button 42 on your MasterMine toolbar Itgives you a snapshot of your current report allowing you to continue trying alternatives You can collect the new reports thus generated into an aptly 115 named workbook or move them to another existing workbook by right clicking on the tab and selecting Move or Copy Finally don t forget to add charts and graphs to your presentations as these will often give you insight where mere numbers on the page can t Don t assume that the purpose of a chart is always to make a grand presentation to someone else Sometimes it can provide you with a key to understanding you would have otherwise missed Dynamic Management Reporting MasterMine is to management reports what the internet is to television You actively ask questions and probe some say surf your data rather than simply accept what s there By adding interactivity MasterMine can replace much of your static management reporting Apart from the sheer fun of noodling in your data you should aim to create a series of dynamic management reports that give you a comprehensive view of the unique key
56. while the report is open by checking the appropriate boxes in the 100 Pivot Table Options dialog Find this dialog by right clicking anywhere on the pivot table and selecting Table Options from the short menu When you save a formatted report be sure to change the name to reflect more closely what you use it for This way it won t be accidentally overwritten when you create a new report of the same type Copy and Send Reports Outside of MasterMine Sometimes you need to share the results of a MasterMine report with a non MasterMine or even non GoldMine user The easiest way to do this is to use the snapshot feature on the MasterMine toolbar to generate a non MasterMine workbook containing the report The snapshot can be attached to an e mail or copied to another user the same as any Excel workbook 101 102 103 Modify Underlying Query Once you have created a MasterMine report you ate typically finished with SQL expressions for the purposes of that report The rest of your slicing and dicing is handled graphically within the pivot table With a well chosen base query using the pivot table functions gives you many of the same results formerly available only to those who could build a sophisticated SQL query With this in mind there are often cases where a report could be made mote efficient or updated or easily improved by modifying its underlying query Rather than go back to the report c
57. within the Excel workbook so security conscious users may wish to avoide saving the SQL password m Snapshot Archive Take a snapshot of the current worksheet with all pivot table attributes frozen This is a great way to create an archived version of the worksheet within the same workbook You may also use this to export the current sheet to a new workbook that can be transferred to non MasterMine users Print Scheduler Schedule a report to automatically print while unattended On a pre set schedule using Windows Scheduler the report automatically launches refreshes and prints to the designated network or local printer Enter Print Schedule For Mastermine Report Reports may be scheduled to run once weekly or monthly Schedule the current worksheet or the whole Weekly EAN workbook to print on a regular basis C Monthly Monday Thursday Saturday Tuesday Friday Sunday Wednesday Print Current Worksheet Option C Entire Workbook Copies Printer Schedule Task Cancel 64 65 66 18 Customer 19 Past Client Grand Total Formatting Reports Once the MasterMine Create Report dialog has been used to extract your GoldMine information into Excel the genius of working with data in a pivot table becomes immediately apparent You can now shape and organize your data on the screen with immediate results without the usual procedure of going back and forth
58. you use the reference field to indicate products drag that field down to the row area and drop it next to the USERID field button Before you drop it watch the mouse icon carefully to ensure that it is in the row atea and see how the shaded border between cells indicates which rank in the row area will receive the field If you dropped to the left of USERID the table will show sales counts by product by user If you dropped to the right of the USERID the table will show sales counts by user by product These are very different types of information both potentially very useful and easy to create from the other Create New Data Groupings 124 22 Now return Reference to the Page Area leaving USERID as the only Row field 23 Most companies group their sales people by function by territory or by product category If your users fall into such groups you can group them accordingly and use these groupings as items in yet another field button First arrange the users in order by selecting and dragging them into the order you desire within the row atea Then drag select the top group of users When they are highlighted click the Group button gt on the MasterMine toolbar This creates a new field button named USERID2 which includes an item called Group 1 instead of the users selected in USERID Click on Group 1 once and type a name for the group that reflects the group s function Similarly select and group
59. 000 2 2 00 3 680 00 0 20 0 20 31 3 3 00 2 590 00 0 35 0 35 32 2 2 00 5 180 00 0 20 0 20 EN 1 1 00 0 00 0 00 0 00 193 230 00 281 599 08 27 90 37 00 35 PEI Original IERI E Nxo00g4D gt 4 4 8 Ready Ma What the Report Says 12 The report currently shows pending sales broken out by year and month in the following columns e CARecords the count of pending sale records in the Calendar or CA table e Potential Units a sum of all units for pending sales e Potential Sales Vol a sum of all projected dollar values of sales pending e Probability CARecords adjusted for probability per sales records a sale with 70 probability is counted as 7 of a sale and summed accordingly e Probable Units Sum of units adjusted for probability A 3 unit sale with 70 probability counts here as 2 1 units 123 13 14 e Probable Sales Vol Sum of dollar values adjusted for probability A 500 sale with 709 o probability counts here as 350 The fields in column A stacked above the table itself are Page fields available for dragging into the table itself or by selecting elements in their list boxes for setting parameters for the report Blue fields are Contact fields from the Contact table in GoldMine green fields are Calendar fields from the Cal table in GoldMine Because the parameter Linked to Contact is set to Yes our report includes onl P P y pending sales that are linked to a contact
60. 1C2 Weekly Activities by User Analysis of completed activities by user in selected week CHC1C2 Analysis of records newly added to the database within the New Prospects past 365 days C1C2 All prospects by how recently contacted linked with full contact info Last Contact is most recent call appointment or e mail successfully completed See GM Help for Last Contact On definition Use reports in Activity Analysis toolkit for finer Most Recent Contact distinctions of what constitutes contact C1C2 Prospects not contacted in the past 90 days e g no successful call appointment or e mail See GM help for Last Contact On definition Use reports in Activity Analysis toolkit for finer Last Contact Over 90 Days distinctions of what constitutes contact C1C2 132 Workbook Name Description GM Tables Analysis of all prospects by how recently an attempt was made to contact including GM phone dial See definition of Last Most Recent Attempt Attempt in GM help C1C2 Referrals Analysis of client referrals by quantity and quality CSC1RFCFC2 Analysis of pending sales linked to the original referring client Referral Sales Pending including probability schedule summaries CSC1CARF Referral Sales Completed Analysis of closed sales linked to the original referring client CSC1CHRF Analysis of pending sales by client creation date including Pending Sales by Original Entry probabili
61. 4 Profiles that are defined in Details Plus will have D appended at the end of the name For these details the fields list below will show the field labels defined in DetailsPlus rather than the generic fieldnames like CS CSAddress1 etc or the user defined fieldnames from GoldMine s own setup These fields will also reflect the proper field types Character Date Numeric defined in DetailsPlus Opportunities Projects and Cases If you have these options turned on in preferences they are available here on the front of the create dialog MasterMine produces a simple default report for each of these if selected under Design New but the majority of reports for these areas of GoldMine are launched using the Use Template option described in the next section Click Finish when ready Having selected your target GoldMine area you could just hit Finish now to create a workbook of GoldMine accounts information This 35 workbook alone could provide you with a multitude of views into your GoldMine database However there are many further refinements available to you Technical Point Table Joins Those familiar with Transact SQL may want to know how MasterMine includes and joins tables For most reports MasterMine queries an Outer join all activities plus their associated contact information if you include only data from the activity table and Contactl If three tables are inv
62. 6 Edward Castelle Uptown Systems NC Customer Referral Suspect Food Bill SE 4 19 2005 A0012841532 IDLR7 B edward uptowt 17 Bryan Carlisle _BAX Global GA Radio Advertising Suspect Food Laura SE 7 19 2006 97061337523773096Bre___C 18 Tyler Morris Ill Tyler Morris Company TN Trade Show Suspect Media Bill SE 2 22 2006 AD022423256 amp SZV Iss C 19 Barry Bellamtin CBS GA Customer Referral Suspect Media Gertrude SE 3 4 2005 ADDU105283248F 18 B barry tcbs cor 20 Roger Williams IMAC Industries FL Customer Referral Suspect Media Pete SE 7 13 2004 98071549217 KGA _Rog A roger imac co 21 Marilyn Davidson AFSM Inc FL Customer Referral Suspect Media Gertrude SE 6 27 2005 99062930828 R_ e mdavidson afs 22 Venus Markelis Menda Associates GA Radio Advertising Suspect Media Bill SE 9 10 2005 96121604685070566Var B 23 Madison Frank KMS Inc NC Customer Referral Prospect Media Laura SE 9 18 2005 99092056194 1A lt VCar_ A madison frank 24 Monique Kardu Tradewinds Solutions FL Magazine Ad Prospect Media Bill SE 1 6 2006 98072229404 J 7G Mad B nique attgloba 25 Judith Martin Barters amp Baubles FL Web Import Past Client Media Bill SE 7 3 2005 96100834813425188Joh B _judith b b con 26 Kevin Kaiser Downtown Market Makers SC Other Pharmaceuticals Laura SE 5 29 2006 AD020731174 89 1 B kmk mindsprir 27 Jeffrey Garrett Commercial Partners TN Yellow Pages Pharmaceuticals Pete SE 4 27 2007 ADO12450054 WKQ7 C jga
63. 6 C2 USERDEF06 C2 USERDEF06 C2 USERDEFO7 C2 USERDEFO7 C2 USERDEFO7 C2 USERDEFO8 C2 USERDEFO8 C2 USERDEF08 C2 USERDEFO9 C2 USERDEFO9 C2 USERDEFO9 C2 USERDEF10 C2 USERDEF10 C2 USERDEF10 iif C2 CALLBACKON is null C2 Call Back On Coalesce C2 CALLBACKON NullDate NullDate C2 CallBackOn C2 Call Back Freq C2 CALLBKFREQ C2 CALLBKFREQ Coalesce C2 LASTCONTON NullDate iif C2 _LASTCONTON is C2 LastContactOn null NullDate C2 LASTCONTOn iif C2 LASTATMPON is null C2 Last Attempt On Coalesce C2 LASTATMPON NullDate NullDate C2 LASTATMPOn Coalesce C2 MEETDATEON NullDate iif C2 MEETDATEON is null C2 Meeting On NullDate C2 MEETDATEOn 140 Source MM Field Name SQLExpression dBase Expression C2 Comments C2 COMMENTS C2 COMMENTS C2 Previous Result C2 PREVRESULT C2 PREVRESULT C2 Next Action C2 NEXTACTION C2 NEXTACTION iif C2 ActionON is null NullDate C2 Action On Coalesce C2 ActionON NullDate C2 ActionOn iif C2 CLOSEDATE is null NullDate C2 Close On Coalesce C2 CLOSEDATE NullDate C2 CLOSEDATE C2 Contact2 contains your user defined fields Fields are automatically formatted depending on their field type C2 CharacterField RTrim CharacterField C2 CharacterField lif C2 NumericField is null 0 C2 NumericField Coalesce C2 NumericField 0 C2 NumericField lif C2 DateField is nu
64. Acct Last User ER CX LASTUSER CS LASTUSER iif CX LASTDATE Is Null NullDate CX LASTDATE iif C Coalesce CX LASTDATE NullDate C X LASTDATE Is CX Acct Last Modified oalesce CS LASTDATE NullDate Null NullDate CX LASTDATE CX C1AccountNo CX ACCOUNTNO CX ACCOUNTNO CX ACCOUNTNO CX ACCOUNTNO CX CXRecords CX RECID CS RECID CX RECID CS RECID 146 Source MM Field Name SQLExpression dBase Expression Opportunity Manager Sub Tables OM Opportunities Main table OD Details OK Tasks OT Team Members OP Competitors Ol Issues OC Influencers OM Opp Record Type Rtrim OM RECTYPE OM RECTYPE OM Stage Rtrim OM STAGE OM STAGE OM Manager Rtrim OM USERID OM USERID OM Opp Flags Rtrim OM FLAGS OM FLAGS OM Opp Company Rtrim OM COMPANY OM COMPANY LEFT rtrim OM CONTACT abs charindex OZ OM RECTYPE iif left OM RECTYPE 2 lt gt OZ OM Opp Contact 1 len rtrim OM CONTACT OM CONTACT left rtrim OM CONTACT charindex OZ OM RECTYPE len rtrim OM CONTACT iif let OM RECTYPE 2 OZ OM Reason Win Loss OM CONTACT OM Opportunity Rtrim OM NAME OM NAME OM Opp Status Rtrim OM STATUS OM STATUS OM Opp Code Rtrim OM CYCLE OM CYCLE OM Opp Source Rtrim OM SOURCE OM SOURCE OM Fi Rtrim OM F1 OM F1 OM F2 Rtrim OM F2 OM F2 OM F3 Rtrim OM F3 OM F3
65. Any and All function the ance same when only one element is sought Case Sensitive Search fa Wildcard substitute for single character Enter elements being sought separated by commas If a comma is part of the search term replace the comma in the box above the elements to indicate a different multi element delimiter Even if you are searching for only one element but that element contains or is a comma change the delimiter to some other value such as amp Check Case Sensitive Search to include only items that match precisely the case of the elements being sought Check Begins with to include only items that begin with the elements being sought If None is selected above only items that do NOT begin with the sought elements will be included You may enter any group name to assign to the group after it is created If a single element is being sought the default name is the element MasterMine searches through all the items in the selected field groups the ones containing the target values under NewGroup and moves this new group to the top of the list Now you can use NewGtoup or whatever name you choose as a selection criterion in the page area You can also leave it in the display area and double click on it to roll up or roll down detail Group On Content Search can find and isolate groups that contain any all or none of the elements you list in the Enter elemen
66. Appendix E of this user manual 89 Data Management MasterMine can not only easily creates traditional style reports from your GoldMine data but also can interacts with reports and with the data itself including directly changing and reorganizing the data within GoldMine Here are a few of the key techniques Drill Down Double click on any numeric or date item in the Data area that interests you MasterMine creates a new worksheet giving a line item view of all underlying data for the item selected on the PivotTable The column heads are your fieldnames as they appear in the pivot table version of your MasterMine report The drill down screen itself can be viewed as an instant report as in Give me a list of Columns appear in the order they are listed in the underlying query You can control this order either by sorting the fields at the time you create the report or by modifying the underlying query using MSQuery as described in a later chapter If you have substituted new labels for any of the fieldnames or for field items the substitute name will appear in the drilldown sheet by default This feature can be turned off so that you see the raw data instead MasterMine Pivot Table Control Stats Drilldown Connection Query Text Security About You have several options regarding how to display data in a drilldown sheet which you can control in one of two ways From inside a drilldown sheet double
67. COUNTNO OC ACCOUNTNO OC InfluencerRecords OC RECID OC RECID 149 Source MM Field Name SQLExpression dBase Expression Project Manager Sub Tables PJ Projects PK Tasks PD Details PF Contacts PT Team Pl Issues PC Influencers PJ Project Record Type PJ RECTYPE PJ RECTYPE PJ Project Rtrim PJ NAME PJ NAME PJ Proj Company Rtrim PJ COMPANY PJ COMPANY PJ Proj Contact Rtrim PJ CONTACT PJ CONTACT PJ Manager Rtrim PJ USERID PJ USERID PJ Proj Code Rtrim PJ CYCLE PJ CYCLE PJ Proj Status Rtrim PJ STATUS PJ STATUS PJ Proj Stage Rtrim PJ STAGE PJ STAGE PJ Proj Source Rtrim PJ SOURCE PJ SOURCE PJ Start Date coalesce PJ STARTDATE NullDate PJ STARTDATE PJ End Date coalesce PJ CLOSEBY NullDate PJ CLOSEBY PJ Actual End coalesce PJ CLOSEDDATE NullDate PJ CLOSEDDATE datediff day Coalesce PJ STARTDATE PJ Age getdate getdate date PJ STARTDATE DaysFromStartToAc datediff day PU STARTDATE coalesce P PJ tualEnd J Closeddate getdate PJ CLOSEDDATE PJ STARTDATE DaysFromTodayUnti datediff day getdate Coalesce PJ CLO PJ IEndDate SEBY getdate PJ Closeby date PJ Proj Notes udfDBO udfMMStripHTML PJ NOTES PJ NOTES substring udfDBO udfMMStripHTML PJ PJ Proj Notes2 NOTES 254 253 mid PJ NOTES 254 substring udfDBO udfMMStripHTML PJ
68. CX Address SS1 CX ADDRESS1 CS ADDRESS1 rtrim CX ADDRESS1 rtrim Coalesce nu CX Address1 iif CS ADDRESS1 is Null CX Address Filled llif CS ADDRESS1 CX ADDRESS1 CX ADDRESS1 CS ADDRESS1 rtrim CX ADDRESS2 rtrim CS ADDRE CX Address2 SS2 CX ADDRESS2 CS ADDRESS2 rtrim CX ADDRESS2 rtrim coalesce su bstring CS ADDRESS32 1 datalength CS ADDRESS24CS ADDRESS 1 space as cii CS ADDRESS1 ascii CS ADDRESS CX Adadress2 if CS ADDRESS is Null CX Address Filled 1 CX ADDRESS2 CX ADDRESS2 CS ADDRESS2 rtrim CX ADDRESS3 rtrim CS ADDRE CX Address3 SS3 CX ADDRESS3 CS ADDRESS3 rtrim CX ADDRESS3 rtrim coalesce su bstring CS ADDRESSS3 1 datalength CS ADDRESS34CS ADDRESS 1 space as cii CS ADDRESS1 asci CS ADDRESS CX Address3 if CS ADDRESS is Null CX Address3 Filled 1 CX ADDRESS3 CX ADDRESS3 CS ADDRESS3 rtrim CX CITY rtrim Coalesce nullif CS CX CITY IIF CS CITY is null CX CITY CX City City CX City CS CITY 145 Source MM Field Name SQLExpression dBase Expression rtrim CX STATE rtrim Coalesce nullif C CX STATE IIF CS STATE is null CX State S State CX State CX STATE CS STATE rtrim CX ZIP rtrim Coalesce nullif CS ZI CX ZIP IIF CS ZIP is null CX ZIP CX Zip P CX ZIP CS ZIP substring CX ZIP 1 3 substring Coalesc e nullif rtrim CS ZIP left CX ZIP 3 eft iif CS ZIP is null C
69. Cancel Note that you can use MasterMine to quickly and easily build GoldMine Groups See Create and Use GoldMine Groups page 91 These groups can in turn be used to filter a new report This circular technique is often used to methodically hone a report down to a set of records that meet many different kinds of criteria without needing to build a single filter or query that logically includes all the criteria Filter on Key Values Allows you to select a section of GoldMine data according to the contents of the key fields from GoldMine i e those fields in the lower right quadrant of the standard record screen In most cases these fields represent key points of information about your clients and prospects and therefore allow you to include only major segments of your account database For example if you have a Contact Type field that includes lookup items Clients Prospects and Employees you might use Key filtering to set up your report to exclude Employees since they will never by required for the report By default all potential values are included Build your filter the most efficient way either by eliminating a few of the checked boxes to exclude only a few items or by 46 switching off whole fields and then re including one or a few items Hit the Count button to see immediately how many records match your criteria Pre counting is only possible for relatively simple queries
70. ContHist is parsed into three fields for easier interpreting The first character or SRECTYPE if present denotes the general Record Type The left two characters denote a more detailed categorization so the button is called Record Type Detail The third character of RECTYPE U if unsuccessful is interpreted in the Success Field button See the GoldMine Reference Manual for database structures and internal logic e MasterMine provides a Short Reference field consisting of the first n characters of the REF field in Cal and ContHist where n is a preference set in the MMINI xls This assists efficient loading since the REF fields in these files are 80 and 65 characters long respectively If you need the entire REF field select it specifically on the Fields tab in Expert Mode when creating a report e The REF field in History is stripped of the oc contact reference This reference appears separately in the CH Contact field e Sales projection reports include calculated fields for probable sales That is projected sales counts and dollar amounts are multiplied by the probability taken from the NUMBER1 field in Cal e The Company field in the CAL database contains either contact or company information according to settings in GoldMine It s best to rely on a report that joins Cal and Contactl and use C1 Company for true Company information e Field bu
71. Display has six tabs to present different aspects of the pivot table Statistics In addition to information about the pivot table overall Stats includes backeround on the selected pivot field Again this helps a user understand the source of data in the report Here the user can determine the type of underlying data usually SQL or dBase the date the query was last refreshed against the GoldMine data the number of records in the query and memory size of the query The user can also see how many data caches are contained in the workbook and which one is being used by the current pivot table This can be significant if there are memory issues or if the report is a fairly complex one containing many pivot tables with MasterMine Pivot Table Control C1AccountNo Result Group Call Res Contact l OnYear v Completed INP 1 Stats Drilldown Connection Query Text Security About Selected PivotTable Data Type SQL different data sources Le Last Refresh 8 19 2008 4 00 57 PM different undetlying Records Queried 9 442 ES Query Size 1 180 KBytes queries 20 2006 CacheIndex 1 of 1 in this workbook 21 2007 Th E 22 2008 Selected PivotField ere 1s also information Grand Total about the selected field on 24 Display Name Call Result 25 Field Name Result Code the pivot table which can 26 help the user trace a value 27 Select
72. ECID CH CHRecords CH RECID CH RECID CH CHAccountNo CH ACCOUNTNO CH ACCOUNTNO iif CH Ondate is null NullDate CH OnWeek Coalesce CH OnDate NullDate CH ondate CS CSRecordType Rtrim CS RECTYPE CS RECTYPE CS Detail Type Rtrim CS CONTACT CS CONTACT CS Detail Ref Rtrim CS CONTSUPREF CS CONTSUPREF CS CSTitle Rtrim CS TITLE CS TITLE CS CSLinkAcct Rtrim CS LINKACCT CS LINKACCT CS CSCountry Rtrim CS COUNTRY CS COUNTRY CS CSZip Rtrim CS ZIP CS ZIP CS eMail Priority Substring CS ZIP 2 1 Mid CS ZIP 2 1 CS CSExtension Rtrim CS EXT CS EXT CS CSState Rtrim CS STATE CS STATE CS CSAddress1 Rtrim CS ADDRESS1 CS ADDRESS1 CS CSAddress2 Rtrim CS ADDRESS2 CS ADDRESS2 CS CSAddress3 Rtrim CS ADDRESS3 CS ADDRESS3 CS CSDear Rtrim CS DEAR CS DEAR CS CSPhone Rtrim CS PHONE CS PHONE CS CSFax Rtrim CS FAX CS FAX datevalue iif isdate format mid cs city 9 Convert datetime coalesce nullif substrin 8 g CS CITY 9 Isdate Substring CS CITY format mid CS CITY 9 8 CS CSCreateOn 9 8 8 0 NullDate NullDate CS CSCreateBy Substring CS CITY 1 8 left cs city 8 CS EmailMergeCodes Rtrim CS MERGECODES CS MERGECODES CS CSLastUser Rtrim CS LASTUSER CS LASTUSER CS CSCity Rtrim CS CITY CS CITY 144 Source MM Field Name SQLExpression dBase Expression iif CS LASTDATE is null CS CSLastModified Co
73. F 1 CH REF substring Ltrim Stuff CH REF Charindex left iif CH REF Like oc CH REF 1 200 Charindex 9e 0c 96 left CH REF instr CH REF oc CH LogUpdate oc CH REF 1 11 7 1 CH REF instr CH REF 1 142 Source MM Field Name SQLExpression dBase Expression trim mid iif CH REF Like 0c left CH REF instr CH REF oc substring CH REF 15 1 CH REF instr CH REF 3 instr 17 CH LogField Charindex stuff CH REF 1 15 CH REF instr CH REF 3 Ltrim substring Stuff CH REF Charindex oc CH REF 1 200 Charindex trim mid iif CH REF Like oc CH REF Y oc left CH REF instr CH REF oc CH LogChange Charindex Char 39 CH REF 25 1 CH REF instr 17 CH REF 2 Replicate sign Charindex oc CH REF iif CH REF Like Substring CH REF Charindex 0c left trim mid CH REF inst oc CH REF 5 50 Charindex r CH REF oc 4 len trim mid CH RE CH CH Contact oc CH REF F instr CH REF oc 4 1 isnull nullif rtrim Replicate sign Charin dex oc CH REF Substring CH REF iif CH REF Like Charindex 00 left trim mid CH REF instr oc CH REF 5 50 Charindex CH REF 0c 4 len trim mid CH REF oc CH REF rtrim C1 Contact instr CH REF oc 4 CH CH or C1Contact 1 trim C1 Contact CH
74. G OTHER FIELD CONTENTS AND CONVENTIONS uuuunessnessesnersnersnersnennnennnennnennnn nn 157 APPENDIX H PIVOT TABLE LIMITATIONS ooooconcconononcnnnconoconocancnnnonnnonnnonnncnn non ene nnne erret rennen 159 APPENDIX I LIMITATIONS WITH MICROSOFT OFFICE 97 sees 162 INDEX Co 164 viii FOREWORD This manual is designed to give you a quick introduction to using MasterMine for GoldMine MasterMine itself is designed to be a user friendly low skill entryway to your GoldMine data so this manual is meant to assist both novice Excel users who have no desire to develop deep skills as well as those who wish to become true experts The basic functionality everyone must learn to make use of MasterMine should not take more than an hour For those who wish to dig deeper this manual goes on to discuss important and useful concepts for GoldMine managers such as data mining GoldMine database structures and Microsoft Excel spreadsheets and Pivot Tables We hope our explanations are clear and understandable enough to make MasterMine use easy productive and fun even for the most inexperienced Data Miner However this manual by itself cannot provide a thorough knowledge of any of these important management skills We recommend that users seek further information in a variety of resources including online training books on data mining itself the GoldMine Reference Manual and the many off the shelf books on Microsoft Excel The
75. MASTERMINE SOFTWARE INC Business Analysis and Reporting for GoldMine MasterMine for GoldMine v6 0 User s Guide BUSINESS ANALYSIS AND REPORTING FOR GOLDMINE MasterMine for GoldMine User s Guide For Distribution with MasterMine Version 6 0 1999 2008 MasterMine Software Inc 840 Hampden Ave Suite 204 St Paul MN 55114 Phone 651 917 5010 Toll free 877 877 7212 MasterMine software is protected under U S Patent 7 185 279 All Rights to Software and Documentation reserved GoldMine is a registered trademark of FrontRange Solutions Inc Microsoft Excel Office 97 Office 2000 Office XP Office 2003 Office 2007 PivotTable and MS Query are registered trademarks of Microsoft Corporation All other trademarks are the property of their respective owners Table of Contents INTRODUCTION 5 55 a Pape sie codussonsesssveesensdeonesdesveenesesbonssdeeseocesesonesdscvenessesse 2 IS MASTERMINE RIGHT FOR YOU ccccccssssscececeeecsesssseeecceccssssseeecceceessseeeeececeesssseeeeeeseeensaaes 2 What MasterMine can do for you eese nennen nennen emen eee trennen 2 Who should use MasterMine eee eene nenne hene eene nennen enne eene eene enne 2 KNOWLEDGE REQUIREMENTS cccccccessssscceeccecessssseceeeccecsesssseeeeccesenssseeeesecesenesuseeeceesensssaeeeeees 3 Beginning MasterMine Users etr t t Her Re RETE b va ed Ye is 3 Intermediate MasterMine Users ccccccccccccccccccccc
76. MINLxls called Save SOL Reports to GMSQL MasterMine keeps track of the SQL login information needed to access the SOL C MasterMine Reports Browse database This information is required during installation and again later if MasterMine fails Faten while attempting to log into SQL when launched General Preferences Preferences set on the General tab affect how MasterMine builds field expressions and where reports are stored Character s ending Profile field names to indicate Treat field as Date and Character s ending Profile field names to indicate Treat field as Numeric These 2 character codes designate field types in GoldMine detail records See Appendix A Details Tab Reporting on Page 120 24 Standard length to pull from Reference Product field 0 all The Reference field in history records is often used to contain multiple pieces of information If you want to pull only the first x characters from the Reference field enter a number here Another usage References are especially long fields and can resultin Out of Memory errors when querying large amounts of data on older computers using older versions of Microsoft Excel In order to keep this to a minimum MasterMine allows you to query Reference data in a field called ShortRef which contains the first x characters of the Reference field by default You can adjust this length for all subsequentl
77. OT mean that MasterMine can t be used on your system if you have more than 9000 records in your database Here are several suggestions for dealing with this limitation 1 Get Office XP or higher Each successive version dramatically raises the memory limits 2 Consider that fields with many unique values like Company or Contact can still be accessed in drill down mode So any time you need a detailed list of companies that meet various criteria simply double click on a pivot table segment that targets the records you want to see and all contact info will be there 3 Use filtering criteria see page 45 above to limit your overall query LOOSELY around the specific information you want to see There is plenty of opportunity to further refine your selection once you have the information in your MasterMine workbook Using filtering criteria up front however may help reduce your overall selection to a more manageable level 49 50 Getting Oriented Report Parts and Features This chapter orients you to the various parts of a MasterMine report and to some useful language for talking about it Understanding these terms will help you to make sense of reporting concepts as discussed in this manual as well as to discuss problems and solutions with colleagues and MasterMine support staff Terminology Pivot Tables The standard unit of a MasterMine report is a pivot table trademark of Microsoft Corporation A pivot ta
78. PJ Proj Notes3 NOTES 507 253 mid PJ NOTES 507 substring udfDBO udfMMStripHTML PJ PJ Proj Notes4 NOTES 760 253 mid PJ NOTES 760 substring udfDBO udfMMStripHTML PJ PJ Proj Notes5 NOTES 1013 253 mid PJ NOTES 1013 PJ F1 Rtrim PJ F1 PJ F1 PJ F2 Rtrim PJ F2 PJ F2 PJ F3 Rtrim PJ F3 PJ F3 PJ ProjectFlags Rtrim PJ FLAGS PJ FLAGS PJ ProjectlD PJ OPID PJ OPID PJ PJAccountNo PJ ACCOUNTNO PJ ACCOUNTNO PJ ProjectRecords PJ RECID PJ RECID PK PKRecType Rtrim PK RECTYPE PK RECTYPE PK Task User Rtrim PK USERID PK USERID PK Task Reference Rtrim PK COMPANY PK COMPANY PK Task Status Rtrim PK STATUS PK STATUS PK Task Priority Rtrim PK STAGE PK STAGE 150 Source MM Field Name SQLExpression dBase Expression PK Task Begin Date PK STARTDATE PK STARTDATE PK Task End Date PK CLOSEBY PK CLOSEBY PK Task PctDone PK CLOSEAMT PK CLOSEAMT PK Task Notes udfDBO udfMMStripHTML PK NOTES PK NOTES substring udfDBO udfMMStripHTML P PK Task Notes2 K NOTES 254 253 mid PK NOTES 254 substring udfDBO udfMMStripHTML P PK Task Notes3 K NOTES 507 253 mid PK NOTES 507 substring udfDBO udfMMStripHTML P PK Task Notes4 K NOTES 760 253 mid PK NOTES 760 substring udfDBO udfMMStripHTML P PK Task Notes5 K NOTES 1013 253 mid PK NOTES 1013 PK PKAccountNo PK ACCOUNTNO PK ACCOUNTNO PK TaskRecords PK RECID PK RECID
79. Partner Information If you are a GoldMine partner or you use one as your primaty support for GoldMine enter their name and phone number then click OK This information will appear for easy reference in MasterMine on the opening splash screen and under Help About MasterMine 11 Indicate whether components of GoldMine are used At setup or by preference parts of GoldMine not being used such as Opportunities Projects or Service can be excluded from the MasterMine dialog MasterMine goes on to complete installation of components A link is added to your GoldMine View menu for launching MasterMine This link will not be visible until the same user closes and re opens GoldMine MasterMine installs this user manual in your local program folder for easy access Call it up anytime using Start Programs MasterMine for GoldMine Click OK to proceed Troubleshooting Installation If you encounter any difficulties your first resort after this document is MasterMine s online troubleshooting page and online Knowledge Base under support at 15 Gel Enter SOL Password GoldMine Contact Files raniSales SOL Server Hane IEILDMINE N Cancel Contact Sct Jatabasc Mame GaldMine GM Base Detabase Name GaldMine SOL Use Marne ss SQL Password Save Report To EA Mastorhd ne Reporte Ej www mastermine net Secondarily you may get help by calling the contact number in the title page of this manual
80. Rtrim C1 COMPANY C1 COMPANY isnumeric substring C1 ACCOUNTNO 2 C1 Linked To Contact 1 C1 ACCOUNTNO Is Not Null C1 Last Name Rtrim C1 LASTNAME C1 LASTNAME C1 Department Rtrim C1 DEPARTMENT C1 DEPARTMENT C1 Title Rtrim C1 TITLE C1 TITLE C1 Secr Rtrim C1 SECR C1 SECR udfDBO udfMMPrimaryEmail C1 ACCO C1 PrimaryEmail UNTNO udfDBO udfMMPEmailMergecodes C1 C1 PrimaryEmail Merge ACCOUNTNO C1 Phone Rtrim C1 PHONE1 C1 PHONE1 C1 Phone2 Rtrim C1 PHONE2 C1 PHONE2 C1 Phone3 DELE PHONE3 C1 PHONES C1 Fax Rtrim C1 FAX C1 FAX C1 Ext1 Rtrim C1 EXT1 C1 EXT1 C1 Ext2 Rtrim C1 EXT2 C1 EXT2 C1 Ext3 Rtrim C1 EXT3 C1 EXT3 C1 Ext4 Rtrim C1 EXT4 C1 EXT4 C1 Address Rtrim C1 ADDRESS1 C1 ADDRESS1 C1 Address2 Rtrim C1 ADDRESS2 C1 ADDRESS2 C1 Address3 Rtrim C1 ADDRESS3 C1 ADDRESS3 C1 City Rtrim C1 CITY C1 CITY C1 State Rtrim C1 STATE C1 STATE C1 Zip Rtrim C1 ZIP C1 ZIP C1 Zip5 substring C1 ZIP 1 5 left C1 ZIP 5 139 Source MM Field Name SQLExpression dBase Expression C1 Postal Zone substring C1 ZIP 1 3 left C1 ZIP 3 C1 Country Rtrim C1 COUNTRY C1 COUNTRY C1 Dear Rtrim C1 DEAR C1 DEAR C1 Source Rtrim C1 SOURCE C1 SOURCE C1 KEY1 Rtrim C1 KEY1 C1 KEY1 C1 KEY2 Rtrim C1 KEY2 C1 KEY2 C1 KEY3 Rtrim C1 KEY3 C1 KEY3 C1 KEY4 Rtrim C1 KEY4 C1 KEY4 C1 KEY5 Rtrim C1 KEY5 C1 KEY5 C1 C1Notes udfDBO
81. TIONG sssssscccccesssssssnccecccceesssseeescccessnsseeeeccecsnsessaeeseeesessssaeeseeesesenaaes 19 SHORTCUTS FOR MULTIPLE MASTERMINE INSTALLATIONS cccsesssceecceeeesssseeeeceeesesssnseeeees 19 FIRE LOCATIONS 20 lien tl e et ee e ee ti Ea 20 TERMINAL SERVICES INSTALLATION ccccooooooonnnnnccnonnnnnnnnnnncnonnnnnnnnnnocononnnnnnnnncononnnnnnnnnncononannnanos 21 UNINSTALEING u ee E ta Ae 21 Ld DI UO HOLLO DE M 24 General Preferences i ed te eg tee i edge 24 Toolkits Preferences ee io 25 Integration Preferences eee Hp Reed 26 Compatibility Preferences x uci ete e e e Fee ete ede tet abet 26 Defaults Preferences ie oet a as erede ed 27 CREATING NEW REPORTS scsssssssssssssrsssscssrsesscssrsesscssrsesscssssesscssesesscssssessessesessessrsesees 32 GOLDMINE VIEW OR GO TO MENU aeriene a anaE A ER OaE EE Ra e SEER 32 GENERATING ANEW REPORT ndinii iia ni idas 33 CONTA a ES O AOA A RES O Ro For 34 ACUVIlleS y A tec ttai tie tt d ed tee ds 34 Detailss amp o d tse etc d ttt no eno dtt dtu a d teu s 34 Opportunities Projects and Cases essent enne een enne nne 35 Technical Point Table Joins eese seen nononnnn no ran nro n aa nn cnn naaa na n nnne 36 DESIGN NEW A NON 36 Sales Reporis cte aede A 36 SUSE TEMPEBATE ternas eere rer meto eie Ned ece geben e Dod re ee herede peste
82. Table In practice creating a Pivot Table from an external database that contains a very large number of records can strain the performance of the workstation on which Excel is running and can take a very long time to complete if your query is so large that it uses up all available RAM Maximum Query Size With Office 2000 or lower you will receive an Out of Memory error if you try to query more than 100MB of data For Office XP and later there is no fixed maximum size to Pivot Table cache memory The maximum size is usually limited only by the amount of available memory on your computer Data Intersections Column Fields and Row Fields For Office 2003 and below your Pivot Table limits the number of fields you can put simultaneously in the Row and or Column areas of your report There is NOT a fixed limit on the number of fields however The limit is set by the product of all unique items in each of the fields in the row or column area This limitation is much less significant for later versions of Office As long as the data are in the page area the limitation is primarily a function of RAM However once they are brought down into the Pivot Table Row or Column Area the data start running into these pre defined limits The key issue is the number of data intersections that are set up Suppose for example you add three fields to the Row area of your report and each of these fields has 100 possible values unique items
83. The features contained in the MasterMine Toolbar in report Group Building functions Drilldown to GoldMine and GoldMine Group Creation all uniquely integrate the power of Pivot Table reports with GoldMine to seamlessly enhance your GoldMine management capabilities Security MasterMine has read only access to GoldMine data except ContGroups so there is no danger of directly changing underlying GoldMine data Access to GoldMine itself is through the usual log on and other security measures although MasterMine offers a powerful Drilldown feature that expedites the process of finding individual target records No MasterMine user can directly affect GoldMine data that is not otherwise accessible to that user through GoldMine s security However because MasterMine renders powerful overviews of your entire business including individual and group performance data you may wish to restrict the use of some MasterMine toolkits to the management level and those who have master rights of access in GoldMine itself Certain capabilities can be denied selected users simply by removing them or by purchasing appropriate licenses For instance the ability to create new reports can be removed altogether by appropriate Share licensing OR by manually removing the Create MasterMine Report line item from the user s User ini file in the GoldMine application folder Users with Share licenses can be restricted to only those reports crea
84. X Postal Zone rtrim CX ZIP 1 3 CX ZIP CS ZIP 3 rtrim CX COUNTRY rtrim Coalesce null CX COUNTRY IIF CS COUNTRY is CX Country if CS COUNTRY CX COUNTRY null CX COUNTRY CS COUNTRY CX Fax rtrim CX FAX rtrim CS FAX CX FAX CS FAX rtrim CX FAX rtrim Coalesce nullif CS CX FAX IIF CS FAX is null CX FAX CX Fax Filled FAX CX FAX CS FAX CX Dear rtrim CX DEAR rtrim CS DEAR CX DEAR CS DEAR CX Source Rtrim CX SOURCE CX SOURCE CX KEY1 Rtrim CX KEY1 CX KEY1 CX KEY2 Rtrim CX KEY2 CX KEY2 CX KEY3 Rtrim CX KEY3 CX KEY3 CX KEY4 Rtrim CX KEY4 CX KEY4 CX KEY5 Rtrim CX KEY5 CX KEY5 CX CXNotes udfDBO udfMMStripHTML CX NOTES CX NOTES substring udfDBO udfMMStripHTML C CX CXNotes2 X NOTES 254 253 mid CX NOTES 254 substring udfDBO udfMMStripHT ML C CX CXNotes3 X NOTES 507 253 mid CX NOTES 507 substring udfDBO udfMMStripHTML C CX CXNotes4 X NOTES 760 253 mid CX NOTES 760 substring udfDBO udfMMStripHT ML C CX CXNotes5 X NOTES 1013 253 mid CX NOTES 1013 CX CreateBy Rtrim CX CREATEBY CX CREATEBY coalesce convert datetime char ascii CX iif isdate format mid CX ACCOUNTNO accountno 17 isdate substring CX acc 2 5 199 datevalue format mid ountno 1 6 CX ACCOUNTNO 2 5 if left CX ACC 1 substring cx accountno 2 5 NullDat OUNTNO 1 A 200 HH 1 99H HH CX CreateOn e NullDate CX Owner Rtrim CX OWNER CX OWNER rtrim CX LASTUSER rtrim CS LASTUS CX
85. a from other locations for instance lookups from pivot tables on other worksheets To view the same data in multiple ways on the same worksheet you can copy an entire A B c E et E RR n t EN pivot table from one sheet to 1 2 another To do this highlight the 4 OnY 2007 Y B E Onion Mar Ej entire pivot table including if Ce Pp a necessaty some empty cells in the 9 LAURIE 602 14 9 625 40 NANCY 223 50 21 294 areas surrounding the pivot table 11 PAUL 17 5 22 Ta SuSE 4 3 1 2 then copy the area and paste it to 14 Grand Total 872 B7 36 975 15 the new location on another 16 worksheet bes eese 20 OnYear 2007 Y 21 OnMonth Mar Y s 2 cog RES Adding charts is easy and it often ne E m E E NANCY PAUL ROB SUSIE Grand Total 250 gt 1 S asa aaa 7 i E makes your report immediately 26 3 4 2007 3 10 2007 161 46 5 1 5 2191 2 asise a 27 3 11 2007 3 17 2007 168 86 4 8 4 270 150 ROB more teadable Right click on any 28 3 18 2007 3 24 2007 167 91 3 8 269 PAUL 4 35 aeea O RE 938 suum Chart and select Location from 31 aA oi m the short menu to plug the chart 33 2 25 2007 3 4 2007 31 2007 3 18 2007 3 25 2007 2 P ES 3 3 2007 30 2007 37 2007 3 24 2007 3 31 2007 into a spreadsheet containing 36 d Ed other pivot tables or charts
86. a number of pseudo tables inorder to facilitate the mixing of data from various similar tables The most commonly used are as follows CX Contactl and ContSupp Mixed includes main AND supplementary contacts in the same query CX can mean either table because the report overlays addresses from supplementary contacts records where this information is missing but present in the Contactl record PH Pending and History is a special table name designating Cal or ContHist for reports that pull data from both tables in parallel For instance the PH ActivityCode field pulls data from both tables and presents them as though Pending and ContHist were really one table so your report can combine both and ignore the distinction Field Selection In the list box in the bottom half of the report creation dialog simply check to the left of every field you want to include in your report either as content OR as filtering or selection criteria Think not only of what fields you want to display but also what conditions you want to use to select or sort what will be displayed Obviously your selection of fields determines the data that will be available in the reports you generate from the templateyou select The more fields you include the more flexible will be the reports you create HOWEVER pulling more fields also requires more system memory so you should select your fields wisely with both eventual usage and system limitations in min
87. a screen set for higher resolution at least 1024 x 768 pixels If all data are not visible on the page or some cells contain HHHH instead of numbers try hitting the autosize columns button e on the MasterMine toolbar to bring them into view 122 11 If all data are still not visible you may want to freeze the column and row heads while scrolling to other parts of the report To toggle this feature on or off click the Freeze Unfreeze Panes button Ghi on the MasterMine toolbar Ele Edit view Insert Format Tools Data Window Help Laa Bl o amp 81 2 M4 100 E Pio Bo Ct M MM Til Heh ete ey 003 323 Account Exec All v Product Semice Al Ar Te Bus jm d tB RA 5 Contact Type Al pd 6 Omer Jin gt 8 Linked To Contact Yes PivotTable UM A 9 Probability cal 10 Short Reference 11 Activity Code 4 4 ji2UserID AN 13 Record Type tv 14 OnQtr AM 15 OnWeek AI E e ME ar ata 19 OnYear v OnMenth v CARecords Potential Units Potential Sales Vol Probability Probable Units Prob 1 1 00 1 190 00 0 10 0 10 2 1 00 0 00 0 35 0 25 59 66 00 77 520 00 8 35 9 05 33 32 00 46 729 08 5 65 5 55 51 49 00 90 125 00 6 05 5 85 13 12 00 7 945 00 1 95 1 85 14 18 00 24 210 00 2 40 2 80 7 10 00 10 340 00 1 50 3 00 2 2 00 0 00 0 20 0 20 3 31 00 12 090 00 0 60 7 60 2
88. a second field into the Column Area of your report The result is a table also called a cross tab showing a breakdown simultaneously by both fields Suspect Prospect Customer Past Client Grand Total 79 2 92911420009 pm In this example the database is broken down by Contact Type and Territory Each Contact Type is broken down by Territory in rows and each Territory is broken down by Contact Type in columns As one example there are 15 Suspects in the MW Territory This is obviously the simplest kind of matrix report showing one dimension in Rows and another in Columns creating a 2 dimensional cube Note that adding this dimension changes the size and shape of the pivot table so it occupies much more of the spreadsheet If the cells that would be covered by the expanded pivot table contain any information Excel will ask the user whether overwriting those cells is okay Multi dimensional Matrix You can drag more fields into either the Row or Column ateas thus showing more and more detail for each item in the fields already there Adding a second field to the Row Area to the tight of the Contact Type field changes the report to look like this 69 Territory Grand Total 18 Suspect 23 Prospect Customer own p a Pharmaceuticals System Design 30 Media 31 Pharmaceuticals 32 Past Client 33 34 B5 36 Pharmaceuticals Grand Total
89. abase This will result in a report that looks similar but not identical to the images here After this run through try following similar steps within another report This will give you a good feel for the many possibilities of reporting using these simple MasterMine techniques Create the Sales Pipeline report 1 2 3 4 5 On the GoldMine View or GoTo menu select Create MasterMine Report Click Pending to specify future activities In the list box to the right click Sale to specify only sales records Give your report a unique name like Sales Pipeline Leave Save to as is for now You only need to change this if you wish to save reports to somewhere other than the default location If you change this setting your report will not appear in the Open MasterMine Report dialog Hit the Create button to generate the report A progress bar will keep you posted on the development of the report MasterMine informs you when your report is done Click OK to finish View Data in Default Layout 8 10 Note that the default table shows projected and probable sales multiplying the probability times the number and amount of sales by month for all data in the file Depending on your monitor settings you may wish to get a better view of your worksheet by changing the zoom on your Excel toolbar from 100 to 75 MasterMine and Excel are best suited to
90. access to GoldMine account fields C1C2QH Invoices at Item level of detail No GoldMine detail Invoices by Item included QIQH Invoices by Item GoldMine Invoices at Item level of detail GoldMine contact detail linked included C1C2QIQH Forecast Forecast by part QIQH All documents linked to GoldMine Accounts Summarized Forecast GoldMine linked at individual item level C1C2QIQH Forecast based on GoldMine forecast sales linked to QW with GoldMine Forecast QuoteWerks documents C1C2CAQH 138 Appendix D Common Fields and Expressions The following tables summarize the logic behind report field contents including the SQL expression used to extract them The soutce column indicates source files in GoldMine aliased as follows Most common tables Special use tables Alias Table Name For Alias Table Name RF Contact1 Referrals C1 Contact GS GSSites GoldSynch Sites C2 Contact2 GH GSSites GoldSynch Headers CH ContHist AH InfoMine Knowledgebase 2i m NET ContSupp Alarms CS ContSupp___ LD ContSupp Linked Documents OM OpMgr EET UL UserLog UserLog SL SysLog System Log EM ContSupp E mail Addresses LK Lookup Lookups CG ContGrps Group Headers G2 ContGrps Group Members RI Resltems Company Resources Source MM Field Name SQLExpression dBase Expression C1 Contact Rtrim C1 CONTACT C1 CONTACT C1 Company
91. act records linked to main contact info CSC1C2 CS H 137 Quote Werks Toolkit MMQuoteWerks xls MasterMine for GoldMine includes a QuoteWerks toolkit that integrates GoldMine data with QuoteWerks data into seamless reports Integration with QuoteWerks is described elsewhere in this manual Here are details on the reports and tables available from QuoteWerks for reporting Alias Table C1 Contact C2 Contact2 CH ContHist CA Cal CS ContSupp OM OpMgr CX Contact1 QH DocumentHeaders Ql Documentltems Workbook Name Description GM Tables All Quotes in QuoteWerks regardless of links to GoldMine Quotes accounts QH Quotes GoldMine linked Quotes linked to GoldMine Accounts C1C2QH Quotes at the Item level of detail Only Quotes not marked Quotes by Item lost are included QIQH Quotes linked to GoldMine Accounts Only Quotes not Quotes by Item GoldMine linked marked lost are included C1C2QIQH Orders All Orders QH All Orders linked to specific GoldMine account records Orders GoldMine linked Includes access to GoldMine account fields C1C2QH Orders by Item Orders at Item level of detail No GoldMine detail included QIQH Orders at Item level of detail GoldMine contact detail Orders by Item GoldMine linked included C1C2QIQH Invoices All Invoices QH All Invoices linked to specific GoldMine account records Invoices GoldMine linked Includes
92. after report created By default the MasterMine Create dialog stays in computer memory when it finishes creating your report If you prefer to have MasterMine shut down automatically each time you run a report check this box on Excel visible during creation of report Instead of the MasterMine progress message checking this box allows you to see the creation of the pivot table in Excel beginning with the querying of the data including formatting fields orienting them in the report etc Some users may find this view confusing so it is best to leave it off unless you are debugging 29 30 31 Creating New Reports If you are too impatient to read this user manual and you ate already familiar with Microsoft Excel PivotTables at least read this chapter to get started It contains everything you need to know to generate new MasterMine reports You can learn the rest by doing or by reading later chapters H 1 H CL ColiMine View or Go To Menu Eile Edit View Lookup Contact Schedule Complete Tools Wir Q a New Contact Window Contact Groups MasterMine s installation creates two entries in GoldMine s View Menu or Go To Menu in GoldMine Premium Edition only for the user who was logged in during installation After installation these any MasterMine So items are in that user s View menu the next time the bie Rob Machalek user logs into GoldMin
93. ains templates to generate workbooks like Call Value and Past Due Pending Activities In turn each of these workbooks is capable of displaying the targeted information in infinitely many formats sorts and filters to get at a true picture of your business Database Terms In the modern world formerly scientific terms like database field and query have seeped into the language of ordinary people though usually without full understanding of what they mean MasterMine attempts to make reporting easy for people who know nothing about databases but it is helpful to understand how the technical parts of database structure and usage relate to the more common user understood concepts A Query is a technical instruction to a database specifying the body of data to be pulled from the database into the pivot table for processing In MasterMine queries are in the form of Transact SQL scripts A single Query returns a result set of data also called a cache of data in computer memory which may provide the basis for one or more pivot tables in a single workbook A database Table is a set of records all with a consistent structure containing data that logically belongs together A database typically contains a set of tables that fit together to form a coherent set of information The GoldMine tables used by MasterMine Contactl etc are listed elsewhere in this manual Each table contains a number o
94. alesce CS LastDate NullDate NullDate CS LastDate CS CSAccountNo CS ACCOUNTNO CS ACCOUNTNO CS CSRecords CS RECID CS RECID CS CSHasNotes CS STATUS CS STATUS CS CSNotes udfDBO udfMMStripHTML CS NOTES CS NOTES substring udfDBO udfMMStripHTML C CS CSNotes2 S NOTES 254 253 mid CS NOTES 254 substring udfDBO udfMMStripHTML C CS CSNotes3 S NOTES 507 253 mid CS NOTES 507 substring udfDBO udfMMStripHTML C CS CSNotes4 S NOTES 760 253 mid CS NOTES 760 substring udfDBO udfMMStripHTML C CS CSNotes5 S NOTES 1013 253 mid CS NOTES 1013 rtrim CX CONTACT rtrim CS CONTAC CX Contact T CX CONTACT CS CONTACT CX Company Rtrim CX COMPANY CX COMPANY Main or Supp CX Contact Main Supp Main Supp CX Last Name Rtrim CX LASTNAME CX LASTNAME rtrim CX DEPARTMENT rtrim CS CON trim CX DEPARTMENT trim CS CON CX Dept Ref TSUPREF TSUPREF CX Title rtrim CX TITLE rtrim CS TITLE trim CX TITLE trim CS TITLE CX Secr Rtrim CX SECR CX SECR udfDBO udfMMPrimaryEmail CX ACC OUNTNO udfDBO udfMMSuppContE CX Email Address mail CS RecID udfDBO udfMMPEmailMergecodes CX ACCOUNTNO udfDBO udfMMSuppE CX Email MergeCodes mailMerge CS RecID CX Phone rtrim CX PHONE1 rtrim CS PHONE CX PHONE1 CS PHONE CX Extension rtrim CX EXT1 rtrim CS EXT CX EXT1 CS EXT rtrim CX MERGECODES rtrim substrin CX MERGECODES mid CS MERGEC CX Merge Codes g CS MERGECODES 2 20 ODES 2 20 rtrim CX ADDRESS1 rtrim CS ADDRE
95. all C List All Templates Opp Pending Activities Next Pending activity for each contact record or P y None if none exists default or by Select fields to include gt 2 x pM LU toolkit then report C1 Contact w CA OnWweek w C1 MM Partner C1 Company CA Duration C1 Account Rep 4 name by tight wv CA Activity Type Y LA Last User wi C1 Product Int clicking on the list and selecting the appropriate CA Activity Contact w CA Last Modified w C1 CreateOn 2 v CAOnDate v CA CAAccountNo TCI Cl ccauntilo sorting option CA DaysTill v CA CARecords CA Company wv L User ID Cl Linked To Contact Ca OnDay v D Activity Code Y 01 Source CA OnTime H ll il l m l 44 C Short Reference w Cl Account Type LA nHour ow to see all ava ab e te p ates CA Reference w Cl Status CA Product E T J For the broadest selection of templates click List All Templates at the bottom of the area Report Name Nest Pending Activity only All Contacts selections on the left If you use this option you SaveTo Path CAMastei Mine Reports will probably need to search by keyword to find Save To Template Filter Finish Cancel the right template Search for the right template by keyword You can use keywords to find the template that best suits your needs In the space above the template list enter keywords separated by spaces and click Search The list is narrowed down still further to include only templates fr
96. alphabetical order for the selected field 57 Clone Report Creates a duplicate of the current worksheet at the end of the tab row for the current workbook Users ate expected to use this function frequently while developing custom data analysis workbooks and dashboards Formatting Submenu MasterMine Refresh Format Hide Page Fields showing Call Show Page Fields with Hidden Items Hide Row Field Subtotals Sort Items in Selected Field Delete all worksheets named Sheet Delete all Log tabs al Hide Page fields set to show All items Too many fields in the Page area of a pivot table can be visually confusing and cluttered This option quickly moves all unused Page fields to the Pivot Table field list These fields can be easily retrieved by dragging and dropping back into the report from the PivotTable field list Show Page Fields With Hidden Items Shows a list of Page fields that contain hidden items Page fields containing hidden items may prevent records containing those items from showing in the display below This is determined by whether the table option to Subtotal Hidden Page Items it turned on or off 2 Hide Row Field Subtotals Hides subtotals for all fields in the Row area of the pivot table This option is used if many fields have been pulled into the report to create a line listing type report In this case subtotalling by every field unnecessarily complicates t
97. at a premium In most cases detail level 2 the default is sufficient A lower detail level gives you fewer fields in your report but speeds data querying and refreshing of your reports and may allow you to load many more records than would otherwise fit into your computer s memory A higher level of detail gives you all possible relevant fields in the target data files but may result in slow data querying Detail can be fine tuned by selecting or de selecting fields to be included individually The information box below the detail level indicates the number of bytes per record that will be loaded into memory at the currently selected detail level Multiply this times the number of records likely to be included to determine how much RAM will be required for MasterMine defaults to include a selection of fields for the type of report you have selected However every field in the affected tables including all your custom designed fields is available for inclusion Under select fields to include click on fields you wish to include in your report either as filtering criteria OR for display Each field name is preceded by a two character table indicator which tells explicitly which table the field comes from The most commonly used tables are as follows C1 Contactl C2 Contact2 CA Cal CH ContHist CS ContSupp OM OpMgr GS Gsites GoldSync Sites UL User Log 40 MasterMine reports include
98. ates after installation is complete By default these files are located for dBase in MasterMine sub folders of the contacts sets to which they refer or for SQL in the folder indicated by SaveTo in MMINI xls 22 23 Preferences Preferences Launch the Preferences menu from the MasterMine Create New Analysis Workbook dialog It is not mandatory to change preferences at setup time but you may wish to go through this dialog at least once to familiarize yourself with available options Preferences apply at the workstation level Initial settings are held in a file called MMINKI xls in the Support folder in the MasterMine application tree on each workstation This ini file is updated by changing the settings in Preferences Integration Compatibility Defaults Ini settings may be shared among multiple ek Character s ending Profile field names to indicate Treat field as Date workstations by copying the file MMini xls from TN Character s ending Profile field names to indicate Treat field as Numeric one wotkstation to that of any other MasterMine user They may also be updated directly by 20 7 Standard length to pull from Reference Product field 0 all opening the MMINI xls file in Excel and applying 1 1 1983 v Default Start Date for History Data changes manually 1 1 1989 Date to substitute when date fields contain no data or unreadable date value On a separate tab in M
99. ause UNITS may be null in some cases the val function will not interpret it without special handling Adding Tables MasterMine provides templates for all key GoldMine database files in most useful combinations If you are building a report from scratch careful selection of your template makes this step unnecessary However if you already have a report you wish to modify or you wish to include dbf files from outside GoldMine here s how to do it Click on the menu Table Add Tables Select the table you wish to add even if it is outside the currently selected contact set and click once on the Add button Even if you don t see it yet you have already added the table Drag the Add Tables box to the side to check Now hit the Close button Your query now includes one record in each table for every one in the other table This is probably not what you wanted so you need to specify a link or join between the tables in your query as follows Adding or Adjusting Joins Joins are tricky and they can lead to misinterpretation of your data so read closely A Join defines the relationship of one table to another usually through a key field or fields that appear s in both tables To specify a join click on the key field in one table and drag it onto the corresponding field in the other table For example both the Cal and Contact tables contain an ACCOUNTNO field Most Cal records are linked to th
100. b OM ProbableAmount OM ForProb 0 100 Is Null O val OM ForProb 100 Coalesce Convert Real iif OM CloseAmt Is OM Opp CloseAmount OM CloseAmt 0 Null 0 val OM CloseAmt OM Opp Notes udfDBO udfMMStripHTML OM NOTES OM NOTES substring udfDBO udfMMStripHT ML O OM Opp Notes2 M NOTES 254 253 mid OM NOTES 254 substring udfDBO udfMMStripHTML O OM Opp Notes3 M NOTES 507 253 mid OM NOTES 507 substring udfDBO udfMMStripHT ML O OM Opp Notes4 M NOTES 760 253 mid OM NOTES 760 substring udfDBO udfMMStripHT ML O OM Opp Notes5 M NOTES 1013 253 mid OM NOTES 1013 OM Opp ID OM OPID OM OPID OM OMAccountNo OM ACCOUNTNO OM ACCOUNTNO OM OppRecords OM RECID OM RECID OK OKRecType Rtrim OK RECTYPE OK RECTYPE OK Task User Rtrim OK USERID OK USERID OK Task Reference Rtrim OK COMPANY OK COMPANY oK Task Status Rtrim OK STATUS OK STATUS OK Task Priority Rtrim OK STAGE OK STAGE OK Task Begin Date OK STARTDATE OK STARTDATE OK Task End Date OK CLOSEBY OK CLOSEBY OK Task PctDone OK CLOSEAMT OK CLOSEAMT OK OKAccountNo OK ACCOUNTNO OK ACCOUNTNO OK TaskRecords OK RECID OK RECID OD ODRectype OD RECTYPE OD RECTYPE OD Item Rtrim OD FNAME OD FNAME OD Reference Rtrim OD FVALUE OD FVALUE OD Sync substring OD LINKEDDOC 8 1 mid OD LINKEDDOC 8 1 iif isdate format mid OD LINKEDDOC 2 3 8 H HH datevalue format mid convert datetime substring OD LINKEDD OD LINKEDDOC 23 8 OD Detail CreatedOn OC 23 8 NullDate OD Linked Doc subst
101. ble is an analytical structure built into Excel that allows the user to quickly and easily rearrange and view data from many angles A pivot table is more generically a multi dimensional data cube Users who master the idea of cubes can intuitively understand exactly how moving fields around a pivot table will affect what they see For the rest of us the experience of simply playing with a pivot table more slowly but effectively gives us a feeling for how a pivot table works Pivot tables ate extremely convenient for displaying data in any scope converting immediately from a narrow summary to finest detail MasterMine leverages this capability and ties it to the capabilities of GoldMine to produce both a powerful analytical tool and a means of managing people processes and data itself Workbooks and Reports A Workbook is an Excel file that may contain one or many Worksheets The term MasterMine Report may be used to refer to either a workbook OR one of the worksheets within a MasterMine workbook It is therefore clearer to say Workbook or Worksheet to refer to the specific item rather than a Report Since Report is the most common way of referring to MasterMine output we use it frequently here with the expectation that users will understand in context Once a Report is created initially as a single worksheet in a new Excel workbook it can then be quickly cloned tweaked and m
102. cccccccccccecececececececccececececececececececececececececececeseeess 4 Advanced MasterMine Users oooonnnnnnncccncccnnncnonononononononononononononononnnnnononononononononononononenenenenes 4 NEW FEATURES IN VERSION 5 0 amp 5 1 usseessssssssnnnnnsnssssssnnnnnnnnnnnnnnnnnnnnnnnnnnnnssnnnnnnnnnnsnsnnnnnnn 4 NEW FEATURES IN VERSION 6 0 ccc ccccccccsssssseeececceecesssceeecceecesssseeeeceeceesssseeeeeeeseeessaseeeceeeeeetaees 6 GETTING HELP AREN E S E S dad e eR eg 7 Technical SUpport Ge 4e Mesa et de la Reel tte tte 7 Report Writin E id AA aida dd ibid dio 7 Trainin En AAA A A A dida 8 INSTALLATION ED 10 DEMO eee ebenen re unde 10 LICENSE OPTIONS asada 10 REQUIRED SOBTWARE vss u a liest st 11 REQUIRED HARDWARE cuidado 12 HOW MASTERMINE WORKS sssssssscceessssssssceeccccesesssseceeecceesesseeeeecceeeesssseeeecceceesssaseeeceesesensaaes 13 A A MM a OE 13 SETUP PROCED RE 2 e eleibeovie ts e eaa esi eai edet e Eye tecti oss 4 TROUBLESHOOTING INSTALLATION ccccsesssceceeesssssssececcccecessnsseeecceeeessseeeececessessseeeeeeeeeentaaee 15 SETTING UP FOR SQL INSTALLATIONS c cccsssseceessseeeeeeseeeessneeeeseeeeensaeeesesneeesseeeeeseeeeessaes 16 INTEGRATION WITH GOLDMINE ADD ON PRODUCTS ccooccccccnnnononnnnnnccnnnnnnnnnnnnccnonnnnnnononocnonannnnos 17 Integrating Details Plus oo as 17 Integrating QuoteWerks 9 iae dett t os 18 LICENSING WORKSTA
103. cts Design New Uo Template types of activities tracked in GoldMine under Main dcl Schedule or Complete menus and displayed in Fr except E mail r H Call the Pending and History tabs In the dialog simply M ARAS check on which types of activities to include in the Appointment m Literature Request tepott Sale C Details Other Action N S P sie m Sales is a special kind of activity If you select only C Cases E mail Sale in the activity type dialog your report will be automatically formatted and appropriate default Select fields to include fields selected to do reporting on Forecast or vi C1 Contact vi C CAAccountNo Completed Sales v C1 Company v CA CARecords CA Activity Company vV CA Activity Type v C1 Linked To Contact CA OnTime M v C Activity Contact v Cl State CA OnHour If you select multiple types of activity or if you leave vV CA OnDate Y C1 Source CA DaysTill th d 1 f h le d f di All v CA OnDay v Cl Account Type CA Short Reference e default selection checked on or Pen ng gt v CA User ID v C1 Status CA Product for Histo All exce t E mail ou will still be vI CA Activity Code v C1 MM Partner CA Probability ty p Ml y an Y CA Reference Y C1 Account Rep CA Duration able to filter or group by Activity Type within your v CA r Week v C1 Product Int CA Meeting Confirm lt MasterMine report Limit your report to only one or a couple of activity types only if you wish to
104. d Because you can include fields just by checking them on in the list box this is the easiest time to include fields you may want later Therefore as long as it does not unduly slow your query or cause the pivot table performance to be sluggish you should include any related fields that may be needed for the report you are currently building AND any derivative reports you may wish to make in the future How to find the fields you want to include In most cases MasterMine displays fields using the User defined Labels assigned by your GoldMine database administrator This is so that you do not need to know and translate GoldMine labels to the underlying database fieldnames in dBase or SQL However the fields are sorted by database Table Contactl Contact2 Cal ContHist ContSupp etc so that the user can find and connect these fields in a logical manner Therefore fields appear with a prefix of C1 C2 etc corresponding to the table from which they are derived Note that MasterMine includes many fields that are not really fields in GoldMine That is they are logically dertved from data that is stored in GoldMine but the data has been converted to a more useful form for reporting purposes To take one simple example GoldMine stores the duration of an activity in the form HH MM SS for hours minutes seconds which is not an easily reportable format In addition to providing you with the raw Duration fie
105. d and may corrupt the pivot table and render it unrefreshable In this event immediately attempt to repair the connection string or close the workbook without saving changes in order to preserve the report Note that if the SQL password is saved in your pivot table it is obscured by in the Connection box You may enter a new username password combination here if desired by turning on SQL password saving in table options The syntax for the password line is PWD password Where password is the SQL password and the line terminates with a semicolon Changing Data Connection Strings Note that changing the DATABASE name here may not fully accomplish a transfer of your report to a new server database combination The database reference may also be contained in the Query Text which must be updated on the following tab In many 61 cases it is not possible to update both the SQL Server name AND the Database references simultaneously Therefore we recommend removing explicit database qualification from the Query Text first then making changes to the SERVER and DATABASE parameters on the Connection tab MasterMine Pivot Table Control Stats Drilldown Connection Query Text Security About This text defines the data returned to your pivot table Select Rtrim C1 CONTACT AS Contact Rtrim C1 COMPANY AS Company CA RECTYPE AS Activity Type Rtrim CA COMPANY AS CAContact Coalesce CA OnDate
106. d with pivot table A pivot table may have the SQL login data including password saved with it when the Mas wotkbook is saved Microsoft warns that saving your SQL password is not entirely secure since hackers can open an Excel workbook to see a password stored within it MasterMine Pivot Table Control Stats Drilldown Connection Query Text Security Help User Manual About MasterMine MasterMine Path c program filesimastermine mastermine exe MasterMine License 22 4 n4t Sc dp RO 04 Help Front end application GoldMine GoldMine path goldmine vm goldmine en EROS SH The Help tab inside the Query Display provides access to Excel path C Program Files Microsoft Office OFFICE11 MasterMine s underlying information in the About MasterMine Excel version 11 0 text window as well as a link to this User Manual Click on the User Manual to launch this manual as a PDF file You must have Acrobat Readet installed on the workstation Windows User Rob More Toolbar Buttons HI GoldMine Group Create GoldMine group from all rows listed in Drill Down screen This button is enabled only when viewing a drill down screen 8 Add to GoldMine Taskbar Adds the current report to a GoldMine taskbar for one button launch either in your own GoldMine or in another user s This is the quickest easiest way to deploy a report for easy repeated use Note that other users must have MasterMine lice
107. da senses ERE 37 Templates Finder os 38 Toolkits id di a as 39 SELECT FIELDS TO INCEUDE 3er epe ettet nte enean rE AE Nanot EIEEE SASE o EERTE UREE ea ESAS 40 FIELD SELECTION 5 rette EAE reet eode eee gen s elucestuecesestuvdavevsgsuseas da cdeeutivecerss 42 How to find the fields you want to include sese 42 Why limit fieldssto include a eR a e a ed esi eei teens 43 Trial Queries iae a debes wierd edhe testes 43 Field List Gontrolssa5 ed a e indi 43 Choose Report Name ode ea ie e aude e a e ee ette vidit diede 44 SAVE TO a as 45 LIMITING RECORD SELECTION OR FILTERING sccsesceceseeesnceeeeeceaceeesceceaecececseeceaeeceneeeeaeees 45 History First Date filter aste eae dotate ded deste d ve cade e eben 45 Additional filtering criteria si dca i e Ee geste edite ul 45 VIEW THE SOL QUERY MENU eiecti Ghee edt eee leit 47 HIT FINISH AND THE REST IS FINE TUNING eese enne nnne neret enne 48 GETTING ORIENTED REPORT PARTS AND FEATURES eeeee eee eee ene eene etnue 51 TERMINOLOGY da 51 PIVOtLGblese Met ao kd esso A Ee b td ssec cla iEn eet noci Mask nrc alb diee tee 51 Workbooks and Reports eite te RR a A ee aa ers e HE aes 51 Templates and Toolkits iR aate i ed eed 52 Database Terms ei e ii dae e lett eoo eese eerte deve oes ee is 52 MASTERMINE SCREEN ELEMENTS scccescecsscesscecescecsnecesceceeeeescecsaeeesaeecsaeecsaeecsaee
108. dater and you have Master rights as a GoldMine user you can use MMUpdater to mass update records in almost any GoldMine table Remember Always back up GoldMine data before making mass changes It is much easier to restore a database from backup than to recover lost or damaged data record by record On any drilldown sheet use ordinary Excel processes including simple typeover drag fill find replace cell formulas etc to change data in any cell Typically changes like this will be made in a single column at a time When finished making changes for a column highlight the entire column OR only the changed cells containing data you want to push into GoldMine The selected cells need not be in contiguous rows since you can use E Ctrl click to highlight cells one at a time or Ctrl drag select to add groups of cells to the highlighted set MM Updater File Edit Help MM Updater will update the data in one GoldMine field for each record found inthe 19 selected rows With cells highlighted click the MMUpdater icon a on the MasterMine toolbar Update existing data in this GoldMine table Contacti Use this MasterMine column to match CHAccountNo S AccountNo or RecordID of target records Activity Code H X Replace the contents of this GoldMine field with the contents of this MasterMine column Options Add numbers to existing Field data Add strings to end of existing Field data Do
109. ddress GoldMine s data you may find it difficult to make MGH Master License 1 F Cases ee sensible reports You can learn more about Profiles in GoldMine s Help or in the GoldMine User Select fields to include 28x Manual This will prepare you to report on this vital v C1 Contact v CS Status vi CS CSAccountNo GoldMine data v C1 Company v CS VAR Contact v CS CSRecords v CS CSRecordType v CS VAR Company V CS CSHasNotes v CS Detail Type vi CS CSAddress3 v CS CSNotes Once you understand Profile you can use v CS Detail Ref v CS CSDear v C1 State s COMI dul fip A MasterMine to leverage this powerful component of v CS MM License v CS CSCreate n v Cl Account Type GoldMine even better This knowledge will also vI CS Version CS CSLastUser v 01 Status hel f d 53 amp l Y CS Date issued vi CS CSCity vi C1 MM Partner elp you to set up preferences and tweak Profile v CS Usersttt v CS CSLastModified v C1 Account Rep field definitions SO that you can get the most from lt il f the information contained here Report Name GM Master License If your reports do not work as expected see the SaveTo Path C MasterMine Reports section on Appendix A Details Tab Reporting n 120 Save To Template Filter Finish Cancel bi page 0 If you use DetailsPlus an add on for GoldMine from Solica Consulting Ltd and you have entered the proper information in the integration tab in Preferences see page 2
110. designed reports by keyword with full description and context provided in advance Each template is the starting point for thousands of immediately available variations Save new templates launch recently used reports After choosing a template and making field selections you may save settings under your own user defined template name and recall it at will for more variations later Also easily launch recently used reports under the File menu Office 2007 and Windows Vista Compatibility MasterMine handles new workbook type variations under Office 2007 and takes advantage of new features in Office 2007 to make reports even more flexible and back end query management easier Archive pages Click a button to copy the current page out to a static archive workbook or to another page within the same workbook 5 Ease of use features in Excel Many functions and features requested by our more than 5000 users worldwide including a Search dialog new grouping and add to group dialogs set filter settings across multiple reports in a workbook clean up unused field buttons delete drilldown sheets and Logsheets from MMUpdater save the SQL password automatically in workbooks and much more 6 Auto schedule report printing Submit existing reports to Windows Scheduler to run automatically every day week month 7 MMUpdater enhancements Delete activity or profile records on a mass basis update Lookups notes blob fields and updat
111. dropping a numeric or date field into the data area double click on the field not its contents to define how that field is to be summarized For a numeric field such as Amount in a Sale record you get a Sum of the numeric values in the field by default You can make the pivot table perform more sophisticated calculations by changing this setting to Average Maximum Minimum Product Count of Numeric Items Standard Deviations and Variances You can also cause summed or counted data to show as a percentage of the Row or Column or many other variations Excel provides some other ways to calculate and compare data using the field definition form including custom calculations calculated fields and calculated items Most of these options are quite sophisticated and require more in depth discussion than we can provide here If you wish to explore these options use the built in Excel Help topic Summarize and Calculate Data In a PivotTable to learn more While you re here click on the Number button to format how this number is to be presented using decimal places etc Date Fields Date fields can be displayed in the Data area By default dragging a date field to the data area results in it being counted like any character field However you can set the field to show the Max or Min date for its row and column by doing the following Drop a date field into the Data area double click the field name s
112. e ept Open MasterMine Report Title President Create MasterMine Report urce Calendar F Activity List F6 E mail Center F5 E mail Waiting Online To add MasterMine functionality for another user on a workstation where MasterMine has already been installed do the following InfoCenter Projects ress 740 Mississipp Personal Rolodex F11 Suite 10E 1 Run GoldMine and log in under the new user s Literature Fulfillment UE eder Tool City Saint Paul Diaries Analysis Btate MN untry 2 Use Windows Explorer to navigate to the Gobble Lowe MasterMine application folder by default it s c Program Files MasterMine and double click wksetup exe MasterMine installs the view menu items for the new user visible the next time he or she logs in Open MasterMine Report is a standard Windows dialog that provides access to the MasterMine generated workbooks reports you have created and saved Initially of course there are none so this dialog will be empty More on this dialog later Create MasterMine Report Launches the MasterMine report generator featuring an intuitive interface that makes generation of new reports a snap The rest of this chapter covers the usage of the MasterMine Create dialog 32 Generating a New Report MasterMine is designed to make extracting information EASY You simply select which cat
113. e almost always reusable rather than re generating and formatting reports repeatedly you can re open an existing report and refresh it Setting a hard last date will cause future refreshes to ignore new history items On SQL systems MasterMine uses optimized index querying against history records This is important because it speeds up querying tremendously relative to queries against dBase systems where every record must be searched and the date compared to your criteria View the SQL Query Menu A technical note for users familiar with Transact SQL As a final step before hitting the Finish button to create a new MasterMine report MasterMine allows you to view and alter the SQL Query that will be built from your selected settings to extract your data from GoldMine If you are familiar with Transact SQL you can make certain changes using the SQL Query dialog The most likely purpose here would be to alter one or more field expressions or the WHERE clause of the query so as to affect record selection For example in a history report you may wish to include only records where JOHN was the UserID the person who did the activity which is NOT necessarily the assigned account rep for the contact To do this set up all other fields as normal 47 in the main MasterMine dialog then hit SQL Query to display and edit the query At the bottom of the SQL Query you could add to the existing Where clause something
114. e Contactl database through this Account number which is unique in Contactl and Contact2 for that matter Drag ACCOUNTNO from Cal onto ACCOUNTNO in Contactl and drop it You have created an Inner Join which means that only records with at least one mate in the other table are included In our example this excludes records for instance GoldMine e mail messages in Cal that are not linked to any record in Contactl or that are linked to records in different contact sets It also excludes Contactl records that have no pending activities linked to them 108 The other kind of join is an Outer Join which includes all records from one table and only records in the other that have a mate in the first To see ALL of the records in your main selected table you must specify an Outer Join otherwise you may trick yourself into thinking you are seeing all data when you are not To re define your join as an Outer Join double click on the joining line between the two table boxes in MSQuery Using the three radio buttons in the Join window select the type of join you desire and click Add then Close If you selected an Outer Join the connecting line between the tables is now an arrow Notes on Joins Only inner joins are allowed when the query includes three or more tables MasterMine templates based on Cal dbf use only inner joins by default since installations with multiple contact sets include many pending records that
115. e Linked Documents MMUpdater now includes virtually every table in GoldMine that users normally access Other ease of use improvements Getting Help Technical Support Free support for installation and set up issues is available every business day between 8 30 and 5 00pm CST at 877 877 7212 This support does not include normal use training customization or report development support To report bugs or to inquire about functionality please send e mail to Support mastermine net or visit the Knowledge Base on our website at www mastermine net Report Writing MasterMine Software s services include online report development which can give you the most immediate possible results For the most efficient start have customized templates or reports set up for you by your GoldMine Solutions Partner or by MasterMine report engineers See our website or contact your local GoldMine Solutions Partner for details Training Set up and use of MasterMine to generate basic reports requires no special training However advanced reporting and integration of non GoldMine data can be somewhat trickier Some GoldMine Solutions Partners provide local classes or one on one training in MasterMine Further training is available directly from MasterMine Software Inc including online one on one courses or classroom training for larger groups See our website for details Installation Demo The demo allows you to experience dynamic reporti
116. e from the Logsheet Undo Changes If you have made updates to GoldMine as long as the Logsheet exists and undo has not yet been performed you can undo these changes in GoldMine To undo click again on the MMUpdater button on the MasterMine toolbar You can save the Logsheet within your MasterMine workbook as long as you wish That is you might save and close your workbook containing the Logsheet then open the worksheet some days later activate the Logsheet and again click on the MMUpdater icon on the MasterMine toolbar Ideas for MMUpdater tasks The most common process is to type over the data in cells you wish to update in GoldMine However if you apply some imagination you can use MMUpdater to accomplish many other useful functions Note that MMUpdater does NOT require you to update data in place That is you may insert a new column in your spreadsheet and use Excel formulas to construct an entirely new value from other data on the spreadsheet or a calculation of some kind If you do make sure you add a header in Row 1 and format that header the same as others in the row You may also update for example a Contact2 field with the total sales to a given client for a particular date range thus allowing GoldMine users to see within the contact record an accurate number representing total sales for the client This may also help you create some otherwise tricky reports since you can use such fields in a new MasterMine
117. e trouble with some templates or difficulty setting up your own Pivot Tables especially if you are working with an older version of Excel like Office 2000 or 97 outdated drivers may be an important cause Get the most recent service pack from Microsoft Fairly technical users may be already familiar with Transact SQL and so will be very comfortable operating in the MSQuery environment Even if you are such a user note that MSQuery has important limitations that may prevent using syntax that works in other situations It is often best to use the graphical interface rather than typing changes directly into the SQL text In this way the most MSQuery compatible syntax is automatically inserted If you find that something you ve done really should work but doesn t paid support is always available from MasterMine to get you unstuck Modifying Your Query Following is a series of functional instructions to achieve specific results with MSQuery that can t be achieved any other way We cannot provide a complete guide to using MSQuery although help is available directly from Microsoft Formal MasterMine training at the higher levels includes significant work with MSQuery in order to enable users to achieve almost any report configuration imaginable Accessing MSQuery 1 With your cursor anywhere on a PivotTable click the PivotTable Wizard button Ea on the MasterMine Toolbar OR on the MasterMine Toolbar 105 click the info
118. e with users of Office 2003 and earlier f While it is possible for users of Office 2003 to acquire a free add in from Microsoft that Preferences i 2 allows opening and reading xlsm files full compatibility between versions is not possible If you have Office 2007 or higher compatibility with earlier versions is optional Saving Pivot tables that use the higher memoty l mits a file in Office 2007 format may render it unusable by those with Office 2003 or earlier of Office 2007 become invalidated when Always save in Office 2003 or earlier format xls safer for sharing x opened in other versions Always save in Office 2007 format Ism most functionality Ask user each time which Office format to save to E Therefore when creating new reports with Office 2007 it is necessary to choose which version s format to store the report Typically if every computer that might be used to share MasterMine reports has Office 2007 you will want to automatically save every report as Office 2007 compatible Cancel Save and Close If only some workstations have been upgraded you may wish to select compatibility each time a new report is created You may set the preference to always create in Office 2003 format but note that this will limit your memory usage to Office 2003 limits and this may deprive you of some useful functionality 26 Preferences Defaults Preferences MasterMine of
119. ears these limitations have made each major release of Excel and therefore MasterMine even MORE useful Here s a summary of the specifications and limitations for the succession of Excel releases Read below for a full explanation of what these limitations actually mean in practice Limited Feature Excel 2000 Excel XP Excel 2003 Excel 2007 Max Unique Items per Field 8 000 32 768 32 768 1 Million for display Boy owed 64 000 64 000 64 000 1 Million Pivot Table Columns Allowed in Pivot Table 255 255 255 16 000 Maximum Number of No maximum No maximum No maximum No maximum Records Maximum Query Size 100MB Available RAM Available RAM Available RAM 32 768 32 768 32 768 1 Million Column Field Intersections possible Actual Actual Actual intersections intersections intersections intersections Row Field abbot A aa er ic MET possible available Intersections i intersections intersections intersections memory Pivot Tables ate best suited to display up to a few screens full of data at a time If they are wisely used they can almost always display what you need to see when you need to see it However they and by extension MasterMine are of limited use as a data dumping device as opposed to an analytical summary reporting or simple line listing device 159 Maximum Number of Records There is no fixed maximum number of records you can use when you create a Pivot
120. ed Item Completed that appears in the report to 28 com i 59 the field in GoldMine that 30 contains it In the image 2 here the user selected the E pivot item Completed within the field Call Result when calling up the information display The Display box reveals that the GoldMine field Result Code has a local name of Call Result in this report The latter may be a more meaningful label in the context of the current report but it helps to know what field it represents in GoldMine Likewise the item Completed is a report label for the value COM in the GoldMine Result Code field Drilldown Set user preferences to determine how different aspects of drilldown work 60 Do not format data or headers on drilldown sheet When the user double clicks any Data area element a new drilldown sheet is created showing in rows each record that comprises the element MasterMine adds some formatting to this native Excel process by passing through fieldname and label substitutions the user has added to the MasterMine Pivot Table Control Stats Drilldown connection Query Text Security About Drilldown From PivotTable to Line Listing Do not format data or headers on Drilldown sheet Drilldown from Excel to GoldMine Show sample of source row fields inside GoldMine Open new window in GoldMine For Found record Note Drildown preferences affect only the currently active
121. ed to select one Optimize Column Widths For selected Pivot Table or if there is only one Pivot Table automatically resets column width to accommodate display of all data on the table Chart For selected Pivot Table or if there is only one Pivot Table creates a simple chart for all data and innermost row and column of labels Excludes grand totals automatically Users need to further customize the chart by using standard Excel charting techniques Pivot Query Information Display Shows or hides the MasterMine Pivot Query Information Display for the current worksheet The MasterMine Pivot Query Display The MasterMine Display Dialog gives technical information about the pivot table you are using and the data query that underlies it You can use this information to better control how your pivot table displays information and to investigate connections between what you see in the report and what you see in GoldMine itself The Display dialog indicates the sources and criteria for the data that were queried to create the PivotTable on the sheet These criteria are set at the time the worksheet is created and may be changed by an advanced user in MS Query via this dialog 59 The Display is useful as a reminder of the source data being analyzed As such it helps prevent misinterpreting reports by giving backeround information that helps you trace the appearance of certain data in your report back to specific records in GoldMine The
122. ee taa ee 104 vii WORKING WITH MS QUERY cccessccesssseeeseeeeeesssnecessseeeeesseeceseseeecseeeeeesaeeeesesaeeessnsesesseeeeess 104 Technical Nolla A de dia 105 MODIFYING YOUR QUERY 22 nsinsnuenienenin nennen sank 105 Accessing MS QU ery a uice tet Re d to esta Bias Sao eee Dee ea 105 Resort Columns for Drilldown eese eren 106 Add Fields to QUeT ys 0 eene tU e tees 106 Apply SQL or dBase Functions and Expressions eese nene eene 106 Adding or Changing Selection Criteria esee nennen een een eene 107 Adding Tables ua iae ette SER RR deselea uaa deeper 108 Adding or Adjusting Joins i tei te Site da dte eto E t eie 108 Finishing Query Changes and Returning Data esee nennen 109 Formatting After Query Modification eese eene nennen 109 REDIRECT TO A NEW DATA SOURCE cooococccooconnconnconnconnconnconncnnncnno nono ronca nonn non n non ncnn cnn nro eene nns 110 MANAGING YOUR GOLDMINE BUSINESS ccscssscssscssssccssccsscssscssscscssssesssessssseseceseees 114 INTRODUCTION TO DATA MINING eese E eine OaS E ERORE R Eea EEEIEE E EEEE 114 Dat Mining Techniques dos 115 The Art of Presentation sida 115 DYNAMIC MANAGEMENT REPORTING essere rennen nen nenne trennen neret nacen 116 Creating Functional Workbooks eese eene nennen rennen nenne 116 Troubleshooting Reports nenn nen san e i ebenen 116 Clean up Afte
123. eets in Microsoft Excel are limited to 256 columns Because of this even if you successfullycreate a Pivot Table that contains a large number of column fields you may not be able to display the entire expanded Pivot Table Managing Row Fields The product of the number of items in all row fields in a Pivot Table cannot exceed 2 31 2 raised to the 31st power or approximately 2 1 billion items The same logic that applies to column fields also applies to row fields Assuming you wanted to build a report whose first several columns consisted of personal data such as names and phone numbers and that each name and number was unique in the report you could include the following before running into the 2 1 billion limit 3 columns of 1 285 unique items each 1 285 to the 32 power is less than 2 1Billion 4 columns of 215 unique items each 215 to the 4 power lt 2 1Billion 5 columns of 73 unique items each 73 to the 5 power lt 2 1 Billion 6 columns of 35 unique items each etc 35 to the 6 power lt 2 1 Billion Items per Field This is the most significant pivot table limit as far as reporting is concerned A field in the Page Row or Column areas of your pivot table may not contain more unique items than the limits shown in the table above If you attempt to pull in a field containing more items than he limit you will be shown an error message This limitation is the best reason to upgrade to a later version of Office if
124. egory of information you want to report or analyze and MasterMine presents it to you in the flexible analytical format of a pivot table Typically the process includes these steps e Select data category e Select fields to include optional e Sort fields for drill down presentation optional e Add macro filtering criteria optional e Hit finish ES Create New MasterMine Workbook Je These steps are explained in detail here File Toolkits SQL Query Preferences Help Which part of GoldMine s information do you want to see Select data categoty Contacts Design New C Use Template Y Main Select the part of GoldMine you wish to analyze y Additional or report The top left of the main screen shows soo the available areas in GoldMine from which you N Pendi xii can pull information These include at least History C Details C Opportunities ontacts Cases e Activities e Details Select fields to include v C1 ClAccountNo C1 Toll Free H E C1 Company C1 Linked To Contact C1FAX with subcategories under Contacts Main and C1 State C1 Last Name C1 Ext Additional and Activities Pending and History vV C1 Source C1 Dept C1 Ext2 v C1 Account Type C1 Title C1 Ext3 vi C1 Status C1 Suppt C1 Ext If your organization uses GoldMine v C1 MM Partner C1 PrimaryE mail C1 Address un q Y Cl Account Rep C1 PrimaryE mail Merge C1 Address2 Opportunities Projects or for GoldMine v
125. eld can be automatically grouped by several calendar units year quarter month date at once This allows you for example to quickly organize any report around year to year or month to month comparisons date ranges or single dates First make sure the date field you wish to Grpuping group is in either the Row or Column area of Auto your report Then select any date item in the starting at field and click the group button gt on the Ending at 8 20 2008 MasterMine toolbar Indicate your desired beginning and ending date for data to include and highlight the time grouping s desired If a Hire certain number of days like 7 for a week is Days desired for the group only days may be dol selected Hit OK to create new fields for all ee v highlighted date unit groupings By Minutes Now you can show or hide any of your new date sub groups including all records dated _ Cancel groups 8 before the Starting at date or after the Ending at date Simply uncheck or check them as desired in the dropdown list associated with the date field 83 Date grouping requires that ALL items in the field are of the datatype date not character number or blank The MasterMine query ensures this in most templates by substituting the date 01 01 1989 if the date field is null for a given record The user may set the substitute date in MasterMine s preferences
126. es MasterMine uses a 2 letter alias for each GoldMine table In the following template descriptions the GM Tables are referred to with these codes or aliases Most Common Tables Special use tables Alias Table Name Alias Table Name _ For C1 Contact RF Contact1 Referrals C2 Contact2 GS GSSites GoldSynch Sites CH ContHist GH GSSites GoldSynch Headers CA Cal AH InfoMine Knowledgebase CS ContSupp AL ContSupp Alarms OM OpMgr LD ContSupp Linked Documents UL UserLog UserLog SL SysLog System Log EM ContSupp E mail Addresses LK Lookup Lookups CG ContGrps Group Headers G2 ContGrps Group Members RI Resltems Company Resources 127 GoldMine Administrator s Toolkit DBM Toolkit xls Every GoldMine administrator needs tools to clean up the GoldMine database to find errors in data entry to streamline business processes and to clear up training issues In addition to the main reports of the Starter Toolkit he she should use these tools which are designed to help the GM administrator find data exceptions and monitor e mail address accuracy to fax via automated systems like FaxRush to synchronize via GoldSynch etc Suggestions about other administrative tools you d like to see are welcome at support mastermine net Workbook Name Description GM Tables Summary of records with and without e mail address Optimized E Mail for large databases but does not include C2 user de
127. esired click Filter at the bottom of the page to pre filter your query so that your workbooks can more elegantly pivot around only the part of data of interest to you Filtering options ate as follows Use Active GoldMine Filter If checked on and a filter is active in GoldMine that filter will be applied to the new quety The checkbox is disabled if no filter is active in GoldMine Create New MasterMine Workbook File Templates SQL Query Preferences Help Use GoldMine Group May be used GoldMine Pre Filtering Options in combination with GM filter This M No GoldMine filter Active powerful option limits the query to Use GoldMine Group I members of a GoldMine group After the report is created if membership in TIE the group changes and the report is Y All Key Field Values z H Al Values for Contact Type refreshed the new group membership V All Values for Status will be represented in the report All Values for Reseller All Values for Account Rep a All Values for Product Groups available in this list box are those for the current GoldMine user only If you wish to use another user s GoldMine group as the basis for your report clone the group to one under Filter on Key Values our own username Number of Matching Records Count y Note that multiple groups with m identical names may be combined into a single query by using this feature All contacts in groups with the same name are included Finish
128. et to Max or Min under Summarize by and click Number to format the date as desired Tools amp Skills for Formatting and Analyzing Once you ve created a MasterMine report you can begin to hone it to your specifications setting up groups and parameters to meet your daily information needs Filtering in the Page Area anything else to see a working report you can play around with Try these basic skills on your The easiest way to filter a report is simply to select an item in the dropdown list of any field or fields in the Page area of your report This limits the display area simple report to get used to them below to only those records for which the related page field contains the selected before moving on to create a real report field item This simultaneously creates a visual confirmation of the filtering which you will find very useful by displaying the selected filter value to the right of the field With this technique you can easily set multiple parameters for your report by selecting items within several fields in your Page area Beginning with Excel 2007 it is possible to select multiple items within a single field in the Page Area When you select multiple items a filter icon appears on the dropdown button next to the field so you know a filter using that field is active 74 Earlier versions of Excel include a technique available to filter multiple field items i
129. f records 52 A Record in GoldMine is considered to be a single company or account or contact unit The major screen consists of an Account Record that has many ancillary points of data tied to it In a database context a main Table called Contact1 contains one record per company But each company can also have many records associated with it contained in related tables like ContHist History Cal Pending etc In MasterMine we speak of Account rather than record to refer to a main company contact in the top part of the GoldMine contact screen This is because at the database level there are many Records associated with each account namely all of the history records each line on the History tab is a record in this sense calendar records supplementary contact records detail records etc that comprise the full body of data related to a single Account A Field in GoldMine is a single box on the data entry screen into which you can type an item of data In MasterMine a Field roughly corresponds to one of these boxes but since MasterMine is reporting across many GoldMine records each Field in MasterMine is really a container for many such items Field Items in MasterMine are the smallest units of data comprising the answers that get entered into fields in GoldMine In a MasterMine report dragging a Field into the lower area of the re
130. f these methods Manually Group Field Items Highlight the i items within a Row or Column field you wish to group and click the group button gt on the MasterMine toolbar You can highlight these items either by drag selecting them if they are next to each other or if the desired items ate not next to each other click the first item and control click the rest You can add more items to your highlighted selection by control clicking or control drag selecting additional ones BEFORE hitting the group button If items in this field have not previously been grouped MasterMine creates a new field button with the name of the old field button plus 2 and opens a group naming dialog showing the default name for the new group Group1 Overwrite Group1 with a name that reflects the group s function or leave it as Group1 You can treat the new grouped field button the same as any other field in the PivotTable If items in this field have already been grouped before you may either leave the selected items grouped into a new distinct group or you may add the selected items to another existing group by selecting that group in the list box instead of accepting the default genetic group name Group Field Items by Content Search Sometimes you need to isolate or to group items that contain a certain value or one of several values This is easy if there are only a few items in the field in question so you can 81 scan
131. faults to Alarms by User show only those with alarm CAC1 131 Sales Manager Toolkit SMToolkit xls Sales Managers need to monitor every aspect of their sales forces efforts to report to THEIR managers in order to train coach identify opportunities and successful techniques and share them to motivate and discipline the sales team These workbooks and the ones available in the more advanced Sales Tracking Toolkit give Sales Managers ability tool to easily get the necessary information to do their jobs successfully Workbook Name Description GM Tables Analysis of pending sales including probability schedule summaries and linked to basic client info such as source key Sales Forecast by User fields etc CAC1C2 Analysis of pending sales including probability schedule summaries and linked to basic client info such as source key Sales Forecast by Product fields etc CAC1C2 Sales History by User Analysis of completed sales by user CHC1C2 Sales History Current User ONLY Analysis of completed sales by user CHC1C2 Top Customers by overall Amount sold descending from the Top Customers top Rearrange to see by year month sales rep etc CHC1C2 Pending activities dated before today by user type of activity Past Due Pending Activity and standard contact information CAC1C2 Analysis of sales progress reflected in record of updates to key History Log Analysis fields CHC
132. fers a number of options regarding the initial setup of newly created MasterMine workbooks Users who have some experience with MasterMine can shortcut some of the formatting choices that would normally be made later in the process In setting your choices here bear in mind that doing so might leave users unawate of formatting choices that are available Hide page fields set to AIP General Toolkits Integration Compatibility Defaults New Report Defaults V Hide page fields set to All V Hide page fields with no valid items Many MasterMine reports start with a long list of fields in the Page filtering area of the report The user is Turn off subtotals for all fields expected to make filtering selections ot N Substitute blank space for blank in field items to pull these fields into the display area IV Use Logo C My Documents My Pictures MM Logo MMsw_BW bmp of the report then hide the Load Behavior remaining fields in the Pivot Table IV Always save SOL password in new reports Field List Leaving this item IV Always dock toolbar on report open unchecked produces this result Hide fields list on report open V Document visible during refresh on open Checking this item causes all page Close Dialog After Report Created fields for which thete is no set default Excel Visible During Creation of Report selection to be automatically hidden This produces a cleaner report with Ca
133. fined fields C1EM E mail addresses linked to contacts and contact2 user defined E Mail with C2 fields Does NOT include records that have no e mail address C1EMC2 E mail addresses linked to contacts and contact2 user defined fields INCLUDES records that have no e mail address May be E Mail with Contact2 slow on large databases C1EMC2 WebSites Summary of records with and without website C1CS Linked Documents Linked Documents plus Contact info LDC1C2 User Log Report Hours logged in GoldMine UL Open query on all Contact records Analysis based on territory Contacts Inner Joined region other key fields and user defined Contact2 fields C1C2 Missing C2 Detects Contact records with missing Contact2 C1C2 Pending unlinked and unsent faxes FX or FAX in UserID Faxes Pending FaxRush standard CA Analysis of completed faxes both individual and mass faxes Faxes Sent Result code is FAX FaxRush standard CH All Contact records and their associated Details Includes Contacts and linked Details contacts with NO Details profiles C1CS All Accounts and linked All Contact records and any pending activities associated with pending them Includes contacts with NO pending activity C1CA No Pending Activity Contacts with no pending activities nothing scheduled C1XXC2 All Contact records and history associated with them Includes All Accounts and linked history contacts with NO history C1CH 129
134. g a row by tow listing of the target records You may wish to aggregate records from several different cells in your pivot table into a single GoldMine group Do this by grouping items see Grouping Field Items page 79 within the pivot table to bring them all into a single subtotal then drilling down on the subtotal Alternatively you can drill down on separate items creating separate drilldown sheets then copy paste data from one sheet to another If you use this method be sure to create only one header row in row 1 Xj Once you have created a drilldown sheet displaying your MasterMine will create a new group in GoldMine out of the records on this page Please supply a valid GoldMine group name below target records simply hit the Group button pfi on the MasterMine toolbar and specify a name for the group You may optionally specify a field only if it is part of the original query that will be used to sort the group members as well as Group Name E mail These a field whose items can help you to reference records once Sort Members By Reference Field Group Code Assign to User OnDate None you return to GoldMine Optional Optional Group Selected Rows If you want to build a group on only some of the rows represented in your MasterMine drilldown screen highlight the rows or individual cells in only one column for the rows to be included MasterMine will detect that you have done so and b
135. g changes and this requires a change in the GoldMine master serial number a new MasterMine license must be obtained from MasterMine Software Inc website www mastermine net Click License Now dialog read the instructions and click Continue to launch the licensing gt Check to make sure the GoldMine serial number displayed is the correct one for the MasterMine license you ate about Please enter a unique MasterMine serial number for to enter If so enter the numbers precisely as they are given this workstation GoldMine S N 15 39144252 0 on your license certificate or as provided by your GoldMine administrator When finished click the Register button MasterMine S N If you have one of MasterMine s old Site Licenses this product is no longer sold the final box of the Register Use Demo Help Register Cancel dialog will contain SL If you have a named user license your license number will be sequentially assigned to users Therefore if you have 5 user MasterMine license each successive user will enter their license entering 01 02 or 03 etc into the last box so that there is no overlap between users If a user attempts to license MasterMine using a sequence number that was already assigned the dialog will respond that the license number is already in use and that a unique number should be entered The computer will store a in the fina
136. g in There are many approaches to doing this For instance the manager could drag the region field to the page atea and select MW then pull the UserID field into the report This would create a view ONLY of the MW region preventing the person from comparing it conveniently with other regions Another option is simply to Roll Down the MW field item by double clicking on MW A dialog asks which type of further detail to show for the MW field In the dialog User IVZ ACIVe Reps m 1 manager sel rl lick CETTE e anager selects UserID and clicks Amount Status 0 0 0 ft Region v UserlD Active Inactive Dead Grand Total CN 62 352 61 5 393 80 485 00 7313141 Now the manager has an in context Ne CAIL Bo zu EM breakdown of MW by UserID that still LAURIE 62 006 19 3 588 00 PENNY 11 750 71 160 00 allows him or her to see the ROB 26 575 17 995 00 995 00 proportionality between individual MW Total 106 750 78 4 843 00 1 394 00 NE 142 373 54 7 485 00 103 075 01 Users performance against one another NW 17 745 80 5 943 00 7317 80 SE 87 730 30 27 797 60 32 116 25 AND against other ADIOS so 56 361 43 20 805 22 16 091 00 SW 114 209 12 8747 07 22 384 95 341 The manaset can then proceed to toll Grand Total 587 523 68 82 014 69 186 764 01 856 302 38 E P down additional Regions again by double clicking on each region as needed He or she can roll them up again by yet again double click
137. g reports to the same server on which the data resides If re hosting from dBase to SQL Plan to re create your reports from scratch as the underlying query needs to be created in a completely different syntax than the one in which it started While it is technically possible to preserve an existing report and replace the back end query with a new one this method contains many potential pitfalls and should only be handled by an experienced technician Contact MasterMine Software if you wish us to do this for you With these suggestions in place here are some technical notes that may assist you in redirecting your data pointer within an existing MasterMine report If you followed the advice above and the only change in your data location is the SQL Server name you can easily redirect reports by opening the Information Display Dialog see The MasterMine Pivot Query Display page 59 and going to the Connection tab On this tab overwrite the old Server name value with a new one and click Apply Changes below When you next refresh your report is current and can be saved in the same location as the old one 110 If you changed both the GoldMine database name SQL and the path to the GoldMine data dBase you need to change both in the underlying query We recommend calling MasterMine for online help to do something this significant 111 112 113 Managing Your GoldMine Business MasterMine reports can be used
138. g unit sales times probability Weighted sales projections 155 Special Fields cont d ContHist table of completed activities Field Name Contains Useful For Featured in these toolkit reports OnDay Day of the week on which activity took Building calendar type tables History Calendar lace DaysSince Number of days since event occurred Ageing prioritizing activities LogUpdate Designates how many fields were updated History Log reporting works best if all LOG History Log at once and recorded in a history LOG records are 1 s Use this field to multiple record lupdate LOG records LogField Field whose updating is recorded in a Tracking sales progress and general History Log LOG record in history contact updates LogChange Change that was made to LogField and Tracking sales progress and general History Log recorded in LOG record contact updates CH Contact Person actually contacted Blank if none Seeing who was actually contacted given in history record CH or Person actually contacted Contains Tracking actual contact instead of merely C1Contact Contact field from Contacti ifthereis the lead contact from Contact1 none given in the ContHist record HasNotes Yes or No if there are notes in the Tracking level of activity data entry by All tables with ContHist data Contact record creator of history record Minutes Duration f
139. he report A ay Sort Field Items Sorts items in the selected field in ascending alphabetical order by contents of the field x Delete All Worksheets named Sheet Deletes drill down sheets still generically named This helps clean up a report where many drilldowns have been performed 58 Al worksheets named Log MM DD YYYY Deletes logsheets created by I d x E m T o Eg MMUpdater During intensive updating a user may create many Log sheets that are not needed once the job is complete Other Toolbar Buttons Group Field Items This button groups selected field items and creates a new field in which the new group is one item Many enhancements on this item make this the preferred method of grouping compared to Excel s native group function See Grouping Field Items page 79 Ungroup Field Items Ungroups selected group Same as PivotTable s normal Ungroup function Search Field Items Search a field for specific elements or words All field items in the group containing the sought elements are grouped and moved to the top of the field containing it This is an excellent way to find lost items as well as to group large numbers of records according to content See Content Search page 79 Freeze Panes Freezes panes on the worksheet so that the top row and column of Pivot Table are the crux of the frozen pane If there are several Pivot Tables and none is selected user is prompt
140. he list at the logical location according to the sort New items in a field that is under Manual Sort will appear at the end of the list 77 Roll up Roll down With multiple fields in the Row or Column areas double clicking any field item Rolls down or Rolls up the detail for that item This simple but powerful activity gives you the ability to summarize OR show greater detail in an instant Z User IDZ ACTIVE Reps m amp M i3 Reaiond North America Region North America Ej The pivot table at left is an ordinary two 4 dimensional table with one field Region in 5 Amount the Row Area and one field Status in the 6 Region Inactive Grand Total Col An sine R 1 I7 CN 6 393 80 4 385 00 73 131 41 OIE ATEA TUBIS SIEG DUE Te DOE S qupd dE IB MW 106 750 78 4843 00 1 394 00 112 987 78 representation of sales broken out by i9 NE 142 373 64 7 485 00 103 075 01 25293365 region and by account status 20 NW 17 745 80 5 943 00 7 317 80 31 006 60 M SE 87 730 30 27 797 60 32 116 25 147 644 15 2 SO 56 361 43 20 805 22 16 091 00 9325765 Because there are multiple sales agents 23 SW 114 209 12 8 747 07 22 384 95 145 341 14 operating in each region the user a sales 24 Grand Total 587 523 68 82 014 69 186 764 01 85630238 manager perhaps may want to know how one particular region MW breaks out by UserID thus answering the question Within the MW region how much sales revenue is each user bringin
141. hend large amounts of data Even if the executive learns nothing else about MasterMine reporting these two skills alone are enough to provide key levers to manage their business through GoldMine Grouping Field Items Grouping items within your report is one of the most powerful and useful features of a pivot table With Groupings you can isolate display or compare not just data elements but whole subsets of data that are meaningful to you and to re use those subsets in a vatiety of ways You can build Groups on the fly in seconds using the following methods One common example is grouping UserIDs by Sales region or division so that even if those users ate not formally grouped or otherwise associated within GoldMine your reports can better reflect reality Without grouping the items in your report can only be summarized by values actually contained in the database For example a call report that shows each user s calls broken out by result code might look like this Y Aa y 4 SOO Oo ea a Ban 25 Grand Total 2h 79 Grouping these items logically allows us to add a great deal of meaning to our report even though the GoldMine database itself does not contain groupings of users or of Result Codes First we group users by East and West according to their areas of responsibility In this case we used Manual Grouping described below UTIVYEER AII tuf Account Rep Al C1Acc
142. icals 1 1 2 37 Grand Total 8 30 37 39 23 34 19 36 28 254 an The Hidden area contains all available fields including additional page fields not displayed on the worksheet The latter may be added to the table unhidden by dragging them from the toolbar Office 2000 or Pivot Table field list Office XP and higher to the desired Pivot Table area Fields appear bold in the hidden area if they are also displayed in the pivot table or normal font if they are not 54 Field Buttons in row column and page areas have a gray backeround and usually correspond to specific fields in GoldMine database files Not all GoldMine fields are available in MasterMine templates but advanced users can add any field or portion of a field For a full list of available fields see the GoldMine Database Layout Reference in the back of this manual Field Colors Note that the Field Button Color in MasterMine tables reflects the underlying data files from which the fields come The color scheme is as follows Blue Contactl Red Contact2 Green Cal Brown ContHist Pink ContSupp Orange OpMgr Items are the contents of fields and are visible with white backgrounds when their fields are present in the Row or Column areas or by clicking the list box associated with the field in the page area Usually an item represents a summary of all occurrences of that item in a given GoldMine field 55 56 The MasterMine Toolbar Man
143. ield converted to a decimal Tracking time on phone time doing Calls Completed Call Value number of minutes various activities timesheets billing records etc Hours Duration field converted to decimal Tracking time on phone time doing Timesheet number of hours various activities timesheets billing records etc Days2Sale Days difference between the date of sale Calculating sales cycle tracking followup Sales Cycle OnDate and date the sale record was created as a pending record CreateOn by sales reps 156 Appendix G Other Field Contents and Conventions e OnDate is pre grouped into Year Month Day and Week groups and appropriately renamed Other Date fields can be similarly grouped by the end user e Dates appear as 1 1 1989 if they are null This enables grouping by month year etc Records appearing under this date are good candidates for data integrity checking e Special fields Reports involving an outer join where all records from Cal or ContHist file are included and ONLY those in Contactl or Contact2 where there is an ACCOUNTNO match have a field called Linked to Contact A Yes in this field indicates that the target record is linked to the selected contact set By default this field is set to Yes in templates if there are any linked records at all Report results can be misleading if the user fails to pay attention to this field s setting e RECTYPE in Cal and
144. iif OM StartDate is null NullDate OM Opp StartDate Coalesce OM StartDate NullDate OM StartDate iif OM ClosedDate is null NullDate OM Opp CloseDate Coalesce OM ClosedDate NullDate OM ClosedDate iif OM CloseBy is null NullDate OM Opp CloseBy Coalesce OM CloseBy NullDate OM CloseBy Datediff day Coalesce OM StartDate Nul date iif OM StartDate is null OM Age IDate getdate NullDate OM StartDate Datediff day Coalesce OM StartDate Nul iif OM ClosedDate is null NullDate StartDateTo Actual IDate OM ClosedDate iif OM StartDate is OM Close Coalesce OM ClosedDate NullDate null NullDate OM StartDate Datediff day Coalesce OM StartDate Nul iif OM CloseBy is null NullDate StartDateTo IDate OM CloseBy iif OM StartDate is null OM Projected Close Coalesce OM CloseBy NullDate NullDate OM StartDate iif OM ForAmt Is OM Opp Forecast Coalesce Convert Real OM ForAmt 0 Null O val OM ForAmt OM Opp Units Coalesce Convert Real OM F3 0 iif OM F3 Is Null 0 val OM F3 iif OM ForProb Is OM Opp Probability Coalesce Convert Real OM ForProb 0 Null O val OM ForProb 147 Source MM Field Name SQLExpression dBase Expression Coalesce Convert Real iif OM ForAmt Is Opp OM ForAmt 0 Coalesce Convert Real Null 0 val OM ForAmt iif OM ForPro
145. ine Pivot Table Control Note that if you hit this PivotTable toolbar button and the cursor Maul is not on a PivotTable the PivotTable Wizard will attempt to create a new table with a new query Stats Drildown Connection Query Text Security about Security Parameters for Current Workbook Security Selections will affect all pivot tables in this workbook 7 To prevent tampering or to disallow access to sensitive data in GoldMine the report designer may disable and or password protect certain capabilities Enter a password to prevent other users from altering the report Enter password to protect selection Disable data refresh Disable drilldown Do not save data with Excel workbook IV Save SQL password with pivot table Disable access to Pivot Table Wizard MSQuery Prevents users from tampering with the underlying quety thereby gaining access to data they may otherwise be curtained from in GoldMine See Working With MS Query chapter 6 Save 62 Disable data refresh Prevents users from updating the report with current GoldMine data Disable drilldown Prevents users from double clicking on data area numbers to see the underlying information in a line listing format Do not save data with Excel workbook Reduces size of MasterMine workbooks to as little as a few KB Also prevents GoldMine data from being stored within the report thereby increasing security of your data Save SQL passwor
146. ine users simply by sending them around as e mail attachments or by deploying them as explained below Deploy Reports to Other Users 125 26 You will probably create several different versions of this report for various users in your organization Although based on the same information the report needed by an individual sales rep is different from that needed by the sales manager and still different from what top executive management needs As you ve seen making variations on a single report is very easy by repeatedly cloning and reorganizing So SHARE appropriate versions of the reports as you make them Simply click the button on the MasterMine toolbar in Excel Then enter the user and name of GoldMine taskbar to deploy to Several security and convenience options are included See Report Deployment toolbar button on page 63 End Session and Return 27 Save your changes when you exit Excel When you want to return just select Open MasterMine Report on the GoldMine View menu If you return on a later day be sure to Refresh the data so that your report is always current You do not need to keep track of where you put your report workbooks as long as you use the default save to location The Excel worksheet we have been using was automatically saved under the name Sales Pending xls in the path directly beneath the target GoldMine contact set unless you specified another location before creating
147. ing on the same region names This selective roll up roll down feature should be used actively by any end user as a means to quickly understand large amounts of data and to present it for others in the most revealing fashion Combine Rollup Rolldown with Item Grouping The above example shows the detail you see when rolling down the items of a second field UserID from the database This is very useful However you can also create groups within a single field for example grouping Sales Rep field items the sales reps into Regions even though you don t have a field in your database called Regions Even 9 10 1 2 3 4 5 EE 16 HE 18 9 gt es 1 2 23 24 1 1 1 2 2 2 UTIVVBEK Account Rep THERESA TRISHA VAN AII Result Code wh UserlD y COM INP PRO LMI LM2 LM3 Grand Total though it doesn t exist as a field in the database this allows you to treat Region as a field in your report with all the capabilities of a real data field As you learn about grouping field items in the next section consider how the Rollup Rolldown capability combined with this type of grouping creates a powerful interactive tool for executive level management It frees the user to see information summarized in full detail or at any level in between Combine Rollup Rolldown capability with skillful grouping to create components for a digital dashboard that any executive can easily use to compre
148. is a good idea to double check your results against your old static reports to be sure that you re getting what you think you are If there is a lack of agreement between a MasterMine spreadsheet and a static report here are several possible items to check e Make sure data is refreshed 116 e Check the selection criteria in the MasterMine display box for the query against the parameters for your report e Check the selection criteria in the page area of the table e Check to see if you have hidden items in any of your fields e Check the logic of your table joins see Adding or Adjusting Joins in Chapter 6 Clean up After Yourself Drilling down and using Show Pages can add many sheets to your workbook in a hurry Remember to select these sheets and delete them before you finish working Otherwise you may find that your system RAM is quickly used up when you load the workbook again Presentation Features Excel provides a number of formatting and display features that can help make very impressive presentations from MasterMine reports You can learn about them in depth from the Excel help menu or by using a number of fine books on Excel AutoFormatting Once your data is arranged as you like it you can select the most impressive format for presentation Start by clicking anywhere on the Pivot Table and select menu Format Autoformat Now you can choose from 16 different pre defined formats for your data or at the
149. ith reports from MasterMine templates but bear in mind that your own workbooks contain the aggregate effort you have put into them over 104 multiple sessions including your own groupings data labels etc If you change the query that underlies any Pivot Table and get an improper result and then save your workbook you will not be able to recover the lost report without rebuilding it yourself This is the only way you can ruin an otherwise functional report in MasterMine If you are not confident in your skills with MSQuery we encourage you to seek further MasterMine training either with your GoldMine Solutions Partner or from MasterMine directly or have us prepare some customized templates for you Technical Note MSQuery is a wizard for building query statements using a simplified set of SQL System Query Language conventions called Transact SQL The syntax of your Transact SQL statements will vary depending on the underlying database type GoldMine uses The currently supported GoldMine versions v7 0 and higher use Microsoft SQL or a variant SQL Express or MSDE depending on SQL version as its database engine Older versions of GoldMine used dBase as a database engine so MasterMine was built to work as seamlessly as possible with both back ends MSQuery uses ODBC Open DataBase Connectivity data drivers which are supplied with various releases and service packs of MS Office to connect to the GoldMine database If you hav
150. ith the new report in Excel Now the hard part is done To come up with the report you want you now need to organize and format it as desired That s it With these few simple steps you are now able to extract a large portion of the most interesting information from GoldMine Here s where the fun begins 48 Once you ve selected your criteria and hit Finish the data are extracted from GoldMine and presented for further arranging and formatting in your Excel environment Before you go on however consider these thoughts about the selections you make in the MasterMine interface The fewer files and fields that are referenced the quicker your data refreshes This is a very important consideration if your data files are large If you are working with more than 100 000 records in yout queried files be sute you have at least 100MB of unused RAM available Otherwise data refreshing and re calculating can be very slow A sure sign of memory shortage is if your hard drive seems to be working very hard during a long data loading refreshing process It s probably swapping data with your memory which is a tedious and unnecessaty process Getting more RAM is well worth it The end of this guide includes a handy reference see page 120 that tells you the fields and functions contained in various GoldMine files Excel 2000 and earlier can display only fields containing fewer than about 9000 unique values This DOES N
151. k Manufacturing Media Customer Referral 36 Brandy Smith Maldensen Products Media Word Of Mouth 1 TT geen do PP tit etm Anad dea mbl ul snsuttants y STU a LLL ns Drill down Report Another type of Line Listing report can be created instantly by simply double clicking on any data area number This automatically creates a new worksheet and your data no longer in the form of a pivot table appears in rows going down the page You can think of this report in two ways as a raw line by line listing of the contents of the drilled down part of the pivot table report or as another report formatted differently looking at a segment of data from the pivot table This report shows all rows of your report s underlying query that underlie the double clicked Data Area number Every field from your query is included in this query with the more interesting fields toward the left of each row and the more technical fields like Accountno and RecordID towatd the right 71 D J K par CreateOn C1AccountNo Bi Ema 2 Tara Tooley Orthodontic Solutions SC Radio Advertising Suspect Aviation Gertrude SE 12 7 2005 99120926269 D 1X Tar_ C tooley orthoso 3 Elizabeth Roberts Abacor Inc NC Customer Referral Suspect Aviation Tammy SE 3 25 2005 99102131609 4 P A elizabethp abz 4 Brooks Barbee Triangle Technology NC
152. king all other types of activity produces a sales oriented report such as Forecasted Sales or Completed Sales Sales reports are amount based but also allow you to track counts units and probabilities as appropriate 36 Scenarios A few examples of how to make selections in the Design New dialog If you want a report showing Then select Area Button Record Type Forecasted sales for the coming year Pending Sale The past month s calls and appointments History Calls compared to other months Appointments All activity associated with any customer Pending All shown in one place History or All except e mail A report of custom profile or detail Details Select the name of records by any name the profile A list of contacts both main and Main none use supplementary that meet certain criteria Additional filter button Another option for quickly reaching your goal is to use one of MasterMine s built in report templates which are designed to anticipate reporting or specialized query needs that users frequently encounter when working with GoldMine The next section covers the use of these invaluable tools Use Template MasterMine has more than 125 built in report templates each of which instantly branches off to literally thousands of variations as needed Using these templates wisely can get you the results you need instantly to without needing to build every re
153. king on the icon Welcome screen Click Next gt License Agreement If you accept click I accept the terms in the license agreement then hit Next gt Readme Information Read about support and report writing options then click Next gt Choose Destination Location Specify your preferred destination directory for MasterMine s program files and Click Next gt Ready to Install Click Install InstallShield Wizard Completed Click Finish After a brief pause you should see a dialog that looks like this 14 GoldMine Initial Settings BAX This information will be used to set up MasterMine to work most conveniently with your GoldMine usage These settings can be changed in the Preferences menu within the MasterMine Create Report dialog GoldMine Partner Name for support IM asterMine Software Inc GoldMine Partner Phone 1 877 877 7212 toll free Do you use GoldMine Opportunities No C Yes and each Opportunity is linked to a GoldMine contact most common Yes and Opportunities are independent of GoldMine contacts except for Influencer and Team tabs Do you use GoldMine Projects No C Yes and each Project is linked to a GoldMine contact most common Yes and Projects are independent of GoldMine contacts except for Contacts and Team tabs Do you use GoldMine Cases Premium Edition or higher only No C Yes cora 10 Enter GoldMine
154. l box until a valid unused license number is substituted Convenience If your MasterMine license number is in your copy paste buffer you can enter it all at once by right clicking on the first box and clicking paste Shortcuts for multiple MasterMine installations 1 Installing on Multiple Machines 19 You can expedite the process of installing MasterMine on a number of machines with the same non standard INI parameters Do this if 1 you have a SQL installation with multiple MasterMine users OR 2 you ate a GoldMine Solutions Partner and you wish to have clients call you instead of MasterMine when they are ready to license the software In this case update the VARName and VARPhone fields with your own information before installing generally Copy a MMINI xls with your preferred settings into the same folder as the mm5setup exe on the CD desktop or other folder from which you launch it then run the setup as normal MasterMine will detect the presence of your customized MMINI and substitute it for the default one 2 Installing for Multiple Users on One Machine In a site license situation you have as many MasterMine licenses as GoldMine licenses multiple users can use MasterMine on the same machine launching while logged into their own GoldMine account To set up the necessary View menu items after MasterMine has already been installed on the workstation log into GoldMine under the new user s username Hit
155. l take advantage of much of MasterMine s dynamism and GoldMine integration functionality Share users are able to do everything a full user can do within access limits set by the creator of each report For example if you have a 15 user GoldMine license but only 5 users docked and undocked need to make reports while most users simply open and view standard reports you may purchase a 10 user Share license for the majority of users 10 Site License Users of MasterMine versions earlier than V6 0 could purchase a MasterMine license that matched their GoldMine seat count while saving on the per seat cost To serve these legacy customers MasterMine site license seats may be purchased for an existing site license but new site licenses are no longer sold The site license option gives full MasterMine functionality to all users although access can be limited at installation if desired As a result of the requirement to match GoldMine seat counts as the GoldMine seat count increases MasterMine will cease to function until additional MasterMine site license seats are installed to match the GoldMine seat count Share Site License For ease of implementation MasterMine offers a centrally administered Share Site License which allows all users in GoldMine to open manipulate save and SaveAs existing reports subject to limitations you administer For a single price all GoldMine users who have MasterMine i
156. ld from completed or pending activities as GoldMine stores it MasterMine converts this field into a numeric field called Hours and another numeric field called Minutes which can be easily grouped and summed for time based reporting This manual includes a fairly comprehensive description of the GoldMine data structure in the References section at the end Obviously the references here cannot include fields unique to your customized GoldMine installation In general note that Contact level user defined fields are found in the Contact2 table and their database names begin with the letter U MasterMine also includes fields from user defined Profiles Details and from the Opportunity Manager and Project Manager tables Depending on your access rights in GoldMine you can Ctrl DoubleClick on a field in the main GoldMine screen to see that field s database name and definable attributes Why limit fields to include System performance is directly affected by the quantity of data both number of fields and number of records from which those fields will be drawn With a fast machine and Office XP or higher you are limited only by the available RAM on your computer and to some extent by the potential complexity of your displayed data see page 158 In Office 2000 and earlier Excel reserved only 100 MB of RAM for a given pivot table query so bear this limitation in mind when selecting your fields Excel 2000 users with a ve
157. like AND CH Userid JOHN This would permanently limit the current report not the next usage of the template which would revert to normal to include only records completed by JOHN Limitations to your tweaking of the SQL query in Transact SQL 1 You may not alter the number or sequence of fields in the query expression 2 You may not create an outside join if more than two tables are being joined 3 Generally your query syntax is constrained to that which is readable by MSQuery as it accesses your SQL or dBase data using the applicable Microsoft Data Access Components MDAC ODBC drivers Hit Finish And The Rest Is Fine Tuning When you have specified which part of your GoldMine information you want to see in general and how to filter it if necessary hit the Finish button MasterMine will now extract your selected information from GoldMine gather it into a pivot table object in Excel and present it for your further arrangement and formatting This should take only a few seconds per thousand records queried Progress Box MasterMine displays a progress box while creating the report loading the necessary data from GoldMine and naming field buttons so far as possible according to your customized GoldMine setup Please wait MasterMine is constructing your report Adding Field Labels amp Color a When finished it tells you where your report has been stored Click OK to begin working w
158. ll 1 1 1989 C2 DateField Coalesce C2 DateField 1 1 1989 C2 DateField CA Activity Type CA RECTYPE CA RECTYPE CA Activity Company Rtrim CA COMPANY CA COMPANY CA Activity Contact Rtrim CA COMPANY CA COMPANY if CA ONDATE Is CA OnDate Coalesce CA OnDate NullDate Null NullDate CA ONDATE substring datename weekday Coalesce CA OnDay CA OnDate NullDate 1 3 WeekDay CA OnDate CA OnTime CA OnTime CA OnTime CA OnHour substring CA OnTime 1 2 left CA OnTime 2 iif CA OnDate is null NullDate CA DaysTill Datediff day getdate CA OnDate CA OnDate date CA User ID Rtrim CA USERID CA USERID CA Activity Code Rtrim CA ACTVCODE CA ACTVCODE substring rtrim CA Ref 1 CA Short Reference ShortRefLen left CA Ref ShortRefLen CA Product Rtrim CA REF CA REF CA Reference Rtrim CA REF CA REF if CA ONDATE Is CA OnWeek Coalesce CA OnDate NullDate Null NullDate CA ONDATE iif CA duration is null 0 Convert float Coalesce CA Duration 0 iif CA RECTYPE lt gt S 0 CA duration 10 CA Probability 100 0 CA Duration Coalesce CA Duration 0 iif CA duration Is Null O CA DURATION CA Meeting Confirm CA ACONFIRM CA ACONFIRM CA Appointment User Rtrim CA APPTUSER CA APPTUSER iif CA status Is Null CA HasNotes Coalesce substring CA STATUS 2 1 0 mid CA STATUS 2 CA CANotes udfDBO udfMMStripHTML CA NOTES CA NOTES substring udfDBO udfMMStripHT ML C CA CANotes2 A NOTES 254 253 mid CA NOTES 254 sub
159. lumns and rows 85 Data Options EE 86 MORE TIPS TRICKS IN EXCEL ns tia tiritas aiii andara 86 Branch Off From Existing Reports ormsins sasainn nennen rennen nnne 66 Ato Refresh nn etu ett eO rete aes eti led etes te a 67 Set Filters Across Worksheets Page Selection eese nennen 87 Working with NOLES cursante dada 88 DATA MANAGEMENT EEE conecdesscsesscvensestopescnsveneedesvotsodesv ncesesssenscestcnessusseses 90 DRILL DO ER 90 DRILL THROUGH TO POP GOLDMINE RECORDS essere entente nnne nnne 90 CREATE AND USE GOLDMINE GROUPBS eese nennen nnetnne inner innen innen ennnt enne enne 91 MMUPBDATER u ee terio eee till M ce A 93 MMU pdater Options ita a e e A ete e ded tds 94 MMUpdater Logsheet J 3 esta tent e ob dett e t e ttd 95 Undo Changes stt ars eut en 95 Ide s for MMOpdater tasks tt e o eet eite tution 95 DEPLOYING SHARING AND MANAGING REPORTS ee eeeee eee eee eere neenon etna senno 98 DON T DISTRIBUTE DEPLOY a race 98 SHARING REPORTS des 99 OPENING EXISTING REPORTS ui tias 99 Open via Windows dial g ni iaa 100 Launch from a GoldMine Taskbar eese eene een rennen 100 REFRESHING REPORTS nun nenn SEV EXTR eR Ue Ve vh aim nannten 100 COPY AND SEND REPORTS OUTSIDE OF MASTERMINE eee nennen 101 MODIFY UNDERLYING QUERY eee eeeee eee esee eno seen seen seta setas etas etas eta set ea seen
160. lus fields in the field list box as though they were native to GoldMine Integrating QuoteWerks Quote management software by Aspire Technologies www quotewerks com QuoteWerks is a popular stand alone or GoldMine integrated quoting and order processing application QuoteWerks enables companies to handle much more sophisticated quoting forecasting and order processing models than native GoldMine can support MasterMine reads QuoteWerks v4 0 or higher SQL only document data allowing QuoteWerks users to perform simple or sophisticated analysis ES Create New MasterMine Workbook File Templates SQL Query Preferences Help Quotewerks Toolkit O Ue Tables OH of any QuoteWerks data even in conjunction with linked GoldMine account data Detail Level Quotes GoldMine linked Quotes by Item To Query Quotes by Item GoldMine linked Integrate QuoteWerks launch the Description Q ders DR Preferences menu in MasterMine s Create Orders GoldMine linked ae ae Orders by Item ds New Analysis Workbook dialog Click on the Orders by Item GoldMine linked Integration tab In the Quote Werks section check the box to Enable then browse to ot enter the path to the QuoteWerks application Y OH BillToCompany v QH Result icall is 1 folder nam methine like v GH BillToContact v DH SalesRep Typ a this S a folder named some 18 v GH CompletionD ate Y QH ShipDate C Program Files QuoteWerks Next in the
161. m PI USERID PLUSERID Pl Issue Notes udfDBO udfMMStripHTML PILNOTES PILNOTES substring udfDBO udfMMStripHTML PI PI Issue Notes2 NOTES 254 253 mid PI NOTES 254 substring udfDBO udfMMStripHTML PI Pl Issue Notes3 NOTES 507 253 mid PI NOTES 507 151 Source MM Field Name SQLExpression dBase Expression substring udfDBO udfMMStripHTML PI PI Issue Notes4 NOTES 760 253 mid PI NOTES 760 substring udfDBO udfMMStripHTML PI PI Issue Notes5 NOTES 1013 253 mid PI NOTES 1013 PI PlAccountNo PI ACCOUNTNO PI ACCOUNTNO PI IssueRecords PI RECID PI RECID PC PCRectype Rtrim PC RECTYPE PC RECTYPE PC ContactCompany Rtrim PC COMPANY PC COMPANY PC ContactName Rtrim PC CONTACT PC CONTACT PC ContactTitle Rtrim PC CYCLE PC CYCLE PC ContactRole Rtrim PC STATUS PC STATUS PC PCAccountNo PC ACCOUNTNO PC ACCOUNTNO PC ContactRecords PC RECID PC RECID 152 Appendix E A Note on Notes Notes fields in GoldMine ate of unlimited length which poses a challenge to users who want to work with them in reporting or analysis In order to make them manageable MasterMine extracts Notes fields from your data in sequentially numbered segments each 253 bytes characters long These segments with names like CS Notes CSNotes2 CSNotes3 etc must remain contiguous in the MasterMine query in order for MasterMine to handle them properly as de
162. n creating reports anew every time they ate needed Because MasterMine gives end users much more flexibility even with the most basic form of each report that is the ability to pivot and drill down you have several options for deployment Each MasterMine report has both the path to the GoldMine data stored within it and the ability to be automatically refreshed Being aware of this relationship helps you choose wisely among the following options Don t distribute Deploy Too many organizations bottleneck all reporting through a single highly trained or not report writing person or department This results in only a handful of people getting information when they need it The rest of the people depend on the availability of this person as well as on their own ability to accurately describe information needed the report writer s ability to correctly interpret it and other dubious factors to get a report One of MasterMine s big advantages is that it breaks this bottleneck putting much more power and flexibility into the hands of the end user who needs the information The key is to send a live refreshable changeable report to the user so he or she can run their own report as needed with a simple button click Since anyone can learn MasterMine s most basic moves refresh drill through any user can turn a single simple report deployed to them by a Full Licensed user into a wealth of information that is li
163. n several parallel columns instead of one long one This can be very useful if you like to keep a lot of fields in your Page Area for easy access Data Options Save Data with Table Layout Unchecking this box saves disk space when saving large Pivot Table reports However when reloading you will always have to explicitly refresh data before you can see anything unless you use Refresh on Open This option automatically refreshes the PivotTable upon opening In the case of large source files this can make loading a long process For reports that you open several times a day and don t need up to the minute data it s best to leave this one unchecked Refresh Every __ Minutes Setting a refresh time on live reports gives you current information at any moment on key business processes without any intervention on your part This allows you to turn your dynamic report into a real time display Those familiar with Microsoft s Digital Dashboard will appreciate how easy this makes building your own dashboard element Background Query Allows you to work with your PivotTable while data is loading This can be a time saver for long refreshes when you may want to begin moving fields around before you have your final data Be aware that when the query finishes different data may alter the layout you want to see Optimize Memory Try this one at your own risk Microsoft introduced this feature with Office 97 but it never worked p
164. n the Page area To do this you need to create a group within the field See instructions on grouping below under Grouping Field Items The steps are 1 Move the field to the Row Area 2 Create a group of the items you want to select and give that new group a logical name 3 Move the grouped field back to the Page area 4 Select the named group as a filter Thus your report shows only data for records where the field contains one of the items in the group An added benefit is that you can use this group in a way similar to how you would use a single field item Set Page Filters Across Multiple Pivot Tables and Worksheets Mote sophisticated reports often end up with many views of the same data cloned into many worksheets within a single workbook You may wish to set your filter selection once for similar reports across the entire workbook rather than going to each worksheet Here s how after selecting a Page field value to filter data in one pivot table double click on the same value MasterMine asks if you want to set this value for this Page field in all pivot tables in the workbook Filtering in the Row or Column Area Mote simply but with fewer visible cues you may filter your report by hiding some field items in a displayed field With your field in the Row or Column atea click the dropdown arrow on the right side of the field This shows a checkbox list of all items in the field This list includes all values that
165. ncel ene dios many fewer fields on the page On the other hand users need to be awate that fields can be retrieved from the Pivot Table Field List at will by dragging and dropping Hide page fields with no valid items Hiding page fields with no valid items provides a less drastic alternative to the previous item It simply cleans up the page area of your report by removing fields that are included by default in the report but for which there is no data in the GoldMine database Such fields serve no purpose in the report Turn off subtotals for all fields By default every field in an Excel pivot table has the subtotaling function turned on under the assumption that most reports are meant to sum and ageregate data In the case of line listing type reports where many fields are dragged into the row area of the report this selection automatically turns it off for all fields Users then need to deliberately turn on the subtotaling function for fields where they wish to use it To turn them back on double click each field button for which you wish to restore subtotaling and check Automatic on the left side under subtotals 27 Substitute blank space for blank in field items By default blank spaces in pivot tables are represented with the written out notation blank For users who prefer that reports default to a true blank data field this alternative automatically substitutes a
166. nd in regular units year month 30 day group etc as long as fields are designated as Date or Numeric type Set the Profile field as a Date at the end of the field name in the definition for the profile To properly update the GoldMine profile definition note the appropriate designator on the General tab in your MasterMine Create Report dialog By To track additional information about the details related to your contacts ec ec amp you may customize up to 8 custom fields and designate a custom folder for default these are amp amp for Date and HH for this Detail Once done with the settings below select the Info folder of the expanded Detail to edit and view the additional information Field GM License Field2 MM License Field3 Version Field4 Date issue amp amp 10 Zip Tab Name MMLic 20 Title Numeric but you can change them to any other value i in the Preferences screen Field5 Userst 6 Ext 20 LinkAcct Field6 Status 20 State For instance you may want to change the date 20 Country Field VAR Contact 40 Address1 designator to the word Date Be aware that Field8 Deactvatet 40 Address2 120 MasterMine_will then interpret any profile s field label ending in Date as a date Next go to the GoldMine profile setup screen Do this by starting to enter a new profile of the type you
167. nder GM Tables below In most cases the alias corresponds with the OpMgr RecType or recotd type used to identify them in GoldMine Alias Table For Tab C1 Contact1 Main Contacts C2 Contact2 Custom Fields CH ContHist History CA Cal Pending CS ContSupp Supp Contacts OM OpMgr Opportunities OK OpMgr Tasks OD OpMgrFld Details OC OpMgr Influencers OT OpMgr Team OP OpMgr Competitor Ol OpMgr Issues Workbook Name Description GM Tables Opportunities Contacts relating to Opportunities OMC1C2 Tasks relating to Opportunities OM OpMor fields Opportunity Tasks OK Task fields OMOKC1C2 Opportunity Details Details and Linked Documents relating to Opps OMODC1C2 Opportunity Influencers Influencers relating to Opps OMOCC1C2 Opportunity Teams Team Members relating to Opps OMOTC1C2 Team Members and Opportunities they link to For team Opportunity Team with members that are GM Contacts Contact info of Team member Team Contact Info is included OMOTC1C2 Opportunity Competitors Competitors relating to Opps OMOPC1C2 Opportunity Issues Issues for Opportunities and Opps OMOIC1C2 Forecast sales using only info in Opportunity record not the Opportunity Forecast attached Sale records on Pending tab OMC1C2 Opportunity Pending Sales Forecast Pending Sale records associated with Opportunities OMCAC1C2 Opportunity Sales Completed Completed Sales associated with Opportunities OMCHC1C2 134 Workbook Name Dpportunity Pending De
168. ne s enhancements e Have detailed knowledge of GoldMine s underlying database structures and can do the following with MasterMine e Replace most static management reports with a standardized set of dynamic tables reflecting key company processes updatable at the click of a mouse e Add any GoldMine field to existing MasterMine templates e Easily format tables for formal presentation to colleagues and customers e Develop new spreadsheets from all MasterMine templates making full use of customized fields and data items e Graphically present data using Excel s full native charting functionality Advanced MasterMine Users have the following additional knowledge e Thorough knowledge of Excel Pivot Table and other Excel functions e Knowledge of MS Query functionality e Ability to use SQL functions for refined querying in MS Query and can do the following with MasterMine e Present and analyze any conceivable data relationship by modifying template queries or constructing original PivotTables from GoldMine data e Integrate data from other applications such as accounting order processing production or customer support softwate e Optimize queries to enable faster refreshing of large source data files New features in Version 5 0 amp 5 1 Version 5 includes a multitude of small feature enhancements and bug fixes but some top features are these 1 Combined Pending History reporting The usual acti
169. ng and analysis on yout own data before you purchase The demo works for a limited time and is the same software as the actual licensed product To unlock the remaining toolkits and workbooks you must purchase a MasterMine license and enter it using the register now button on the initial welcome screen You can install and register as many MasterMine installations as you need all using the same demo or setup file On most systems installation should be very automatic and won t take more than five minutes See Setup Procedure on page 14 License Options Each MasterMine software license is associated with exactly one GoldMine master license designated during the setup procedure If you have more than one GoldMine master license determine before you begin which license will be associated with this installation of MasterMine software and have the license number s ready when you call to register MasterMine licensing is structured several ways to allow you the most advantageous and cost effective distribution of reporting and data management capabilities for your company Named User Full License You may choose to license only a few MasterMine users independent of your GoldMine user count Full report creation and opening using capabilities are available to named user licensees Named User Share Licenses At a reduced price additional users who open and use reports provided by Full users can stil
170. nses to launch these reports This dialog allows you to select any GoldMine user whose taskbar will have a button which will lauch the current wotkbook at will An existing taskbar may be selected or a new one created The Caption is the label which will accompany the new icon on the GoldMine taskbar 63 Set security and convenience parameters in the lower frame of the dialog as follows Add Report to GoldMine Taskbar Add this report to a taskbar in GoldMine for easy one button launch GoldMine Username ov e Auto Refresh on Open Causes the report to refresh i Nen em CT against GoldMine s data as the report is opening Most effective when database is small enough to allow data Main Account Info loading without significant delay Deploy this report with the following preferences Disable Drill Through Prevents the user from rand double clicking on data items to see underlying data gt sis Save Report Without Data Vastly reduces the size of nn saved files especially important if file is to be e mailed o to another user Also promotes security by preventing id eii storage Of database data within the Excel workbook Save SQL password Save SQL Password To avoid requiring users to enter a valid username password combination every time their report refreshes against the SQL database you may elect to save the SQL password with the report IMPORTANT NOTE the SQL password is not encrypted when stored
171. nstalled and accessible could launch existing reports as defined by your Full users Required Software MasterMine works best with newer versions of MS Office In general it is best to simply Install all components of MSOffice as space considerations are rarely significant on newer machines If you are working with MS Office 97 and do not want to upgrade MasterMine will function but with some limitations Please see Chapter 9 Limitations with Office 97 for details Note that as of Version 5 0 MasterMine Software no longer supports Office 97 installation issues without charge Beginning with Version 6 0 some new features may be unavailable for Office 2000 users MasterMine works with GoldMine 4 0 or higher Standard or Corporate Edition and works with SQL or dBase GoldMine database files Some features that rely upon Microsoft SQL may not be available to standard dBase users To begin ensure that your MS Office installation includes MS Excel and MS Query MSQuery is not part of the standard installation for Office 97 or 2000 so you must add it using your original MS Office media For Office 2000 or higher Select Custom Installation when you install Office or re run the Office 200 installer CD and select Add or Remove Features Find Microsoft Query under Office Tools and select Run from My Computer in the setup MSQuery will optionally self install the first time it is used
172. olved such as Cal Contact1 and Contact2 an inner join is used Some templates expressly reverse this join including all contacts regardless of whether they have activities linked to them but also include the pertinent activities An example is the All Accounts and linked pending report see Use Template below Design New MasterMine is designed to make it easy to create reports from scratch to go from idea to report in only a few seconds without necessarily loading a template Many users will take the route offered by traditional report writing software and simply create a new report for evety use enjoying the short time e to outcome and confident that there are no Create New MasterMine Workbook File Toolkits SQL Query Preferences Help Which part of GoldMine s information do you want to see Design New Use Template i j We forgotten filters or setup quirks in their new Main report Additional asset gt rea ALE A a e For this approach the default Design New serves the purpose If you have selected the Activities area of your GoldMine data to view the window under Design New will also display a list of Activity Types tracked by GoldMine In the list check one or more activity types to include in your report By default MasterMine produces a count of each of the selected activities in a calendar based format Sales Reports Selecting Sale only and wnchec
173. om the GoldMine area you selected on the left that have all the keywords you entered To broaden your search click List All Templates at the bottom of the area selection or remove some keywords from the search box then click Search again 38 Example Toolkit Sales Manager Toolkits Another way to find an appropriate template is to search by Toolkit MasterMine templates ate grouped into job and task related toolkits which contain report templates formatted for specific purposes It is a good idea to familiarize yourself with the templates in the Job related toolkits as they contain many useful reports that you may not have thought of on your own but which may help fulfill role related responsibilities There are two ways to see templates sorted by Toolkit The first is to simply sort the templates in the template search window as described above The second is to use the Templates menu Templates menu If you are already familiar with the available templates and know which toolkit you need to use simply click on the Template menu select a toolkit and see a dropdown list of templates contained in that toolkit Your GoldMine administrator may regulate which toolkits are available to you by removing or adding toolkits from the Toolkits folder Workbook Past Due Pending Activity within your MasterMine application folder Some Toolkits may be This one workbook can be the basi
174. ometimes do not automatically re size to fit the data resulting in a display of HHHHHE signs if columns are too narrow or a table full of space that extends far to the right of the screen if columns are too wide Also Notes fields tend to be too wide to work with conveniently when placed in the Row Area Hit the button on the MasterMine toolbar to quickly re size columns to fit Set Subtotaling Behavior Select a Row or Column field right click it and select Field in the submenu Indicate Automatic or None to add or remove subtotaling for the field and click OK Add or Remove Grand Totals Right click anywhere on the table and use the submenu item Table Options Select or de select the appropriate box for Grand totals for columns tows Include Hidden Items in Totals You have the option of hiding page field items in a table By default hidden items are excluded from totals To change this setting for the whole Pivot Table right click on the table and select Options from the submenu Check or uncheck the box by Subtotal hidden page items Format Numeric Field Items Right click a data item you wish to format and select Field on the submenu Then hit the Number button and format the numeric data in the field as desired Working with Notes Notes fields in GoldMine are special because they are of indefinite length This poses unique problems for reporting which are discussed at length in
175. onal L Trade Show Suspect Media M Recod DD e aaa Canto the contents of the field to be returned it is also logical to change the column heading the field name returned to the MasterMine report to reflect the new contents by typing a new word or label in the Column heading box In this case it makes sense to change the heading to Source3 When finished click OK and MSQuery will automatically re query the data Check the new returned data below the header to make sure it is the desired result Adding or Changing Selection Criteria On the menu above click Criteria Add Criteria Select the desired Field Operator and Value from the list boxes provided You may enter a dBase or SQL expression instead of the Field name Simple Example You want to include only Sale records in the Cal Database that are scheduled to occur before the end of 2001 Add two criteria as follows Field RECTYPE OnDate Operator Equals is less than or equal to Value S 12 31 01 107 Complex Example If you want to limit your query in ContHist to include only sales of more than 10 Units enter as follows Field if ContHist UNITS is null 0 val ContHist UNITS Operator is greater than Value 10 The dBase expression is necessary because you need to make a numeric comparison with Units which is a string field As a string 2 evaluates as greater than 10 Also bec
176. ontact in the current the currently selected GoldMine contact CH linked to Contact2 GoldMine contact set set CUSTOM User customized fields non standard in Including all kinds of customized data FIELDS GoldMine Cal table of pending activities Field Name Contains Useful For Featured in these toolkit reports OnDay Weekday name on which pending activity Building Calendar type tables Calendar is scheduled to occur OnHour First 2 characters of OnTime Summarizing by hour within OnDate Calendar DaysTill Days between today and OnDate Prioritizing calculating average days until New Client Pending Sales activities are due etc Short Referenc First N characters of Reference field where N is a default stored in the MasterMine ini file MMINI xls Summarizing on Reference field without including 60 characters per record in the query saves RAM All tables with CAL data HasNotes Yes or No if pending record contains Tracking level of activity data entry by All tables with CAL data data in the notes field creator of pending records Potential Numeric dollar value of pending sales Summing pending sale volume Sales Pending Referral Sale Amount Pending New Client Sales Pending etc Potential Units Numeric value of pending unit sales Summing pending unit sales Probable Amount Dollar value of pending sales times probability Weighted Sales projections Probable Units Pendin
177. or GoldMine interface Click the Fields tab and alter the selection of fields for inclusion in the new report When the report is created re format the report as the earlier one was 162 Index Button lancha dades 69 Add in Excel 26 27 AutoFormatting esses 123 Automatic Refresh sss 123 Background Query sss 92 Bug Repottinp tete eres Is Calculated Fields 80 110 163 Change a Field Button Name 93 Change an Item Name 93 Changing Data Connection Strings 68 Chartinsan nase sinn 10 65 123 ln M M 64 Column Area 60 166 Column Ofdet i aii dee 50 Content Search ee eR 88 Create MasterMine Report 38 128 Customization een 13 110 111 Dt a 60 Data Gleanup css 0s een 124 Data Mining 9 120 121 DA as 82 89 dBase expressions 112 Demo Limitations eese 16 Detail Level 46 Details Plus cani ais 23 Details Plus aia ins 41 Digital Dashboard sss 92 Disable data refresh onere 69 Disable dtilldown sss 69 Disable drillthrough sss 70 Display BOX eeeenedentereieettintete 123 totes ria 116 Display Box show hide socios 65 Display Dialog eed e 65 Dildo nin 67 Drill Down 9 19 96 123 124 Duplicate Worksheet
178. or them to appear in your drilldown screens As you click fields will rearrange themselves in the order selected If you make a mistake simply click Stop Sorting e and begin again You need not select every field that will be included When finished click Stop Sorting and continue T Show GoldMine Database names You may toggle between seeing local field names and seeing the actual GoldMine database field names under the list view of fields by clicking the GoldMine button xX Uncheck default fields MasterMine checks a typical selection of fields to include in your report based upon the area of GoldMine in which you are working You may wish to reject this selection and start with no fields selected If so click the Uncheck button To restore the default selection of fields click the check button again 21 Sort unselected fields The default order of fields in the list box is either the order they appear in a typical GoldMine dialog or for custom user defined fields in Contact2 the order in which they were added to the database To make it easier to find a desired field the Sort button alphabetically sorts fields within the table In combination with the Show GoldMine Database names button this allows you to find and include fields using any known name or label Choose a Report Name MasterMine assigns a default name to every report but it makes sense to customize the name you
179. originally e To summarize on a date field grouped both in Months Quarters and Years as well as in day groupings like 7 day weeks you need an additional instance of the field in order to group In Query just below the toolbars find the box for the table that contains your target field If the table is not present reconsider whether you started with the right template In the list box under that table s name find the target field click and drag it down to the column area To make any adjustments such as to specify a dBase or SQL expression using the field s name double click on the column head and change the field name to the desired expression Apply SQL or dBase Functions and Expressions Once you have added a field to your report you may wish to apply functions You must know SQL or dBase expression syntax to accomplish this or use a reference book that covers these functions There are a number of such resources online In this example we will change the source field to return only the first three characters of GoldMine s source field With MSQuery open showing sample data below with each 106 column topped by a header open the Edit Column dialog by double clicking on the field header you wish to modify In the picture the user has double clicked on the Source field header bringing up the Edit Column dialog In this dialog the user can change the field expression by making changes in the bo
180. ortant task overseeing and guiding your business process as it happens Other chapters cover the mechanics of manipulating your data with MasterMine This one helps use your creativity and unique knowledge about your business to turn MasterMine into your most powerful management tool 114 Data Mining Techniques The key to Data Mining success is recognizing the important relationships in your own company and expanding your reach from there These relationships are often expressed with phrases like Key Progress Indicators or chokepoints Your old static management reports are probably the best starting point of where to look Look at your old reports and ask yourself If I could alter this report slightly see the same data for only one division for one useror group of users for one lead source for just this month or for this month next to last month or the same month last year The possibilities quickly become apparent How are your users grouped in your business Group them the same way with MasterMine Compare their performace against each other and show trends over time What business processes do the reports show Calls per day and hour Sales follow ups by month Use MasterMine to compare plans projections and goals with reality Also consider reporting problems that you have had in the past Are you getting too many reports that tell you only partial information or that require you to juggle several prin
181. ountNo Result Code y User D2 UserID COM INP PRO LM LM2 LM3 Grand Total te te c OC RE OW RB OO 25 Grand Total Then we group Result Codes in a logical way by whether the called person was contacted or not so we can better assess the value of our calling efforts 5 Unvveek B Account Rep ff 8 C1AccountNo a No Contact Grand Total User ID2 LMT1 exem West X E EE E Oslo Son CO N O GO h3 h3 h3 h2 2 5 SS M wen 4 4 8 19 4 3 9 4 4 96 1 1 6 14 63 240 26 Grand Total 27 e Finally we can subtotal by group roll up our groups into single items or use them as filters whatever makes it easiest to understand our calling results Here s how the same report looks rolled up into a tight summary 80 7 8 C1AccountNo 9 User ID2 Grand Total 5 Unvveek All B Account Rep kan E Result Code2 Result Code v User ID 7 27 Groups can be treated just like individual field items as filters in the Page area of your report or as display elements row or column items just like regular field items Therefore you can compare not just calls by PAUL PENNY and ROB as individuals but also by region where each region is actually a group of users instead of a separate field value as they are in GoldMine Grouping Techniques and Tricks To build a local Group in MasterMine use one o
182. our query This makes a report more user or job specific and therefore easier to dictate how it will be used and to limit the information available to users It can also help optimize the size of a query thus speeding processing and query refreshing Caution Novice users tend to assume that filtering in the Create dialog is the best way to go However remember that as long as your data source is not large enough to make it cumbersome and slow it is frequently much easier and more beneficial in most situations to filter inside the report History First Date filter The simplest way to do this is with the History First Date field that appears below the field list whenever you have selected a report involving the history ContHist table This dialog allows you to choose a starting date after which records are eligible to be included in the report For installations where GoldMine has been in use for many years this can be extremely helpful However note that you can typically do much finer and more flexible date grouping and selecting within the report itself see Grouping Field Items page 79 Don t accidentally make your report too limited by setting date parameters that prevent you from doing worthwhile analysis later say comparing this year s sales rep call counts versus last year s MasterMine remembers the last entered first date and defaults to it next time Additional filtering criteria 45 If d
183. pHTML OP NOTES OP NOTES substring udfDBO udfMMStripHTML O OP Comp Notes2 P NOTES 254 253 mid OP NOTES 254 substring udfDBO udfMMStripHTML O OP Comp Notes3 P NOTES 507 253 mid OP NOTES 507 substring udfDBO udfMMStripHTML O OP Comp Notes4 P NOTES 760 253 mid OP NOTES 760 substring udfDBO udfMMStripHTML O OP Comp Notes5 P NOTES 1013 253 mid OP NOTES 1013 OP OPAccountNo OP ACCOUNTNO OP ACCOUNTNO OP CompetitorRecords OP RECID OP RECID Ol OlRectype OI RECTYPE OI RECTYPE Ol Issue Rtrim Ol COMPANY OI COMPANY Ol Issue Priority Rtrim OI STAGE OI STAGE Ol Issue Status Rtrim O1 STATUS OI STATUS Ol Issue Start OI STARTDATE OI STARTDATE Ol Issue User Rtrim Ol USERID OLUSERID Ol Issue Notes OI NOTES OI NOTES Ol Issue Notes udfDBO udfMMStripHTML OI NOTES OLNOTES substring udfDBO udfMMStripHTML Ol Ol Issue Notes2 NOTES 254 253 mid OI NOTES 254 substring udfDBO udfMMStripHTML Ol Ol Issue Notes3 NOTES 507 253 mid OI NOTES 507 substring udfDBO udfMMStripHTML Ol Ol Issue Notes4 NOTES 760 253 mid OI NOTES 760 substring udfDBO udfMMStripHTML Ol Ol Issue Notes5 NOTES 1013 253 mid OI NOTES 1013 Ol OlAccountNo OI ACCOUNTNO OI ACCOUNTNO Ol IssueRecords OI RECID OI RECID OC OCRectype OC RECTYPE OC RECTYPE OC InfluencerCompany Rtrim OC COMPANY OC COMPANY OC InfluencerContact Rtrim OC CONTACT OC CONTACT OC Title Rtrim OC CYCLE OC CYCLE OC Role Rtrim OC STATUS OC STATUS OC OCAccountNo OC AC
184. pMgr Projects PK OpMgr Tasks PF OpMgrFld Details PC OpMgr Contacts PT OpMgr Team Pl OpMgr Issues PC OpMgr Influencers Workbook Name Description GM Tables All Projects Contacts relating to Projects PJC1C2 Project Tasks Tasks relating to Projects PJPKC1C2 Project Details Details relating to Projects PJPFC1C2 Project Contacts Contacts relating to Projects PJPCC1C2 Team Members relating to Projects Shows team member information from the Project record only not original GM Project Teams contact PJPTC1C2 Team Members who are GoldMine Contacts not USERS Project Team with Member or manually entered GM Contact info for Team member Contact Info includes full GM contact record PTPJC1C2 Project Issues Issues tab for Projects PJPIC1C2 Project Sales Forecast Pending Sale records linked to projects PJCAC1C2 Project Sales Completed Completed Sales linked to Projects PJCHC1C2 Project Pending Activities Pending Activities linked to projects PJCAC1C2 Project Completed Activities Completed Activities linked to Projects PJCHC1C2 136 Workbooks by GoldMine Table Name MMStandard xls This toolkit is designed for use by those familiar with GoldMine table structures to quickly set up custom reports based upon various table combinations Reports work the same as with other toolkits but are named by the GoldMine tables they contain See Adding or Adjusting Joins in Chapter 7 for an explanation of Joins Workbook Name Description CCG Mable
185. port exposes each Field Item contained in the field along with a corresponding number either a count of occurrences or a sum of something numeric like dollar amounts in the data area MasterMine Screen Elements Basic manipulation of MasterMine Pivot Tables is easy and fast but does require some getting used to Active users of this Excel feature are relatively few but developing good pivot table skills can be very helpful in many situations For more in depth discussion of PivotTable functionality we urge you to use the extensive Microsoft Excel help from the Excel menu For many the best way to learn how Pivot Tables function is simply playing with them Most of the data relationships you want to see can be created by simple dragging and dropping Don t worty you won t damage MasterMine spreadsheets you can always close them without saving changes or go back to the basic MasterMine templates to start over Most importantly remember Undo In most cases you can undo your last change by hitting Ctrl Z or more slowly by clicking Undo in Excel s Edit menu Pivot Table Elements The basic elements of an Excel Pivot Table include Row Column Data Page and Hidden areas arranged as follows 53 Row Area Data Area Hidden Area All available fields listed on the Pivot Table Field List The Row and Column areas show field items the various possible values within a field Row Area fields c
186. port yourself These templates cover a wide variety of areas in GoldMine encompassing virtually every part of GoldMine s data that you see in the GoldMine product itself Selecting the appropriate template can help get your desired outcome quickly whether it is a report a dynamic analysis or data management project 37 Template Finder When you check Use Template a selection of MasterMine templates appears The list you see is ALREADY NARROWED to those appropriate for the area of GoldMine you selected on the left Find the appropriate template by browsing through the list As your mouse hovers over each template name you can see the toolkit and a Create New MasterMine Workbook Which part of Goldkdine s information do you want to see C Contacts Design New Use Template detailed explanation of the template in the EU i information bubble to the right Use this E Alarm Calend information to decide which template to select Activiti DI iis x All Accounts and linked pending E without clicking through each one Pending All Pending History Fases Pending Forecast Sales amp Contact amp Details Profiles Y Detail i a 2 ou can sott the ge a cei eeu AMA Report Next Pending Activity only All Contacts Opportunities mas x T ver U Only Gil Contacts list by report name C Cases Tas infers Pena Toolkit Activity Analysis pp Influencers Pending Activities al habetic
187. powerful but it can be IV AutoFormat table cu FEL misleadingif you fotget that you have ubtotal hidden page items as For error values show turned it on or off Merge labels IV Preserve formatting IV For empty cells show To see how this feature works check IV Repeat item labels on each printed I Set print titles the box on the Options panel and hit page the OK button Right click on a Mark Totals with en ps Er Totals with field in the Page Area and select Field Data options Settings Now you can hide any of Data source options External data options the items within the field by selecting IV Save data with table layout IV Save password them in the box below Watch the IV Enable drilldown Background query Pivot Table Grand Total as you hit Refresh on open Optimize memory the OK button after hiding some Refresh every feo 4 minutes items The Grand Total will decrease by the count of the items you hid cone Be careful There is no other indication on the report that it excludes certain items in this field or that subtotalling in the Pivot Table excludes them You may wish to make a note on the sheet to this effect so that you don t draw false conclusions later on Preserve Formatting Should always be left checked if you want to retain field formatting colors that reflect source files for instance 85 Page Layout Gives you the option of listing page fields i
188. preferences Removing some toolkits not only reduces the number of options a user has on the Templates menu thus possibly reducing confusion but also makes loading of the software faster Another consideration is your use of Opportunities and Projects in GoldMine If your company does not use Opportunities or Projects it makes sense to simply turn them off in this tab 25 MasterMine provides toolkits that work with systems where by the choice of the GoldMine administrator and formal user rules all Opportunities are either linked to a specific Contact or not For reporting purposes you will want to have EITHER the Opportunities Linked to Contacts or the Opportunities Unlinked to Contacts toolkit turned on Likewise turn on EITHER Projects Linked to Contacts or Projects Unlinked to Contacts but it is unlikely you will use both Integration Preferences Integration is covered under setup in Chapter 2 Please see Integration with GoldMine Add on Products on page 17 Compatibility Preferences Since the release of Microsoft Office 2007 not all Excel workbooks are created equal For optimum use of MasterMine store Office 2007 workbooks in a new format that enables background macros extension xlsm This allows MasterMine workbooks to take advantage of much higher memory limits and field item count limits than before Once created these reports may be difficult to shar
189. qe ile 57 Report Builder Templates 43 Requirements 165 knowledge 9 MEMO ias 18 46 55 SOM Lina 17 Row Area aus cies coe ite tes 60 167 Sales 40 Sales Manager s Toolkit 45 Save Data with Table Layout 92 Save report without data 70 Save SQL password 23 34 67 69 70 Schedule printing 70 DEALCH rta 88 SOUL ana ins 19 23 68 select repott criteria sse 130 Selecting Fields 110 Selecting Reports 45 133 UP Eee 20 SBorttRetz oo tese 31 Show GoldMine Database names 50 Show Pages coronas 123 snapshot tte teretes 70 SOft Fields rimar 50 Sort fields for drilldown 50 Sort unselected fields 50 Nou 83 SQL expressions eee 112 SOL Installation ettet 22 SOL Quel Yanina 53 Subtotal Hidden Page Items 91 Summarize numeric data 80 SUP POr 10 12 13 System Requirements 17 18 Template ninio ninio dosis 58 Toolbats MasterMine esee 26 28 Toolkit is 57 Toolkits esess 45 122 124 133 Training 9 14 111 Troubleshooting 122 Uncheck default fields 50 OE 65 Uninstalling eite 2h Upgrades etre 26 User Manual eite 69 User defined Fields s
190. r Yourself A RE e ter ades 117 Presentation Features E 117 DATA INTEGRITY TESTING suecia ciar rio 118 Finding Outliers s AA en tea a ede tete 118 Exception Reports E 118 REFERENCES iere d E 120 APPENDIX A DETAILS TAB REPORTING eseeseeeeeeeeee emere nennen en neee remettre ens 120 APPENDIX B SAMPLE SESSION dee tii ie repeti eret tdt 122 APPENDIX C PRE FORMATTED REPORTS TOOLKITS AND TEMPLATES sesser 127 MasterMine Aliases for Database Tables sees 127 GoldMine Administrator s Toolkit DBMToolkit xls eese 129 Activity Analysis Toolkit MMActivities xls eese eene eene nnne nnne 131 Sales Manager Toolkit SMToolkit xls eese eene nennen 132 Sales Manager Toolkit SMToolkit xls eese eene nennen nnne 132 Opportunities Toolkit MMOpportunities xls and MMOpportunitiesUnlinked xls 134 Projects Toolkit MMProjects xls and MMProjectsUnlinked xls eese 136 Workbooks by GoldMine Table Name MMsStandard xls eene 137 QuoteWerks Toolkit MM QuoteWerks xls eese sees nnnennnennn 138 APPENDIX D COMMON FIELDS AND EXPRESSIONS eeeeeeeeeeeeen nemen een ennemi nns 139 APPENDIX E A NOTEON NOTES tice ete i dr ei Hr t el tee te RH eg tb riens 153 APPENDIX F SPECIAL FIELDS tope p eie t Re eG e Per enge 155 APPENDIX
191. reate one row per field item The Data area shows the actual summarized data usually either a count of items in a field or a summation of the values in a numeric field The data area must include at least one field but it may include multiple fields in more complex reports The Page area includes fields containing data not currently displayed in the table but which may be dragged into Rows or Columns Selecting one or many items within a Page field filters what appears in the rest of the pivot table 7 Budget Create On Column Area Pivot Table Field List 5 Column Fields Column Items ot Hidden Area Source 12 13 C1AccountNo Territofy v Drag items to the PivotTable report 14 Contact Type Industr Y JCN CT MA MW NE PN RM Sc 15 Engineering 1 1 1 E Contact 16 Food 1 1 2 1 al Pharmaceuticals 1 2 2 2 1 2 1 Er 18 Suspect Aviation 1 1 1 3 2 3 2 E OnMonth Engineering 1 4 4 1 2 1 Ej Pre Post Demo Row Area Row Fields Food 1 5 2 2 5 1 Demo gt A 5 5 2 Row Items Pharmaceuticals 1 3 2 E PreiPost Sale zz Awiation 2 E FirstSale 24 Engineering 1 OnDate2 25 Food 1 1 26 Media 2 6 4 5 4 2 Ej ProdRef Bel Pharmaceuticals 1 1 1 E SalesRep 28 System Design 8 1 eet On 29 Customer Engineering 1 30 Media 1 31 Pharmaceuticals 1 32 Past Client Aviation 6 4 3 2 5 1 3 3 27 ai Engineering 1 2 2 3 3 1 12 34 Food 1 2 2 3 2 1 1 12 Ball Media 1 1 1 1 4 36 Pharmaceut
192. reation dialog and start over it s sometimes better to tweak and existing one For that purpose Microsoft Excel has the MSQuery utility Working With MS Query MSQuery is a graphical SQL query builder provided by Microsoft to manage the query that underlies pivot tables With MS Query you can more closely define the data set returned from your GoldMine database With this capability you can e Further customize reports built from MasterMine s templates to use the unique user defined features of your own GoldMine installation e Make your Pivot Tables as efficient as possible in handling larger database files e Vastly increase the power of all your analyses by zeroing in on key data relationships that otherwise cannot be represented in Pivot Tables You can pre format the data in various ways take part or all of a given field s data or perform calculations on it in combination with other fields You can also change the criteria by which data is included or excluded from the query and integrate data tables from other sources within or outside of GoldMine This is very powerful stuff but WARNING Changing the underlying query for your PivotTable is a valuable skill best left to trained users Accurately applying the more powerful actions in Query requires some knowledge of GoldMine data structures and some familiarity with basic dBase or SQL functions like mid left right trim etc We encourage free form tweaking w
193. ring OD LINKEDDOC 47 30 mid OD LINKEDDOC 47 OD Whole Link OD LINKEDDOC OD LINKEDDOC OD DetailRecords OD RECID OD RECID OD Detail Notes udfDBO udfMMStripHTML OD NOTES OD NOTES substring udfDBO udfMMStripHTML O OD Detail Notes2 D NOTES 254 253 mid OD NOTES 254 substring udfDBO udfMMStripHTML O OD Detail Notes3 D NOTES 507 253 mid OD NOTES 507 substring udfDBO udfMMStripHT ML O OD Detail Notes4 D NOTES 760 253 mid OD NOTES 760 substring udfDBO udfMMStripHT ML O OD Detail Notes5 D NOTES 1013 253 mid OD NOTES 1013 OT OTRectype OT RECTYPE OT RECTYPE OT Team Member Rtrim OT CONTACT OT CONTACT OT MemberDepartment Rtrim OT COMPANY OT COMPANY OT MemberTitle Rtrim OT NAME OT NAME OT MemberRole Rtrim OT STATUS OT STATUS OT OTAccountNo OT ACCOUNTNO OT ACCOUNTNO 148 Source MM Field Name SQLExpression dBase Expression OT TeamRecords OT RECID OT RECID OP OPRectype OP RECTYPE OP RECTYPE OP Competitor Rtrim OP COMPANY OP COMPANY OP Product Rtrim OP NAME OP NAME OP Strengths Rtrim OP STATUS OP STATUS OP Weaknesses Rtrim OP CYCLE OP CYCLE OP Rating Rtrim OP SOURCE OP SOURCE OP Status Rtrim OP STAGE OP STAGE OP CompContact Rtrim OP CONTACT OP CONTACT substring No Yes 3 datalength OP ACCOUNTNO 20 OP Linked 1 3 iif OP ACCOUNTNO is null Yes No OP Comp Notes udfDBO udfMMStri
194. rmation button amp 5 go to the Query Text tab and click Modify Query 2 Click Back and then Get Data 3 You may encounter an error that says Query Wizard is unable to open the query or something similar Click OK This opens MSQuery and contains the data previously queried Note that using MSQuery on a large dBase database over a network especially on databases of more than 20 000 or so records can take up to a few minutes If the wait is too long consider moving the data to a local drive and accessing it there or even better upgrade to SQL Once MSQuery is loaded you can do any of the following Resort Columns for Drilldown The order of fields in a query is significant in several ways Sorting fields within your query affects the order of the fields as they appear in your Pivot Table Field List and more importantly the order of columns in your drilldown sheets To re sort fields in your query click once on the column header of a field in MSQuery Once it is highlighted you can grab the field with another click and while holding down the mouse button drag the field left or right to another location Add Fields to a Query You may wish to include additional fields in a query for several reasons among them e To include some regular GoldMine fields that were left out of the base MasterMine template e To include customized fields in Contact2 dbf beyond those distributed with GoldMine
195. rmatted report called Exceptions which is part of the Database Manager s Toolkit provides a starting point for this type of report Add new reports to Exceptions xls as you develop them and check them periodically with refreshed data 118 119 Detail Properties re Detail Info Audit Setup References Appendix A Details Tab Reporting Reporting on Details requires special setup work to ensure that MasterMine reports properly reflect your information GoldMine stores Details as character based data That is even though you may have labelled it in GoldMine as Birth date GoldMine does not prevent you from entering any data not just dates in the Birth date field Querying profiles from GoldMine is especially difficult for most report writers because profile fields are user defined with each field uniquely labeled Being highly GoldMine integrated MasterMine detects the user defined labels for your GoldMine profiles and automatically treats them as though they were regular fields GoldMine stores all Profile data as character data That is it does not give you the option of setting Date or Numeric types to fields and therefore it is not possible to check data entry errors unless you use Details Plus see page 17 for more information However MasterMine helps you get around this limitation for reporting purposes MasterMine can automatically group dates and numbers logically a
196. roperly in that edition We recommend closing the reports you re not using and optimizing memory by careful field selection More Tips amp Tricks in Excel Here in no particular order are some additional tips that can help you get the most value from your GoldMine data Branch Off From Existing Reports Branching off from earlier work instead of starting at square one can save you tremendous time If you have a report that serves you well but a variation might also be useful don t re create it from scratch Make a copy save the original and freely create promising variations Cloning a whole workbook is as simple as hitting SaveAs inside an existing report Remember to save unchanged the report you started with so you can go back to it at will Often it makes sense to group reports with similar source data but different presentations into a folder by themselves Remember also that from the toolbar you can instantly clone any worksheet within a workbook creating variation after variation until you have what you need Then you can 86 go back and delete earlier versions that you didn t need Nothing is wasted by trying various options within a single workbook Auto Refresh When you deploy reports to other users especially people not as familiar with MasterMine as you are you should turn on Auto refresh since they are likely to assume that what they see is current And here s another twist on auto refresh Some use
197. rrett copart 28 Brett White Gilburn Inc FL Trade Show Pharmaceuticals Tammy SE 3 7 2004 98030936944 W P_GES B bwhite gbp co 29 Brent Dessenberger Marathon Innovations MS Customer Referral Past Client Pharmaceuticals Pete SE 1 24 2005 A0020749959 K MO7 c 30 The order of the columns here was set when you originally created the MasterMine report See Field List Controls on page 43 for instructions on setting the order at the time of report creation These fields can also be re arranged after the report exists See Resort Columns for Drilldown on page 106 Charts and Graphs Any MasterMine report can be converted into a chart simply by clicking on the Chart button Mi on the MasterMine toolbar This creates a graphic view of the same table you are using on another worksheet in the same workbook 1 400 1 200 1 000 800 RYAN OINANCY B LAURIE sog E KEN 400 200 Jan Feb Mar Apr May Jun Jul Aug Sep Oct i The full range of Microsoft Excel charting is available to make dozens of types of charts with coloring and formatting options that run the gamut See Excel help for further information 72 Multi table Reports and Digital Dashboards As your reporting becomes more sophisticated you may develop reports that contain any combination of these styles Your reports might have worksheets that contain multiple pivot tables charts and even cell formulas created to read dat
198. rs not only prefer that reports refresh as they open but also that open reports refresh every few minutes A well designed dashboard that shows the key metrics for the business especially ones that summarize a lot of fast changing information for example call results from a large outbound calling center can be a powerful management tool all by itself Set Filters Across Worksheets Page Selection As you clone your report from worksheet to worksheet you create a variety of related reports within a single workbook that combine to tell a story or to serve a reporting need For instance you might have a workbook of Sales reps weekly call reports where each tab in the workbook represents a different sales rep When you load this report each week you might sett the Page field OnWeek on every worksheet to the current week If you have many sales reps this can be a lot of work There is an easier way After setting OnWeek in the Page area on the first worksheet double click on the value you set ie if you set OnWeek to 07 23 08 07 30 08 double click on the cell containing 07 23 08 07 30 08 MasterMine will confirm that you want to apply this setting to all other pivot tables in the workbook with OnWeek in the Page atea and take care of it for you By the way reports on subsequent worksheets need not be cloned from the original sheet They might be reports based on completely different queries The key for this
199. ry large database more than say 250 000 records in the tables you are pulling from may need to be careful to limit the field selections to necessary fields Trial Queties You may need to make several passes at creating your initial query to determine the optimum fields to include MasterMine makes this convenient by leaving open the create dialog after you create a report If your first query is too large if refreshing the report seems to take an unreasonable length of time or you receive an Out of Memory error or a message saying some fields have too many unique items to be displayed you can quickly run the same query after unchecking some fields Field List Controls Beyond simply deciding WHICH fields to include you may have additional considerations about your field selection At the top of the field list box are several buttons that can aid you in selecting and organizing your query fields The buttons look like this BY 43 These buttons help you do the following Sort fields for drilldown presentation The order of fields in your query determines the column order in your drilldown screens see page 90 Drilldown for Detail For this reason you may want to sort the included fields in your query before hitting the Finish button to create your MasterMine workbook Click on the Sort Fields button then click ONCE on each selected field name not the checkboxes in the sequence you want f
200. s for many useful reports each easily created in a few minutes enabled or disabled in the preferences page in your MasterMine interface In addition to the MAIN REPORT BUILDER Toolkit which Full calendar of Past due activities renders the most global overviews of GoldMine data files MasterMine Past due calls by user Past due forecasted sales by territory comes standard with a GoldMine Administrator Toolkit Sales Past due calls amp appts by month Manager Toolkit and a toolkit for building workbooks from Past due activities by user activity type and scratch called by GM Table Name Additional toolkits include product category or any custom field more templates to handle general Activity Analysis Combined Almost any combination you can imagine Pending amp History Projects Opportunities and more lt A discussion of each of these toolkits is available in Chapter 9 References starting on page 120 A customized toolkit may be added by your GoldMine Solutions Partner or by MasterMine Software to simplify and speed up your implementation or to facilitate handling interaction with databases outside GoldMine Although these toolkits look like Excel workbooks they ate password protected and are not for end user manipulation Selecting a toolkit on the Templates menu changes the list templates available in the Template listbox Select Template In the Template listbox selec
201. scribed below The user must decide how many of these segments to use for the job at hand Although notes can be theoretically of unlimited length consider what the maximum length of one of your notes might be in practice or alternatively how much of the longer notes you are willing to ignore for your report A typical user might decide that everything they need is probably contained in the first 253 characters of the notes and therefore would include only CS Notes not CS Notes2 etc in their query If you need more than the first 253 characters check on additional segments as necessary Be sure to start at the first notes field and check on additional segments sequentially Checking too many notes fields will make your report querying very inefficient and may overflow machine memory in extreme cases In your pivot table these segments behave the same as any other field except as follows 1 If you display a notes field in your pivot table Row Area the AutoAdjust Columns button a on the MasterMine toolbar sets the width of that column to 50 characters and wraps its content into multi line cells for easier viewing 2 In the Row area non printable characters in the field such as carriage returns and line feeds appear as square boxes O and do not cause an actual carriage return 3 When you drill down all note segments ate re joined into a single column and re formatted for easy reading Inexperienced users often don t
202. scription GM Tables activities MI Opportunities and linked Pending Activities MCAC1C2 Dpportunity Completed Activities ll Opportunities and linked Completed Activities MCHC1C2 Dpportunity Influencers Pending Activities nfluencers and all associated pending activities MOCCA Dpportunity Influencers History Activities nfluencers and all associated completed activities DMOCCH 135 Projects Toolkit MMProjects xls and MMProjectsUnlinked xls As with Opportunities GoldMine s Projects may be used in different ways depending on the conventions set up within your business Most companies link each project to a contact record so by default MasterMine assumes this is the case However some organizations use projects in such a way that the contact under which the project is linked is irrelevant Enable the appropriate one in your preferences see Chapter 3 Preferences page 24 The opportunity manager table OpMgr contains Projects as well and these are defined by a series of sub tables see GoldMine Data Structure documentation In MasterMine these sub tables have the following aliases as seen under GM Tables below In most cases the alias corresponds with the OpMer RecType or record type used to identify them in GoldMine Alias Table For Tab C1 Contact1 Main Contacts C2 Contact2 Custom Fields CH ContHist History CA Cal Pending CS ContSupp Supp Contacts PJ O
203. single space instead of blank for all fields Note that this is not related to another distinction frequently seen with SQL databases namely the distinction between null values and blank values SQL Server makes this distinction returning to Excel a null value alphabetized at the top of the field item list if the field has never contained a value and returning a or blank alphabetized at the bottom of the field item list and showing up by default as blank Although MasterMine does not do this automatically users may wish to ageregate these two forms of empty by grouping them into a single item or by applying the Isnull function in MSQuery to the field in question Use Logo Check this item and enter a valid path to any image file that Excel can handle jpg bmp gif are the most common to automatically print the image in the upper left corner of the report and automatically link it into the header of each page of the report After opening the report it can be edited or removed in Excel by going to print preview hitting the Setup tab and then hitting the Customer Header Footer button Always save SQL password in new reports y P P For maximum security in SOL the default Excel pivot table requires the user to enter a valid SOL login username and password in order to access the underlying data each time Typically this happens when refreshing the report or entering MSQuer
204. ss 133 User defined Template 58 Website eterne 6 Workbook 57
205. ssaeessaeeseaeers 53 Pivot Table Elements sci aerei ni innata 53 FIELD COLORS pL 55 THE MASTERMINE TOOLBAR c scssssssssssssscssssscssscssssssssscssssssssscssssssssscssssssssscsssscsssscssssssees 57 REFRESH DATA 5 RE 57 Simple RE TES n eoe ts e tst eee ete t he eeu e tt eg 57 Refresh All e ove eem eot e tute e m fe 57 Purge Selected Field u s ee ht amnis 57 Purse T 57 Sort Field Gnd Refreshis i atte tato diede ud 57 CLONEREPORT un ne ERE ERR IER di 58 FORM TTINGSUBMENU ne nun PR AREE ELE E GENER ERE A ERO EE H ELTE ETE RR ERE 58 Hide Page fields set to show All items essent nene nennen 58 Show Page Fields With Hidden Items eese eene nennen 58 Hide Row Field Subtotals esses esee eene eene eennnnn seen nete nnns nennen 56 Sort Field Items vaciitie ted se ote EE OE OE 56 Delete All Worksheets named Sheets nennen eene eennnn nnne nennen 56 Delete worksheets named Log MM DD YYYY ucnserssessnessessnessnensnennnennnennnnnnennnennnennnen 59 GROUPIEIBED TUEMS an rl ee nen een t a e sets 59 UNGROUP FIELD ITEMS itae al Race eo E E 59 vi SEARCH FIELD ITEMS cssscsssccccccsssescccccccccsssesccccssccceuceescecssuceeseeescecssucessesescscseecusessscesseeensneseee 59 FREEZE PANES 4 en dois 59 OPTIMIZE COLUMN WIDTHS soais t disce en ERSTE P Oboe A Ace LE D Node lags 59 SU uM ELE 59 PIVOT QUERY INFORMATION DISPLAY real a nr nal 59 GOLDMINE GROUP aus
206. string udfDBO udfMMStripHT ML C CA CANotes3 A NOTES 507 253 mid CA NOTES 507 substring udfDBO udfMMStripHTML C CA CANotes4 A NOTES 760 253 mid CA NOTES 760 substring udfDBO udfMMStripHTML C CA CANotes5 A NOTES 1013 253 mid CA NOTES 1013 if CA NUMBER1 Is CA Potential Amount Coalesce CA NUMBER1 0 Null 0 CA NUMBERI 141 Source MM Field Name SQLExpression dBase Expression iif CA NUMBER2 Is CA Potential Units Coalesce CA NUMBER2 0 Null 0 CA NUMBER2 if CA NUMBER1 Is Coalesce CA NUMBER1 0 Coalesce C Null 0 CA NUMBER1 iif CA duration is CA Probable Amount A Duration 0 100 null 0 CA duration 100 iif CA NUMBER2 Is Coalesce CA NUMBER2 0 Coalesce C Null 0 CA NUMBER2 iif CA duration is CA Probable Units A Duration 0 100 null 0 CA duration 100 CA DirCode Rtrim CA DIRCODE CA DIRCODE CA CreateBy Rtrim CA CREATEBY CA CREATEBY iif CA CREATEON Is CA CreateOn Coalesce CA CREATEON NullDate Null NullDate CA CREATEON CA CreateAt CA CREATEAT CA CREATEAT if CA ENDDATE Is CA EndDate Coalesce CA ENDDATE NullDate Null NullDate CA ENDDATE CA Last User Rtrim CA LASTUSER CA LASTUSER if CA LASTDATE Is CA Last Modified Coalesce CA LASTDATE NullDate Null NullDate CA LASTDATE CA LastTime CA LASTTIME CA LASTTIME if CA ALARMDATE Is CA AlarmDate Coalesce CA ALARMDATE NullDate
207. t a different column than the one that defaults MMUpdater Options The following options can all be set to default differently in preferences byclicking the preferences menu Add numbers to existing field data If the target field is a numeric field the value from the MasterMine worksheet is added to rather than replacing whatever value is already contained in the target GoldMine field This feature is invaluable for aggregating data from many records 1 e sale records into a single field Add strings to end of existing field data If the target field is a character field the value from the MasterMine worksheet is linked with rather than replacing the value contained in the target GoldMine field Do not update GM field if source cell is blank If a blank cell is encountered on the spreadsheet do not update GoldMine which would wipe out the value in the GoldMine cell This can be useful if you selected an entire column in MasterMine before using MMUpdater and you are not sure if your selection includes blank values Empty target field for all GoldMine records before starting Use thiswhen updating a field in GoldMine that will be used for aggregate reporting In effect this resets this field to blank for all records before MMUpdater processes Delete records from GoldMine Rather than update a field in the target table you may wish to delete the record entirely This can NOT be used to delete account records from GoldMine
208. t a report by name As you select a report a summary explanation of the report appears in the display window below The References Section page 63 of this manual gives you some tips about the contents of each report Most names ate self explanatory Also see the Tips in the next section on selecting the most efficient template for the reports you need 39 Create New MasterMine Workbook File Templates SQL Query Preferences Help GoldMine Administrator Template E Mail Tables CIEM Detail Level 2 Standard Data Fields v Query Summary of records with and without e mail address Description Select fields to include 2 x al v C1 Contact v C1 Reseller C1 Dept v C1 Company v C1 ccount Rep C1 Title v EM Email Priority v C1 Product C1 Asst v EM EmailMergeCodes v C1 CreateOn C1 Phone V EM Email Address v C1 Phone2 v EM Email Contact EM Mime C1 Phone3 v C1 Primary Email EM HTML C1 FAX v C1 Source EM Wrap Lines C1 Ext v C1 Contact Type C1 Linked To Contact C1 Ext2 v Cl Status C1 Last Name C1 Ext3 lt Report Name E Mal 0000000000000 SaveTo Path c MasterMine Reports Filter Finish Cancel the report Select fields to include Select Detail Level Detail level determines the quantity of default selected fields in the field selection list box below This is a memory management feature that is useful mainly with very large databases or on older computer systems where memory was
209. te in MasterMine can be quickly formed into a summaty like this one In this simple summary report a single field Contact Type has been dragged into the Row area The result is a breakdown of the database showing each value in the Contact Type field in GoldMine and a count of the occurrences of each one Note that the Field Items shown are those that actually occur in the database not a list of possible items or the list of lookups that you have defined for the field in GoldMine If an item appears to be missing it is probably because you do not have a real example of that item in your database r Each field item is counted so we can see that there are 112 Suspects 58 Prospects etc in the database and 24 records for which the Contact Type field is blank There are nine fields in the Page Area of the report available to be used as filters or to be dragged down and displayed in the Row or Column Areas of the report To create another simple summary report you can simply drag another field into the row area and drag the Contact Type field out either to the Page area above or entirely off the pivot table Although the results from this process may seem extremely simple they approximate the result of writing a series of SQL statements on your own minus the wasted houts Matrix Reports A 1 x 1 matrix report is a very simple yet powerful way to segment your database To accomplish the one below drag
210. ted between these fields if the user were to drop it Red X If your mouse icon changes to a Red X instead of the pivot table schematic as you drag your field it means that your mouse has moved past the area covered by the pivot table and instead is hovering over the raw spreadsheet You can retrieve the field at any time however from the Hidden Area Te Dropping the field at this point removes it from the pivot table entirely Prospect Custome Past Client Grand Total Pivot Table Field List which appears when you click anywhere on the pivot table and the Field List is turned on To turn on the pivot table field list right click on any cell on the pivot table and click Show Field List at the bottom of the short menu By dragging and dropping fields to different areas of your pivot table you can create all the basic reports you need Some additional organizing and formatting is desirable but this one simple skill gets you most of the way to a finished report in seconds Note This example is drawn from an Excel 2003 environment If you have another version yout environment may vary slightly from this one so try it on your own to see the differences Report Types You can create an infinite variety of reports in MasterMine Here are some basic report types and steps to create them Summary Report The simplest report is a summary that rolls up information into an overview Any report you crea
211. ted for them by full license users They retain the ability to work with flexible pivot capabilities but cannot change which fields appear in their reports at the query level 13 Also consider using Excel s native password protection for sensitive reports While all MasterMine spreadsheets are shareable by default Excel documents can be password protected under SaveTo to restrict users from any access or to read only Finally MasterMine provides convenient ways to restrict at the report level what capabilities are available to users by way of data refreshing query modification and drilldown for detail See Error Reference source not found on page 62 Setup Procedure Obtain the install file from the MasterMine website www mastermine net or from your local GoldMine partner reseller This install file is the same for demo ot live versions of MasterMine Entering the license number makes your demo live 1 In order to install MasterMine you must have GoldMine running on the workstation being set up Log into GoldMine with the username and password of the person who will be using MasterMine If Excel is open on the machine close it You may want to use Windows Task Manager to ensure that all Excel processes are closed Run the setup file MM6Setup exe from the distribution medium or copy it onto your desktop or into a suitably named folder on your system and run it from there by double clic
212. terMine reports saved elsewhere you may navigate within the Open dialog to another folder on your system Launch from a GoldMine Taskbar An even easier way to open a MasterMine report is to launch it from a taskbar icon requiting merely a single click Even the least computer skilled person can launch a MasterMine report from a GoldMine taskbar To get the most from your GoldMine database and to avoid duplicating work MasterMine users should share reports by frequently deploying them to their own ot other network users GoldMine taskbars This is also one of the best ways for an experienced report creator in MasterMine to share reports with less experienced users Refreshing Reports Unlike with traditional report generators it is not necessary to create a new report every time you want to see your information in a certain format If you have already run a workbook and formatted reports to your satisfaction simply select Open MasterMine Report from the GoldMine menu and select a report you have already formatted and saved You can refresh your report several ways 1 After opening the report hit the t icon on the pivot table toolbar or under the Refresh button on the MasterMine toolbar A counter on the lower left of the screen indicates refresh progress so you can anticipate how long this will take 2 You can set the report to refresh automatically every time it is opened or on a periodic basis
213. terally at their fingertips A common workaround for companies with a shortage of report writer skills is to print reports on a regular basis say weekly or monthly and force users to accept the printed reports even if they are obsolete as the only information available to them This not only gives users inaccurate reportsbut such centralized reports typically get muddled with information for various users who need it presented differently for each of their jobs At the same time it forces all users either to make do with a multi use report that only partially serves them Eventually it becomes a bureaucratic process printing and distributing copies of a compromise report instead of a dynamic solution to each person s unique information needs With MasterMine you can use Excel s native SaveTo capability to save a unique version of a report to any disk location where another licensed MasterMine user has access The user can launch the report at will with current data and reorganize it on the 98 page as needed The original report writer person need not be involved after the initial creation process We urge you not to waste MasterMine s end user flexibility by restricting MasterMine usage to a single or only a few users MasterMine provides inexpensive share licenses for precisely this reason to give end users the full flexibility of MasterMine without the responsibility of generating reports from
214. them easily But what if there are hundreds or thousands to search Fortunately MasterMine provides an alternative Rather than hunting through hundreds of items looking for a pattern do the following 1 Move the field to the row area of the pivot table so that items are listed below it 2 Click the field search button d OR select the field button itself with a single click and then click the group Find Item s in a Field and Group button gt on the MasterMine toolbar 1 Select field containing the value s you are searching for 2 MasterMine will create a new group consisting of all currently visible items in the selected field which contain C Any All None of the following elements If you have a cell selected on the field to be 3 Enter elements to be sought separated by commas or specify alternate delimiter seatched the dialog defaults to that field Click Any to your or logic AI to use and logic or None to use and not Begins with 2 Wildcard substitute for string of unknown length logic as desired to guide the seatch If you 4 Enter name to assign group NewGroup odi confused about what will happen read the whole sentence of 2 and substitute Note Existing groups which contain found items may be ungrouped as a result of this action This utility cannot be used with date Fields Any All or None depending on which you et have chosen
215. thout any formatting a number of important expression fields are included for more penetrating analyses Fields of detail level 4 are included only if pre defined as part of a report or if you expressly select them on the tab Fields Used in expert mode They can all be added manually if necessary after a report exists by going to MSQuery and adding them to the query Contactl main table containing basic contact information Field Name Contains Useful For Featured in these toolkit reports Linked To Yes or No if Pending or History record is Distinguishing records that are not tied to All reports including CA and Contact linked to a contact in the currently open the currently selected GoldMine contact CH linked to Contact GoldMine contact set set Postal Zone First 3 digits of zip code Sorting by Sectional Center Facility for All Contacti reports mailing sometimes for territory splitting PrimaryEmail SQL only Returns the e mail of the E mail lists exporting GoldMine data to All reports using Contact1 contact if Primary is designated or if only one exists for the contact outside vendors etc Contact2 supplementary table to basic contact information Field Name Contains Useful For Featured in these toolkit reports Linked To Yes or No if Pending or History record is Distinguishing records that are not tied to All reports including CA and Contact linked to a c
216. tivity Analysis toolkit which resents much requested reports on Last Completed Activity Last Call etc P q P P gt Screen HTML from notes SQL only For SQL users whose notes in Pending or History include HTML coding only text is displayed Formatted drilldown data Change field names or field items in your pivot table as easy as typing over the original so that changes follow through to drilldown sheets New Search Grouping options In addition to grouping on any and all of several elements contained in a row field s items new options include grouping those that contain none of the elements listed and allows the use of wild cards Greatly improves onscreen grouping ability and therefore improves data segmentation and formatting 10 Management of Background Query in Excel New Display box on MasterMine toolbar in Excel allows user to see underlying quety change connection string elements and quickly investigate data source info New features in Version 6 0 Version 6 includes a long list of enhancements which can be viewed in detail on our website www mastermine net Here ate the top 10 Simpler Report Builder dialog Users will find it even easier to go from I need a report that does x to actually having the report in seconds A simpler cleaner dialog makes choices clear and easy Template finder and many more templates Find needed templates from more than 125 pre
217. to start with Once these items ate created you will find altering the selected date range is easier than typing in start and end dates You may wish to use a range based on another date from your database To create date range selection fields identify the field containing the data you want to filter Use the technique described below under Date Ranges Group Using Date Fields on page 83 to create your Date unit fields for easy user selection Refresh Data With any cell on the PivotTable selected click the button on the MasterMine or Pivot Table toolbar to retrieve current data from the GoldMine database files 76 AutoSort options Sorting By default MasterMine automatically sorts tables alphabetically by the outermost field on the left breaking out each item in the field by the field items to the right of it which are themselves sorted alphabetically Thus in the example above Contact Type is alphabetical and within it the types of Industry are alphabetical e g Aviation Engineering Food Media Pharmaceuticals Pivot tables must always sort this way That is the outermost fields govern sorting hierarchically from left to right for Row Fields and from top to bottom for Column Fields Manual Sort By default when a report is first created every field is sorted alphabetically but its sort setting is Manual That is the user can drag field items to a new order To manually re sort field items click
218. touts to find out what you really want to know As you get on top of what s currently happening in your business you may move on to planning for the future by asking What if If so you can use your MasterMine derived data as a source of realistic givens while you build scenarios and models to consider different possible outcomes from altering one or two variables A highly skilled Excel user can use MasterMine together with other scenario building tools built into Excel to reliably plan for the future based on solid information about the past The Art of Presentation Discovering important facts about your business is often more an art than a science When working with pivot tables your changes from one report to the next are so immediate that you can easily let your creativity take over without fear of messing something up You can consider this a legitimate expression of the Art of Management you often read about As with any artistic endeavor being technically proficient with the tools and techniques is only the start but it opens up a whole world of satisfying creative opportunities Using the techniques described earlier in this manual you can freely dig around in your data for nuggets of information that tell you about your business We call the process of altering your data and placing it in interesting configurations analysis as opposed to reporting Sometimes you configureyour data in a new
219. ts box Furthermore you can use wildcards 82 in these elements to get all records that contain some value except for a particular character use as the default wildcard or character string using by default Group On Content Search Example You want to isolate completed Call records that users have coded different ways in the Reference field over time Sometimes they wrote in ABCD sometimes ABXD and sometimes ABED You want to include only those whose Reference field also contains the word sold In this case you would click the All radio button at the top enter the following without quotes into the box after Enter elements to be sought separated by commas AB D sold Finally name the group something non generic like ABxD and sold When you hit the OK button every field element visible in the row area will be searched and if it meets the criteria will be highlighted and grouped then shifted as a group to the top of the row area Now the user has many choices of how to use this grouping further 1 Double click on ABxD and sold to roll it up or down 2 Drag the new field Reference2 up to the page area and select ABxD and sold in the dropdown list next to it thereby filtering the report to include only those in the ABxD and sold group 3 almost anything else you can do with a single field item Date Ranges Group Using Date Fields Any date fi
220. ttons ate re named as described in the table in References called GoldMine Data Sources e The DURATION field in Cal and ContHist is interpreted according to context ___ format is converted to a decimal number of minutes for easier summary If the first character is a number the value of numeric characters up to the first non numeric character is returned If first character is a string 0 is returned If the field data is null 0 is returned 157 The corresponding field button is named Minutes or Sales as appropriate or Minutes Sales if both types of record are possible RECID is included in most tables as the most reliable count field labelled XXRecords where XX represents the source file Since count fields in the data area increment only if data are present in the field and RECID is the most likely to include some data we prefer to use this field for counts in the data area Be careful if you use another field as a count of data field only records with non null values in the target field get counted It s easy to trick yourself into believing false results 158 Appendix H Pivot Table Limitations There are certain limits to what can be displayed in a Pivot Table The limits ate a direct consequence of the extraordinary flexibility and agility that Pivot Tables offer you in displaying your data because Excel keeps all your data in live memory As Excel has evolved over the y
221. ty schedule summaries and linked to basic client Date info like source key fields etc CAC1C2 Analysis of pending sales from clients added in the last 120 days including probability schedule summaries and linked New Client Sales Pending to basic client info such as source key fields etc CAC1C2 Analysis of sales cycle which is number of days between the date the sale was forecast CH CreateOn date and date sale occurred CH OnDate Shows count of sales summaries Sales Cycle by Date Forecast etc CHC1C2 Analysis of sales cycle which is number of days between sale Sales Cycle from Date Lead date and the date account record was created CreateOn for Entered the account Shows count of sales summaries etc CHC1C2 133 Opportunities Toolkit MMOpportunities xls and MMOpportunitiesUnlinked xls GoldMine s Opportunities may be used in different ways depending on the conventions set up within your business Most companies link each opportunity to a contact record so by default MasterMine assumes this is the case However some organizations use Opportunities in such a way that the contact under which the opportunity is linked is irrelevant Enable the appropriate one in yout preferences see Chapter 3 Preferences page 24 The opportunity manager table OpMer contains a series of sub tables see GoldMine Data Structure documentation In MasterMine these sub tables have the following aliases as seen u
222. uild the group of only the selected rows OK Cancel MasterMine creates only one group member for each account number appearing on the page It colors the rows of would be duplicate members blue and the rows of members not found in the current GoldMine contact set red This means you can safely create non duplicated groups even from activity centric reports or from any other kind of report that would contain more than one row per GoldMine account Once you have created the group switch over to GoldMine itself The Group you created is sorted under the group code by default MM some users like to use the date to help identify groups that might be kept for a long time and may be activated using normal GoldMine procedures 92 There are many potential uses of groups in GoldMine You now have a capability that makes creating them far easier and faster even without technical knowledge MMUpdater The combination of MasterMine to build GoldMine Groups and GoldMine built in Global Replace capability is powerful but limited You can use it to modify data only at the account level Sometimes you need to update pending or history activity data data in your custom profiles or data in your opportunities or projects MasterMine offers an optional component to handle this need which also makes it even easier to modify Contactl and Contact2 data in a single step If your GoldMine administrator has licensed you to use MMUp
223. ultiplied into a wide range of derivative Reports some as additional worksheets within the original workbook and some as 51 whole new renamed copies of the workbook Users therefore sometimes think of every report as a template of sorts although the term template has a specific meaning in MasterMine Templates and Toolkits A Template is a model or set of pre determined parameters against which a Report is created Templates cannot be changed by the user The reports they create however can be infinitely varied MasterMine comes with more than 125 templates each of which can be quickly filled with GoldMine data to create a new Report Since each time a template is used a different set of fields might be included or a different filter applied These templates are infinitely re usable Beginning with version 6 0 MasterMine allows users to create new User defined Templates Creating a new template starts by selecting an existing basis template selecting fields to include and sorting them then saving the result with a user specified name Thereafter user defined templates appear alongside standard templates in the MasterMine template finder MasterMine Toolkits are collections of templates for producing a variety of pre defined reports workbooks each one named for its function and designed to anticipate a GoldMine management related information need For instance the Sales Manager toolkit cont
224. un simultaneously for MasterMine to function These components can operate together comfortably in about 512MB of RAM Your real hardware requirements depend directly upon the size of the data files you will be analyzing To work with relatively small GoldMine files you should have at a minimum a Pentium processor with 512MB of RAM Larger data files process much more conveniently with a faster processor and more RAM Since Pivot Tables operate by holding and manipulating large amounts of record level data in memory it follows that the more memory available the better 12 To estimate memoty requirements assume you will need roughly 25 to 75KB per record and multiply by the number of records in your largest database file probably ContHist For example a ContHist file with 100 000 records requires 80MB plus the roughly 48MB needed for the program files involved totaling roughly 128MB of RAM How MasterMine Works The MasterMine analysis engine loads your current data into MS Excel Pivot Tables to create dynamic overviews of all GoldMine data in every possible useful combination Based upon the internal logic of GoldMine and your customized setup MasterMine formats the data and renames fields per your local labels in GoldMine Managers who have used Pivot Tables in the past know that they are very easy and powerful to use but are notoriously difficult to set up MasterMine handles the hard part so you can reap the benefits
225. v EE v e drop down box below select the name of the Y DocName Mun Version A v OH DocNo OH Additional ddress QuoteWerks database in your SQL server To Y QH DocStatus QH AdditionalCharge report through MasterMine QuoteWerks must v H DocType QH AdditionalChargeCode v QH ExpectedCloseD ate QH AlternateAdditionalCharge be defined an the same server as GoldMine and Y QH GrandT otal QH AltemateCurrency opened using a username and password that iuc accesses both Select fields to include To create reports based on QuoteWerks data select the Templates menu dropdown and click PEA SaveTo Path C MasterMine Reports QuoteWerks Toolkit In the resulting Filter Finish Cancel dropdown list you will find a large selection of reports on Quotes Orders and Invoices each of which is configurable to virtually anything you 18 MasterMine Serial Number Required want to report on in QuoteWerks Each report has a version that shows either Document or Item level analysis and each can be created optionally including linked GoldMine data Licensing Workstations The first time you run MasterMine and any time you run it in demo mode the software gives you the option of entering a valid license for it Obtain a license from MasterMine directly or from your GoldMine Solutions Partner A valid MasterMine license functions ONLY with the GoldMine installation for which it was created If GoldMine licensin
226. vity level reporting but combines pending and history into a single report even allowing you to limit a report to calls appointments etc or any combination 2 Primary E mail address as a common field SQL only The e mail address for main contact appears in the field list as a C1 field the same as any other field in the Contactl main account table 4 Optional MMUpdater Add on MMUpdater allows users to make changes in Excel and push these changes to GoldMine in a way that is synch aware and conforms to GoldMine data access rules Details Plus and QuoteWerks Integration Automatically uses DetailsPlus field definitions when you select Details Profiles on the main report screen You get all fields from GoldMine Profiles available and the ability to display and analyze them as dates or values just like in DetailsPlus Users of QuoteWerks version 4 0 SQL version only can now access QuoteWerks only OR QuoteWerks data linked to GoldMine account data For example show all open quotes for records with a Key2 value of Active in GoldMine Add reports to GM Taskbar for 1 button launch From inside any report click the toolbar icon to deploy the report to any GoldMine user for easy 1 button launch Even allows you to conveniently set automatic refresh and to disable MSQuery or drilldown New Templates for Activity Analysis and Administration A dozen new templates including the new Ac
227. want to work with then clicking the Setup tab at the top of the dialog Note that in this example Field4 which corresponds to the Zip field in GoldMine s ContSupp table is labelledDate issue amp amp The amp amp at the end of the field label tells MasterMine to treat the contents of the Zip field for the profile called GM Master License as Dates With this setup in place a user can create a report on the GM Master License profile grouping Licenses by date ranges of years months dates or any combination thereof Likewise the designator at the end of Field5 field Ext in ContSupp tells MasterMine to return numeric data from the Users field so that we can conveniently group Licensees in regular groups like 0 10 11 20 21 30 etc As we ve noted in the Installation Section page 17 you can bypass this setup and get better use of Details in GoldMine by using DetailsPlus an add on product from Solica Consulting LLC In that case with Preferences set to read the DetailsPlus initialization file see Preferences page 24 MasterMine automatically sets up fields with appropriate types and labels 121 Appendix B Sample Session Here is a sample of creating and formatting a MasterMine report This example uses the Sales Pending report to give you valuable tips and tricks for use with all MasterMine templates Follow along by duplicating these steps using your own dat
228. workbook Connection x pivot table Checking this box disables this feature for the current workbook Show sample of source row fields inside GoldMine When drilling through to GoldMine by double clicking anywhere in a row of a drilldown sheet to pop the underlying record this feature shows the first eight fields of the source row This is useful especially if you drilled down from an Activity Report to remind the user which pending or history activity to bring up Open new window in GoldMine for found record By default drilldown causes the currently displayed record in GoldMine to change to that of the drilled down row potentially losing the user s place in GoldMine This option opens a new contact window in GoldMine so that the user can easily go back to the previouscontact The connection string defines the location of the reported GoldMine data as well as driver information A user may change this information for instance if the SQL database MasterMine Pivot Table Control Stats Drilldown Connection Query Text Security About ODBC DRIVER SQL Server SERVER ZGOLDMINE VM UID sa PWD Meee APP Microsoft Office 2003 WSID 49931 DATABASE GoldMine Caution IF changes to connection string cause pivot table to become invalid close workbook without saving to recover PA name or server location is changed Caution Invalid changes to the connection string are not trappe
229. x labeled re Eur field thus altering the data that is returned To return the 2 first 3 characters of the 3 ACCOUNTNO digit Column 3 Source field the new syntax ACTIONON m CALLBACKAT ken would be CALLBACKON Atrim C1 SOURCE X Cancel CALLBKFREQ Y lt gt Microsoft Query Query1 Column heading Source Sale Left rtrim C 1 E SOURCE 33 li Florence Flagg Harvey s Parts Other Maury Millford Skanda Systems Trade Show Pamela Nelson Henley Supplies NV Magazine Ad Patricia Phan Qualified Systesm NC Yellow Pages Note that the new expression Ivan Polanski Assembly Control Systen CA Unknown Mary Stafford Commerce Associates MI Trade Show Tospecl il 1 tw Arthur Lawson PCM Systems NY New York Times Suspect Aviation il contains bi nested Sandy Price Worldwide Warehousing CA Customer Referral Suspect Engineering 4 1 1 Grady Dermott DLP Systems Inc CA LA Times Prospect Media functions including the Stephen Camevale Madison Manufacturing NJ Trade Show Customer Engineering 401 4 1 Aaron Jamison Global Corp IL N Employee N original one Rtrim 0 gt which Kenneth Spaulding Global Corp N A Employee N A George Vaughn Restoration Systems TX Web Import Past Client Aviation trims trailing Spaces fr om the Kam Smith Atlantic Machines NJ New York Times Prospect Media Danny Kinner Lambert Solution Techn TX Customer Referral Suspect Pharmaceuticals C1 SOURCE field Glenn Ramos GESCO Internati
230. y to re format the color to match the appropriate source database Use the table of colors under References GoldMine Data Sources as a guide e If you have changed the tables or selection criteria we recommend refreshing yout MasterMine Display Box to reflect the changes Otherwise you may 109 misinterpret future reports taken from this data To refresh click once on the yellow down arrow button on the Display Box at upper right Redirect to a New Data Source You may occasionally need to redirect a MasterMine report to a new data source This might happen for instance if you install a new version of GoldMine and re host data to a new server Especially if you have a lot of reports with sophisticated setup in them you might want to preserve that work by re pointing to the new data source You can minimize the amount of effort necessary to do this if you follow a few rules For SQL databases e Do not rename the GoldMine database itself if possible e On your new server create usernames and passwords that are the same as on your old server e Unless you need to for security purposes avoid using specific database owner names instead of the default dbo For dBase databases e Keep a copy of your old database accessible during the turnover period so that old reports can still be opened long enough to re direct them to the new data e Because querying dBase data across a network can be very slow consider deployin
231. y active Check other sheets to make sure your changes are appropriate e Grouping data in a MasterMine report may be a difficult skill for some to master but it is the essential skill upon which data analysis is built Teach all of your users to work with Groups in MasterMine and you will take them a long way toward mastering their GoldMine data 84 Pivot Table Settings A pivot table itself has certain settings that can dramatically affect formatting Right click on a pivot table and select Table Options from the short menu Depending on your version of Excel this dialog may vary somewhat from the image here From this panel you can set your preferences for the Pivot Table you are working with Most selections are self explanatory and all are explained in the internal Excel documentation and help screens We ll point out just a few Format Options Grand Totals for Columns Rows Turns on off totaling for columns and tows AutoFormat Table If an AutoFormat has been applied to a pivot table unchecking this option turns off autoformatting and returns the pivot table to a generic non formatted PivotT able Options X state Name Subtotal Hidden Page Items Can Format options be used to include or exclude data IV Grand totals For columns Page layout Down Then Over from your report by Hiding es within fields in the Page Area This v Grand totals For rows Fields per column o can be very
232. y created reports by changing the number here Set this parameter to 0 to have ShortRef pull the entire reference field Note that there is a full Reference field also available in the field list as well as Product for sales related reports all of which pull data from the same REF field in the GoldMine data Default start date for history data When running a history report the user designates a starting date so that queries on very large databases won t unnecessarily bog down the process This is the default date that comes up in the Start Date field on both the front screen and the filter screen Date to substitute when date fields contain no data or unreadable date value Querying dates into a MasterMine report can provide powerful date range groupings etc However to accomplish this all items in a date field must contain a valid date If the source field is blank null or a character type field that contains a non valid date MasterMine substitutes the default Null date Save SQL Reports to MasterMine saves GoldMine reports to this location when the source data is MS SQL For reports with dBase source data the software automatically saves reports to the MasterMine folder within the GoldMine contact set folder to which they refer Toolkits Preferences Toolkits are sets of report templates listed under the Templates menu You can turn off toolkits you don t use on this tab in
233. y of the skills and techniques you will use to manipulate your pivot table involve using buttons on the MasterMine toolbar which greatly enhance native Excel pivot table functionality Here is a detailed look at the functions built into the MasterMine toolbar Refresh Data The Refresh submenu gives you several choices to refresh your Pivot Table data MasterMine Format Refresh Data Refresh 7 d 1 2 ML 38 i 651 Refresh All Purge Selected Field of Inactive Items Purge ALL Fields of Inactive Items Sort Items in Selected Field Simple Refresh Re query the underlying data to refresh report El Refresh All Refresh all pivot tables in the current workbook T Purge Selected Field Purges the field of inactive items The pivot table field remembers all items that have ever appeared in a field Over time adding new data and changing or deleting old data in your GoldMine database can cause the item list in a field to include many obsolete items We recommend purging old values that are no longer valid especially after doing significant data corrections Tal Purge All Purges ALL fields of Inactive Items Report wide purge that for systems with slow processors or in the case of very large databases may take considerable time A Zt Sort Field and Refresh Sorts items in the selected field After initial query each new value for a field is added at the end of the list This function re establishes
234. y to modify the undetlying query Check on this preference to store the SOL password within the Excel wotkbook and to use it automatically when needed Be aware that the password is not encrypted when saved in the Excel workbook and can therefore be hacked by a savvy user MasterMine does NOT expose this password unencrypted in any of its functions Always dock toolbar on report open For cleanest startup some users prefer to have the MasterMine toolbar dock with the other toolbars by default The alternative for frequent MasterMine toolbar users is to move it manually to the top of the Excel window when not in use 28 Hide fields list on report open The pivot table fields list is not necessarily needed for reports with stable design and for which fields will not be dragged in and out of visibility Hiding the fields list automatically reduces visual clutter when the report is opened Note that the list can be opened any time from the Pivot Table toolbar in Excel Document visible during refresh on open Sometimes reports are set to refresh on open reading data from the GoldMine database to make them current while loading rather than waiting for the user to manually click the refresh button Checking this item makes them visible before the refresh operation begins so that the user can see the progress of the refresh the number of queried items appears at the bottom of the Excel window Close dialog
235. you have a large database that is one containing more than say 20 000 account records or if you wish to report on details of completed activities more than 30 000 history recotds 161 Appendix I Limitations with Microsoft Office 97 At the time of this release MasterMine for GoldMine contains no known incompatibilities with Microsoft Excel 97 except for those documented here These documented limitations are inherent to Office 97 specifically MSQuery and are therefore not subject to correction Sophisticated users who find these limitations intolerable are advised to upgrade to a newer Office version in order to get the fullest possible functionality with MasterMine for GoldMine Reports generated under Excel 97 are fully upwardly compatible with Excel 2000 and higher Attempting to directly modify the query underlying a MasterMine created Pivot Table using Get Data in screen 2 of 4 in the Pivot Table Wizard which invokes MSQuery is likely to result in loss of assigned button names and the loss of formatting in the current PivotTable If you have already done this you can undo it by pressing Ctrl Z or exit the spreadsheet without saving Workarounds Load the unmodified spreadsheet on another machine in a higher version of Excel make the change and save it This will preserve formatting The saved document can be loaded in Excel 97 and will function normally Generate a new report using the MasterMine f

- MasterMine Software

Contents

Download Pdf Manuals

Related Search

Related Contents