Creating Custom Reports in EngageIP Billing

Summary

This article outlines the process of creating custom reports in EngageIP Billing.

A custom report is one which uses an SQL query or an existing database view to return data. This allows you to make any report you want in EngageIP. It even allows the ability to collect and report on data across more database than just EngageIPs database.

How to Create a Custom Report

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

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

   2. 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]) If you are using Package or User Package columns, note that permissions are based off the column names. So if you have a column named Package and its really a user package, permissions will apply to any hyperlinks on it as if it was related to setup tab package items, and not USER items.
      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.
      Notice: EngageIP only currently recognizes the following datatypes, please be sure to CAST your data appropriately: int, double, decimal, money, varchar, nvarchar, datetime

2. Log in to the EngageIP Billing application and configure the Custom Report settings

   1. Click on Setup

   2. Click on Custom Reports

   3. Click the Add button

      Open

      

      1. Specify a name for the report (this will show on the reports page and as a role permission) NOTE: Do NOT use a system table name as this will conflict with existing reports
         Note: 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

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

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

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

      5. 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 FIRST limit the date range of returned values to minimize performance and wait time

   4. Click the Save button or Save/New to add another Custom Report

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. Grant permission to which roles will be able to view the report:

   1. Click on Setup

   2. Click on Roles

   3. Select the role you wish to give view rights to the custom report

   4. Search for the name of the report under the Role Permissions section

   5. Click the LIST check box next to that permission

   6. Scroll to the bottom of the permission list and click the Save button

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

5. Your report is now accessible, to view it:

   1. Click on Reports

   2. Scroll to the bottom of the screen to see your Custom Reports

   3. Click on the custom report's name

The created report will now output the contents of the table you created using the same format as all other reports in the system. You can use the view to filter the report, create custom views, export and/ or print the report as you would with any other list.

Troubleshooting Custom Reports

If you are experiencing errors running your custom report or do not see it at all on the reports tab, please review these troubleshooting items:

• Is your connection string correct?

  • Is your IP / hostname correct

  • Is the username and password correct?

  • Try creating an ODBC connection from the web server to the SQL server to be sure that your credentials are valid

• Did you turn on the permissions on your role? You will need to enable the permission of the role

  • Don't see the report in your permissions list? Make sure to log out and  log back in to see the updated list of selectable permissions

• Your permissions are set but you do not see the report

  • First log out after saving your role permissions, this will prompt the AdminPortal to reload your permissions, then check the report page after logging back in

• Custom report is not emailing users in it when clicking 'email' link and selecting canned message

  • Make sure that UserID exists as the first column, the system will use that to know whom to email using the canned email option