Building apps & reports

Create report types, joins and reports through the API — for provisioning tenants on the fly, or migrating reports in from another BI tool.

What this is for

Most integrations only read from Yurbi. This guide covers writing: creating the semantic layer and the reports on top of it, entirely through the API.

Two situations need it:

• Dynamic data sources. A tenant's schema isn't known ahead of time, so the report type has to be built at provisioning time rather than by hand in App Builder.
• Migration. Bringing reports in from another BI tool. Those tools usually store joins per report; Yurbi stores them in the report type, so the report type has to exist before the report can be created.

The two layers

A report names its report type by name, not by ID. Two report types sharing a name inside one app makes every report on that name ambiguous, so keep names unique.

1. Find the app

GetAnyDbModulesList returns every AnyDB app with the two identifiers everything else needs:

{ "ID": "17779219821", "ReportModule": { "ID": "1005", "Name": "AssetWorks Source ORACLE" } }

ID is the RegServerID, ReportModule.ID is the ModuleID.

2. Read before you write

GetAnyDbModule returns the app's report types. Read it first, always — for two reasons:

• to compute the next free RepTypeID (max + 1)
• to snapshot the current state, because the write is destructive

Look at ReportTypes[].SQLTables to see what tables an app already has. DBSQLTableList.AllDbtables is live introspection of the database server and comes back empty when the app has no live connection.

These responses get large. An app with a couple of hundred tables and their columns runs to tens of megabytes.

3. Build the report type

A report type is SQLTables plus FieldTree.

Tables and joins

Each table carries a single join describing how it attaches to the model:

{
  "tablename": "UNIT_MAIN",
  "tableowner": "",
  "tablealias": "UNIT_MAIN",
  "join": {
    "table1": "VIEW_ALL_WORK_ORDERS", "field1": "UnitNo",
    "table2": "UNIT_MAIN",            "field2": "UNIT_NO",
    "jointype": "Inner Join", "joinop": "=", "filter": "", "index": 1
  }
}

table1 is the parent — the table already in the model. table2 is the one being attached. One table (the root) has an empty join.

Because each table has exactly one join, the join model is a tree, not a graph. Every table has one parent. That rules out:

• the same pair of tables joined on two column pairs (composite keys)
• a table reachable from two different parents
• cycles

jointype is "Inner Join", "Left Join" and so on — mixed case, no OUTER. joinop is a bare "=" with no padding. filter is appended to the join clause verbatim if set.

tableowner should match whatever the app already uses. Many AnyDB apps register tables with an empty owner even when the underlying database has a schema — check SQLTables on an existing report type rather than assuming.

The field tree

FieldTree is the field picker: folder nodes (nodetype: "G") with field nodes (nodetype: "F") beneath them.

flevel is a colon-delimited materialised path made of the parent's path plus the node's own id:

id 0  flevel "0"      G  VIEW_ALL_WORK_ORDERS     (root folder)
id 1  flevel "0:1"    F  WorkOrderNo
id 2  flevel "2"      G  UNIT_MAIN
id 3  flevel "2:3"    F  Make

Two things that are easy to get wrong:

• tablealias on a field node is its own table's alias.
• parenttablealias is the root table of the whole report type, the same value on every field node — not the field's immediate join parent.

idx_order starts at 1.

Common datatype codes: cha text, num numeric, dat timestamp, doz date-only. The date codes are not interchangeable — see Dates below.

Writing it

SaveAnyDbModule deletes and re-inserts every report type in the payload. Report types you leave out are untouched, so send only the ones you are creating:

{
  "sessionToken": "…",
  "anydbmodule": {
    "ReportTypes": [ { /* just the new one */ } ],
    "DBSQLTableList": { "AllDbtables": [] },
    "isExcelCSV": false,
    "ApiEndpointList": null
  }
}

Posting a whole 16 MB module back means deleting and re-inserting thousands of rows that were fine.

It returns 1 on success. Errors are swallowed and returned as 0, so verify by reading the module back rather than trusting the return value.

4. Build the report

Start from NewReport so you inherit the current defaults, then add fields.

The reliable way to add a field is to read the report type's tree with GetReportTree and pass nodes to NewField, which fills in derived values like RenamedField (the SELECT alias) rather than making you reproduce the rules.

Filters

Put structured criteria on the field and leave the compiled strings empty — SaveReport compiles them:

{
  "DisplayFieldName": "Status",
  "Criteria": [
    { "Index": 0, "Field": "Status", "Op": "inlist",
      "Value1": "D,O", "Value2": "", "Logical": "And", "cond": "is" }
  ],
  "SearchCriteria": ""
}

Criteria.Field must be the field's display name. It is the key binding a criteria back to its field on load; if it doesn't match, the criteria is orphaned and rebuilt from a compiled string, which changes the operator's casing and breaks the criteria editor.

Operators are lowercase: =, <, >, <>, between, inlist, like, not like, null, empty. cond is is or isnot. Multi-value lists are comma-separated in Value1 — values containing a comma will be split.

Formulas

Formula holds raw SQL in the target dialect. ? is replaced with the field's own column, alias-qualified:

Formula: "ROUND((SYSDATE - ?), 0)"
becomes: ROUND((SYSDATE - "VIEW_ALL_WORK_ORDERS"."OpenDate"), 0)

Reference a different column by qualifying it explicitly with its table alias — "UNIT_MAIN"."MAKE". Quoting follows the platform ("x" on Oracle and PostgreSQL, [x] on SQL Server).

Set Fieldtype to what the formula returns, not what it reads. ROUND(SYSDATE - "OpenDate", 0) reads a date and returns a number, so it is num.

Aggregates

There is no aggregate flag. Put an aggregate function in Formula and the engine detects it and groups every non-aggregated column automatically:

{ "DisplayFieldName": "Ticket Count", "Formula": "count(?)", "Fieldtype": "num" }

Recognised: sum(, avg(, min(, max(, count(, stddev(. A formula that is a subquery will not be treated as an aggregate.

Saving

Build the object you save from NewReport rather than assembling it from scratch. The template initialises collections such as linkreports that the save expects to be present; a hand-built object that omits them returns ErrorCode 9000.

SaveReport branches on ReportID:

• empty string → create; the server mints an ID and returns it
• existing ID → update

Sending an ID that doesn't exist fails with 9001 Permission Denied — the permission check runs against a report that isn't there. Never invent an ID.

folderid must be a library folder the user can modify, or the save fails with Folder Permission Denied. Get candidates from GetAllLibraryTree; its real tree is nested under a rootnode key on each entry, and isUserAdmin/isUserModify tell you what's writable.

5. Validate before you commit

Two endpoints run the pipeline without saving anything:

• GetSQL takes a whole report object and returns the SQL it would generate.
• ReportProcUI compiles criteria and totals and hands the object back.

GetSQL does not compile criteria, so a freshly built object produces SQL with no WHERE clause. Chain them — ReportProcUI first, then GetSQL — to preview the whole statement.

This is the cheapest possible feedback loop: build the object, look at the SQL, fix it, and only then write.

Dates: pick the right code

dat and doz are both dates, and they behave differently:

For values that are literal dates with no timezone, doz reproduces them exactly. dat is right when time of day genuinely matters.

Either way, check period boundaries: with doz, Between '2024-12-01' and '2024-12-31' excludes times later on the 31st.

Putting it together

GetAnyDbModulesList        find the app
GetAnyDbModule             read current state, snapshot, next RepTypeID
  ↓
SaveAnyDbModule            create the report type   (send only the new one)
GetAnyDbModule             verify it was written
  ↓
GetReportTree              read the new field tree
NewReport + NewField       assemble the report
ReportProcUI → GetSQL      preview the SQL, no writes
  ↓
GetAllLibraryTree          pick a writable folder
SaveReport                 ReportID: ""  → returns the new ID

Things worth knowing

• Report types are referenced by name. Re-creating one with the same name but a different join graph breaks existing reports on it. Check for an existing match before creating.
• SaveAnyDbModule returns 0 on error, with no message. Verify by reading back.
• Only the tables a report uses are joined. Selecting fields from two tables of a ten-table report type generates a two-table query, and intermediate tables needed to connect them are pulled in automatically. A broad report type therefore costs a narrow report nothing — one report type can serve many reports.
• Snapshot before writing. GetAnyDbModule output is your rollback.
• Reports can be removed. DelReport deletes a report by ID and returns 0 on success, which makes it straightforward to roll back an automated build that produced the wrong result.
• Grant your integration user access to the app. Only the built-in admin account bypasses application permissions. Any other account needs an Architect role on the application before these calls will see it.