Curating a reporting schema for self-service users: views, friendly names and what to hide

By Razi Syed. Sample code: github.com/dotnetreport/dotnetreport-aspnetcore-quickstart

Most self-service reporting projects don't fail in the report designer. They fail the first time a user opens the field list and sees tbl_ord_hdr, IsDeleted, RowVersion, LegacyStatusCd2 and four hundred tables in alphabetical order. They pick the wrong Status column, join orders to the wrong customer key, get a number that's off by the soft-deleted rows, and from then on "the reports are wrong" — even though every query ran exactly as written.

The fix isn't a better UI. It's treating the schema you expose to report builders as a product surface: curated, named for humans, pre-joined where joins are hard, and with the dangerous parts simply absent. This article is the checklist I use, with the setup screen of Dotnet Report (which I work on — disclosure) as the concrete example. The principles apply whatever tool you use.

The rule: curate it like a public API

Your database schema is shaped for your application: normalized, full of plumbing columns, named by whoever wrote the migration in 2017. A reporting schema should be shaped for the question "what happened to the business?" Those are different consumers, and you'd never ship your internal tables as a public API. Same idea here.

Concretely that means four decisions per table: does it go in at all, what is it called, how does it join, and what's hidden.

1. Prefer views over raw tables

My default is that end users report on views, not base tables. A view lets you fix the problems once, in SQL, under version control, instead of hoping every user remembers them:

CREATE VIEW reporting.Orders AS
SELECT
    o.OrderId,
    o.OrderDate,
    c.CompanyName      AS Customer,
    r.RegionName       AS Region,
    o.Subtotal,
    o.TaxAmount,
    o.Subtotal + o.TaxAmount AS OrderTotal,
    CASE o.StatusCode
        WHEN 1 THEN 'Open'
        WHEN 2 THEN 'Shipped'
        WHEN 3 THEN 'Cancelled'
    END                AS Status,
    o.TenantId
FROM dbo.tbl_ord_hdr o
JOIN dbo.Customers c ON c.CustomerId = o.CustomerId
LEFT JOIN dbo.Regions r ON r.RegionId = c.RegionId
WHERE o.IsDeleted = 0;

That one view removes soft-deleted rows, turns a magic status code into words, pre-computes the total everybody would otherwise calculate slightly differently, and flattens the two joins that users get wrong most often. Put views like this in their own schema (reporting. here) and ship them in your normal migrations, so they're reviewed like any other code.

In Dotnet Report's setup screen (/dotnetsetup), the Database Tables tab loads both tables and views from your connection — views are marked (view) — and you tick the ones to expose. The ones you don't tick simply don't exist as far as report builders are concerned. Start with fewer than you think: ten well-shaped views beat two hundred raw tables.

When raw tables are fine: small, already-clean lookup tables (Products, Regions) where a view would just be SELECT *. Don't add ceremony where there's no problem to fix.

2. Name everything for the reader

Every table and column you expose gets a Display Name. tbl_ord_hdr becomes Orders, cust_nm becomes Customer Name. In the setup screen the database name stays visible on hover ("DB field is …"), so developers can still trace a field back to the source while users only ever see the friendly one.

Two other per-column settings are worth five minutes each:

• Field Type. Beyond the obvious types, you can mark a numeric column as Money or Percentage, and a date column as Date Only or Time Only. The designer uses the type to decide what makes sense for a column — numeric types like Money can be summed and charted on a value axis, a Percentage column is formatted as a percentage by default — so it's the difference between 0.1834 and 18.34%.
• Categories. Group tables into a handful of categories (Sales, Inventory, Finance) via Manage Categories, so the table list reads like a menu instead of a dump.

A naming convention helps more than any individual name: singular vs plural, "Date" suffix on every date, no abbreviations. Write it down once and apply it everywhere.

3. Get joins right once, centrally

Self-service tools build SQL dynamically, so they need to know how tables relate. The Relations tab is where that lives, and it gives you three ways to fill it:

• Import from Database reads the foreign keys actually defined in your database.
• Auto Add Joins guesses joins from column names ending in Id and shows each candidate with a status (New, Already exists, and so on) before adding it.
• Add new Join for anything else, with a join type of INNER, LEFT, RIGHT or CROSS, plus extra AND conditions for composite keys.

Treat the guesses as a draft. The classic mistake is an INNER join from orders to an optional relationship (a sales rep, a promo code) that silently drops every order without one. If a relationship is optional, make it LEFT. And if a join path is genuinely ambiguous — orders have both a billing and a shipping customer — don't make users choose: pre-join it in a view with two clearly named columns.

Lookups instead of ids. Users shouldn't filter on RegionId = 7. Mark the column as a Foreign Key and point it at the lookup table: the Foreign Key Field is the id, the Foreign Value Field is what users see in the designer. Tick Only for Filter? when you want the friendly dropdown in filters, but the column in the report itself should keep its stored value without joining the lookup table in. You can also set a parent for a foreign key to get cascading filters (pick a country, then only its regions).

4. Decide what to hide — and what to never include

There are two different things people mean by "hide", and mixing them up is how data leaks.

Presentation hiding. Do Not Display removes a column (or a whole table) from what report builders can pick, while keeping it available for joins. Use it for keys and plumbing columns that a join needs but no human wants to see: CustomerId, RowVersion, TenantId.

Security exclusion. Password hashes, tokens, raw personal data you don't need for reporting, internal notes — these shouldn't be in the reporting schema at all. Leave them out of your views. A column that isn't selected can't leak through a bug, a misconfiguration or a creative custom SQL field later on. The view is your allowlist.

Between those two sits role-based access. Each table and column has Manage Role Access, where you tick which roles may use it (the dialog notes that if no roles are set, all users have access). The roles come from what you pass as CurrentUserRole in GetSettings():

settings.UserId = User.Identity.Name;
settings.CurrentUserRole = User.Claims
    .Where(c => c.Type == ClaimTypes.Role)
    .Select(c => c.Value)
    .ToList();

So Salary can exist on the Employees view for HR and be invisible to everyone else. I'll cover roles, folders and sharing properly in a follow-up. And tenant isolation is a separate layer again — ClientId and DataFilters, covered in Multi-tenant reporting in ASP.NET Core: row-level security your users can't bypass. Curation decides what users can see; row-level security decides whose rows.

5. Custom tables: views you don't have to deploy

Sometimes you need a reporting shape but can't (or don't want to) add a database view — a customer's database you don't own, or a quick experiment. The Custom Tables tab lets you define a table as a SQL SELECT; Dotnet Report reads the result's columns and you then name and configure them like any other table.

Custom table SQL is checked before it's accepted: GetSchemaFromSql rejects anything that isn't a single read-only statement — it must start with SELECT or WITH, and multiple statements or sp_/xp_ procedures are refused. That's a sensible guardrail, not a reason to put complex logic there. My rule of thumb: if the shape is permanent and you own the database, make it a real view in your migrations; use custom tables for the cases where you can't.

6. Guardrails for expensive columns

Curation is also about keeping users from writing the one query that scans five years of events. Two per-column options help:

• Require Filter if Column is selected / Require Filter if Table is selected — the report can't run until the user picks a value, e.g. a tenant, a store, or a date.
• Restrict Filter Date Range — cap a date filter at a number of days, months or years, so nobody asks for "all time" on your largest table.

These are cheap to set and save you from explaining to someone why their report took four minutes.

7. Lock the setup screen itself

Everything above is configuration that changes what every user sees, so the setup page is an admin tool, not a user feature. The page itself warns you not to expose it to end users. In the source, the controller is gated by a claim:

public class DotNetSetupController : Controller
{
    [CustomAuthorize(ClaimsStore.AllowSetupPageAccess)]
    public async Task<IActionResult> Index(string databaseApiKey = "")
    {
        return View();
    }
}

and admin mode in the report designer is gated separately in GetSettings():

settings.CanUseAdminMode = ClaimsHelper.HasAnyRequiredClaim(
    User as ClaimsPrincipal, ClaimsStore.AllowAdminMode);

Give AllowSetupPageAccess to the developers or data owners who curate the schema, and nobody else.

The checklist

Before you hand self-service reporting to users, walk each exposed table through this:

1. Is it a view? If the raw table needs any fix-ups (soft deletes, codes, computed totals, awkward joins), yes.
2. Is every name human? Table, columns, categories, consistent convention.
3. Are the joins reviewed? Imported or guessed joins checked, optional relationships LEFT, ambiguous paths pre-joined.
4. Are ids shown as names? Foreign-key lookups set up for every id users might filter on.
5. Is anything sensitive in the view at all? If so, remove it from the view rather than hiding it.
6. Who should see it? Role access on sensitive tables and columns.
7. Can it hurt the database? Required filters and date-range caps on the big ones.

None of this is glamorous, but it's where self-service reporting succeeds or fails. Users trust numbers from a schema that makes sense to them, and they stop asking you to "just pull a quick report."

If you're starting from zero, set up the report builder first with Add self-service ad hoc reporting to an ASP.NET Core app, then come back to this list before you invite your first users.

---

Razi Syed builds Dotnet Report, an embedded self-service reporting platform for .NET whose report-builder front-end is source-available on GitHub. He writes about adding reporting and analytics to SaaS products without rebuilding them from scratch.