Using this document
This document contains a number of different report styles that represent a range of complexities, purposes and styles. Each report section contains a sample output for that report, followed by a detailed breakdown, including a description of what the report contains and its purpose, the underlying query for the report, and the queries for any associated sub-reports.
In each query, certain important elements have been highlighted to draw your attention to it. In each query, we have highlighted the referenced database tables in YELLOW. Important clauses, functions, or parameters, such as linking a report to a browselist, have been highlighted in BLUE.
Important Considerations
- You do not need to import sub-reports
- In the clause to limit the report to a browse list the table name that holds the key you are searching on MUST match the table name of the primary key.
- Do not forget to include dbo. before functions. Never put dbo. before field names.
- Use the properties bar on the right side of report builder for precision moves and resizing.
- Rebuild your data source if there are problems with the report definition while saving.
Entity Profile
Entity Profile Details
Description:
This report provides a summary of information for the selected entity, including: the contacts at that entity, a summary of that entity’s assets, the details of that entity’s assets, the 3 most recent completed activities, the 3 oldest activities not marked as completed, any accounts under that entity, and any other contacts and entities linked to that entity.
Query:
SELECT pbus.*, A.caddress1, A.caddress2, A.caddress3, A.ccity, A.cprovince, A.cZippost, A.ccountry FROM PBUS
JOIN PADDRESS A ON PBUS.IBUSKEY = A.IBUSCONKEY
WHERE PBUS.IBUSKEY IN (SELECT * FROM DBO.FN_GETCURRENTSELECTION('business',30))
Related Sub-Reports
Entity Profile – Contacts
SELECT *
FROM PCONTACT
WHERE PCONTACT.IBUSKEY = @IBUSKEY
Entity Profile – Asset Summary Query
SELECT *
FROM PASSET
WHERE PASSET.IBUSKEY = @IBUSKEY
Entity Profile – Asset Details
SELECT *
FROM PASSTALL
WHERE PASSTALL.IBUSKEY = @IBUSKEY
Entity Profile – Completed Activities
SELECT TOP 3 *
FROM PTICKLER
WHERE PTICKLER.IBUSKEY = @IBUSKEY AND LCLOSED = 1
order by dcomment DESC, campm,
CASE WHEN CTIME LIKE '12%' AND CAMPM = 'PM' THEN '00' + SUBSTRING(CTIME,3,10) ELSE CTIME END
Entity Profile – Undone Activities
SELECT TOP 3 *
FROM PTICKLER
WHERE PTICKLER.IBUSKEY = @IBUSKEY AND LCLOSED = 0
order by dcomment, campm,
CASE WHEN CTIME LIKE '12%' AND CAMPM = 'PM' THEN '00' + SUBSTRING(CTIME,3,10) ELSE CTIME END
Entity Profile – Linked Businesses
SELECT * FROM
(SELECT plink.ikey1 [OriginKey], pbus.Cbusiness [LINKEDBIZ], plink.Crole2 [LINKEDROLE],
paddress.Ccity [LINKEDCITY]
FROM PLINK
JOIN pbus ON plink.Ikey2 = pbus.Ibuskey
JOIN paddress ON pbus.Ibuskey = paddress.Ibusconkey
WHERE PLINK.CTABLE1 = 'pbus'
AND PLINK.ctable2 = 'pbus' UNION
SELECT plink.ikey2 [OriginKey], pbus.Cbusiness [LINKEDBIZ], plink.Crole1 [LINKEDROLE],
paddress.Ccity [LINKEDCITY]
FROM PLINK
JOIN pbus ON plink.Ikey1 = pbus.Ibuskey
JOIN paddress ON pbus.Ibuskey = paddress.Ibusconkey
WHERE PLINK.CTABLE1 = 'pbus'
AND PLINK.ctable2 = 'pbus') LINKS
WHERE LINKS.OriginKey = @IBUSKEY
Entity Profile – Linked Contacts
SELECT plink.Ikey1, RTRIM(pcontact.Cfirst) + ' ' + RTRIM(pcontact.Clast) [WHOLENAME],
pbus.Cbusiness [LINKEDBIZ], plink.Crole2 [LINKEDROLE]
FROM PLINK
JOIN pcontact ON plink.Ikey2 = pcontact.Iconkey
JOIN pbus ON pcontact.Ibuskey = pbus.Ibuskey
WHERE PLINK.CTABLE1 = 'pbus'
AND PLINK.ctable2 = 'pcontact'
AND plink.Ikey1 = @IBUSKEY
Entity Negligence Report
Entity Negligence Report Details
Description:
This report displays the Business, City, Rep Name, Number of Phone calls, Emails, Documents, Mass Mails, and other Activities for any business in the browselist that has not had an appointment within the specified date range, as well as the dates of the Last Activity and Last Appointment for that business.
Query:
SELECT pbus.ibuskey, temp.*
FROM pbus
INNER JOIN (
SELECT pbus.Cbusiness AS BusinessName, pbus.ibuskey AS BusinessId, paddress.ccity,pbus.crep,pbus.dlastactivity,pbus.dlastappt,
(SELECT COUNT(*) FROM pdoc WHERE pdoc.ibuskey=pbus.ibuskey AND
Ddocument>=@StartDate AND Ddocument<=@EndDate) AS DocCount,
(SELECT COUNT(*) FROM ptickler WHERE ptickler.ibuskey=pbus.ibuskey AND ptickler.lClosed = 1 AND ptickler.cactivity LIKE 'Appointment%' AND
ptickler.dcomment>=@StartDate AND ptickler.dcomment<=@EndDate) AS MeetCount,
(SELECT COUNT(*) FROM ptickler WHERE ptickler.ibuskey=pbus.ibuskey AND ptickler.lClosed = 1 AND (ptickler.cactivity LIKE '%Phone%' OR ptickler.cActivity IN ('conf.
call', 'fax', 'conference call')) AND ptickler.dcomment>=@StartDate AND
ptickler.dcomment<=@EndDate) AS PhoneCount,
(SELECT COUNT(*) FROM ptickler WHERE ptickler.ibuskey=pbus.ibuskey AND
ptickler.lClosed = 1 AND (ptickler.cactivity LIKE 'Email%' OR ptickler.cactivity LIKE 'E-Mail%')
AND ptickler.dcomment>=@StartDate AND ptickler.dcomment<=@EndDate) AS EmailCount, (SELECT COUNT(*) FROM ptickler WHERE ptickler.ibuskey=pbus.ibuskey AND ptickler.lClosed = 1 AND NOT ptickler.cactivity LIKE 'Appointment%' AND NOT (ptickler.cactivity LIKE '%Phone%') AND ptickler.cActivity NOT IN ('conf. call', 'fax', 'conference call')AND NOT
(ptickler.cactivity LIKE 'Email%' OR ptickler.cactivity LIKE 'E-Mail%') AND ptickler.dcomment>=@StartDate AND ptickler.dcomment<=@EndDate) AS OtherActCount, (SELECT COUNT(*) FROM pmail WHERE pmail.ibuskey=pbus.ibuskey AND pmail.ddatesent>=@StartDate AND pmail.ddatesent<=@EndDate) AS MassmailCount
FROM pbus
JOIN paddress ON pbus.ibuskey = paddress.ibusconkey
) AS TEMP ON pbus.ibuskey = temp.BusinessId
WHERE pbus.Ibuskey IN (SELECT * FROM dbo.fn_GetCurrentSelection('business',30)) AND MeetCount = 0
ORDER BY BusinessName
Related Sub-Reports
<<None>>
Contact Profile
Contact Profile Details
Description:
This report displays a detailed profile of the selected contacts, including: First Name, Last Name, Mailing Address, Entity
Switchboard, Job Title, Fax #, Email address, Direct Phone, Create Date, Mobile Phone, Contact Rep, Roles & Mailing Lists, Interests, Additional Contact Information, Any entities the contact is linked to, any other contacts the contact is linked to, the 3 most recent Activities for that contact, and any documents for that contact.
This report is tied to a browselist. The contacts included and the Sort Order are dictated by the browselist. Query:
SELECT pbus.Cbusiness, pbus.cbphswitch , paddress.Caddress1, paddress.Caddress2, paddress.Caddress3, paddress.Ccity + ', ' + paddress.Cprovince + ' ' + paddress.Czippost
[ADDRESSBLOCK], pcontact.*, dbo.fn_GetMultiPickList('CONLIST1', pcontact.iconkey) [MAILINGS],
dbo.fn_GetMultiPickList('CONLIST2', pcontact.iconkey) [INTERESTS]
FROM pcontact
JOIN pbus ON pcontact.Ibuskey = pbus.Ibuskey
JOIN paddress ON pcontact.Iconkey = paddress.Ibusconkey AND paddress.Lmailadd = 1
WHERE pcontact.Iconkey IN (SELECT * FROM dbo.fn_GetCurrentSelection('Contact',30))
Related Sub-Reports:
Contact Profile – Linked Businesses Query
SELECT plink.Ikey2 [OriginKey], pbus.Cbusiness [LINKEDBIZ], plink.Crole1 [LINKEDROLE],
paddress.Ccity [LINKEDCITY]
FROM PLINK
JOIN pbus ON plink.Ikey1 = pbus.Ibuskey
JOIN paddress ON pbus.Ibuskey = paddress.Ibusconkey
WHERE PLINK.CTABLE1 = 'pbus'
AND PLINK.ctable2 = 'pcontact'
AND PLINK.Ikey2 = @ICONKEY
Contact Profile – Linked Contacts Query
SELECT * FROM
(SELECT plink.ikey1 [OriginKey], paddress.Ccompany [LINKEDBIZ], plink.Crole2 [LINKEDROLE], paddress.Ccity [LINKEDCITY], RTRIM(pcontact.Cfirst) + ' ' + RTRIM(pcontact.Clast) AS
[LINKEDCONTACT]
FROM PLINK
JOIN pcontact ON plink.Ikey2 = pcontact.Iconkey
JOIN paddress ON pcontact.Iconkey = paddress.Ibusconkey AND paddress.Lmailadd = 1
WHERE PLINK.CTABLE1 = 'pcontact'
AND PLINK.ctable2 = 'pcontact' UNION
SELECT plink.ikey2 [OriginKey], paddress.Ccompany [LINKEDBIZ], plink.Crole1 [LINKEDROLE],
paddress.Ccity [LINKEDCITY],
RTRIM(pcontact.Cfirst) + ' ' + RTRIM(pcontact.Clast) AS [LINKEDCONTACT] FROM PLINK
JOIN pcontact ON plink.Ikey1 = pcontact.Iconkey
JOIN paddress ON pcontact.Iconkey = paddress.Ibusconkey AND paddress.Lmailadd = 1
WHERE PLINK.CTABLE1 = 'pcontact'
AND PLINK.ctable2 = 'pcontact') LINKS
WHERE LINKS.OriginKey = @ICONKEY
Contact Profile – Linked Activities
SELECT TOP 3 *
FROM PTICKLER
WHERE PTICKLER.iconkey = @ICONKEY
ORDER BY dcomment DESC, campm,
CASE WHEN CTIME LIKE '12%' AND CAMPM = 'PM' THEN '00' + SUBSTRING(CTIME,3,10) ELSE CTIME
END
Contact Profile – Linked Documents
SELECT *
FROM WHERE PDOC.ICONKEY = PDOC @ICONKEY
Duplicate Contact
Duplicate Contact Details
Description:
This report displays the Contact Rep, Contact Special ID, First Name, Last Name, Business Rep, Business Special ID, Relationship Type, City, Team Name, Privacy Scope, Create Date and Last Update Date for all contacts where the First Name and Last Name match in more than 1 record.
Query:
SELECT pcontact.cfirst, pcontact.clast, pcontact.Dcreated, PCONTACT.dlastupd, pbus.cbusiness, pbus.cClass, pbus.cUserpick14, pbus.cTeamName, pbus.cPrivacyScope, pbus.cspecialid AS BusinessID, dbo.SatuitFormatString(a.cCity) AS City, pcontact.cRep AS ContactRep, pcontact.cspecialID AS
ContactID, pbus.cRep AS EntityRep
FROM pcontact
JOIN pbus ON pcontact.ibuskey=pbus.ibuskey
LEFT JOIN paddress a ON pcontact.iConKey = a.iBusConKey AND a.lMailAdd=1
WHERE iConKey IN (SELECT iConKey FROM PContact WHERE
RTRIM(pcontact.clast)+RTRIM(pcontact.cfirst) IN
(SELECT RTRIM(pcontact.clast)+RTRIM(pcontact.cfirst)
FROM pcontact
GROUP BY RTRIM(pcontact.clast)+RTRIM(pcontact.cfirst) HAVING COUNT(*)>1))
ORDER BY cFirst, cLast
Related Sub-Reports:
<<None>>
Activity Long Form
Activity Long Form Details
Description:
This report displays the Start Date, End Date, Activity Rep, Other Internal Participants, Activity Type, Product, Completion Status, Primary Contact, Primary Contact’s Direct Phone, Subject, Switchboard for the Entity, and Comments for all actvities in the browselist.
The Date Range and Sort Order will be dictated by the browselist.
Query:
SELECT pbus.Cbusiness, ISNULL(pcontact.ccphDirect,'') ccphDirect, ptickler.*,
dbo.fn_getmultipicklist('activitylist2',ptickler.icomkey) [AdditionalContacts], pbus.cbphswitch FROM ptickler
JOIN pbus ON ptickler.Ibuskey = pbus.Ibuskey
LEFT JOIN pcontact ON ptickler.iconkey = pcontact.Iconkey
WHERE ptickler.icomkey in (select * from dbo.fn_GetCurrentSelection('Activity',30))
Related Sub-Reports:
<<None>>
Activity Summary
Actvitiy Summary Details
Description:
This report contains 4 charts that display Activities by Rep- by Month, Activities by Rep- by Activity Type, Activities by Product, and Activities by Relationship Type.
The charts are followed by the details of the included Activities, displaying the Actvity Rep, Entity,
Relationship Type, Product, Primary Contact, Activity Date, Activity Type, and Subject; grouped by Activity Rep, and sorted by Rep then Date.
This report is tied to a browselist. The Date Range is dictated by the broweslist and any filtering (Rep, Activity Type, etc) is done in the browselist.
Query: select ptickler.*, RTRIM(pbus.cClass) AS [cClass], pbus.Cbusiness from ptickler
JOIN pbus on ptickler.ibuskey = pbus.ibuskey
where ptickler.Icomkey in (select * from dbo.fn_GetCurrentSelection('activity',30))
Related Sub-Reports:
<<None>>
Opportunity Long Form
Opportunity Long Form Details
Description:
This report displays all the details of the included activities, including: Primary Contact, Opportunity Rep,
Product, Enter Date, Probability, Revenue Estimate, Relationship Type, Sales Phase, Target Assets, Origination, Current Status, Decision Date, Comments, Reason for Lost Business, Any Activities linked to the Opportunity, and any Contacts linked to the Opportunity.
Results are sorted by Enter Date.
Query:
SELECT PBUS.CBUSINESS, PPRODUCT.*
JOIN PBUS ON FFROM PRODUCT PRODUCT.ISBUSKEY = PBUS.IBUSKEY
WHERE PPRODUCT.IPRODKEY IN (SELECT * FROM dbo.fn_GetCurrentSelection('Sales',30))
Related Sub-Reports: Opportunity Long Form – Linked Activities
SELECT plink.Ikey2, ptickler.Lclosed, ptickler.Dcomment, ptickler.cActivity, ptickler.ccontact,
ptickler.Crep, ptickler.mcomment
FROM plink
JOIN ptickler ON plink.Ikey1 = ptickler.Icomkey
WHERE ctable2 = 'PPRODUCT'
AND ctable1 = 'PTICKLER'
AND plink.ikey2 = @IPRODKEY
ORDER BY DCOMMENT
Opportunity Long Form – Linked Contacts
SELECT plink.Ikey2, pcontact.Cfirst + ' ' + pcontact.Clast [Name], plink.Crole1,
paddress.Ccompany, paddress.Ccity
FROM plink
JOIN pcontact ON plink.Ikey1 = pcontact.Iconkey
join paddress on pcontact.Iconkey = paddress.Ibusconkey AND Lmailadd = 1
WHERE ctable2 = 'PPRODUCT'
AND ctable1 = 'Pcontact'
AND plink.ikey2 = @IPRODKEY
Opportunity Pipeline
Opportunity Pipeline Details
Description:
This report contains 2 charts, an Opportunity Count by Sales Phase, and an Opportunity Revenue by Sales Phase.
The charts are followed by a table containing the details of the included Opportunities, including: Sales Cycle, Entity, Opportunity Rep, Product, Sales Amount, Current Status, Trend, Last Updated Date, Curent Aging, and Aging.
Aging shows the number of days between the date of the report and the Create Date, and Current Aging shows the number of days between the Last Update Date and the date of the report.
The Table is grouped by Sales Cycle, Sorted by Sales Cycle, then by Opportunity Rep, with a Total Sales Amount for each Sales Cycle and a Grand Total.
Query: select pproduct.*,udf1.*,pbus.Cbusiness,udf3.modon3
from pproduct full join
(select pproduct.Iprodkey,d.cOldValue oldvalue1,d.cNewValue newvalue1,cModifiedBy
modby1,dModifiedOn modon1 from pAuditData d
join pAuditTran t on d.iTranKey = t.iTranKey join pproduct on iPrimaryKey = Iprodkey
where cColumn = 'cudf1'
and dModifiedOn > dateadd(mm,-2,getdate())
and cOldValue is not null and cTable = 'pproduct'
and d.iDataKey in (select MAX(d.iDataKey) from pAuditData d join pAuditTran t on
d.iTranKey = t.iTranKey join pproduct on iPrimaryKey = Iprodkey where cColumn = 'cudf1'
and dModifiedOn > dateadd(mm,-2,getdate())
and cOldValue is not null and cTable = 'pproduct'
group by pproduct.Iprodkey)) udf1 on pproduct.Iprodkey = udf1.Iprodkey join (select pproduct.iprodkey,dModifiedOn modon3 from pAuditData d join pAuditTran t on d.iTranKey = t.iTranKey join pproduct on iPrimaryKey = Iprodkey
where cColumn = 'cudf1' and cTable = 'pproduct'
and d.iDataKey in (select MAX(d.iDataKey) from pAuditData d
join pAuditTran t on d.iTranKey = t.iTranKey join pproduct on iPrimaryKey = Iprodkey
where cColumn = 'cudf1' and cTable = 'pproduct'
group by pproduct.Iprodkey)) udf3 on udf3.Iprodkey=pproduct.Iprodkey
join pbus on pbus.Ibuskey = pproduct.Ibuskey
where pproduct.lclosed = 0 AND pproduct.ldeleted = 0
Related Sub-Reports:
<<None>>
Document Report
Document Report Long Form Details
Description:
This report displays Document record details, including: RFP, Document Rep, Product, Assigned To: (Dept.),
Document Type, Auto Renew Detail, Primary Contact, Date Entered, Date Due, Date Sent, Term, Term Details, How Sent?, Current Status, Notice Requirement, Notice Detail, and Effective Date
This report is tied to a browselist. Date Range and Sort Order are dictated by the browselist
Query:
SELECT PBUS.CBUSINESS, PDOC.*
FROM PDOC
JOIN PBUS ON PDOC.IBUSKEY = PBUS.IBUSKEY
WHERE PDOC.IDOCKEY IN (SELECT * FROM dbo.fn_GetCurrentSelection('Document',30))
Related Sub-Reports:
<<None>>
Travel Itinerary
Travel Itinerary Details
Description:
This report is intended to show upcoming appointments, for example, to organize visits to clients on a business trip. It is tied to a browse list search, and therefore criteria such as Date Range, Rep, City, etc. are dictated by the browse list. The report displays the Date, Entity, Rep, Entity Address, Switchboard, Fax #, Website, Primary Contact (Attendee), any additional key contats, directions to the location, and comments/details.
The report is sorted by Date and Time, oldest to newest.
Query:
SELECT PTICKLER.*, PBUS.cbusiness, PBUS.cbphswitch, PBUS.cbphfax, PBUS.cbwebsite, PBUS.mcomment,
PADDRESS.caddress1, PADDRESS.caddress2,
PADDRESS.Ccity + ',' + PADDRESS.Cprovince + ' ' + PADDRESS.Czippost AS [ADDRESS BLOCK],
PBUS.mcomment AS [pbus_mcomment]
FROM PTICKLER
JOIN PBUS ON PTICKLER.ibuskey = PBUS.ibuskey
JOIN PADDRESS ON PBUS.ibuskey = PADDRESS.ibusconkey
WHERE PTICKLER.Lclosed = 0
AND PTICKLER.cActivity LIKE 'Appointment%'
AND PTICKLER.icomkey in (select * from dbo.fn_GetCurrentSelection('activity',30)) ORDER BY Dcomment, Campm,
CASE WHEN Ctime LIKE '12%' AND Campm = 'PM' THEN '00' + SUBSTRING(CTIME,3,3)
ELSE Ctime
END
Related Sub-Reports: Itinerary Report Attendee with Key Query
SELECT pTicklerContacts.iComKey, pTicklerContacts.iConKey, pTicklerContacts.cContact,
pTicklerContacts.lPrimary, PCONTACT.Ctitle, PCONTACT.ccphDirect
FROM pTicklerContacts
JOIN PCONTACT ON pTicklerContacts.iConKey = PCONTACT.Iconkey
WHERE pTicklerContacts.iComKey = @iComKey;
Itinerary Report with Key Contact Query
SELECT pTicklerContacts.iComKey, pTicklerContacts.iConKey, pTicklerContacts.cContact, pTicklerContacts.lPrimary, PCONTACT.Ctitle, PCONTACT.ccphDirect, PCONTACT.lkey
FROM pTicklerContacts
JOIN PCONTACT ON pTicklerContacts.iConKey = PCONTACT.Iconkey
WHERE pTicklerContacts.iComKey = @iComKey;
Contact Mailings
Contact Mailings Details
Description:
This report shows a matrix of contacts by mailing list. It displays each contact at the entity and which mailing list they are included on, with a total count of mailings that individual received. Each column shows a count of how many contacts receive that particular mailng.
This report is tied to a browse list and any criteria are dictated by that browse list. The report is grouped by entity.
Query: select
rtrim(pbus.cbusiness) as 'Business',
rtrim(pcontact.cfirst)+' '+rtrim(pcontact.clast) as 'Contact',
p_pick.cdesc as 'Mailing List', pcontact.lkey as 'Key Contact', prep.cfullname as 'Rep' FROM pcontact
join pbus on pcontact.ibuskey = pbus.ibuskey join plist on plist.ibusconkey = pcontact.iconkey join p_pick on plist.ipickkey = p_pick.ipickkey and p_pick.ctag = 'CONLIST1'
join prep on pcontact.crep = prep.crep where pcontact.Iconkey in (select * from dbo.fn_GetCurrentSelection('contact',300))
Related Sub-Reports:
<<None>>