1. Help Center
  2. Reporting
  3. Corporate SQL Reports

DirectScale Client SQL Reports

Avatar
Learning Experience Team

Admin: Corporate Admin
Page: Reports > Custom
URL: [CLIENTID].corpadmin.directscale.com/Corporate/Reports/Custom
Permission: ManageCustomReports() or ViewCustomReports()

On the Custom Reports page, you’ll find a wide range of reports created by DirectScale to provide your company with valuable data for business intelligence.

These reports resemble the custom SQL reports you may have created previously. You can use the reports in their current form or as a starting point to find the data that you need the most.

Adding Values to Reports

Several reports require you to enter specific values to properly function, such as Back Office ID or KPI. Reports that require values:

  • Associates with most growth (edit to specify KPI)

  • Autoship Orders Declined w/Reason (last 30 days)

  • Binary Report left/right leg (edit to specify KPI)

  • Search Most Recent Login (edit to specify user)

  For details on each report, see the Report Definitions section or the instructions provided in the report’s SQL.

To add your value:

  1. Click the button.

    Edit button

    You navigate to the SQL Report Builder page.

    SQL Report Builder page

  2. Use the instructions in the SQL to add or know where to add your values, some with alternate values you could use instead. For example, in the Search Most Recent Login (edit to specify user), you have the options to input the Associate’s Back Office ID or username.

  3. In the panel, click Save.

    A pop-up opens with the Report Name field populated with the report’s current name.

    Save Report pop-up

  4. Leave the name unchanged to save changes to the report. If you update the name and confirm the save, then the report will save under the new name; it does not create a new report.

  5. In the pop-up, click the Save button.

Editing Reports

If you want to customize a report, complete the following steps:

  To make changes to a report, copy SQL and create a new report; otherwise, the system update will override your changes.

  1. Click the button.

    Edit button

    You navigate to the SQL Report Builder page.

    SQL Report Builder page

  2. Copy the SQL by pressing Ctrl A on your keyboard to highlight all, then Ctrl C to copy.

    Copy SQL data

  3. Navigate back to the Custom Reports page by clicking the button on your browser.

  4. Create a new Custom SQL Report.

    Add Report button

Report Definitions

  • Active Associates Count by Type - A summary of the Associates with an "Active" status (status ID 1) grouped by Associate Type.

  • Associate Top Earners (last 6 Months) - Totals the commissions earned for the previous six months rounded to the nearest dollar. This total includes commissions earned regardless of payment status.

  • Associates All Current Commissions Stats - This shows all Associates with every stat for the current open period.

  • Associates not on Autoship - This shows all active Associates who do not have an AutoShip.

  • Associates with GV without Orders this Month - This shows Associates who have Group Volume (GV) and the last date they placed an order to find Associates who may be missing out on qualifications.

  • Associates with most growth (edit to specify KPI) - This shows data based on committed periods and the growth percentage for a specified KPI for an Associate. Top 100 top growing Associates. Compare the last committed period to the second to last committed period. You must update this report by modifying the following before the report will return the data:

    • SET @SalesVolumeKPI = 'EQVKPI' - Replace EQVKPI with your total sales volume KPI or group volume KPI or any KPI of which you want to analyze the growth.

    • SET @TemplateName = 'XMLWeekly' - Replace XMLWeekly with the template name of your commissions stats template.

    • SET @LastPeriodMin = 100; - This shows only Associates who earned more than 100 two periods ago. Play with this number to see Associates that started with smaller or larger numbers to compare.

  • Associates with Tree Movements (last 30 days) - Associates who have had a Tree Movement in the previous 30 days and the corporate user that authorized the move.

  • Associates as Potential Duplicates (last 6 months) - Lists anyone with the same first and last name who has signed up in the previous six months in a downline with their Upline ID.

  • Autoship Created each Month (Summary) - This shows the total number of AutoShips created each month. If a month did not have any AutoShips created, you would not see a row for that month.

  • Autoship Orders Declined w/Reason (last 30 days) - This shows all declined AutoShips in the previous 30 days and the reason why provided by the payment provider (merchant). Modify the following SQL to change the OrderType to see all orders.

    • WHERE O.OrderDate >= DATEADD(DAY, -30, GETDATE()) AND O.OrderType = 2 - To see any Order Type, remove AND O.OrderType = 2

  • Autoship Profiles Cancelled (last 30 days) - List of AutoShips canceled within the previous 30 days.

  • Autoship Profiles Created (last 30 days) - This shows all AutoShip profiles created in the previous 30 days.

  • Autoship SKU Search - Specify a SKU to return a list of all Associate AutoShip templates with that SKU by modifying the following SQL:

    • WHERE SKU = 'DS123' - Replace DS123 with the SKU you want to report on

  • Autoship SKUs (Summary) - This shows a list of all SKUs with a count of how many AutoShip templates currently have that SKU attached.

  • Autoships Created each Month by Type (Summary) - This shows the total number of AutoShips created each month sorted by Associate Type. If a month or Associate Type did not have any AutoShips created, you would not see a row for that month/type.

  • Binary Report left/right leg (edit to specify KPI) - This shows the current KPIs for Associates with their Binary Leg data all in one view. Update this report to specify your Binary KPIs from your compensation plan.

    • set @NewRight = 'RLQV'; - Replace RLQV with your KPI for New Right Leg Volume.

    • set @CFRight = 'RLCOKPI'; - Replace RLCOKPI with your KPI for Carry Forward Right Leg Volume.

  • Clawbacks (last 30 days) - This report shows clawbacks by Associates and the total amount clawed-back.

      If importing clawbacks, ensure to include the OrderNumber or they will not show in this report.

  • Commissions Paid (last 30 days) - This shows all the commission payment records for the previous 30 days.

  • Coupons Used (last 30 days) - This shows all coupons used on orders in the previous 30 days.

  • New Associates by State (last 30 days) - This shows the number of new Associates by state/region in the previous 30 days. To see this report by country, modify the following SQL:

    • A.State, - Use A.CountryCode for count by country.

    • AND D.SignupDate > DATEADD(day, -30, getDate()) - Use DATEADD(month, -1, getDate()) to see the previous calendar month.

    • GROUP BY A.State - Use A.CountryCode if using count by country.

  • Order Count and Revenue by Assoc Type (last month) - This shows the total number of orders, the total revenue, CV, and QV by Associate Type for the previous calendar month. Revenue is calculated using the subtotal minus discounts and does not include shipping, tax, or RWD (rewards) payments. To adjust the date filtering, modify the following SQL:

    • AND O.Orderdate >= DATEADD (mm, DATEDIFF (mm, 0, GETDATE ()) - 1, 0) AND O.Orderdate < DATEADD(month, DATEDIFF(month, 0, GetDate()), 0) - To see the last rolling 30 days, comment out the previous line and use instead AND O.Orderdate >= DATEADD(DAY, -30, GETDATE()).

  • Order Package Info (last 7 days) - This shows all orders invoiced (orders marked "Paid") in the previous seven days with order totals and package information.

  • Orders Count by Month (current and last year) - This shows the count of all paid orders by month for the current and previous year.

  • Percent of Autoship Sales (last 30 days) - This report calculates the subtotal of sales for the previous 30 days and shows the percentage that was AutoShip orders. To see a different date range, update both DATEADD statements.

  • Potential Duplicate Orders in the last 5 days - This shows Associates who have placed more than one successful order in the previous five days, which may be duplicate orders.

  • Rank Advancement (current month) - This shows who advanced Rank this month based on the first time they hit a new High Rank. This report works on uncommitted periods and is based on Stats. Modify the following SQL to specify the commissions template information:

    • SET @YOURCompPlanName = 'YOURMonthly' - Replace 'YOURMonthly' with the commission plan revision name that sets Ranks (most commonly your monthly plan).

    • WHERE PeriodType = 'Monthly' - If you establish Ranks weekly, replace Monthly.

  • Rank Advancement (previous month) - This shows who advanced Rank last month based on the first time they hit a new High Rank. This report works on committed periods and is based on the CommissionAssociateValues table.

  • Revenue by Assoc Type & Ord Type (cur & last year) - This shows the month-over-month growth of each Associate Type broken out by Order Type to show even more detail of revenue trends. Revenue is subtotal minus discounts, not including shipping, tax, and RWD (rewards) payments.

      This report would be great to turn into a line chart showing the history of each Associate type/order type.

  • Revenue by Country by Month (current & last year) - This shows the total revenue (subtotal minus discounts) by month for the current and previous year. This report does not include shipping, tax, or RWD (rewards) payments.

      Export this report to Excel and create some cool graphs to illustrate market growth.

  • Revenue by Month (current and last year) - This report shows the total of all sales by month for the current and previous year. It shows the subtotal minus any discounts. This report does not include shipping or tax.

  • Revenue Monthly Growth % (current & last year) - This report shows the total revenue (subtotal minus discounts) with the country’s month-over-month growth percentage for the current and previous year. Totals do not include shipping, tax, or RWD (rewards) payments. A country’s first month will show the Revenue as the percent increase.

  • RMA Reason Report (last 30 days) - This shows the SKU details and notes included with each Return Merchandise Authorization (RMA) for the previous 30 days.

  • Sales by SKU with Option Details (last 30 days) - Similar to the Sales by SKU report but with the names of the options added to make it easier to understand. This report shows sales for each SKU+SKU Option in each currency.

  • Search Most Recent Login (edit to specify user) - This shows the sign-up date and last login timestamp of Associates. You can also use this report to check employee login timestamps by modifying the following SQL:

    • WHERE D.backofficeID = '15F92' - Input Back Office ID here.

    • Or, to search via username, remove the previous WHERE statement in favor of WHERE u.username = 'samspace'.

    • For a corporate employee, insert their login username in WHERE u.username = '{employee_login_username}'. Employees are all tied to the CRM_Distributors data for your Corporate Account; however, username and last login will be unique.

  • Services/Subscriptions Expiring (next 30 days) - This shows the Associates Services expiring in the next 30 days.

  • Shipped Voided Orders (last 30 days) - This shows voided orders in the previous 30 days that still have a shipment record. Use this report to find orders that have been sent to your fulfillment center and may need to be canceled.

  • SKU Search with Order Details (last 30 days) - Specify a SKU to see everyone who purchased it in the previous 30 days. Similar to the Sales by SKU report but this report can not be filtered by country.

  • SKUs with Options and their SKUExtensions - Lists all SKUs with options set and their SKU Extensions.

Was this article helpful?
0 out of 0 found this helpful
Previous Next

Comments

0 comments

Please sign in to leave a comment.