# About Hey! I'm Alexander. [Mprove](https://mprove.io/) is an [Open Source](https://github.com/mprove-io/mprove) Business Intelligence tool focused on Metrics. With Mprove, instead of directly querying tables, you query semantic data models: - For SQL data sources, see [Malloy Models](/content/docs/reference/model-malloy). - For HTTP API data sources, see [Store Models](/content/docs/reference/model-store). All Models, Charts, Reports and Dashboards are stored under git version control. I started working on Mprove in 2015 inspired by Looker. Understanding how things should work and look comes through a lot of trial and error. This is my third big attempt to build it right. Initially, Mprove YAML had its own module for SQL transformation logic, similar to LookML. In 2025, I replaced this part with Malloy as the next-generation data modeling language. # Access Roles Access roles can be assigned to project members by users with **ProjectAdmin** role. Dashboards, Reports and Models have `access_roles` parameter: - Syntax for Mprove YAML - `access_roles: [sales, marketing]` - Syntax for Malloy model custom tags - `access_roles="sales, marketing"` Only project members with "sales" or "marketing" access roles will be able to access these objects. If an object has no `access_roles` then any project member can access this object. This means that the object is public for all project members. If a user has access to a model, then he also has access to all model's charts. A user may have access to the dashboard, but not have access to the models of dashboard tiles. A user may have access to the report, but not have access to the models of report rows (metrics). # Connections ## SQL Data Sources Mprove connects to SQL databases that are supported by Malloy: - PostgreSQL - MySQL - Presto / Trino - SnowFlake - BigQuery - DataBricks - MotherDuck (DuckDb) ## API Data Sources The API connection is universal and can work with different API providers. Logic for creating a request body and logic for transforming a response data must be defined in the [Store Model](/content/docs/reference/model-store). Store connection types: - API - Google API For **API** query, Mprove does a single request to the data source. For **Google API** query, Mprove does 2 requests: - Auth request to get credentials - Data request (query) ## Internal Host and Internal Port Connection Parameters Some connection types have optional internal host and internal port parameters. If you provide connection credentials to an external sandbox provider, it will use the host and port that are not internal, because the sandbox is not on a local network with the mprove server. If the mprove server has an internal host and internal port specified for a connection, it will use them instead of the host/port. This removes an extra network hop from the mprove server to the DWH. # Docs for AI These documentation pages are available as `.mdx` files for AI and LLM tools. ## Docs Full LLM text file for Docs section (without CLI, Skills and OpenAPI) is available at /llms-full.txt. ## Skills ## MCP ## CLI ## OpenAPI # DWH Schema There are three types of Connection Schemas in Mprove: - **Raw schemas** are fetched automatically from SQL connections - tables, columns, data types, foreign keys, and indexes. - **Extra schemas** are YAML files with extra metadata - descriptions, examples, and relationship type. - **Combined schemas** merge Raw and Extra schemas. They are useful for LLMs and humans to build Malloy semantic models. User see combined schemas in UI and explore Relationships Graph. LLM can use MCP tool. There is also `get-schemas` mprove CLI command. Raw schemas are cached. User must click "Refresh" button in UI to refresh a raw schema cache when db schema changes. Raw schema cache is not refreshed on page load. Mprove validates (rebuilds) all files on every file save in UI or repo sync through CLI. During validation a raw cache is provided for Malloy compilation in a compatible format. This way Malloy does not need to fetch schema from db. It improves compilation time. Without a cache, Malloy retrieves the schema for each table separately. Even with a cache, in some cases, Malloy needs to sample data (some SnowFlake data types) to determine the data structure. ## File Format Each schema must be stored as **connection_name.schema_name.schema** YAML file: ```yaml schema: c1_postgres.fleet description: 'Fleet management system tracking vehicles, drivers, trips, and maintenance' tables: - table: trips description: 'Individual vehicle trips with route, distance, and assigned driver' columns: - column: trip_id description: 'Unique identifier for each trip' example: '80234' - column: vehicle_id description: 'The vehicle used for this trip' example: '152' relationships: - to: vehicles.vehicle_id type: many_to_one - column: driver_id description: 'The driver who operated the vehicle on this trip' example: '38' relationships: - to: drivers.driver_id type: many_to_one - column: customer_id description: 'The customer who requested this trip' example: '4501' relationships: - to: customers.customer_id to_schema: c1_postgres.billing type: many_to_one - column: status description: 'Trip completion status' example: 'completed' cache_unique_values: true - table: vehicles description: 'Fleet vehicles with make, model, and current operational status' columns: - column: vehicle_id description: 'Unique identifier for each vehicle' example: '152' - column: license_plate description: 'Vehicle registration plate number' example: 'AB-1234-CD' - table: drivers description: 'Drivers certified to operate fleet vehicles' columns: - column: driver_id description: 'Unique identifier for each driver' example: '38' - column: name description: 'Full name of the driver' example: 'Carlos Rivera' cache_unique_values: true - table: maintenance_records description: 'Scheduled and unscheduled vehicle maintenance events' columns: - column: record_id description: 'Unique identifier for each maintenance record' example: '6010' - column: vehicle_id description: 'The vehicle that was serviced' example: '152' relationships: - to: vehicles.vehicle_id type: many_to_one - column: service_type description: 'Type of maintenance performed' example: 'oil change' cache_unique_values: true ``` ## Reference ### Schema | Name | Type | Required | Description | | ----------- | ------------------ | -------- | -------------------------------------------------------- | | schema | string | yes | Connection and schema name in `connection.schema` format | | description | string | no | Human-readable description of the database schema | | tables | [Table []](#table) | yes | List of table definitions | ### Table | Name | Type | Required | Description | | ----------- | -------------------- | -------- | ------------------------------------------ | | table | string | yes | Table name | | description | string | no | Business-oriented description of the table | | columns | [Column []](#column) | no | List of column definitions | ### Column | Name | Type | Required | Description | | ------------------- | -------------------------------- | -------- | -------------------------------------- | | column | string | yes | Column name | | description | string | no | What this column represents | | example | string | no | A realistic sample value | | cache_unique_values | boolean | no | Cache unique values for column or not | | relationships | [Relationship []](#relationship) | no | List of relationships to other columns | ### Relationship | Name | Type | Required | Description | | --------- | ------ | -------- | -------------------------------------------------------------------- | | to | string | yes | Target in `table.column` format (exactly one dot) | | type | enum | yes | `one_to_one`, `one_to_many`, `many_to_one`, `many_to_many` | | to_schema | string | no | For cross-schema relationships: `connection_name.schema_name` format | ## Relationship Rules Defining one side is sufficient (no need to define both sides). **Preferred convention:** Define `many_to_one` on the "many" side pointing to the "one" side. **Mirror consistency:** If you define both sides of a relationship (e.g., A->B and B->A), the types must be consistent mirrors: | Side A | Side B | | -------------- | -------------- | | `many_to_one` | `one_to_many` | | `one_to_one` | `one_to_one` | | `many_to_many` | `many_to_many` | **No duplicates:** A column cannot have two relationships with the same `to` and `to_schema` values. **Cross-schema:** Use `to_schema` to reference a table in a different schema on the same connection. See the `customer_id` column in the example above. Cross-connection relationships are not supported. **Partial coverage:** You do not need to define every table or column. You should include tables and columns that have relationships. Also include those columns where you want to add descriptions or examples. Omitted columns still appear in the combined schema with their raw metadata. ## Cache Unique Values `cache_unique_values: true` marks the column as recommended for caching unique values. If a column unique values are cached, the Explorer AI session can look up the correct model fields by value before producing the charts. # Environments Each project has **prod** environment by default. All project members have access to it. A project administrator can create additional environments and give a project member access to that environment. For example, if you have a test instance of a data warehouse, you can create a test environment and run queries against it. ## Environment Connections Mprove models refer to connections by name. Each connection is tied to one environment. Two or more connections can have the same name but belong to different environments. Connection names are not unique across environments. Users with **FilesEditor** role can switch between environments in UI while working in their Dev repositories. ## Environment Variables Environment variables are not implemented yet. If you need environment variables for your use case, please let us know on [Github](https://github.com/mprove-io/mprove/issues). # Quickstart To self host Mprove use [README.md](https://github.com/mprove-io/mprove/blob/master/README.md)
### Sign Up After signing up, you will be able to log in and create an organization.
### Create Organization To create a new organization, click the org selector in navbar and click the **New Org** button. Enter your organization name and click **Create**.
### Create Project To create a new project, first select your organization in navbar. Click the project selector in navbar and click the **New Project** button. Enter your project name and click **Create**.
### Add Project API Keys Click the project gear icon in navbar and click the **API Keys** button. Add Sandbox Provider api key Add LLM Provider api key or use [OpenAI Codex Setup](/content/docs/ai-sessions/openai-codex-setup)
### Add Connection To create a new database connection, first select your project in navbar. Click the project gear icon in navbar and click the **Connections** button. On the Project Connections page, click the **New Connection** button. Enter your connection details and click **Create**.
### Ask AI to Setup Mprove Project Click the **Builder** in navbar. Toggle Editor Session. Ask "Setup mprove project and use malloy types"
### Explore created Model in UI On the Models page, click on the model selector and select the model you created. Click the **Schema** tab. Select the measure and dimension from the schema. Save the draft chart as a new chart file. All charts, reports, and dashboards in Draft status are deleted during the repository validation process. If you save a draft as a file, it is no longer a draft and will not be deleted during the repository validation process. Repository validation process is triggered whenever a user manually edits files in their Dev repository or performs Git-related actions in either the Dev or Production repository. For example, when a user with the FilesEditor role pushes new changes to the Production repository, this Git-related action deletes all drafts for all users in the Production repository. Keep this in mind when planning how often to push changes to production.
### Commit changes On the Files page, review the changes that need to be committed. Click **Commit** button. Enter a commit message (optional) and click **Commit**.
### Push changes to Production On the Files page, review the changes that need to be Pushed to Production. Click **Push to Production** button.
### Switch to Production Repository Click the **repoName-branchName** selector in navbar and select **production/main** branch. Check that the chart you created is visible on the Models page in the Charts section.
# Row Level Security Malloy models can have parameters called [Givens](https://docs.malloydata.dev/documentation/experiments/givens). In Mprove, givens can be defined at a Project level. They override defaults set in Malloy files. Also, Givens can be assigned to an [Access Role](/content/docs/access-roles). Each project member can have multiple access roles. Therefore, for each Given, project member can have multiple values to choose from. User can select values for each Given in "Project" >> "Selected Givens" page. Selected Givens applied to each user query. # User Roles ## Organization Level Roles ### Organization Owner Mprove users can create new organizations. The creator of the organization is given **Organization Owner** role. It can be changed on the Organization Account page. **Organization Owner** can do the following: - create new projects within the organization - view the Organization Users page "Organization Users" page contains a list of all members of all projects related to this organization. To add or remove users in your organization, use "Project Team" page. ## Project Level Roles Project member can have one or more predefined roles: - **Base** - **Explorer** - **FilesEditor** - **ProjectAdmin** ### Base Any project member has a **Base** role that allows them to: - View and filter reports - View and filter dashboards - View files - Switch between branches of Production repository ### Explorer Users with **Explorer** role can do the following: - Explore models - Create charts, reports and dashboards - Save changes to their charts, reports and dashboards ### FilesEditor Users with **FilesEditor** role can do the following: - Edit files - Save changes to any reports, charts and dashboards - Switch between Dev and Production repositories - Switch between branches of Production repository - Switch between branches of Dev repository - Perform [.git related user actions](/content/docs/version-control#user-actions) {/* - Add and remove project environment variables */} ### ProjectAdmin Users with **ProjectAdmin** role can do the following: - Edit project member roles - Edit project settings - Add and remove project environments - Add and remove project connections - Add and remove project members # Version Control ## Dev, Production and Session Repositories Mprove files: - [mprove.yml](/content/docs/reference/project-config) (Project Config) - [\*.malloy](/content/docs/reference/model-malloy) - [\*.store](/content/docs/reference/model-store) - [\*.chart](/content/docs/reference/chart) - [\*.report](/content/docs/reference/report) - [\*.dashboard](/content/docs/reference/dashboard) Each project has 3 types of repositories that are local to mprove server: - **Production** - repository that mirrors **Remote** repository content - **Dev** - personal repository for each project member with **FilesEditor** role - **Session** - separate repository for each session of type Editor Dev repositories allow users to edit files and test changes without affecting **Production** version of files. Users with **FilesEditor** role can manually edit files in their personal Dev repositories. To be able to edit files, a user with the **FilesEditor** role must select the **Dev** repository in the navigation bar. When user makes a manual change to any file, Mprove does the following: - Validates all files - Shows validation errors (if any) - Updates Models, Charts, Dashboards and Reports structure that have passed validation Once the user is satisfied with the changes made, the user can **_commit_** and **_push (deploy)_** changes to **Production** repository to share results with the team. When changes are pushed to **Production**, they are actually pushed to **Remote** first, and then pulled from **Remote** to **Production**. In other words, changes from the **Dev** repository are pushed to the **Production** repository via the **Remote** repository in a single user action. ## Git Branches Small projects may only need to use one branch (usually called master or main). Advanced git users may work in different branches. Users can switch between branches using **repo-branch** selector in navigation bar. ## User Actions Git related actions are available on the Files page: - **Save and Validate** - Save the changes. Mprove will automatically check all files in the project repository data folder for errors. If errors are found during files validation step, they need to be fixed before pushing to production. - **Commit** - Commit changes in Dev repo. - **Push** - Deploy committed changes from Dev repo to Production repo (and to Remote repo). - **Revert to Last Commit** - Discard saved changes in Dev repo that were not committed. - **Revert to Production** - Revert all changes in the Dev repo to the current Production version of files. - **Pull from Remote** - Fetch changes from Remote repo branch and merge into the selected repo branch. - **Merge branch** - Merge changes from another branch into the current branch. - **Create branch** - Create a new branch. - **Delete branch** - Delete current branch. # Builder Builder is a lightweight IDE section of Mprove UI. Users with File Editor role can browse, edit and validate Mprove and Malloy files. Mprove validates all files on each file save in UI. After successful validation Mprove rebuilds consumable state of UI Models, Charts, Reports and Dashboards. ## Editor AI Session Editor AI Session can build Mprove models and create charts, reports and dashboards that are persisted as files (non-drafts). Editor Session runs in a sandbox ([E2B](https://e2b.dev/) provider). It is powered by open source [OpenCode](https://opencode.ai/) Server with default agents (build/plan + subagents). You can use your own [Opencode config](https://opencode.ai/docs/config/) that is local to project. AI agent uses [mprove sync](/content/cli/main-commands/sync) CLI command to sync uncommitted changes between sandbox repo and Mprove server. ## Notes Each Editor Session runs in its own sandbox. It takes 10 to 15 seconds for session initial activation time (starting sandbox, pulling user project git repo and starting an Opencode server). After session is activated, there is a safe pause logic in place to save sandbox resources when session is 5 min+ idle. To resume a paused session (sandbox) you need to send a new message. Unpause (resume) is fast. Session state is preserved on pause/resume. ## Capabilities - Setup Mprove project - Add metadata to dwh schemas - Build Mprove models - Build charts (non-draft) - Build reports (non-draft) - Build dashboards (non-draft) ## E2B Sandbox Template - node 24.14.0 - brew, curl, git, ripgrep, openssh-client, ca-certificates - opencode - [Opencode global config](https://github.com/mprove-io/mprove/blob/master/skills/opencode-global-config.json) with [Mprove MCP Server](/content/mcp/mcp-server) configured - [Mprove Skills](/content/skills/download-skill-md) - [Mprove CLI](/content/cli/install-cli) (only "mprove sync" is needed, other useful commands have corresponding mcp tools) - Malloy Docs repository ## Credentials Credentials that are provided to sandbox during session activation: - LLM provider api key for selected session Model (see project settings API keys) - `MPROVE_CLI_API_KEY` (has access to mprove session repo branch and session env connections) - `MPROVE_CLI_PROJECT_ID` - `MPROVE_CLI_HOST` ## Global Instructions These global instructions are merged with your project's local instruction files. See [Opencode Docs](https://opencode.ai/docs/rules/). - [mprove-instructions.md](https://github.com/mprove-io/mprove/blob/master/sandbox/mprove-instructions.md) # Explorer Explorer is an AI chat section of Mprove UI. ## Explorer AI Session Explorer data agent can answer user questions by creating charts based on existing models. ## Project-specific Instructions You can add the `mprove-explorer.md` file anywhere in the `mprove_dir` folder, specifying your own instructions. Its contents will be added to the system prompt of Explorer AI session. # OpenAI Codex Setup You can use OPENAI_API_KEY or Codex Subscription for AI Sessions. ## Codex Subscription Open user profile page and click "SignIn with ChatGPT". Follow instructions to authenticate. Your AI Session will be able to use Codex Subscription. Other Mprove users will need to use OPENAI_API_KEY or setup their own Codex Subsctiptions. # Chart Reports, Charts, Dashboards are intended to be created and modified through the user interface. The file representation is for checking errors during validation. Each chart must be stored as **chart_name.chart** YAML file: ```yaml chart: chart_name tiles: # Chart must have exactly one tile - title: 'tile title' description: '' model: model_name select: - field_path - field_path - field_path parameters: - apply_to: field_path conditions: - f\`> 100\` sorts: field_path desc, field_path, ... limit: 500 type: chart_type data: x_field: field_path y_fields: - field_path - field_path multi_field: field_path size_field: field_path pivot_rows: - field_path pivot_columns: - field_path pivot_values: - field: field_path options: x_axis: scale: false y_axis: - scale: false - scale: false series: - data_field: field_path y_axis_index: 0 type: chart_type first_column_width: 235 value_columns_width: 181 ``` ## Reference ### Chart | Name | Type | Default | Description | | ------- | ----------------------------------- | ------- | ------------------------------------------------------- | | chart\* | string | - | Chart name | | tiles\* | [Tile []](/content/docs/reference/dashboard#tile) | - | Chart tile has the same configuration as dashboard tile | # Dashboard Reports, Charts, Dashboards are intended to be created and modified through the user interface. The file representation is for checking errors during validation. Each dashboard must be stored as **dashboard_name.dashboard** YAML file: ```yaml dashboard: dashboard_name title: '' description: '' access_roles: - role - role parameters: - filter: f1 label: 'label' description: '' result: string suggest_model_dimension: model_name.field_path conditions: - f\`%a\` tiles: - title: 'tile title' description: '' model: model_name select: - field_path - field_path - field_path parameters: - apply_to: field_path listen: f1 - apply_to: field_path conditions: - f\`> 100\` sorts: field_path desc, field_path, ... limit: 500 type: chart_type data: x_field: field_path y_fields: - field_path - field_path multi_field: field_path size_field: field_path pivot_rows: - field_path pivot_columns: - field_path pivot_values: - field: field_path options: x_axis: scale: false y_axis: - scale: false - scale: false series: - data_field: field_path y_axis_index: 0 type: chart_type first_column_width: 235 value_columns_width: 181 plate: plate_width: 8 plate_height: 12 plate_x: 0 plate_y: 0 ``` ## Reference ### Dashboard | Name | Type | Default | Description | | ------------ | -------------------------------------------------------------- | ------- | ---------------------------------------------------------------------------- | | dashboard\* | string | - | Dashboard name | | title | string | - | Dashboard title in UI | | description | string | - | Dashboard description in UI | | access_roles | string [] | - | If specified, only users with the listed roles will have access to Dashboard | | parameters | [Top Filter []](/content/docs/reference/parameters#top-filter) | - | Begin a section of dashboard parameters | | tiles\* | [Tile []](/content/docs/reference/dashboard#tile) | - | Begin a section of tiles | ### Tile | Name | Type | Default | Description | | ----------- | ------------------------------------ | ------- | --------------------------------------------------------------------------------------------------------------- | | title\* | string | - | Tile title | | description | string | - | Tile description in UI | | model\* | string | - | Model name | | select\* | string [] | - | List of selected fields | | parameters | [Tile Parameter []](#tile-parameter) | - | Begin a section of tile parameters | | sorts | string | - | A way to sort data | | limit | number | 500 | Limit the number of rows | | type\* | enum | - | | | data | [Tile Data](#tile-data) | - | | | options | [Tile Options](#tile-options) | - | | | plate | [Tile Plate](#tile-plate) | - | `only for dashboard tiles` | ### Tile Parameter Tile parameter must have **listen**, **conditions** or **fractions**. | Name | Type | Default | Description | | ---------- | ---------------------------------------------------------- | ------- | ------------------------------------------------------------------------- | | apply_to\* | string | - | Field or Filter name | | listen | string | - | `only for dashboard tiles`
Specify dashboard parameter to listen to | | conditions | string [] | - | `for parameters mapped to Malloy Model`
List of filter expressions | | fractions | [Fraction []](/content/docs/reference/parameters#fraction) | - | `for parameters mapped to Store Model`
List of fractions | ### Tile Data | Name | Type | Default | Description | | ------------- | ---------------------------------------------------------- | ------- | --------------------------------------------------------------------------------------------------------------- | | x_field\*\* | string | - | `for non-tables` One of selected fields (dimension) to get the X coordinate | | y_fields\*\* | string [] | - | `for non-tables` One or more of selected fields (measure or calculation) to get series data | | multi_field | string | - | `for non-tables` One of selected fields (dimension) for additional grouping of series data based on field value | | size_field | string | - | `for scatter` One of selected fields to set size of data point | | pivot_rows | string [] | - | `for pivot_table` Zero or more of selected fields (dimensions) | | pivot_columns | string [] | - | `for pivot_table` Zero or more of selected fields (dimensions) | | pivot_values | [Data Pivot Values Element []](#data-pivot-values-element) | - | `for pivot_table` One or more elements | ### Data Pivot Values Element | Name | Type | Default | Description | | ------- | ------ | ------- | ----------------------------------------------- | | field\* | string | - | One of selected fields (measure or calculation) | ### Tile Options | Name | Type | Default | Description | | ------------------- | ---------------------------------------------------- | ------- | ----------------- | | x_axis | [Options X Axis](#options-x-axis) | - | | | y_axis | [Options Y Axis Element []](#options-y-axis-element) | - | | | series | [Options Series Element []](#options-series-element) | - | | | first_column_width | integer | 230 | `for pivot_table` | | value_columns_width | integer | 210 | `for pivot_table` | ### Options X Axis | Name | Type | Default | Description | | ----- | ------- | ------- | ------------------------------------------------------------------------ | | scale | boolean | false | "false" to start X Axis from Zero, "true" to start X Axis from Min value | ### Options Y Axis Element | Name | Type | Default | Description | | ----- | ------- | ------- | ------------------------------------------------------------------------ | | scale | boolean | false | "false" to start Y Axis from Zero, "true" to start Y Axis from Min value | ### Options Series Element | Name | Type | Default | Description | | --------------- | ------- | ------- | ----------------------------------------------------------------------- | | data_field\*\* | string | - | `for Dashboard or Chart tile options only`
One of y_fields values | | data_row_id\*\* | string | - | `for Report's options only`
One of row_ids with show_chart "true" | | y_axis_index | integer | 0 | 0 (Y Axis Left) or 1 (Y Axis Right) | | type | enum | - | | ### Tile Plate | Name | Type | Default | Description | | ------------ | ------- | ------- | ------------------------------------------------------------------------ | | plate_width | integer | 8 | Plate width (1 to 24) | | plate_height | integer | 12 | Plate height (min is 1) | | plate_x | integer | 0 | X coordinate of plate's top left corner on the dashboard grid (0 to 23) | | plate_y | integer | 0 | Y coordinate of plate's top left corner on the dashboard grid (min is 0) | # Filter Conditions Reports, Charts, Dashboards are intended to be created and modified through the user interface. The file representation is for checking errors during validation. Filter conditions is an array of [Malloy Filter Expressions](https://docs.malloydata.dev/documentation/language/filter-expressions). They can be applied to the fields of Malloy models. In Mprove: - All non-negated expressions in the array are combined with "OR" - All negated expressions in the array are combined with "AND" - Negated and non-negated expressions cannot be mixed in a single element of the conditions array (they must be split into separate elements of the conditions array) ## Examples ### String Multiple expressions of string filter can only be separated by the "," character, otherwise they must be specified as separate array elements. ```yaml apply_to: string_field_path conditions: ### PostgreSQL ### - f\`\` # true // the same as no filter - f\`a%z\` # OR field LIKE 'a%z' - f\`-FOO-\` # OR field = 'FOO' - f\`%FOO%\` # OR field LIKE '%FOO%' - f\`FOO%\` # OR field LIKE 'FOO%' - f\`%FOO\` # OR field LIKE '%FOO' - f\`null\` # OR field IS NULL - f\`blank\` # OR COALESCE(field,'') = '' - f\`OH, NY\` # OR field IN ('OH', 'NY') - f\`UT, MT%\` # OR (field = 'UT' OR field LIKE 'MT%') - f\`\\\\%%\` # OR field LIKE '\\\\%%' - f\`-a%z\` # AND (field NOT LIKE 'a%z' OR field IS NULL) - f\`-FOO\` # AND (field != 'FOO' OR field IS NULL) - f\`-%FOO%\` # AND (field NOT LIKE '%FOO%' OR field IS NULL) - f\`-FOO%\` # AND (field NOT LIKE 'FOO%' OR field IS NULL) - f\`-%FOO\` # AND (field NOT LIKE '%FOO' OR field IS NULL) - f\`-null\` # AND field IS NOT NULL - f\`-blank\` # AND COALESCE(field,'') != '' - f\`-NC, -ND\` # AND (field NOT IN ('NC', 'ND') OR field IS NULL) ``` ### Number Multiple non-negated expressions of number filter can only be separated by the "or", otherwise they must be specified as separate array elements. Multiple negated expressions of number filter can only be separated by the "and", otherwise they must be specified as separate array elements. ```yaml apply_to: number_field_path conditions: ### PostgreSQL ### - f\`\` # true // the same as no filter - f\`1\` # OR field = 1 - f\`<= 2\` # OR field <= 2 - f\`>= 3\` # OR field >= 3 - f\`< 4\` # OR field < 4 - f\`> 5\` # OR field > 5 - f\`null\` # OR field IS NULL - f\`(6 to 7)\` # OR (field > 6 AND field < 7) - f\`(8 to 9]\` # OR (field > 8 AND field <= 9) - f\`[10 to 11)\` # OR (field >= 10 AND field < 11) - f\`[12 to 13]\` # OR (field >= 12 AND field <= 13) - f\`14, 15\` # OR field IN (14, 15) - f\`16 or 17, 18 or < 19\` # OR (field IN (16, 17, 18) OR field < 19) - f\`!= 20\` # AND (field != 20 OR field IS NULL) - f\`not (21, 22)\` # AND NOT (field IN (21, 22)) - f\`not null\` # AND field IS NOT NULL ``` ### Boolean ```yaml apply_to: boolean_field_path conditions: ### PostgreSQL ### - f\`\` # true // the same as no filter - f\`=true\` # AND field - f\`=false\` # AND NOT (field) - f\`true\` # AND COALESCE(field, false) - f\`false\` # AND NOT COALESCE(field, false) - f\`null\` # AND (field) IS NULL - f\`not =true\` # AND NOT (field) - f\`not =false\` # AND field - f\`not true\` # AND NOT COALESCE(field, false) - f\`not false\` # AND COALESCE(field, false) - f\`not null\` # AND (field) IS NOT NULL ``` ### Timestamp Multiple non-negated expressions of time filter can only be separated by the "or", otherwise they must be specified as separate array elements. Multiple negated expressions of time filter can only be separated by the "and", otherwise they must be specified as separate array elements. ```yaml apply_to: time_field_path conditions: ### PostgreSQL ### - f\`\` # true // the same as no filter - f\`null\` # OR field IS NULL - f\`now\` # OR (field = LOCALTIMESTAMP) - f\`today\` # OR (field >= DATE_TRUNC('day', LOCALTIMESTAMP) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer)) - f\`yesterday\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(1)::integer) AND field < DATE_TRUNC('day', LOCALTIMESTAMP)) - f\`tomorrow\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(2)::integer)) - f\`2025\` # OR (field >= TIMESTAMP '2025-01-01 00:00:00' AND field < TIMESTAMP '2026-01-01 00:00:00') - f\`2025-Q1\` # OR (field >= TIMESTAMP '2025-01-01 00:00:00' AND field < TIMESTAMP '2025-04-01 00:00:00') - f\`2025-01\` # OR (field >= TIMESTAMP '2025-01-01 00:00:00' AND field < TIMESTAMP '2025-02-01 00:00:00') - f\`2025-02-03-WK\` # OR (field >= TIMESTAMP '2025-02-02 00:00:00' AND field < TIMESTAMP '2025-02-09 00:00:00') - f\`2025-02-03\` # OR (field >= TIMESTAMP '2025-02-03 00:00:00' AND field < TIMESTAMP '2025-02-04 00:00:00') - f\`2025-02-03 04\` # OR (field >= TIMESTAMP '2025-02-03 04:00:00' AND field < TIMESTAMP '2025-02-03 05:00:00') - f\`2025-02-03 04:05\` # OR (field >= TIMESTAMP '2025-02-03 04:05:00' AND field < TIMESTAMP '2025-02-03 04:06:00') - f\`2025-02-03 04:05:06\` # OR (field = TIMESTAMP '2025-02-03 04:05:06') - f\`2025-02-03 04:05:06.7\` # OR (field = TIMESTAMP '2025-02-03 04:05:06.7') - f\`this year\` # OR (field < DATE_TRUNC('year', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('year', LOCALTIMESTAMP))+make_interval(years=>(1)::integer)) - f\`this quarter\` # OR (field < DATE_TRUNC('quarter', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('quarter', LOCALTIMESTAMP))+make_interval(months=>(1*3)::integer)) - f\`this month\` # OR (field < DATE_TRUNC('month', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('month', LOCALTIMESTAMP))+make_interval(months=>(1)::integer)) - f\`this week\` # OR (field < (DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY) OR field >= ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(1*7)::integer)) - f\`this day\` # OR (field < DATE_TRUNC('day', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer)) - f\`this hour\` # OR (field < DATE_TRUNC('hour', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('hour', LOCALTIMESTAMP))+make_interval(hours=>(1)::integer)) - f\`this minute\` # OR (field < DATE_TRUNC('minute', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('minute', LOCALTIMESTAMP))+make_interval(mins=>(1)::integer)) - f\`last year\` # OR (field < (DATE_TRUNC('year', LOCALTIMESTAMP))-make_interval(years=>(1)::integer) OR field >= DATE_TRUNC('year', LOCALTIMESTAMP)) - f\`last quarter\` # OR (field < (DATE_TRUNC('quarter', LOCALTIMESTAMP))-make_interval(months=>(1*3)::integer) OR field >= DATE_TRUNC('quarter', LOCALTIMESTAMP)) - f\`last month\` # OR (field < (DATE_TRUNC('month', LOCALTIMESTAMP))-make_interval(months=>(1)::integer) OR field >= DATE_TRUNC('month', LOCALTIMESTAMP)) - f\`last week\` # OR (field < ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(1*7)::integer) OR field >= (DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY)) - f\`last day\` # OR (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(1)::integer) OR field >= DATE_TRUNC('day', LOCALTIMESTAMP)) - f\`last hour\` # OR (field < (DATE_TRUNC('hour', LOCALTIMESTAMP))-make_interval(hours=>(1)::integer) OR field >= DATE_TRUNC('hour', LOCALTIMESTAMP)) - f\`last minute\` # OR (field < (DATE_TRUNC('minute', LOCALTIMESTAMP))-make_interval(mins=>(1)::integer) OR field >= DATE_TRUNC('minute', LOCALTIMESTAMP)) - f\`next year\` # OR (field < (DATE_TRUNC('year', LOCALTIMESTAMP))+make_interval(years=>(1)::integer) OR field >= (DATE_TRUNC('year', LOCALTIMESTAMP))+make_interval(years=>(2)::integer)) - f\`next quarter\` # OR (field < (DATE_TRUNC('quarter', LOCALTIMESTAMP))+make_interval(months=>(1*3)::integer) OR field >= (DATE_TRUNC('quarter', LOCALTIMESTAMP))+make_interval(months=>(2*3)::integer)) - f\`next month\` # OR (field < (DATE_TRUNC('month', LOCALTIMESTAMP))+make_interval(months=>(1)::integer) OR field >= (DATE_TRUNC('month', LOCALTIMESTAMP))+make_interval(months=>(2)::integer)) - f\`next week\` # OR (field < ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(1*7)::integer) OR field >= ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(2*7)::integer)) - f\`next day\` # OR (field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(2)::integer)) - f\`next hour\` # OR (field < (DATE_TRUNC('hour', LOCALTIMESTAMP))+make_interval(hours=>(1)::integer) OR field >= (DATE_TRUNC('hour', LOCALTIMESTAMP))+make_interval(hours=>(2)::integer)) - f\`next minute\` # OR (field < (DATE_TRUNC('minute', LOCALTIMESTAMP))+make_interval(mins=>(1)::integer) OR field >= (DATE_TRUNC('minute', LOCALTIMESTAMP))+make_interval(mins=>(2)::integer)) - f\`monday\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7+1)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7)::integer)) - f\`last monday\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7+1)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7)::integer)) - f\`next monday\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>((1-((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)+6)%7+1)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>((1-((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)+6)%7+2)::integer)) - f\`3 days ago to now\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(3)::integer) AND field < LOCALTIMESTAMP) - f\`3 weeks ago to now\` # OR (field >= ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(3*7)::integer) AND field < LOCALTIMESTAMP) - f\`5 days ago\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(4)::integer)) - f\`5 days from now\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(5)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(6)::integer)) - f\`5 days ago for 2 days\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer) AND field < ((DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer))+make_interval(days=>(2)::integer)) - f\`5 days from now for 2 days\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(5)::integer) AND field < ((DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(5)::integer))+make_interval(days=>(2)::integer)) - f\`5 weeks ago for 2 months\` # OR (field >= ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(5*7)::integer) AND field < (((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(5*7)::integer))+make_interval(months=>(2)::integer)) - f\`5 weeks from now for 2 months\` # OR (field >= ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(5*7)::integer) AND field < (((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(5*7)::integer))+make_interval(months=>(2)::integer)) - f\`last 5 days\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer) AND field < DATE_TRUNC('day', LOCALTIMESTAMP)) - f\`next 5 days\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(6)::integer)) - f\`5 days\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(4)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer)) - f\`before 2025-Q2\` # OR (field < TIMESTAMP '2025-03-01 00:00:00') - f\`before 2025-02-03-WK\` # OR (field < TIMESTAMP '2025-02-02 00:00:00') - f\`before 2025-02-03 04:05:06.7\` # OR (field < TIMESTAMP '2025-02-03 04:05:06.7') - f\`after 2025-Q2\` # OR (field >= TIMESTAMP '2025-06-01 00:00:00') - f\`after 2025-02-03-WK\` # OR (field >= TIMESTAMP '2025-02-09 00:00:00') - f\`after 2025-02-03 04:05:06.7\` # OR (field >= TIMESTAMP '2025-02-03 04:05:06.7') - f\`before tuesday\` # OR (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7+1)::integer)) - f\`after tuesday\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7)::integer)) - f\`starting tuesday\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7+1)::integer)) - f\`through tuesday\` # OR (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7)::integer)) - f\`last monday to next friday\` # OR (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7+1)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>((5-((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)+6)%7+1)::integer)) - f\`2025-01-02 03:04:05 to 2026-07-08 04:05:06\` # OR (field >= TIMESTAMP '2025-01-02 03:04:05' AND field < TIMESTAMP '2026-07-08 04:05:06') - f\`today or tomorrow\` # OR (field >= DATE_TRUNC('day', LOCALTIMESTAMP) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) AND field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(2)::integer)) -- negation - f\`not null\` # AND (field IS NOT NULL) - f\`not now\` # AND ((field != LOCALTIMESTAMP OR field IS NULL)) - f\`not today\` # AND (field < DATE_TRUNC('day', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer)) - f\`not yesterday\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(1)::integer) OR field >= DATE_TRUNC('day', LOCALTIMESTAMP)) - f\`not tomorrow\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(2)::integer)) - f\`not 2025\` # AND (field < TIMESTAMP '2025-01-01 00:00:00' OR field >= TIMESTAMP '2026-01-01 00:00:00') - f\`not 2025-Q1\` # AND (field < TIMESTAMP '2025-01-01 00:00:00' OR field >= TIMESTAMP '2025-04-01 00:00:00') - f\`not 2025-01\` # AND (field < TIMESTAMP '2025-01-01 00:00:00' OR field >= TIMESTAMP '2025-02-01 00:00:00') - f\`not 2025-02-03-WK\` # AND (field < TIMESTAMP '2025-02-02 00:00:00' OR field >= TIMESTAMP '2025-02-09 00:00:00') - f\`not 2025-02-03\` # AND (field < TIMESTAMP '2025-02-03 00:00:00' OR field >= TIMESTAMP '2025-02-04 00:00:00') - f\`not 2025-02-03 04\` # AND (field < TIMESTAMP '2025-02-03 04:00:00' OR field >= TIMESTAMP '2025-02-03 05:00:00') - f\`not 2025-02-03 04:05\` # AND (field < TIMESTAMP '2025-02-03 04:05:00' OR field >= TIMESTAMP '2025-02-03 04:06:00') - f\`not 2025-02-03 04:05:06\` # AND ((field != TIMESTAMP '2025-02-03 04:05:06' OR field IS NULL)) - f\`not 2025-02-03 04:05:06.7\` # AND ((field != TIMESTAMP '2025-02-03 04:05:06.7' OR field IS NULL)) - f\`not this year\` # AND (field < DATE_TRUNC('year', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('year', LOCALTIMESTAMP))+make_interval(years=>(1)::integer)) - f\`not this quarter\` # AND (field < DATE_TRUNC('quarter', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('quarter', LOCALTIMESTAMP))+make_interval(months=>(1*3)::integer)) - f\`not this month\` # AND (field < DATE_TRUNC('month', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('month', LOCALTIMESTAMP))+make_interval(months=>(1)::integer)) - f\`not this week\` # AND (field < (DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY) OR field >= ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(1*7)::integer)) - f\`not this day\` # AND (field < DATE_TRUNC('day', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer)) - f\`not this hour\` # AND (field < DATE_TRUNC('hour', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('hour', LOCALTIMESTAMP))+make_interval(hours=>(1)::integer)) - f\`not this minute\` # AND (field < DATE_TRUNC('minute', LOCALTIMESTAMP) OR field >= (DATE_TRUNC('minute', LOCALTIMESTAMP))+make_interval(mins=>(1)::integer)) - f\`not last year\` # AND (field < (DATE_TRUNC('year', LOCALTIMESTAMP))-make_interval(years=>(1)::integer) OR field >= DATE_TRUNC('year', LOCALTIMESTAMP)) - f\`not last quarter\` # AND (field < (DATE_TRUNC('quarter', LOCALTIMESTAMP))-make_interval(months=>(1*3)::integer) OR field >= DATE_TRUNC('quarter', LOCALTIMESTAMP)) - f\`not last month\` # AND (field < (DATE_TRUNC('month', LOCALTIMESTAMP))-make_interval(months=>(1)::integer) OR field >= DATE_TRUNC('month', LOCALTIMESTAMP)) - f\`not last week\` # AND (field < ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(1*7)::integer) OR field >= (DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY)) - f\`not last day\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(1)::integer) OR field >= DATE_TRUNC('day', LOCALTIMESTAMP)) - f\`not last hour\` # AND (field < (DATE_TRUNC('hour', LOCALTIMESTAMP))-make_interval(hours=>(1)::integer) OR field >= DATE_TRUNC('hour', LOCALTIMESTAMP)) - f\`not last minute\` # AND (field < (DATE_TRUNC('minute', LOCALTIMESTAMP))-make_interval(mins=>(1)::integer) OR field >= DATE_TRUNC('minute', LOCALTIMESTAMP)) - f\`not next year\` # AND (field < (DATE_TRUNC('year', LOCALTIMESTAMP))+make_interval(years=>(1)::integer) OR field >= (DATE_TRUNC('year', LOCALTIMESTAMP))+make_interval(years=>(2)::integer)) - f\`not next quarter\` # AND (field < (DATE_TRUNC('quarter', LOCALTIMESTAMP))+make_interval(months=>(1*3)::integer) OR field >= (DATE_TRUNC('quarter', LOCALTIMESTAMP))+make_interval(months=>(2*3)::integer)) - f\`not next month\` # AND (field < (DATE_TRUNC('month', LOCALTIMESTAMP))+make_interval(months=>(1)::integer) OR field >= (DATE_TRUNC('month', LOCALTIMESTAMP))+make_interval(months=>(2)::integer)) - f\`not next week\` # AND (field < ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(1*7)::integer) OR field >= ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(2*7)::integer)) - f\`not next day\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(2)::integer)) - f\`not next hour\` # AND (field < (DATE_TRUNC('hour', LOCALTIMESTAMP))+make_interval(hours=>(1)::integer) OR field >= (DATE_TRUNC('hour', LOCALTIMESTAMP))+make_interval(hours=>(2)::integer)) - f\`not next minute\` # AND (field < (DATE_TRUNC('minute', LOCALTIMESTAMP))+make_interval(mins=>(1)::integer) OR field >= (DATE_TRUNC('minute', LOCALTIMESTAMP))+make_interval(mins=>(2)::integer)) - f\`not monday\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7+1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7)::integer)) - f\`not last monday\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7+1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7)::integer)) - f\`not next monday\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>((1-((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)+6)%7+1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>((1-((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)+6)%7+2)::integer)) - f\`not 3 days ago to now\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(3)::integer) OR field >= LOCALTIMESTAMP) - f\`not 3 weeks ago to now\` # AND (field < ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(3*7)::integer) OR field >= LOCALTIMESTAMP) - f\`not 5 days ago\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(4)::integer)) - f\`not 5 days from now\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(5)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(6)::integer)) - f\`not 5 days ago for 2 days\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer) OR field >= ((DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer))+make_interval(days=>(2)::integer)) - f\`not 5 days from now for 2 days\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(5)::integer) OR field >= ((DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(5)::integer))+make_interval(days=>(2)::integer)) - f\`not 5 weeks ago for 2 months\` # AND (field < ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(5*7)::integer) OR field >= (((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))-make_interval(days=>(5*7)::integer))+make_interval(months=>(2)::integer)) - f\`not 5 weeks from now for 2 months\` # AND (field < ((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(5*7)::integer) OR field >= (((DATE_TRUNC('week', LOCALTIMESTAMP + INTERVAL '1' DAY) - INTERVAL '1' DAY))+make_interval(days=>(5*7)::integer))+make_interval(months=>(2)::integer)) - f\`not last 5 days\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(5)::integer) OR field >= DATE_TRUNC('day', LOCALTIMESTAMP)) - f\`not next 5 days\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(6)::integer)) - f\`not 5 days\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>(4)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>(1)::integer)) - f\`not before 2025-Q2\` # AND (field >= TIMESTAMP '2025-03-01 00:00:00') - f\`not before 2025-02-03-WK\` # AND (field >= TIMESTAMP '2025-02-02 00:00:00') - f\`not before 2025-02-03 04:05:06.7\` # AND (field >= TIMESTAMP '2025-02-03 04:05:06.7') - f\`not after 2025-Q2\` # AND (field < TIMESTAMP '2025-06-01 00:00:00') - f\`not after 2025-02-03-WK\` # AND (field < TIMESTAMP '2025-02-09 00:00:00') - f\`not after 2025-02-03 04:05:06.7\` # AND (field < TIMESTAMP '2025-02-03 04:05:06.7') - f\`not before tuesday\` # AND (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7+1)::integer)) - f\`not after tuesday\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7)::integer)) - f\`not starting tuesday\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7+1)::integer)) - f\`not through tuesday\` # AND (field >= (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-2+6)%7)::integer)) - f\`not last monday to next friday\` # AND (field < (DATE_TRUNC('day', LOCALTIMESTAMP))-make_interval(days=>((((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)-1+6)%7+1)::integer) OR field >= (DATE_TRUNC('day', LOCALTIMESTAMP))+make_interval(days=>((5-((EXTRACT(dow FROM LOCALTIMESTAMP)::integer+1)-1)+6)%7+1)::integer)) - f\`not 2025-01-02 03:04:05 to 2026-07-08 04:05:06\` # AND (field < TIMESTAMP '2025-01-02 03:04:05' OR field >= TIMESTAMP '2026-07-08 04:05:06') ``` # Format Number The goal of formatting numbers is to help display numbers in a human-readable form. There are two ways of formatting numbers in Mprove: - SSF - D3-format (recommended) ## SSF [SSF](https://docs.sheetjs.com/docs/constellation/ssf/) can be used in [Field-Level Formatting & Rendering Tags](https://github.com/malloydata/malloy/blob/main/packages/malloy-render/docs/renderer_tags_overview.md#field-level-formatting--rendering-tags) of [Malloy Sources](/content/docs/reference/model-malloy). ## D3-format [D3-format](https://d3js.org/d3-format#d3-format) can be used in [Project Config](/content/docs/reference/project-config#project), [Store Fields](/content/docs/reference/model-store#store-field), [Report Rows](/content/docs/reference/report#row) and [Malloy Sources](/content/docs/reference/model-malloy). To use D3-format in Mprove there are 3 parameters: - `format_number` - `currency_prefix` - `currency_suffix` Example of D3-format usage in Malloy Sources: ```malloy title="c1_order_items.malloy" #(mprove) model source: c1_order_items is c1_order_items_tx extend { -- 1057.1258 >> $1,057.13 #(mprove) format_number='$,.2f' currency_prefix='$' currency_suffix='' measure: total_profit is total_sale_price - inventory_items.total_actual_cost -- 0.25 >> 25% #(mprove) format_number='.2p' measure: profit_margin is total_profit / total_sale_price } ``` If both SSF and D3-Format are specified for a Malloy field, D3-Format will take precedence. Example of D3-format usage in Store Fields: ```yaml fields: # store's fields section - dimension: price result: number format_number: "$,.2f" # 1057.1258 >> $1,057.13 currency_prefix: $ currency_suffix: "" - measure: orders_count format_number: "," # 2143 >> 2,143 ``` ### Syntax Fields with `result: number` (implicitly or explicitly set) can accept **format_number** using special syntax of [D3-format](https://github.com/d3/d3-format): `[[fill]align][sign][symbol][0][width][,][.precision][type]` If options are specified, they must preserve its order. ### Fill, Align, Width The **align** can be: - `<` - left alignment - `^` - center alignment - `>` - right alignment (default) The **fill** can be any character. Fill can be specified only if **align** specified. It fills empty characters if value requires less characters than width. The **width** defines the minimum field width in characters. If not specified, then the width will be determined by the content. `[[fill]align][sign][symbol][0][width][,][.precision][type]` | Input | format_number | output | description | | ----- | ------------- | ------------- | ------------------------------------------------ | | 5 | B\<7 | 5BBBBBB | set width to 7 and fill empty characters with B | | 5 | \*^7 | \*\*\*5\*\*\* | set width to 7 and fill empty characters with \* | | 5 | A>7 | AAAAAA5 | set width to 7 and fill empty characters with A | ### Sign The **sign** can be: - `-` - nothing for zero or positive and a minus sign for negative (default) - `+` - a plus sign for zero or positive and a minus sign for negative - `(` - nothing for zero or positive and parentheses for negative `[[fill]align][sign][symbol][0][width][,][.precision][type]` | Input | format_number | output | | ----- | ------------- | ------ | | 5 | + | +5 | | -5 | + | -5 | | 5 | - | 5 | | -5 | - | -5 | | 5 | ( | 5 | | -5 | ( | (5) | ### Symbol The **symbol** can be: - `$` - apply currency_prefix and currency_suffix - `#` - for binary, octal, or hexadecimal types, prefix by 0b, 0o, or 0x, respectively. `[[fill]align][sign][symbol][0][width][,][.precision][type]` | Input | format_number | output | description | | ----- | ------------- | --------- | -------------------------------------------------------------------------------------- | | 5 | $ | $5 | | | 5 | $ | 5 | | | 5 | $ | 5$ | | | 5 | $ | usd 5 | | | 5 | $ | 5 usd | | | 5 | $ | abc 5 def | | | 5 | $ | 5 units | | | 100 | #b | 0b1100100 | | | 100 | #o | 0o144 | | | 100 | #x | 0x64 | | ### Zero The zero `0` option enables zero-padding; this implicitly sets fill to `0`. `[[fill]align][sign][symbol][0][width][,][.precision][type]` | Input | format_number | output | | --------- | ------------- | --------- | | 5 | 07 | 0000005 | | 123 | 07 | 0000123 | | 123456789 | 07 | 123456789 | ### Comma The comma `,` option enables the use of a group separator for thousands. `[[fill]align][sign][symbol][0][width][,][.precision][type]` | Input | format_number | output | | --------- | ------------- | ---------- | | 1000000 | , | 1,000,000 | | -57023.55 | , | -57,023.55 | | 705 | , | 705 | ### Precision and Type The available type values are: - `e` - exponent notation - `d` - decimal notation, rounded to integer - `%` - multiply by 100, and then decimal notation with a percent sign - `p` - multiply by 100, round to significant digits, and then decimal notation with a percent sign - `s` - decimal notation with an SI prefix, rounded to significant digits - `b` - binary notation, rounded to integer - `o` - octal notation, rounded to integer - `x` - hexadecimal notation, using lower-case letters, rounded to integer - `X` - hexadecimal notation, using upper-case letters, rounded to integer - `c` - (not yet supported) converts the integer to the corresponding unicode character before printing - `g` - either decimal or exponent notation, rounded to significant digits - ` ` - (none) like g, but trim insignificant trailing zeros - `f` - fixed point notation - `r` - decimal notation, rounded to significant digits - `n` - shorthand for ,g For the `g`, `n` and ` ` (none) types, decimal notation is used if the resulting string would have **precision** or fewer digits; otherwise, exponent notation is used. Depending on the **type**, the **precision** either indicates the number of digits that follow the decimal point (types `f` and `%`), or the number of significant digits (types ` ` (none), `e`, `g`, `r`, `s` and `p`). If the **precision** is not specified, it defaults to 6 for all types except ` ` (none), which defaults to 12. **Precision** is ignored for integer formats (types `b`, `o`, `d`, `x`, `X` and `c`). `[[fill]align][sign][symbol][0][width][,][.precision][type]` #### Exponential | Input | format_number | output | | -------- | ------------- | ----------- | | 2000 | e | 2.000000e+3 | | 20003010 | e | 2.000301e+7 | | 0.000001 | e | 1.000000e-6 | #### Integer | Input | format_number | output | | ------ | ------------- | ------ | | 1000.1 | d | 1000 | | 1000.5 | d | 1001 | | -1.12 | d | -1 | #### Percentage | Input | format_number | output | | ----- | ------------- | ----------- | | 1 | % | 100.000000% | | 0.999 | % | 99.900000% | | 0.12 | % | 12.000000% | | -0.12 | % | -12.000000% | | 0.25 | .3% | 25.000% | | 1 | p | 100.000% | | 0.999 | p | 99.9000% | | 0.12 | p | 12.0000% | | -0.12 | p | -12.0000% | | 0.25 | .3p | 25.0% | #### Scientific | Input | format_number | output | | -------- | ------------- | -------- | | 0.000001 | s | 1.00000µ | | 0.001 | s | 1.00000m | | 1 | s | 1.00000 | | 1000 | s | 1.00000k | | 1000000 | s | 1.00000M | #### Binary | Input | format_number | output | | ----- | ------------- | ----------- | | 1 | b | 1 | | 8 | b | 1000 | | 16 | b | 10000 | | 2012 | b | 11111011100 | #### Octal | Input | format_number | output | | ----- | ------------- | ------ | | 1 | o | 1 | | 8 | o | 10 | | 16 | o | 20 | | 2012 | o | 3734 | #### Hexadecimal | Input | format_number | output | | ----- | ------------- | ------ | | 17 | x | 11 | | 2012 | x | 7dc | | 17 | X | 11 | | 2012 | X | 7DC | #### General | Input | format_number | output | | ------------- | ------------- | ----------- | | 1000000000000 | g | 1.00000e+12 | | 2000 | g | 2000.00 | | 2000.0301 | g | 2000.03 | | 0.00012 | g | 0.000120000 | | 0.987654 | .1g | 1 | | 0.987654 | .2g | 0.99 | | 0.987654 | .3g | 0.988 | #### None | Input | format_number | output | | ------------- | ------------- | --------- | | 1000000000000 | | 1e+12 | | 2000 | | 2000 | | 2000.0301 | | 2000.0301 | | 0.00012 | | 0.00012 | | 0.987654 | .1 | 1 | | 0.987654 | .2 | 0.99 | | 0.987654 | .3 | 0.988 | #### Fixed | Input | format_number | output | | ------------- | ------------- | -------------------- | | 1000000000000 | f | 1000000000000.000000 | | 2000 | f | 2000.000000 | | 2000.0301 | f | 2000.030100 | | 0.00012 | f | 0.000120 | | 0.987654 | .1f | 1.0 | | 0.987654 | .2f | 0.99 | | 0.987654 | .3f | 0.988 | #### Rounded | Input | format_number | output | | --------- | ------------- | ----------- | | 2000 | r | 2000.00 | | 2000.0301 | r | 2000.03 | | 0.00012 | r | 0.000120000 | | 0.987654 | .1r | 1 | | 0.987654 | .2r | 0.99 | | 0.987654 | .3r | 0.988 | # Model - Malloy For SQL connections, Mprove creates Data Models based on [Malloy Sources](https://docs.malloydata.dev/documentation/language/source). In Malloy, the term “model” usually refers to a file containing Malloy code. In Mprove, the term “model” refers to a Malloy Source with the tag `#(mprove) model`. To minimize Malloy compilation time in Mprove, it is important to store Malloy sources in separate files from other Malloy elements, such as `run: ...` or `query: ...`. This file structure is also easier to maintain. Malloy Sources are reusable. Multiple Sources and Queries can reference the same Source. ```malloy title="c1_order_items.malloy" ##! experimental{} import '../../givens.malloy' import './tables/c1_order_items_tx.malloy'; import './tables/c1_orders_tx.malloy'; import './tables/c1_users_tx.malloy'; import './tables/c1_user_order_facts_tx.malloy'; import './tables/c1_inventory_items_tx.malloy'; import './tables/c1_products_tx.malloy'; import './tables/c1_distribution_centers_tx.malloy'; #(mprove) model #(mprove) label="Order Items" #(mprove) top_label="Order Items" #(mprove) access_roles="sales, marketing" source: c1_order_items is c1_order_items_tx extend { join_one: orders is c1_orders_tx on order_id = orders.order_id join_one: users is c1_users_tx on orders.user_id = users.user_id join_one: user_order_facts is c1_user_order_facts_tx on users.user_id = user_order_facts.user_id join_one: inventory_items is c1_inventory_items_tx on inventory_item_id = inventory_items.inventory_item_id join_one: products is c1_products_tx on inventory_items.product_id = products.product_id join_one: distribution_centers is c1_distribution_centers_tx on inventory_items.distribution_center_id = distribution_centers.distribution_center_id measure: total_profit is total_sale_price - inventory_items.total_actual_cost profit_margin is total_profit / total_sale_price where: orders.created_at_ts >= $START_TS AND distribution_centers.distribution_center_id IN $DISTRIBUTION_CENTER_IDS } ``` Example of building Metrics that can be used in [Reports](/content/docs/reference/report): ```malloy title="tables/c1_orders_tx.malloy" ##! experimental{} type: c1_orders_type is { order_id :: string, user_id :: string, created_at :: number, created_at_ts :: timestamptz, status :: string } source: c1_orders_table is c1_postgres.table('ecommerce.orders')::c1_orders_type extend { rename: created_at_timestamp is created_at_ts } source: c1_orders_tx is c1_orders_table include { internal: created_at_timestamp created_at public: * } extend { primary_key: order_id measure: orders_count is count() #(mprove) build_metrics field_group="Created At" dimension: created_at_t is created_at_timestamp created_at_ts is created_at_t created_at_day is created_at_t.day created_at_week is created_at_t.week created_at_month is created_at_t.month created_at_quarter is created_at_t.quarter created_at_year is created_at_t.year created_at_hour is created_at_t.hour created_at_minute is created_at_t.minute created_at_second is created_at_t.second } ``` ## Reference ### Source #(mprove) Tags | Name | Type | Default | Description | | ---------------------- | ---- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | model | tag | - | If specified, the Malloy source will become available for exploration as an Mprove model. | | label | tag | - | Model label in UI | | top_label | tag | - | Top node label in the Model Schema | | access_roles | tag | - | User roles list separated by comma. If `access_roles` tag is set, only users with specified roles will be able to explore the model. If `access_roles` tag is not specified, all project users will be able to explore the model. | | tree_double_underscore | tag | - | If this tag is set, the portion of the field name preceding the double underscore will be used to create a top-level group in the model fields tree in the UI. | ### Dimension #(mprove) Tags | Name | Type | Default | Description | | ------------- | ---- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | build_metrics | tag | - | Mprove creates a metric for each pair of measure and tagged time based dimension (for all measures of the malloy source). `build_metrics` `field_group` must have fields with "\_t" and "\_ts" suffixes. Timeframes can only be based on a field with "\_t" suffix. | | field_group | tag | - | Mprove adds the tagged fields to a subgroup in the web interface (model fields tree). | # Model - Store ## API Store Presets Presets are reusable Store templates for API data sources. Mprove has a [Store preset for Google Analytics](https://github.com/mprove-io/mprove/blob/master/apps/blockml/src/presets/google_analytics.store.preset). Minimal Store file that uses the preset looks like this. ```yaml store: google_analytics connection: c7_google preset: google_analytics ``` Additional parameters specified in the Store file will override those taken from the preset. ## Store Model **Store Model** defines the schema and logic for retrieving query data via an HTTP request. To use **Google Analytics** integration create a Store file that references a Store Preset. For custom API data sources (when no preset is available), you need to create a full Store file. This page shows how to create a custom integration from scratch. **Google Analytics** is used as an example. Each Store must be stored as **store_name.store** YAML file: ```yaml store: ga connection: c7_google # preset: google_analytics label: Google Analytics description: 'Google Analytics Reporting' access_roles: [] method: POST request: | response: | date_range_includes_right_side: true parameters: - filter: top_config required: true max_fractions: 1 fraction_controls: - selector: ga_property label: Property value: '123456789' options: - value: '123456789' - date_picker: start_date label: Start Date value: $METRICS_DATE_FROM - date_picker: end_date label: End Date value: $METRICS_DATE_TO results: - result: number fraction_types: - type: equal label: Equal meta: operation: EQUAL filter_type: numericFilter controls: - input: value_input - type: less_than meta: operation: LESS_THAN filter_type: numericFilter controls: - input: value_input - type: less_than_or_equal meta: operation: LESS_THAN_OR_EQUAL filter_type: numericFilter controls: - input: value_input - type: greater_than meta: operation: GREATER_THAN filter_type: numericFilter controls: - input: value_input - type: greater_than_or_equal meta: operation: GREATER_THAN_OR_EQUAL filter_type: numericFilter controls: - input: value_input - type: between meta: operation: BETWEEN filter_type: betweenFilter controls: - input: value_from_input - input: value_to_input - result: string fraction_types: - type: exact meta: match_type: EXACT filter_type: stringFilter controls: - input: value_input - type: begins_with meta: match_type: BEGINS_WITH filter_type: stringFilter controls: - input: value_input - type: ends_with meta: match_type: ENDS_WITH filter_type: stringFilter controls: - input: value_input - type: contains meta: match_type: CONTAINS filter_type: stringFilter controls: - input: value_input - type: full_regexp meta: match_type: FULL_REGEXP filter_type: stringFilter controls: - input: value_input - type: partial_regexp meta: match_type: PARTIAL_REGEXP filter_type: stringFilter controls: - input: value_input - type: in_list meta: match_type: IN_LIST filter_type: inListFilter controls: - list_input: values_input field_groups: - group: "attribution" label: "Attribution" - group: "demographics" label: "Demographics" - group: "ecommerce" label: "Ecommerce" - group: "event" label: "Event" - group: "geography" label: "Geography" - group: "other" label: "Other" - group: "page_screen" label: "Page Screen" - group: "platform_device" label: "Platform Device" - group: "publisher" label: "Publisher" - group: "revenue" label: "Revenue" - group: "session" label: "Session" - group: "traffic_source" label: "Traffic Source" - group: "user" label: "User" - group: "user_lifetime" label: "User Lifetime" field_time_groups: - time: event_created_at group: event label: 'Event Created At' build_metrics: - time: event_created_at fields: ``` ### Store Fields ```yaml fields: # Time Dimensions - dimension: date # "20250127" result: string time_group: event_created_at detail: days description: 'The date of the event, formatted as YYYYMMDD' meta: apiName: date - dimension: iso_year_iso_week # "202504" label: 'Week Monday' result: string time_group: event_created_at detail: weeksMonday description: 'The combined values of isoWeek and isoYear. Example values include 201652 & 201701' meta: apiName: isoYearIsoWeek - dimension: year_week # "202505" label: 'Week Sunday' result: string time_group: event_created_at detail: weeksSunday description: 'The combined values of year and week. Example values include 202253 or 202301' meta: apiName: yearWeek - dimension: year_month # "202501" label: 'Month' result: string time_group: event_created_at detail: months description: 'The combined values of year and month. Example values include 202212 or 202301' meta: apiName: yearMonth - dimension: year # "2025" label: 'Year' result: string time_group: event_created_at detail: years description: 'The four-digit year of the event. For example, 2020 or 2024' meta: apiName: year - dimension: date_hour # "2025012722" label: 'Hour' result: string time_group: event_created_at detail: hours description: 'The combined values of date and hour formatted as YYYYMMDDHH' meta: apiName: dateHour - dimension: date_hour_minute # "202501272212" label: 'Minute' result: string time_group: event_created_at detail: minutes description: 'The combined values of date, hour, and minute formatted as YYYYMMDDHHMM' meta: apiName: dateHourMinute - dimension: hour # "4" result: string group: event description: "The two-digit hour of the day that the event was logged. This dimension ranges from 0-23 and is reported in your property's timezone" meta: apiName: hour - dimension: minute # "40" result: string group: event description: "The two-digit minute of the hour that the event was logged. This dimension ranges from 0-59 and is reported in your property's timezone" meta: apiName: minute - dimension: day # "26" result: string group: event description: 'The day of the month, a two-digit number from 01 to 31' meta: apiName: day - dimension: day_of_week # "0" label: 'Day of week' result: string group: event description: 'The integer day of the week. It returns values in the range 0 to 6 with Sunday as the first day of the week' meta: apiName: dayOfWeek - dimension: day_of_week_name # "Sunday" label: 'Day of week name' result: string group: event description: 'The day of the week in English. This dimension has values such as Sunday or Monday' meta: apiName: dayOfWeekName - dimension: week # "05" label: 'Week' result: string group: event description: 'The week of the event, a two-digit number from 01 to 53. Each week starts on Sunday. January 1st is always in week 01. The first and last week of the year have fewer than 7 days in most years. Weeks other than the first and the last week of the year always have 7 days. For years where January 1st is a Sunday, the first week of that year and the last week of the prior year have 7 days' meta: apiName: week - dimension: month # "01" result: string group: event description: 'The month of the event, a two digit integer from 01 to 12' meta: apiName: month - dimension: iso_year # "2025" label: 'ISO year' result: string group: event description: 'The ISO year of the event. For details, see http://en.wikipedia.org/wiki/ISO_week_date. Example values include 2022 & 2023' meta: apiName: isoYear - dimension: iso_week # "04" label: 'ISO week of the year' result: string group: event description: 'ISO week number, where each week starts on Monday. For details, see http://en.wikipedia.org/wiki/ISO_week_date. Example values include 01, 02, & 53' meta: apiName: isoWeek - dimension: nth_year # "0000" label: 'Nth year' result: string description: 'The number of years since the start of the date range. The starting year is 0000' group: event meta: apiName: nthYear - dimension: nth_month # "0000" label: 'Nth month' result: string group: event description: 'The number of months since the start of a date range. The starting month is 0000' meta: apiName: nthMonth - dimension: nth_week # "0001" label: 'Nth week' result: string group: event description: 'A number representing the number of weeks since the start of a date range' meta: apiName: nthWeek - dimension: nth_day # "0004" label: 'Nth day' result: string group: event description: 'The number of days since the start of the date range' meta: apiName: nthDay - dimension: nth_hour # "0142" label: 'Nth hour' result: string group: event description: 'The number of hours since the start of the date range. The starting hour is 0000' meta: apiName: nthHour - dimension: nth_minute # "8532" label: 'Nth minute' result: string group: event description: 'The number of minutes since the start of the date range. The starting minute is 0000' meta: apiName: nthMinute # Dimensions (full list is available on github) - dimension: "country" result: "string" description: "The country from which the user activity originated." group: "geography" label: "Country" meta: apiName: "country" uiName: "Country" - dimension: "city" result: "string" description: "The city from which the user activity originated." group: "geography" label: "City" meta: apiName: "city" uiName: "City" # Measures (full list is available on github) - measure: "active_users" result: "number" description: "The number of distinct users who visited your site or app." group: "user" label: "Active Users" meta: apiName: "activeUsers" uiName: "Active users" type: "TYPE_INTEGER" - measure: "sessions" result: "number" description: "The number of sessions that began on your site or app (event triggered: session_start)." group: "session" label: "Sessions" meta: apiName: "sessions" uiName: "Sessions" type: "TYPE_INTEGER" - measure: "screen_page_views" result: "number" description: "The number of app screens or web pages your users viewed. Repeated views of a single page or screen are counted. (screen_view + page_view events)." group: "page_screen" label: "Views" meta: apiName: "screenPageViews" uiName: "Views" type: "TYPE_INTEGER" ``` ### Store createRequest.js ```js let storeFields = $STORE_FIELDS; let queryOrderBy = $QUERY_ORDER_BY; let selectedDimensions = $QUERY_SELECTED_DIMENSIONS; let selectedMeasures = $QUERY_SELECTED_MEASURES; let queryParameters = $QUERY_PARAMETERS; let queryLimit = $QUERY_LIMIT; let orderByElements = []; queryOrderBy.forEach(x=> { let orderBy; if (selectedDimensions.map(field => field.name).indexOf(x.field.name) > -1) { orderBy = { dimension: { dimensionName: x.field.meta.apiName }, desc: x.desc } } else if (selectedMeasures.map(field => field.name).indexOf(x.field.name) > -1) { orderBy = { metric: { metricName: x.field.meta.apiName }, desc: x.desc } } orderByElements.push(orderBy); }); let dimOrExpressions = []; let dimAndNotExpressions = []; let mcOrExpressions = []; let mcAndNotExpressions = []; queryParameters.map(filter => { let field = storeFields.find(x => x.name === filter.fieldId); filter.fractions.forEach(fraction => { let apiFilter; let apiFilterType = fraction.meta?.filter_type; if (apiFilterType === 'stringFilter') { apiFilter = { fieldName: field.meta.apiName, stringFilter: { matchType: fraction.meta.match_type, value: fraction.controls.find(control => control['name'] === 'value_input')?.value, caseSensitive: $PROJECT_CONFIG_CASE_SENSITIVE } } } if (apiFilterType === 'inListFilter') { apiFilter = { fieldName: field.meta.apiName, inListFilter: { values: fraction.controls.find(control => control['name'] === 'values_input')?.values, caseSensitive: $PROJECT_CONFIG_CASE_SENSITIVE } } } if (apiFilterType === 'numericFilter') { apiFilter = { fieldName: field.meta.apiName, numericFilter: { operation: fraction.meta.operation, value: { doubleValue: Number(fraction.controls.find(control => control['name'] === 'value_input')?.value) } } } } if (apiFilterType === 'betweenFilter') { apiFilter = { fieldName: field.meta.apiName, betweenFilter: { fromValue: { doubleValue: Number(fraction.controls.find(control => control['name'] === 'value_from_input')?.value) }, toValue: { doubleValue: Number(fraction.controls.find(control => control['name'] === 'value_to_input')?.value) }, } } } if (field.fieldClass === 'dimension') { if (fraction.logicGroup === 'OR') { dimOrExpressions.push({ filter: apiFilter }) } if (fraction.logicGroup === 'AND_NOT') { dimAndNotExpressions.push({ notExpression: { filter: apiFilter } }) } } if (field.fieldClass === 'measure') { if (fraction.logicGroup === 'OR') { mcOrExpressions.push({ filter: apiFilter }) } if (fraction.logicGroup === 'AND_NOT') { mcAndNotExpressions.push({ notExpression: { filter: apiFilter } }) } } }); }); let dateRanges = [{ startDate: queryParameters.find(x => x['fieldId'] === 'top_config')?.fractions[0] .controls.find(control => control.name === 'start_date')?.value, endDate: queryParameters.find(x => x['fieldId'] === 'top_config')?.fractions[0] .controls.find(control => control.name === 'end_date')?.value }]; let dimAndGroupExpressions = []; let mcAndGroupExpressions = []; if (dimOrExpressions.length > 0) { dimAndGroupExpressions.push({ orGroup: { expressions: dimOrExpressions } }); } if (dimAndNotExpressions.length > 0) { dimAndGroupExpressions = [ ...dimAndGroupExpressions, ...dimAndNotExpressions ]; } if (mcOrExpressions.length > 0) { mcAndGroupExpressions.push({ orGroup: { expressions: mcOrExpressions } }); } if (mcAndNotExpressions.length > 0) { mcAndGroupExpressions = [ ...mcAndGroupExpressions, ...mcAndNotExpressions ]; } let body = { dimensions: selectedDimensions.map(x => ({ name: x.meta['apiName'] })), metrics: selectedMeasures.map(x => ({ name: x.meta['apiName'] })), dateRanges: dateRanges, dimensionFilter: (dimOrExpressions.length > 0 || dimAndNotExpressions.length > 0) ? { andGroup: { expressions: dimAndGroupExpressions } } : undefined, metricFilter: (mcOrExpressions.length > 0 || mcAndNotExpressions.length > 0) ? { andGroup: { expressions: mcAndGroupExpressions } } : undefined, limit: queryLimit, orderBys: orderByElements, currencyCode: 'USD', keepEmptyRows: true, returnPropertyQuota: false, cohortSpec: undefined, // supported in ga_cohorts.store metricAggregations: undefined, // not supported comparisons: undefined, // not supported offset: undefined // not supported } let propertyId = queryParameters.find(x => x['fieldId'] === 'top_config')?.fractions[0] .controls.find(control => control.name === 'ga_property')?.value; let urlPath = \`/v1beta/properties/\$\{propertyId\}:runReport\`; return { urlPath: urlPath, body: body }; ``` ### Store processResponse.js ```js let data = $RESPONSE_DATA; let storeFields = $STORE_FIELDS; let dimensionHeaders = data.dimensionHeaders?.map(header => header.name); let metricHeaders = data.metricHeaders?.map(header => header.name); newData = data.rows?.map(row => { let newRow = {}; row.dimensionValues?.forEach((dimension, index) => { let dimensionName = dimensionHeaders[index]; let value = dimension.value; let date; if (dimensionName === 'year') { date = new Date(\`\${value}-01-01T00:00:00Z\`); } else if (dimensionName === 'yearMonth') { date = new Date(\`\${value.slice(0, 4)}-\${value.slice(4, 6)}-01T00:00:00Z\`); } else if (dimensionName === 'isoYearIsoWeek') { // Parse ISO year and week (e.g., "202504" -> year=2025, week=4) // Create a date in week 1 of the ISO year (January 4th is always in week 1) // Get the Monday of week 1 // Add weeks to reach the target week let year = parseInt(value.slice(0, 4), 10); let week = parseInt(value.slice(4, 6), 10); date = new Date(Date.UTC(year, 0, 4)); let day = date.getUTCDay(); // 0 (Sunday) to 6 (Saturday) date.setUTCDate(date.getUTCDate() - (day === 0 ? 6 : day - 1)); date.setUTCDate(date.getUTCDate() + (week - 1) * 7); } else if (dimensionName === 'yearWeek') { // Parse year and week (e.g., "202505" -> year=2025, week=5) // Find the first Sunday of the year // Add weeks to reach the target week let year = parseInt(value.slice(0, 4), 10); let week = parseInt(value.slice(4, 6), 10); let firstDay = new Date(Date.UTC(year, 0, 1)); let day = firstDay.getUTCDay(); // 0 (Sunday) to 6 (Saturday) let firstSunday = new Date(firstDay); firstSunday.setUTCDate(firstDay.getUTCDate() - day); date = new Date(firstSunday); date.setUTCDate(firstSunday.getUTCDate() + (week - 1) * 7); } else if (dimensionName === 'date') { date = new Date(\`\${value.slice(0, 4)}-\${value.slice(4, 6)}-\${value.slice(6, 8)}T00:00:00Z\`); } else if (dimensionName === 'dateHour') { date = new Date(\`\${value.slice(0, 4)}-\${value.slice(4, 6)}-\${value.slice(6, 8)}T\${value.slice(8, 10)}:00:00Z\`); } else if (dimensionName === 'dateHourMinute') { date = new Date(\`\${value.slice(0, 4)}-\${value.slice(4, 6)}-\${value.slice(6, 8)}T\${value.slice(8, 10)}:\${value.slice(10, 12)}:00Z\`); } let storeField = storeFields.find(x => !!x.meta && x.meta['apiName'] === dimensionName); if (!!storeField) { let fieldId = storeField.name; newRow[fieldId] = !!date ? date.getTime()/1000 : value; } }); row.metricValues?.forEach((metric, index) => { let metricName = metricHeaders[index]; let value = metric.value; let storeField = storeFields.find(x => !!x.meta && x.meta['apiName'] === metricName); if (!!storeField) { let fieldId = storeField.name; newRow[fieldId] = value; } }); return newRow; }); return newData || []; ``` ## Reference ### Store Constants These constants are provided based on Store file, [Project Config](/content/docs/reference/project-config#project) and UI control values set by user for a specific query. | Name | Type | Default | Description | | ------------------------------- | ----------------------------------- | --------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- | | `PROJECT_CONFIG_CASE_SENSITIVE` | boolean | false | Should string filter be case sensitive or not | | `METRICS_DATE_FROM` | YYYY-MM-DD | today's date | The start date of time range (based on the time filter on the Reports page, otherwise today's date) | | `METRICS_DATE_TO` | YYYY-MM-DD | tomorrow's date | The end date of time range (based on the time filter on the Reports page, otherwise tomorrow's date) | | `STORE_FIELDS` | \ [] | [] | [STORE_FIELDS](/content/docs/reference/model-store#store_fields) - all store parameters and fields in a single array | | `QUERY_ORDER_BY` | orderByField [] | [] | [QUERY_ORDER_BY](/content/docs/reference/model-store#query_order_by) - fields by which the user wants to sort the data | | `QUERY_SELECTED_DIMENSIONS` | storeField [] | [] | [QUERY_SELECTED_DIMENSIONS](/content/docs/reference/model-store#query_selected_dimensions) - dimensions selected by user | | `QUERY_SELECTED_MEASURES` | storeField [] | [] | [QUERY_SELECTED_MEASURES](/content/docs/reference/model-store#query_selected_measures) - measures selected by user | | `QUERY_PARAMETERS` | storeParameter [] | [] | [QUERY_PARAMETERS](/content/docs/reference/model-store#query_parameters) - filters set by user | | `QUERY_LIMIT` | number | 500 | Max number of rows in response | | `RESPONSE_DATA` | any | - | Response data that needs to be converted into Mprove compatible format as a result of [processResponse.js](/content/docs/reference/model-store#store-processresponsejs) | #### STORE_FIELDS ```js const STORE_FIELDS = [ // list of store parameters and store fields definitions in a single array { required: 'true', max_fractions: '1', fraction_controls: [ { label: 'Property', value: '123123123', options: [ { value: '123123123' } ], name: 'ga_property', controlClass: 'selector' }, { label: 'Start Date', value: '2025-02-24', name: 'start_date', controlClass: 'date_picker' }, { label: 'End Date', value: '2025-02-24', name: 'end_date', controlClass: 'date_picker' } ], name: 'top_config', fieldClass: 'filter', label: 'Top Config', group: 'mf' }, { result: 'string', group: 'geo', description: 'The country from which the user activity originated', meta: { name: 'country' }, name: 'country', fieldClass: 'dimension', label: 'Country', type: 'custom', }, { result: 'number', group: 'users', description: 'The number of distinct users who visited your site or app', meta: { name: 'activeUsers', type: 'TYPE_INTEGER', }, name: 'active_users', fieldClass: 'measure', label: 'Active Users', format_number: ',.0f', currency_prefix: '$', currency_suffix: '', }, // ... other store parameters definitions // ... other store fields definitions ]; ``` #### QUERY_ORDER_BY ```js const QUERY_ORDER_BY = [ { fieldId: 'city', field: { result: 'string', group: 'geo', description: 'The city from which the user activity originated', meta: { name: 'city' }, name: 'city', fieldClass: 'dimension', label: 'City', type: 'custom', }, desc: false } ]; ``` #### QUERY_SELECTED_DIMENSIONS ```js const QUERY_SELECTED_DIMENSIONS = [ { result: 'string', group: 'geo', description: 'The city from which the user activity originated', meta: { apiName: 'city', uiName: 'City' }, name: 'city', fieldClass: 'dimension', label: 'City', type: 'custom', } ]; ``` #### QUERY_SELECTED_MEASURES ```js const QUERY_SELECTED_MEASURES = [ { result: 'number', group: 'users', description: 'The number of distinct users who visited your site or app', meta: { apiName: 'activeUsers', uiName: 'Active users', type: 'TYPE_INTEGER', }, name: 'active_users', fieldClass: 'measure', label: 'Active Users', format_number: ',.0f', currency_prefix: '$', currency_suffix: '', } ]; ``` #### QUERY_PARAMETERS ```js const QUERY_PARAMETERS = [ { fieldId: 'city', fractions: [ { operator: 'Or', type: 'StoreFraction', storeResult: 'string', storeFractionSubType: 'exact', meta: { match_type: 'EXACT', filter_type: 'stringFilter', }, logicGroup: 'AND_NOT', storeFractionSubTypeOptions: [ { value: 'exact' }, { value: 'begins_with' }, { value: 'ends_with' }, { value: 'contains' }, { value: 'full_regexp' }, { value: 'partial_regexp' }, { value: 'in_list' } ], controls: [ { name: 'value_input', controlClass: 'input', value: 'h' }, { value: false, label: 'Case Sensitive', name: 'case_sensitive_switch', controlClass: 'switch' } ] } ], field: { id: 'city', hidden: false, label: 'City', fieldClass: 'dimension', result: 'string', sqlName: 'city', topId: 'geo', topLabel: 'geo', description: 'The city from which the user activity originated', type: 'custom' } }, { fieldId: 'top_config', fractions: [ { type: 'StoreFraction', controls: [ { options: [ { value: '123123123' } ], value: '123123123', label: 'Property', name: 'ga_property', controlClass: 'selector' }, { value: '2025-02-24', label: 'Start Date', name: 'start_date', controlClass: 'date_picker' }, { value: '2025-02-24', label: 'End Date', name: 'end_date', controlClass: 'date_picker' } ] } ] }, ]; ``` ### Store | Name | Type | Default | Description | | -------------------------------- | ---------------------------------------------------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | store\* | string | - | Store name | | connection\* | string | - | Connection name | | preset | string | - | When you specify a preset, all missing definitions in the current store will be set to the same values ​​as in the preset. Available presets:
  • **google_analytics**
| | label | string | - | Store label in UI | | description | string | - | Store description in UI | | access_roles | string [] | - | If specified, only users with the listed roles will have access to Store Model | | method\* | enum | - | Request method:
  • **POST**
  • **GET** (does not have a payload)
| | request\* | string | - | JavaScript function to build Request JSON payload body and URL path. Must return \{ urlPath: string, body: any \}; | | response\* | string | - | JavaScript function to convert Response payload data into the required Mprove format | | date_range_includes_right_side\* | boolean | - | Specify whether the API time filter includes the right range boundary | | parameters | [Store Filter []](#store-filter) | - | Begin a section of store parameters | | results\* | [Store Result []](#store-result) | - | Begin a section of store results | | field_groups\* | [Store Field Group []](#store-field-group) | - | Begin a section of store field groups | | field_time_groups | [Store Field Time Group []](#store-field-time-group) | - | Begin a section of store field time groups | | build_metrics | [Store Build Metric []](#store-build-metric) | - | Begin a section of store BuildMetrics | | fields\* | [Store Field []](#store-field) | - | Begin a section of store fields | ### Store Filter | Name | Type | Default | Description | | ----------------- | ------------------------------------------------------------ | ------- | -------------------------- | | filter\* | string | - | Filter name | | label | string | - | Override filter name in UI | | description | string | - | Filter description in UI | | max_fractions | number | - | Max number of fractions | | required | boolean | - | Always select filter | | fraction_controls | [Fraction Control []](/content/docs/reference/parameters#fraction-control) | - | List of fraction controls | ### Store Result | Name | Type | Default | Description | | ----------------- | ------------------------------------------------ | ------- | ----------------------------- | | result\* | string | - | Result name | | fractions_types\* | [Result Fraction Type []](#result-fraction-type) | - | List of result fraction types | ### Result Fraction Type | Name | Type | Default | Description | | -------- | ------------------------------------------------------------ | ------- | ------------------------- | | type\* | string | - | Type name | | label | string | - | Override type name in UI | | meta | any | - | Custom metadata | | controls | [Fraction Control []](/content/docs/reference/parameters#fraction-control) | - | List of fraction controls | ### Store Field Group | Name | Type | Default | Description | | ------- | ------ | ------- | ------------------------- | | group\* | string | - | Group name | | label | string | - | Override group name in UI | ### Store Field Time Group | Name | Type | Default | Description | | ------ | ------ | ------- | ------------------------- | | time\* | string | - | Time group name | | group | string | - | Parent group name | | label | string | - | Override group name in UI | ### Store Build Metric | Name | Type | Default | Description | | ------ | ------ | ------- | --------------------------------------------- | | time\* | string | - | Specify the field time group to build Metrics | ### Store Field | Name | Type | Default | Description | | --------------- | ------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | dimension | string | - | Field name | | measure | string | - | Field name | | label | string | - | Override field name in UI | | description | string | - | Field descripiton in UI | | result\* | string | - | Store field result | | group | string | - | Parent group name | | time_group | string | - | Parent time_group name | | detail | enum | - |
  • years
  • quarters
  • months
  • weeksMonday
  • weeksSunday
  • days
  • hours
  • minutes
  • timestamps
| | required | boolean | - | Always select field | | meta | any | - | Custom metadata | | format_number | string | - | [Format Number](/content/docs/reference/format-number) | | currency_prefix | string | - | [Format Number - Symbol](/content/docs/reference/format-number#symbol) | | currency_suffix | string | - | [Format Number - Symbol](/content/docs/reference/format-number#symbol) | # Parameters Reports, Charts, Dashboards are intended to be created and modified through the user interface. The file representation is for checking errors during validation. Parameters can be a part of [Report](/content/docs/reference/report), [Dashboard](/content/docs/reference/dashboard) or [Store](/content/docs/reference/model-store) files. Store parameters schema is an array of [Store Filters](/content/docs/reference/model-store#store-filter). Dashboard and Report parameters schema is an array of [Top Filters](/content/docs/reference/parameters#top-filter). Dashboard tile parameters schema is an array of [Tile Parameter](/content/docs/reference/dashboard#tile-parameter). Report row parameters schema is an array of [Row Parameter](/content/docs/reference/report#row-parameter). One dashboard parameter can be applied to several tiles at the same time. A tile parameter is only affected by a dashboard parameter if tile parameter has a **listen** pointing to dashboard parameter. One report parameter can be applied to several rows at the same time. A row parameter is only affected by a report parameter if the row parameter has a **listen** pointing to report parameter. ```yaml # Report or Dashboard parameters: # this filter can be listened by tiles or rows based on Malloy models - filter: filter_name label: '...' description: '...' result: filter_result suggest_model_dimension: field_path conditions: - 'filter expression' # this filter can be listened by tiles or rows based on specified Store model - filter: filter_name store_model: store_model_name store_result: store_result_name # filter can be mapped to fields that have specified result fractions: - logic: OR type: contains controls: - input: value_input value: a # this filter can be listened by tiles or rows based on specified Store model - filter: filter_name store_model: store_model_name store_filter: store_filter_name # filter can be mapped to specified store filter fractions: - controls: - selector: ga_property value: 123123123 - date_picker: start_date value: $METRICS_DATE_FROM - date_picker: end_date value: $METRICS_DATE_TO ``` ## Reference ### Top Filter | Name | Type | Default | Description | | ----------------------- | ------------------------ | ------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | filter\* | string | - | Filter name | | label | string | - | Override filter name in UI | | description | string | - | Filter description in UI | | result | enum | - |
  • **string**
  • **number**
  • **boolean**
  • **ts** (timestamp)
| | store_model | string | - | Store name with "store_model\_" prefix | | store_result | string | - | Store result name | | store_filter | string | - | Store filter name | | suggest_model_dimension | string | - | `for parameters mapped to Model Fields of type string`
Suggest values ​​from the specified dimension for the filter if the user selects an "is equal to" or "is not equal to" filter expression | | conditions | string [] | - | `for parameters mapped to Model Fields and Filters`
List of filter expressions that will be applied to this filter | | fractions | [Fraction []](#fraction) | - | `for parameters mapped to Store Fields and Filters`
Begin a section of fractions | ### Fraction | Name | Type | Default | Description | | -------- | ------------------------------------------------------------ | ------- | ------------------------------------ | | logic | enum | - |
  • OR
  • AND_NOT
| | type | string | - | Store result type | | controls | [Fraction Control []](/content/docs/reference/parameters#fraction-control) | - | Begin a section of controls | ### Fraction Control | Name | Type | Default | Description | | ----------- | ----------------------- | ------- | ------------------------ | | input | string | - | Input control name | | switch | string | - | Switch control name | | selector | string | - | Selector control name | | date_picker | string | - | Date_picker control name | | value | string, number, boolean | - | Value | # Project Config Each project must have mprove.yml config file: ```yaml mprove_dir: ./ case_sensitive_string_filters: false format_number: ",.0f" currency_prefix: $ currency_suffix: "" ``` ## Reference ### Project | Name | Type | Default | Description | | ----------------------------- | ------- | ------- | ------------------------------------------------------------------------------------------------------------------- | | mprove_dir\* | string | ./ | Relative subpath of the project repository to the folder that should be used by Mprove | | case_sensitive_string_filters | boolean | false | `for Store Models`
Specify whether string filters should be case sensitive | | format_number | string | ',.0f' | [Format Number](/content/docs/reference/format-number). The default d3-format string if no overrides are specified in the fields. | | currency_prefix | string | $ | [Format Number - Symbol](/content/docs/reference/format-number#symbol). The default d3-format currency prefix. | | currency_suffix | string | '' | [Format Number - Symbol](/content/docs/reference/format-number#symbol). The default d3-format currency suffix. | {/* | week_start | enum | Monday | Start day of the week:
  • Sunday
  • Monday
| */} {/* | allow_timezones | boolean | true | Allow users to change timezone in UI | */} {/* | default_timezone | enum | UTC | Specify default timezone for UI (useful if allow_timezones is false) | */} # Report Reports, Charts, Dashboards are intended to be created and modified through the user interface. The file representation is for checking errors during validation. Each Report must be stored as **report_name.report** YAML file: ```yaml report: report_name title: '' access_roles: - role - role parameters: - filter: f1 label: '' description: '' result: string suggest_model_dimension: model_name.field_path conditions: - f\`%a\` - filter: f2 store_model: ga store_result: string fractions: - logic: OR type: contains controls: - input: value_input value: a - filter: f3 store_model: ga store_filter: top_config fractions: - controls: - selector: ga_property value: 123123123 - date_picker: start_date value: $METRICS_DATE_FROM - date_picker: end_date value: $METRICS_DATE_TO rows: - row_id: A type: empty - row_id: B type: header name: 'name in UI' - row_id: C type: metric metric: metric_id show_chart: true format_number: '$,.0f' currency_prefix: $ currency_suffix: '' parameters: - apply_to: field_path listen: f1 - row_id: D type: metric metric: metric_id parameters: - apply_to: field_path conditions: - f\`> 100\` - row_id: E type: metric metric: ga.screen_page_views.by.event_created_at parameters: - apply_to: city listen: f2 - apply_to: top_config listen: f3 - row_id: F type: metric metric: ga.screen_page_views.by.event_created_at parameters: - apply_to: city fractions: - logic: OR type: contains controls: - input: value_input value: a - apply_to: top_config fractions: - controls: - selector: ga_property value: 123123123 - date_picker: start_date value: $METRICS_DATE_FROM - date_picker: end_date value: $METRICS_DATE_TO - row_id: G type: formula formula: ($C + $E) / 2 name: 'name in UI' options: series: - data_row_id: C y_axis_index: 0 type: 'line' ``` ## Reference ### Report Constants `$METRICS_DATE_FROM` and `$METRICS_DATE_TO` are intended for use by [Store Models](/content/docs/reference/model-store). Their values are provided based on the time filter set by the user for a specific query. ### Report | Name | Type | Default | Description | | ------------ | ------------------------------------------------ | ------- | ------------------------------------------------------------------------- | | report\* | string | - | Report name | | title | string | - | Dashboard title in UI | | access_roles | string [] | - | If specified, only users with the listed roles will have access to Report | | parameters | [Top Filter []](/content/docs/reference/parameters#top-filter) | - | Begin a section of report parameters | | rows | [Row []](#row) | - | Begin a section of rows | | options | [Tile Options](/content/docs/reference/dashboard#tile-options) | - | Report options schema is the same as dashboard tile options schema | ### Row | Name | Type | Default | Description | | --------------- | ---------------------------------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------ | | row_id\* | string | - | Row Id | | type\* | enum | - | Row type:
  • **metric**
  • **formula**
  • **header**
  • **empty**
| | metric\*\* | string | - | metric_id. Required for row type **Metric**. | | formula\*\* | string | - | PostgresSQL SQL formula for calculating row data using `$ROW_ID` references to other rows (simple math operations). Required for row type **Formula**. | | name\*\* | string | - | Row name. Required for row type **Formula** or **Header**. | | show_chart | boolean | false | Show row data on chart | | format_number | string | - | [Format Number](/content/docs/reference/format-number) | | currency_prefix | string | - | [Format Number - Symbol](/content/docs/reference/format-number#symbol) | | currency_suffix | string | - | [Format Number - Symbol](/content/docs/reference/format-number#symbol) | | parameters | [Row Parameter []](#row-parameter) | - | Begin a section of row parameters | ### Row Parameter Row parameter must have **conditions**, **listen** or **fractions**. | Name | Type | Default | Description | | ---------- | -------------------------------------------- | ------- | ----------------------------------------------------------- | | apply_to\* | string | - | Field or Filter name | | listen | string | - | Specify report parameter to listen to | | conditions | string [] | - | `for parameters mapped to Model`
List of filter expressions | | fractions | [Fraction []](/content/docs/reference/parameters#fraction) | - | `for parameters mapped to Store`
List of fractions |