How to Exclude Test Users from Billing in Atria

Overview

In this article, we will guide you through the process of creating non-billable users for a customer. The method of displaying non-billable users in Atria is to establish billing rules within the Service Billing Configuration page. To achieve this, we will use a database query to identify test users. This approach ensures that the report prioritizes the defined rule, marking the identified test users as non-billable.

Database Configuration

Run SQL Server Management Studio on your Atria database server and connect to your database engine, then add the following table function to your OLM database:

USE [OLM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[tf_ExcludeTestUsers]
(
    @BillPeriodID INT
)
RETURNS @Result TABLE
(
    ObjectID INT,
    MaxAuditID INT
)
AS
BEGIN
    INSERT INTO @Result (
        ObjectID,
        MaxAuditID
    )
    SELECT 
        ObjectID,
        MAX(AuditID) AS AuditID
    FROM 
        PropertyValues_AT
    WHERE 
        PropertyID = (
            SELECT PropertyID
            FROM Properties p
            WHERE p.Name = 'employeetype'
        )
        AND ActionDate <= (
            SELECT EndDate
            FROM BillPeriod
            WHERE BillPeriodID = @BillPeriodID
        )
        AND PropertyValues_AT.Value = 'True'
    GROUP BY 
        ObjectID

    RETURN 
END
GO

User Configuration

1. Logon to Atria web portal.
2. Select a customer by navigating to Customers > Customers and select a customer, then click on Users.
3. Edit an existing user or create a new user.
4. Click on Additional User Properties and select Test User checkbox as shown below:

5. Provision the user.

Billing Reports Configuration

1. In Atria click on Reports > Service Billing Configuration.
2. In the Service Billing Rule Configuration, Click on New Rule to create a new rule so that you can exclude test users.
3. Enter a Description and Override Reason.
4. It is important to enter 1 in Processing Order:

5. Select a value for the Valid From and Valid To date.
6. Click on the Rule Type Drop-Down box and select Custom Where Clause:

7. To exclude test users in a particular Bill period, enter the following text inside the Where Text box:

BillItems.UserID in (select u.UserID from Users u inner join (select * from dbo.tf_ExcludeTestUsers(@BillPeriodID)) as A on u.ObjectID = a.ObjectID)

8. Alternatively, to exclude Test Users without any time filtration, enter the following script in the Where Text box:

SELECT BillItems.UserID FROM BillItems WHERE BillItems.UserID IN (SELECT vw.UserId FROM vw_UserUserProperties vw WHERE ((vw.Property ='employeeType' AND (vw.Value ='True'OR vw.Value ='1'))

9. Click Save to add the rule to the Service Billing Rule Configuration.
10. Click on Reprocess Billing Records.
11. Click on Reports > Service Billing Details to view customer and user services.
12. The test users that were created before will show No under Billable:

info

If you find the Test User previously set is not shown correctly to the report, uncheck Test User, Provision, and again check Test User and Reprovision the user.