Custom Report - Billsoft User Tax Exempt Levels

Summary

This custom report lists user's tax exempt levels in Avalara Billsoft integrated environments.

Configuration

1. Create view

2. Paste in the SQL

   SELECT dbo.[USER].ID AS UserID, dbo.[USER].Name AS 'User', cpa.StateName, coun.Name AS Country, dbo.TaxExemptLevel.Name AS TaxExemptLevel, dbo.TaxExemptType.Name AS TaxExemptType, ust.Name AS UserStatus, ub.Balance FROM dbo.UserAttributeTaxExemptLevel INNER JOIN dbo.[USER] ON dbo.UserAttributeTaxExemptLevel.UserID = dbo.[USER].ID LEFT OUTER JOIN dbo.TaxExemptLevel ON dbo.UserAttributeTaxExemptLevel.TaxExemptLevelID = dbo.TaxExemptLevel.ID LEFT OUTER JOIN dbo.TaxExemptType ON dbo.UserAttributeTaxExemptLevel.TaxExemptTypeID = dbo.TaxExemptType.ID INNER JOIN UserContactConnector ucc ON ucc.UserID = [USER].ID INNER JOIN Contact c ON c.ID = ucc.ContactID INNER JOIN ContactType ct ON ct.ID = c.ContactTypeID INNER JOIN ContactPoint cp ON cp.ContactID = c.ID INNER JOIN ContactPointAddress cpa ON cpa.ContactPointID = cp.ID INNER JOIN Country coun ON coun.ID = cpa.CountryID INNER JOIN UserStatusType ust ON ust.ID = [USER].UserStatusTypeID INNER JOIN ViewUserBalanceFuture ub ON ub.UserID = [USER].ID WHERE ct.Name IN ('billing contact')

3. Click Execute button to check that the query runs

4. Click Save, enter the name of the view - Remember the name of the view created as it will be used below

5. Click Setup

6. Select the Custom Reports option under the Configuration section

7. Click the Add button

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

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

10. Enter name of custom view in the Table field (table name can also be a view name)

11. Click Save

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

13. Click Setup

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

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

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

17. Select the LIST check box next to that permission

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

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

20. Click Reports

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

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