Custom Report - Account Services With Full Address Details

Summary

This article outlines how to create a custom report to display a list of account services with customer addresses attached per contact on each account.

There are two steps when creating a custom report:

1. Create a SQL view in Microsoft SQL Server Management Studio

2. Call the view from EngageIP to display the custom report

Configuration

Creating the SQL View

1. Open Microsoft SQL Server Management Studio

2. Connect to and open the BOSS database

3. Right Click on the VIEW directory and select the New View... option

4. Close the Add Table window

5. Copy the following query in to the view:

   SELECT dbo.UserService.ID, dbo.UserService.ServiceID, dbo.UserService.UserID, dbo.UserService.CreatedDate, dbo.UserService.UserPackageID,
   dbo.Service.Name AS Service, dbo.[USER].Name AS [USER], dbo.UserService.Name, dbo.UserService.BillTimes, dbo.UserService.Amount,
   dbo.UserService.Optional, dbo.UserService.OptionalServiceStartDate, dbo.UserService.OptionalTransactionDate,
   dbo.UserService.OptionalServiceBillDate, ISNULL(dbo.UserService.OneTimeAmount, 0) AS OneTimeAmount, dbo.ContactType.Name AS ContactType,
   dbo.ContactPointAddress.Address1, dbo.ContactPointAddress.Address2, dbo.Contact.FirstName, dbo.Contact.LastName, dbo.Contact.Company,
   dbo.City.Name AS City, dbo.State.Name AS State, dbo.Country.Name AS Country
   FROM dbo.Contact INNER JOIN
   dbo.UserContactConnector INNER JOIN
   dbo.[USER] ON dbo.UserContactConnector.UserID = dbo.[USER].ID ON dbo.Contact.ID = dbo.UserContactConnector.ContactID INNER JOIN
   dbo.ContactPoint ON dbo.Contact.ID = dbo.ContactPoint.ContactID INNER JOIN
   dbo.ContactPointAddress ON dbo.ContactPoint.ID = dbo.ContactPointAddress.ContactPointID INNER JOIN
   dbo.ContactType ON dbo.Contact.ContactTypeID = dbo.ContactType.ID INNER JOIN
   dbo.City ON dbo.ContactPointAddress.CityID = dbo.City.ID INNER JOIN
   dbo.State ON dbo.ContactPointAddress.StateID = dbo.State.ID AND dbo.City.StateID = dbo.State.ID INNER JOIN
   dbo.Country ON dbo.ContactPointAddress.CountryID = dbo.Country.ID RIGHT OUTER JOIN
   dbo.UserService ON dbo.[USER].ID = dbo.UserService.UserID LEFT OUTER JOIN
   dbo.Service ON dbo.UserService.ServiceID = dbo.Service.ID

Note: To limit based on Billing Contact for example, add 'WHERE     (dbo.ContactType.ID = 1)'. You may need to find the correct ID in the ContactType table for the contact type you are looking for

6.Click the Execute SQL button [ ! ] or [CTRL]+R, to run the query
7. 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

EngageIP Configuration

1. Log in to EngageIP

2. Click the Setup tab

3. Select the Custom Reports option under the Configuration section

4. Click the Add button

5. 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

6. Enter the following Connection String; replacing User ID and Password (XYZ) with the correct values

   server=localhost;Trusted_Connection=false;database=boss;uid=XYZ;pwd=XYZ

7. Enter the name of the view you created in SQL in the Table field

8. Click Save

9. Logout and then back in to EngageIP. This will reset the role permission set and allow you to grant access to the report

10. Click on the Setup tab

11. Select the Roles option under the Account and Roles section

12. Select the role from the list to grant view permissions for this report

13. Scroll down to the Role Permissions section and locate the name of the report within the list

14. Select the LIST check box next to that permission

15. Scroll to the bottom of the permission set and click the Save button

16. Repeat steps 11-15 above until all applicable roles have been granted permission to view the report

17. Click the Reports tab

18. Scroll to the bottom of the screen where you will see a new section entitled; Custom Reports

19. 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.