The Reports kit — reports a team builds, and the overview it opens every morning

use Osysharp.Reports@1; use Osysharp.Explore; · policy ReadsReports => …; policy SharesReports => …; · class X : IReportSource { ReportSource Describe() } · ReportOverview(source, figures, now:, columns:, trend:, build:) · ReportBuilder(source, open:) · Keyed(source) · RunSourceReport(key, query, preset) · RunOverview(…) · ReportCsvOf(key, query, preset) · Explore.Root<T>() · Explore.Path<T>(x => x.A.B) · Explore.Run(query) · Explore.Fields(root, depth, pivots) · Explore.Choices(root, path, search) · class X : IExplorePivots { List<ExplorePivot> Pivots() }

`Osysharp.Reports` lets a team build its own reports — start from one thing the app keeps, pick fields by following its relations (Order › Café › Region), group, measure (counts, sums, averages, medians, percentiles, shares) and filter (by pointing, in nested "all of" / "any of" groups, and on the groups themselves) — and gives the app an overview page in three lines. Every report is compiled by the platform (`Osysharp.Explore`) into aggregate SQL run as its reader; the page receives the answer, never the rows. `Osysharp.WorkQueues.Reports` adds the inbox's measures.

Summary#

Say what the app reports on, once, in its own words; put the overview and the builder on pages:

use Osysharp.Reports@1;
use Osysharp.Explore;

policy ReadsReports => IsStaff;       // who opens reports
policy SharesReports => IsLead;       // who shares a saved report with everybody

public class OrderReports : IReportSource {
  public ReportSource Describe() { return OrderSource(); }
}

ReportOverview(OrderSource(), [Orders], build: "/reports/build");  // on the overview page — measures by reference
ReportBuilder(OrderSource());                                      // on the builder page

Signature#

// the source — a class per root, found without being listed (App.Implementations)
public interface IReportSource { ReportSource Describe(); }
class ReportSource { Key, Title, Root, Noun, Nouns, Date, List<ReportMeasure> Measures, List<ReportDimension> Dimensions,
                     List<string> RowFields }
class ReportMeasure { ExploreMeasure Measure; string Label; string Hint; FigureFormat Format; bool HigherIsBetter = true; }
class ReportDimension { string Field; string Label; }

// a report, as data — what the builder makes and a saved report keeps
class ExploreQuery { Root; List<ExploreGroup> GroupBy; List<ExploreMeasure> Measures; ExploreFilterGroup Where;
                     List<ExploreHaving> Having; SortBy; Descending; Limit; Zone; WeekStart; List<ExplorePivot> Pivots }
class ExploreMeasure { Name; MeasureKind Kind; Field; Since; Fraction; List<ExploreFilter> Where; Numerator; Denominator }
class ExploreFilterGroup { FilterMatch Match = All; List<ExploreFilter> Filters; List<ExploreFilterGroup> Groups }
class ExploreFilter { Field; FilterOp Op; string[] Values; CollectionUse Through; string[] Labels }

// the platform's compiler
string Explore.Root<T>()                         // the root's name, checked
string Explore.Path<T>(x => x.A.B.C)             // a path from it, checked against the model
ExploreResult Explore.Run(ExploreQuery q)        // one aggregate statement, as the caller reads
List<ExploreField> Explore.Fields(string root, int depth, List<ExplorePivot> pivots = [])   // every field the caller may reach

// rows read as columns — a custom field's answers — found without being listed
public interface IExplorePivots { List<ExplorePivot> Pivots(); }
class ExplorePivot { Element; Key; Value; Label; Kind; List<ExplorePivotKind> Kinds; Choices }   // paths, checked
class ExplorePivotKind { Is; FieldKind Reads; string[] Choices; string[] ChoiceLabels }
List<ExploreChoice> Explore.Choices(string root, string path, string search, int limit)

// the kit
ReportSource Keyed(ReportSource source)   // marks each measure as the source's own, so it can be handed elsewhere
ReportAnswer RunSourceReport(string key, ExploreQuery q, PeriodPreset preset, DateOnly? first = null,
                             DateOnly? last = null, bool compare = false)
ExploreResult RunSourceNow(string key, ExploreQuery q)          // as things stand: no period
ReportAnswer RunSourceRows(string key, ExploreQuery q, string measure, PeriodPreset preset, DateOnly? first = null,
                           DateOnly? last = null, List<ExploreColumn>? columns = null, ExploreRow? row = null,
                           int limit = 200)   // the rows one figure (or one group's figure) counted — a drill-through
OverviewAnswer RunOverview(string source, List<ReportMeasure> figures, List<ReportMeasure> now,
                           List<ReportMeasure> columns, ReportMeasure? trend, int by, PeriodPreset preset)
string ReportCsvOf(string key, ExploreQuery q, PeriodPreset preset, DateOnly? first = null,
                   DateOnly? last = null)   // the screen's rows, as CSV
Guid SaveReport(string key, string name, ExploreQuery q, PeriodPreset period, bool shared, DateOnly? first = null,
                DateOnly? last = null)      // a chosen period's days are kept with it
ReportOverview(ReportSource source, List<ReportMeasure> figures, List<ReportMeasure> now = [],
               List<ReportMeasure> columns = [], ReportMeasure? trend = null, string build = "", string title = "")
ReportBuilder(ReportSource report, Guid? open = null)
// the clock: Osysharp.TimeZone — its time zone and the day a week starts, on Settings › Time and date (/settings/clock)

Description#

One root, fields by relation#

A report counts ONE kind of row, its root. Every other field is reached by following references from it, up to three steps, and is offered with its trail. One collection may be crossed on a path ("Order › Lines › Coffee"); a field past it says "one per line", and a grouping across it counts each root row once per group, never once per child.

How does a custom field become a column?#

A field an app's admins add to a record — "Their plan" on a customer — is kept as ROWS, one answer per record and field. A report reads them as COLUMNS of the record: "Ticket › Customer › Their plan" is found by its name in the picker, groups by its options, filters and is measured like any field the app declares. A kit says which rows those are and how each kind of field reads (IExplorePivots, found without being listed); Osysharp.CustomFields does, so an app on it declares only the relation back from the record to its answers:

entity Customer {
  [ForeignKey(Customer)] CustomerFieldValue[] Fields;   // the relation a report reads the columns through
}
[Unique(Customer, Field)]                               // one answer per customer and field — what makes it ONE value
entity CustomerFieldValue : FieldValue { [Required] Customer Customer; }

A collection is read this way only when its record holds one row per key, by a unique rule over the record's reference and the key; without one it stays a collection. The columns are the keys the READER's rows name: a field they may read no answer of has no column, and a report naming it is refused. Text kept as a number reads as a number (summed, averaged, compared), text kept as a day as a date (grouped by week or month), a list as its options.

Closed to what the reader may not read#

A field masked for the reader, or a path through an entity they may read none of, is never offered, and a query naming one is refused with a sentence and the paths listed (Unreachable) rather than answered smaller. A base type (the inbox's WorkItem) is reachable when any type it holds is; each row is still read by its own type's rule. Every report runs as its reader: a shared report shows each person their own rows.

Periods on the report clock#

A period preset (today, 7, 30, 90 days, this week, month, quarter, year, last month) is read on the app's clock (The TimeZone kit — the time zone an app's days start in, picked from a list — the time zone and week start staff pick in Settings › Time and date), so a day starts at midnight in that zone and a week on its first day. A trend has a row for every bucket, a quiet one included. "Dates…" takes two days somebody chooses (PeriodPreset.Custom with first and last), and a saved report keeps them.

The rows behind a figure#

Every figure opens the rows it counted: a headline figure's "Show the rows", and a row of a grouped table. The list is the source's RowFields of each root row the report's conditions, its period, the group's own values and the measure's own conditions admit — "Shipped" for one region lists that region's shipped orders, not all of them; a ratio lists its denominator's rows; a day of a trend lists that day's. A group is narrowed by each grouped column's ExploreCell.Key, the value as a filter names it. A source with no RowFields offers no rows.

Filters, groups of filters, and conditions on groups#

A condition fits its field's kind (choices, records, numbers, durations, dates, words, yes or no, empty or not). They nest in "all of" and "any of" groups, and each branch is read as its reader may read. "Show groups where" filters the groups by their measures (HAVING), kept apart from the rows.

The overview joins sources on a grouping of the same name#

An overview names its measures by reference — promises.KeptShare, the KeptShare measure of a source made with Keyed(…) — never as a name typed in a string, so a misspelt one is a compile error. In the breakdown its column comes from that source grouped by ITS grouping with the same label, matched by the record each row is for. A source with no grouping of that name shows a dash.

The inbox's measures#

Osysharp.WorkQueues.Reports — which arrives by itself in an app that uses Inbox and Reports (Bridges — a package that arrives when an app uses the two it completes) — gives InboxWorkSource(key, root, words) (arrived, done, first reply and time to done in calendar time, open now) and InboxPromiseSource(key, words) (promises ended, kept, missed; business-hours medians). Both take the app's InboxReportWords, so they group by the same names.

Examples#

app ReportsExample {
  use Osysharp.Ui;
  use Osysharp.Identity@0;
  use Osysharp.Reports@1;
  use Osysharp.Explore;
  model "model/**/*.osy";
}

using Osysharp.Identity;
using Osysharp.Explore;
using Osysharp.Reports;

[Role] enum RoasteryRole { Authenticator, Roaster, Owner }
policy IsRoaster => RoleGrant.Any(g => g.Member == user && (g.Role == RoasteryRole.Roaster || g.Role == RoasteryRole.Owner));
policy IsOwner => RoleGrant.Any(g => g.Member == user && g.Role == RoasteryRole.Owner);
policy ReadsReports => IsRoaster;
policy SharesReports => IsOwner;
policy ManagesClock => IsOwner;      // who sets the time zone a day is counted in (Osysharp.TimeZone)

entity Region { [Required, MaxLength(60)] string Name; security { allow read, create when IsRoaster; } }
entity Cafe {
  [Required, MaxLength(120)] string Name;
  [Required] Region Region;
  security { allow read, create when IsRoaster; }
}
entity Order {
  [Required] Cafe Cafe;
  DateTime PlacedAt;
  DateTime? ShippedAt;
  decimal Kilos;
  security { allow read, create when IsRoaster; }
}

/// The source, with its measures as members — an overview names them by reference.
public class OrderReport {
  public ReportSource Source = new ReportSource();
  public ReportMeasure Orders = new ReportMeasure();
  public ReportMeasure Kilos = new ReportMeasure();
}

public OrderReport TheOrderReport() {
  var r = new OrderReport();
  r.Orders = new ReportMeasure { Label = "Orders", Format = FigureFormat.Count, Measure = new ExploreMeasure { Name = "orders" } };
  r.Kilos = new ReportMeasure { Label = "Kilos", Format = FigureFormat.Number,
    Measure = new ExploreMeasure { Name = "kilos", Kind = MeasureKind.Sum, Field = Explore.Path<Order>(o => o.Kilos) } };
  r.Source = Keyed(new ReportSource {
    Key = "orders", Title = "Orders", Root = Explore.Root<Order>(), Noun = "order", Nouns = "orders",
    Date = Explore.Path<Order>(o => o.PlacedAt), Measures = [r.Orders, r.Kilos],
    Dimensions = [new ReportDimension { Field = Explore.Path<Order>(o => o.Cafe.Region), Label = "Region" }] });
  return r;
}

public class OrderReports : IReportSource {
  public ReportSource Describe() { return TheOrderReport().Source; }
}

[Title("Orders overview")]
[Route("/overview")]
[Authorize(ReadsReports)]
[Render(CSR)]
component OrdersOverview() {
  render {
    var o = TheOrderReport();
    ReportOverview(o.Source, [o.Orders, o.Kilos], build: "/reports");
  }
}

[Title("Orders report")]
[Route("/reports")]
[Authorize(ReadsReports)]
[Render(CSR)]
component OrdersReport() {
  render { ReportBuilder(TheOrderReport().Source); }
}

// a measure counting only some rows, and a time between two dates
new ExploreMeasure { Name = "shipped", Where = [new ExploreFilter { Field = shipped, Op = FilterOp.HasValue }] }
new ExploreMeasure { Name = "toShip", Kind = MeasureKind.Percentile, Fraction = 0.9m, Field = shipped, Since = placed }
// a share of two counts
new ExploreMeasure { Name = "shippedShare", Kind = MeasureKind.Ratio, Numerator = "shipped", Denominator = "orders" }
// "all of: courier is bike; any of: organic, or more than 8 kg"
new ExploreFilterGroup { Filters = [new ExploreFilter { Field = "Courier", Op = FilterOp.Is, Values = ["Bike"] }],
  Groups = [new ExploreFilterGroup { Match = FilterMatch.Any, Filters = [
    new ExploreFilter { Field = "Organic", Op = FilterOp.Is, Values = ["true"] },
    new ExploreFilter { Field = "Kilos", Op = FilterOp.Above, Values = ["8"] }] }] }