Summary
This custom report displays contact's phone numbers, email addresses, billing contact addresses as well as user extended attributes.
Configuration
There are basically two steps when creating a custom report:
Create a SQL view in Microsoft SQL Server Management Studio
Call the view from EngageIP Billing to display the custom report
Creating the SQL View
Open Microsoft SQL Server Management Studio
Connect to and open the BOSS database
Right Click on the VIEW directory and select the New View... option
Close the Add Table window
Copy the following query in to the view:
[cce lang="sql"]
SELECT TOP (100) PERCENT u.Name AS [User], u.ID AS UserID, CT.Name AS ContactType, C.Name, C.Title, X.EmailAddress, X.PhoneNumber, X.Address, u.CreatedDate
FROM dbo.Contact AS C INNER JOIN
dbo.ContactType AS CT ON C.ContactTypeID = CT.ID INNER JOIN
dbo.UserContactConnector AS ucc ON ucc.ContactID = C.ID INNER JOIN
dbo.[User] AS u ON u.ID = ucc.UserID LEFT OUTER JOIN
dbo.UserExt ON u.ID = dbo.UserExt.UserID LEFT OUTER JOIN
(SELECT CP.ContactID, MIN(CASE CPT.Name WHEN 'Email Address' THEN CP.Value END) AS EmailAddress, MIN(CASE CPT.Name WHEN 'Home Phone' THEN CP.Value END) AS PhoneNumber,
MIN(CASE CPT.Name WHEN 'Address' THEN CP.Value END) AS Address
FROM dbo.ContactPoint AS CP INNER JOIN
dbo.ContactPointType AS CPT ON CPT.ID = CP.ContactPointTypeID
GROUP BY CP.ContactID) AS X ON C.ID = X.ContactID
ORDER BY [user]
[/cce]SELECT TOP (100) PERCENT u.Name AS [USER], u.ID AS UserID, CT.Name AS ContactType, C.Name, C.Title, X.EmailAddress, X.PhoneNumber, X.Address, u.CreatedDate FROM dbo.Contact AS C INNER JOIN dbo.ContactType AS CT ON C.ContactTypeID = CT.ID INNER JOIN dbo.UserContactConnector AS ucc ON ucc.ContactID = C.ID INNER JOIN dbo.[USER] AS u ON u.ID = ucc.UserID LEFT OUTER JOIN dbo.UserExt ON u.ID = dbo.UserExt.UserID LEFT OUTER JOIN (SELECT CP.ContactID, MIN(CASE CPT.Name WHEN 'Email Address' THEN CP.Value END) AS EmailAddress, MIN(CASE CPT.Name WHEN 'Home Phone' THEN CP.Value END) AS PhoneNumber, MIN(CASE CPT.Name WHEN 'Address' THEN CP.Value END) AS Address FROM dbo.ContactPoint AS CP INNER JOIN dbo.ContactPointType AS CPT ON CPT.ID = CP.ContactPointTypeID GROUP BY CP.ContactID) AS X ON C.ID = X.ContactID ORDER BY [USER]
Click the Execute SQL button [ ! ] or [CTRL]+R, to run the query
Save the View (no special characters or spaces, under scores can be used in place of spaces). Remember the name of the view as you will need to enter this value in EngageIP Billing
EngageIP Configuration
Log in to EngageIP Billing
Click the Setup tab
Select the Custom Reports
Click the Add button
Enter a Name (no special characters or spaces, under scores can be used in place of spaces) for the report. The value entered here will become a role permission enabling only accounts within that role to view the custom report. It is recommended that you name all your custom reports with a common prefix so that they will be easy to locate within the role permission list.
Enter the following Connection String; replacing the database name, User ID and Password (XYZ) with the correct values;
server=localhost;Trusted_Connection=false;database=dbname;uid=XYZ;pwd=XYZ
Enter the name of the view you created in SQL in the Table field
Click Save
Logout and then back in to EngageIP Billing. This will reset the role permission set and allow you to grant access to the report
Click on the Setup tab
Select the Roles option
Select the role from the list to grant view permissions for this report
Scroll down to the Role Permissions section and locate the name of the report within the list
Select the LIST check box next to that permission
Scroll to the bottom of the permission set and click the Save button
Repeat steps 11-15 above until all applicable roles have been granted permission to view the report
Click the Reports tab
Scroll to the bottom of the screen where you will see a new section entitled; Custom Reports
Click on the Name of your Custom Report to view the output
Like all other lists within the application, custom reports can be filtered, printed, and/or exported using the appropriate list icons.