Custom Report - Billing Contact Email Addresses

Owned by Mahnoor Khan (Deactivated)
Last updated: Nov 24, 2023 by Micah Gallant
3 min read

Summary

This article explains how to add a custom report that allows you view Customer's Billing Contact's email addresses.

Configuration

  1. Create a table or view which will hold the data for your custom report using Microsoft SQL Server Management Studio.

    • Populate that table using an external query or other method to gather the necessary data. You may also chose to populate the table using a scheduled job that regularly updates the data in the table

      SELECT TOP (100) PERCENT dbo.[USER].ID AS UserID, dbo.[USER].Name AS [USER], dbo.ContactPoint.Value AS [Email Address], dbo.Contact.Name, dbo.ContactType.Name AS ContactType, dbo.[USER].Account FROM dbo.[USER] INNER JOIN dbo.StatusType ON dbo.[USER].Current_StatusTypeID = dbo.StatusType.ID INNER JOIN dbo.UserContactConnector ON dbo.[USER].ID = dbo.UserContactConnector.UserID INNER JOIN dbo.Contact ON dbo.UserContactConnector.ContactID = dbo.Contact.ID INNER JOIN dbo.ContactPoint ON dbo.Contact.ID = dbo.ContactPoint.ContactID INNER JOIN dbo.ContactPointType ON dbo.ContactPoint.ContactPointTypeID = dbo.ContactPointType.ID INNER JOIN dbo.ContactType ON dbo.Contact.ContactTypeID = dbo.ContactType.ID WHERE (dbo.StatusType.Name = N'Active') AND (dbo.ContactType.Name = N'Billing Contact') AND (dbo.ContactPointType.Name = N'Email Address') ORDER BY [USER]

      Or, create a view that you can reference to pull the data
      Note: You must have a userid and user column in your view in order to ensure that account names will be hyperlinked in your report (i.e. Select id as ‘UserID’, name as ‘User’ from [user])
      Note: Role profile questions are not shown on custom report view screens. This is to simplify the custom report framework and improve performance and access. If you require profile questions to show, these will need to be added to your custom report views
      Note: EngageIP only currently recognizes the following datatypes: int, double, decimal, money, varchar, nvarchar, datetime. Please be sure to CAST your data appropriately

  2. Log in to the EngageIP Billing application

    • Click on Setup

    • Under the Configuration section select the Custom Reports option

    • Click the Add button

    • Specify a Name for the report (this will show on the reports page and as a role permission) Tip: If possible try to prefix all your custom report names with similar value. This will make it easier to locate them in the role’s permission list later.

    • Specify the Connection String using a format like this: Data Source=127.0.0.1;Initial Catalog=Boss;Persist Security Info=True;User ID=sa;Password=test

    • Enter the name of the Table in the Table you created in step 1 above (this can be a VIEW name as well from the database)

    • If you would like to ensure the report only returns data specific to an owner or user out of the box, you can enter the ObjectID as being OwnerID or UserID in the Target Entity field. Or you can specify the ‘Restrict By’ dropdown to limit it as well. Once specified, the report with no filters applied in the interface will return data specific to that ownerID or userID. Note: If your query or view already specify an ownerID, that will take precedence over the interface setting

    • Check or uncheck Load View as needed – this option allows you to tell the report to load data immediately when you click on the custom report, or return blank initially to allow the admin user to enter some filter criteria first. This is useful when the report is very large, it allows the admin to limit the date range of returned values to minimize performance and wait time

    • Click the Save button

  3. Log out of EngageIP Billing and then log back in. This step is important, if you skip it you will not be able to view your custom report

  4. Once you’ve logged back in to the system you can now grant permission to which roles will be able to view the report

    • Click on Setup

    • Under the Accounts and Roles section select the Roles option

    • Select the role you wish to give view rights to the custom report

    • Search for the name of the report under the Role Permissions section

    • Select the List check box next to that permission

    • Scroll to the bottom of the permission list and click the Save button

    • Repeat the above steps for each role that should be able to view the report

  5. Your report is now accessible. To view the report:

    • Click Reports

    • Scroll to the bottom of the screen to see your Custom Reports

    • Click on the custom report’s name