Public API GatewayAbstractions Reference
Guides
Help CenterAsk a Dev

Creating a Custom Report Page for Web Office

Suggest Edits

In the Web Office, Associates have access to pages that report on data such as their commissions and order history. If you want to represent other types of data, such as a highest earners report, Sponsor orders report, etc., you can create a custom Web Office page that pulls in data from a custom API.

Prerequisite knowledge:

  • Knowledge of AngularJS (to build the custom page)
  • Visual C# (to create the custom API)
  • SQL (to query the data from the Database)

The specific elements you need to create are:

  • SQL query to get the data
  • Custom API that calls the SQL query
  • Custom page that calls the custom API

Getting the data with SQL

Admin: Corporate Admin
Page: Reports > SQL Manager
URL: {Client_ID}.corpadmin.directscale.com/Corporate/Reports/SqlViewer
Permissions: SqlManagerAdministrator(), SqlManagerBasic(), or SqlManagerReadOnly()

You can use the SQL Manager to test out your SQL query to ensure you're pulling the correct data.

  1. In the SQL Query box, type your SQL statement. For examples of SQL queries, see SQL Query Library.
  2. Click the Run button to view the query. The query results will display under Query Results.

Once your SQL statement is ready to go, keep it handy for later when creating the custom API.

👍Read more: Using the SQL Manager to View Data.

Creating a Custom API

The custom page you will build can only pull in the data via an API. In most cases, however, the data must come from a custom Client API endpoint.

✅

To learn how to create a custom API that pulls report data, see Creating a Custom API.

You create the elements of your endpoint in your Client Extension. You can structure your data model however you wish.

The two main elements that you need to pull in Database data are:

1. Connect to the Database

public class GetHighestEarnersApi : IApiEndpoint
    {
        private readonly IDataService _dataService;

        public GetHighestEarnersApi(IDataService dataService, IRequestParsingService requestParsingService)
        {
            _dataService = dataService;

IDataService provides connection strings for direct Database access.

3. Define a Route in the API Definition

public ApiDefinition GetDefinition()
        {
            return new ApiDefinition
            { 
                Route = "demo/GetHighestEarners",
                Authentication = AuthenticationType.V1
            };
        }

We declare the Route that will trigger the custom API to be called. The URL path will be prefixed with /Command/ClientAPI/ to call this custom endpoint. Do not set Route to a value starting with /.

For example, setting Route to demo/GetHighestEarners can be called from your Corporate Admin domain + /Command/ClientAPI/demo/GetHighestEarners.

AuthenticationType sets the authentication type to use for the endpoint. In this example, we've set it to V1. The alternate option is None.

❗Warning

If set to None, your API will be publically exposed and can be called by anyone. Furthermore, you will have to do authentication manually (see Headers). DirectScale's authentication procedures do not verify authorization. If your custom API is doing something for a specific Associate, make sure that only the Associate initiates the request.

2. Write a response with the SQL query

  1. Install the Dapper NuGet package. The example function below uses query, which is a method defined in Dapper. However, using Dapper is optional. You can use whatever method of data access you want.

  2. Add using Dapper; at the top of the file.

  3. Write a response function that pulls in your SQL data query:

         public IApiResponse Post(ApiRequest request)
     {
         GetHighestEarnersRequest getHighestEarnersRequest = _requestParsingService.ParseBody<GetHighestEarnersRequest>(request);
         using (var _dbConnection = new SqlConnection(_dataService.ConnectionString.ConnectionString))
         {
             string query = @"SELECT top @NumberOfAssociates d.FirstName, d.LastName, d.BackofficeID, s.HighestRank, SUM(h.Amount) AS Amount
                   FROM CRM_CommissionHistory h
                   LEFT JOIN CRM_Distributors d ON h.AssociateID = d.recordnumber
                   LEFT Join CRM_Stats s on h.AssociateID = s.AssociateID
                   WHERE h.PostDate >= DATEADD(DAY, -@NumberOfDays, GETDATE())
                   GROUP BY d.BackofficeID, d.LastName, d.FirstName, s.HighestRank
                   ORDER BY Amount DESC;";
             var associates = _dbConnection.Query<IEnumerable<HighEarner>>(query, getHighestEarnersRequest);
             return new Ok(associates);
         }
     }

Building a Custom Web Office Page

Admin: Web Office Admin
Page: Code Customization > Custom Office Pages
URL: {Client_ID}.admin.directscale.com/#/CustomPages
Permissions: Custom Pages

With your custom API in tow, you can now focus on your custom page. Read the following resource on calling APIs in Custom Pages:

In Angular, to make the call to the API, you must use the $RestService V3PostMethod call:


$RestService.DynamicV3PostMethod(baseUrl, request, true, true).then(function (result) {var response = JSON.parse(result.data.ResponseBody); console.log(response);
}).catch(function (err) {console.log(err);});

Custom API Proxy details these Proxy options.

For an example of a report page to build from:

We have a few docs with guidance on creating custom pages:

Updated over 3 years ago