...
This custom report provides a complete listing of missing jurisdiction codes when using the On-prem Avalara Billsoft integration or the Avalara Avatax Cloud integration.
Configuration
Create view
Paste in SQL
Code Block language sql SELECT dbo.[USER].ID AS UserID, dbo.[USER].Name AS [USER], CAST(dbo.ContactPointAddressAttributeJurisdictionCode.JurisdictionCode AS VARCHAR) AS JurisdictionCode, dbo.ContactPointAddress.Address1, dbo.ContactPointAddress.Address2, dbo.ContactPointAddress.ZipCode, dbo.Contact.Name AS ContactName, dbo.Contact.Company, dbo.ContactPointAddress.CityName, dbo.ContactPointAddress.StateName, dbo.Country.Name FROM dbo.ContactPoint INNER JOIN dbo.Contact ON dbo.ContactPoint.ContactID = dbo.Contact.ID INNER JOIN dbo.ContactPointAddress ON dbo.ContactPoint.ID = dbo.ContactPointAddress.ContactPointID INNER JOIN dbo.ContactPointAddressAttributeJurisdictionCode ON dbo.ContactPointAddress.ID = dbo.ContactPointAddressAttributeJurisdictionCode.ContactPointAddressID INNER JOIN dbo.Country ON dbo.ContactPointAddress.CountryID = dbo.Country.ID LEFT OUTER JOIN dbo.[USER] RIGHT OUTER JOIN dbo.UserContactConnector ON dbo.[USER].ID = dbo.UserContactConnector.UserID ON dbo.Contact.ID = dbo.UserContactConnector.ContactID
Click Execute button to check that the query runs
Click Save, enter the name of the view - Remember the name of the view created as it will be used below
Click the Setup tab
Select the Custom Reports option under the Configuration section
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 User ID and Password (XYZ) with the correct values:
Code Block server=localhost;Trusted_Connection=false;database=boss;uid=XYZ;pwd=XYZ
Enter name of custom view in the Table field (table name can also be a view name)
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 under the Account and Roles section
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
...