A report: accounts by industry

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new RealCase3_ReportByIndustry().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.Extensions;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// A report, in two scenarios: accounts per industry with their total and average revenue (aggregates and group by,
/// ordered by an aggregate); and one number for a dashboard tile (a count with no grouping).
/// </summary>
public sealed class RealCase3_ReportByIndustry : ISample
{
    public string Name => "retrieve-multiple-real-3-report-by-industry";

    public string Title => "A report: accounts by industry";

    public async Task RunAsync(SampleContext dbContext)
    {
        var accounts = new List<ArxEntity>();
        var rows = new (string Number, Industry Industry, decimal Revenue)[]
        {
            ("ACC-R631", Industry.Retail, 1_000_000m),
            ("ACC-R632", Industry.Retail, 3_000_000m),
            ("ACC-R633", Industry.Finance, 9_000_000m),
            ("ACC-R634", Industry.Technology, 2_500_000m),
            ("ACC-R635", Industry.Technology, 1_500_000m),
            ("ACC-R636", Industry.Technology, 500_000m),
        };

        foreach (var (number, industry, revenue) in rows)
        {
            var account = new ArxEntity("account");
            account["name"] = "Report " + number;
            account["account_number"] = number;
            account["industry"] = new ArxSelectionSetValue("Industry", industry);
            account["annual_revenue"] = revenue;
            accounts.Add(account);
        }

        await dbContext.Service.CreateMultipleAsync(accounts);

        await Scenario1_PerIndustryAsync(dbContext);

        await Scenario2_ADashboardNumberAsync(dbContext);
    }

    /// <summary>1. Count, sum and average per industry, the industries with most accounts first.</summary>
    private static async Task Scenario1_PerIndustryAsync(SampleContext dbContext)
    {
        Step("1. Accounts, total and average revenue per industry");

        var query = new ArxQueryExpression("account") { AggregateInfo = new ArxAggregateInfo() };
        query.ColumnSet.AddColumn("industry");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R63");
        query.AggregateInfo.AddAggregateColumn("accounts", "", ArxAggregateOperator.Count);
        query.AggregateInfo.AddAggregateColumn("total_revenue", "annual_revenue", ArxAggregateOperator.Sum);
        query.AggregateInfo.AddAggregateColumn("average_revenue", "annual_revenue", ArxAggregateOperator.Avg);
        query.AggregateInfo.AddGroupByColumn("industry");
        query.AddOrder("accounts", ArxOrderType.Descending);

        var report = await dbContext.Service.RetrieveMultipleAsync(query);

        foreach (var row in report.Entities)
        {
            // The raw code read back; its option from the catalog.
            var industry = row.GetSelectionSet("industry", "Industry", dbContext.Catalog)?.DisplayText;
            Show($"{industry,-12} {row["accounts"]} accounts, total {row["total_revenue"]:N0}, average {(decimal)row["average_revenue"]!:N0}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT industry, count(*), sum(annual_revenue), CAST(AVG(annual_revenue) AS decimal(15, 0))
         * FROM account
         * WHERE account_number LIKE 'ACC-R63%'
         * GROUP BY industry
         * ORDER BY count(*) DESC;
         * -- Expect: 3 rows, 5 (Technology) 3 4500000.00 1500000, 1 (Retail) 2 4000000.00 2000000, 4 (Finance) 1 9000000.00 9000000.
         * ===========================================================
         */
    }

    /// <summary>2. One number for a dashboard: how many of these accounts earn a million or more.</summary>
    private static async Task Scenario2_ADashboardNumberAsync(SampleContext dbContext)
    {
        Step("2. One number for a dashboard tile");

        var query = new ArxQueryExpression("account") { AggregateInfo = new ArxAggregateInfo() };
        query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R63");
        query.Criteria.AddCondition("annual_revenue", ArxConditionOperator.GreaterEqual, 1_000_000m);
        query.AggregateInfo.AddAggregateColumn("big_accounts", "", ArxAggregateOperator.Count);

        var tile = (await dbContext.Service.RetrieveMultipleAsync(query)).Entities[0];
        Show($"{tile["big_accounts"]} accounts with a revenue of a million or more");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number LIKE 'ACC-R63%' AND annual_revenue >= 1000000;
         * -- Expect: 5.
         * ===========================================================
         */
    }
}

  > 1. Accounts, total and average revenue per industry
      Technology   3 accounts, total 4,500,000, average 1,500,000
      Retail       2 accounts, total 4,000,000, average 2,000,000
      Finance      1 accounts, total 9,000,000, average 9,000,000
  > 2. One number for a dashboard tile
      5 accounts with a revenue of a million or more