Umitake BI for Kintone — User Manual

Table of Contents

1. Overview
2. System Requirements
3. Installation
4. Initial Setup
5. Configuring Data Sources
6. Creating Charts
7. Creating and Publishing Dashboards
8. Viewing Dashboards
9. Export (PNG / PDF)
10. FAQ & Troubleshooting

1. Overview

Umitake BI for Kintone is a plugin that lets you create and view PowerBI-style charts and dashboards directly within kintone.
All data fetching, processing, and chart rendering happens inside the browser — no additional server required.

Key Features:

• Join data from multiple kintone apps for cross-app analytics
• Union (append) data from multiple apps into a single source
• Rich chart types: bar, line, area, pie/donut, scatter/bubble, heatmap, pivot table, KPI card, and flat table
• Virtual tables for data shaping (column selection, computed columns, row filters, deduplication)
• Interactive slicers (date range, category, numeric range)
• PNG / PDF export for individual charts and entire dashboards
• localStorage data cache with configurable TTL

User Roles:

2. System Requirements

• kintone (Standard plan or higher recommended)
• Supported browsers: Google Chrome (latest), Microsoft Edge (latest)
• JavaScript must be enabled

3. Installation

3.1 Obtain the Plugin File

Obtain the kintone-bi-tool.zip distribution file.

3.2 Upload to kintone

1. Go to kintone System Administration → Plug-ins
2. Click Import and upload kintone-bi-tool.zip
3. Once uploaded, the plugin will appear in the plug-in list

3.3 Create the BI Hub App

1. On the kintone top page, click + → Create app from scratch
2. Name the app (e.g., "BI Hub") — no fields need to be added
3. Publish the app

3.4 Configure a Custom View

The dashboard viewer requires a Custom View in the BI Hub app.

1. Open the BI Hub app Settings → Views
2. Click + to add a new view
3. Set the View type to Custom view
4. Enter a view name (e.g., "Dashboard")
5. In the HTML field, enter the following (required):

   <div id="bi-viewer-placeholder"></div>
   

6. Save and update the app
7. Set this custom view as the default view

3.5 Install the Plugin

1. Open the BI Hub app Settings → Plug-ins
2. Click Add plug-in
3. Select Umitake BI for Kintone from the list and add it
4. Update the app

4. Initial Setup

4.1 Open the Plugin Settings

1. Open the BI Hub app Settings → Plug-ins
2. Click Settings next to Umitake BI for Kintone

4.2 Create the BI Config App

On first use, no BI Config App exists yet.

1. Click Auto-create BI Config App
2. The plugin automatically creates a dedicated app in kintone to store all configuration
3. Once created, the main settings screen appears

Note: The BI Config App stores data source definitions, chart configs, and dashboard layouts as JSON records. You do not need to manage it manually.

5. Configuring Data Sources

Use the Data Sources tab in the plugin settings.
The recommended setup order is: Data Sources → JOINs / Unions → Virtual Tables.

5.1 Add a Data Source

Register one kintone app as a data source.

1. Click + Add Data Source
2. Enter a name (e.g., Sales Orders)
3. Click Select App to choose the kintone app to pull data from
4. Check the fields you want to use
5. Optionally enter a kintone query filter (kintone query syntax)
   • Example: status = "Closed" and order_date >= "2024-01-01"
6. Click Save

Tip: Unchecking fields you don't need in charts or filters reduces data transfer and improves performance.

Field Refresh: Click the 🔄 button on a data source card to re-fetch the field list from kintone and save the result. Use this after adding or modifying fields in the kintone app form — no need to delete and recreate the data source.

5.2 Configure a JOIN

Use a JOIN to combine data from different apps into a single table.

1. Click + Add JOIN
2. Enter a name (e.g., Orders + Customers)
3. Select the left table and right table (data sources, other JOINs, or virtual tables)
4. Specify join key pairs — each row pairs one field from the left table with one from the right:
   • Click + Add key pair to add more rows for composite key joins (multiple fields combined)
   • A single row behaves the same as a traditional single-key join
5. Choose the JOIN type:

6. Optionally set aliases to distinguish fields with the same name
7. Click Save

Example (single key):

Left table:  Sales Orders   (field: customer_code)
Right table: Customer Master (field: code)
JOIN type:   LEFT JOIN
→ Result: all order rows, each enriched with matching customer data

Example (composite key):

Left table:  Order Lines  (fields: customer_code + product_code)
Right table: Price Master (fields: cust_code     + prod_code)
JOIN type:   INNER JOIN

→ Add two key pair rows:
   Row 1: left=customer_code  right=cust_code
   Row 2: left=product_code   right=prod_code
→ Only rows where both fields match are joined

Note: JOINs can be nested. You can use a JOIN result as the input to another JOIN (JOIN-of-JOIN). Virtual tables are also available as left or right table sources.

Warning: A performance warning is shown when either table exceeds 20,000 records.

5.3 Configure a Virtual Table

A virtual table applies column selection, deduplication, computed columns, and row filters on top of a data source or JOIN result.
Use it to shape data before passing it to a chart.

1. Click + Add Virtual Table
2. Enter a name (e.g., Monthly Sales View)
3. Select a source (data source or JOIN)
4. Configure the options below
5. Click Save

Editing: Click the ✏️ Edit button on an existing virtual table card to reopen its settings modal. Saving overwrites the existing virtual table in place — it does not create a new one.

DISTINCT (Deduplication)

Turn on DISTINCT ON to collapse duplicate records into one.

• No keys selected: removes rows where every column value is identical
• Keys selected: removes rows that share the same values in the checked fields

Example:

Source: Sales Orders (contains order_id, customer_code, product_code, amount)

DISTINCT ON → ON
Keys: customer_code  ✓
      product_code   ✓
      order_id       (unchecked)
      amount         (unchecked)

→ If a customer bought the same product multiple times, only one row is kept

Row Filter (WHERE)

Keeps only rows that match the condition. Reference fields by their field code.

• Click a field chip shown below the input to insert the field code at the cursor position.

Examples:

amount > 10000
status == "Closed"
YEAR(order_date) == 2024
amount > 1000 AND status == "Closed"
(amount > 100000 OR qty > 50) AND status == "Closed"

Computed Columns

Add new columns derived from existing fields.

Click + Add Computed Column, then enter a column name and expression.

Examples:

GROUP BY (Pre-Aggregation)

Setting a GROUP BY on a virtual table lets you aggregate a fact table by a key before joining it to another table.
This prevents fan-out (row multiplication) that occurs when you join two fact tables at different granularities.

What is fan-out?

For example, joining these two tables directly causes problems:

Order Lines:  staff_id | invoice_id | qty    (multiple rows per staff)
Overtime:     staff_id | date       | hours  (multiple rows per staff per day)

→ Direct JOIN produces lines × days rows, making SUM(hours) inflate by the number of order lines

Solving it with GROUP BY:

Step 1: Create a virtual table on top of the Overtime data source
  - Group Keys (GROUP BY): staff_id
  - Aggregated Measure: overtime_hours → sum → total_overtime

  → Output: one row per staff_id (overtime total pre-computed)

Step 2: Use this virtual table as the right table in a JOIN
  Order Lines LEFT JOIN Overtime_VT on staff_id

  → 1-to-1 join — no fan-out

Step 3: In a chart, set staff_id as a dimension and SUM(qty) / SUM(total_overtime) as measures
  → Correct values aggregated

Setup steps:

1. Click + Add Virtual Table
2. Select the Overtime data source as the source
3. In the GROUP BY (Pre-Aggregation) section, check the group key (e.g., staff_id)
4. Click + Add Aggregated Measure and define the aggregated column:
   • Source field: overtime_hours (field to aggregate)
   • Aggregation: sum
   • Output column name (AS): total_overtime (name to reference in charts)
5. Click Save

Note: When GROUP BY is active, the Column Selection (SELECT) and DISTINCT settings are ignored. GROUP BY defines the output schema.

Tip: You can combine GROUP BY with computed columns (which run before GROUP BY). For example, add a computed column YEAR(date) and then GROUP BY that column to aggregate by year.
Computed column aliases are automatically included as candidates in the Group Keys checkbox list and the Aggregated Measures field selector.

5.4 Configure a Union (Vertical Concatenation / Append)

A Union stacks multiple sources vertically (appending rows) into a single source. While a JOIN combines columns horizontally, a Union piles up tables with the same column structure.
Example: merging a "2024 Sales" app with a "2025 Sales" app, combining apps from multiple locations, etc.

1. Click + Add Union
2. Enter a Name (e.g. Combined Sales)
3. Add two or more input tables (data sources, JOINs, virtual tables, or other Unions). Each input can be given a label
4. Define the output columns (see below)
5. Optionally configure the options (source tag, deduplication)
6. Click Save

Editing: Click the ✏️ Edit button on a Union card to reopen the editor. Saving overwrites that Union.

Input tables

Use + Add table to add tables to stack. At least 2 are required. Their order becomes the row order of the result (all rows of the first table → all rows of the next → …).

Output columns (mapping)

Define the columns to output and, in a matrix grid, specify which field of each input table maps to each output column.

• Auto-match by code: When you add an input table, columns with the same field code are mapped automatically. The ⟳ Auto-match by code button regenerates all columns at any time
• Manual adjustment: Use + Add column to add a column and assign each input's field via dropdowns. Even apps with different field codes can be mapped to a shared output column
• Unassigned (—): Choose "— (none)" when an input table lacks that column. Rows from that input will have an empty value there
• Type: Set Number / Text / Date for each output column

Options

To aggregate: A Union focuses solely on appending rows. If you need aggregation, filtering, or GROUP BY, wrap the Union by selecting it as the source of a virtual table — all existing virtual table features (WHERE, GROUP BY, computed columns) then apply.

Slicer integration: Slicers that target a Union are correctly applied to charts that use that Union.

5.5 Expression Syntax (Row Filter & Computed Columns)

Row filters and computed columns share the same expression engine.

Operators

Functions

ROUND digits:
ROUND(amount, 2) → 2 decimal places
ROUND(amount, 0) → integer
ROUND(amount, -3) → round to nearest thousand (1234567 → 1235000)

Compound condition examples

# Closed orders in 2024 over $100,000
YEAR(order_date) == 2024 AND status == "Closed" AND amount > 100000

# Category A or B, with positive amount
(category == "A" OR category == "B") AND amount > 0

# High-value orders handled by a specific rep
(amount > 500000 OR qty > 100) AND assignee == "John Smith"

6. Creating Charts

Use the Charts tab in the plugin settings.

6.1 Add a Chart

1. Click + Add Chart
2. Enter a chart name (e.g., Monthly Sales Bar Chart)
3. Select a chart type (see table below)
4. Select a data source (virtual table, JOIN, or data source)
5. Configure dimensions and measures (see below)
6. Check the preview at the bottom of the panel
7. Click Save

6.2 Chart Types and Dimension / Measure Limits

Each chart type has specific constraints on the number of dimensions, measures, and available roles.

6.3 Dimension Roles

Each dimension has a role that determines its position in the chart.

Bar chart examples

Monthly sales stacked by category:

Dimension 1: year_month  (role: x)
Dimension 2: category    (role: color)
Measure:     amount      (aggregation: SUM)

→ X-axis shows months; each category is a differently colored stack.

Simple monthly sales (no series):

Dimension 1: year_month  (role: x)
Measure:     amount      (aggregation: SUM)

Line chart example

Monthly sales trend by sales rep:

Dimension 1: order_month  (role: x)
Dimension 2: assignee     (role: color)
Measure:     amount       (aggregation: SUM)

→ Each sales rep gets their own colored line.

Heatmap example

Inquiry count by day-of-week and hour:

Dimension 1: weekday  (role: x)
Dimension 2: hour     (role: y)
Measure:     count    (aggregation: COUNT)

Pivot table example — multiple measures

Sales cross-tab by region and category:

Dimension 1: region    (role: row)
Dimension 2: category  (role: column)
Measure 1:   amount    (aggregation: SUM, label: Revenue)
Measure 2:   qty       (aggregation: SUM, label: Units)

→ Column headers become a two-row layout: category values on top, measure names below.

KPI card example

Monthly target achievement:

Measure 1: amount  (aggregation: SUM, label: This Month)
Measure 2: target  (aggregation: MAX, label: Target)

6.4 Aggregation Functions

Using expr (Calculated Measures)

Select expr as the aggregation type. The UI changes as follows:

Example: Units processed per hour (qty / labor hours)

Measure 1: field=qty,           agg=sum,  fieldCode=qty_sum,    label=Total Qty
Measure 2: field=total_overtime, agg=sum, fieldCode=hours_sum,  label=Total Hours
Measure 3: agg=expr, output fieldCode=qty_per_hr, expression=qty_sum / hours_sum, label=Units/Hour

→ qty_per_hr shows SUM(qty) / SUM(total_overtime) per group

Note: The name used in the expression is the output field code you typed — not the label.
Expressions use the same engine as row filters and computed columns: + - * / and parentheses.
A measure row with an empty expression is silently skipped on save.

6.5 Chart Preview

A live preview using real data is shown at the bottom of the chart editor.
It updates whenever you change dimensions or measures.

• Bar / Line / Area / Scatter / Bubble / Heatmap / Pie / Donut: rendered with ECharts
• Pivot Table: shown as a cross-tabulation table
• Flat Table: shown as a record listing
• KPI Card: shown in KPI card style

7. Creating and Publishing Dashboards

Use the Dashboards tab in the plugin settings.

7.1 Create a Dashboard

1. Click + Add Dashboard
2. Enter a name and optional description
3. Select a theme (Light / Dark)
4. Click Save

7.2 Add Widgets

1. Click the ✏️ Edit button on a dashboard card to open the editor modal
2. Click + Chart — the "Select Chart" picker opens
3. Choose a chart and click Add to close the picker (important: close the picker first)
4. Use the ↑ / ↓ buttons to reorder widgets
5. Customize position with col, span, row, and height (h)
6. When all widgets are configured, click Save

Note: If the "Select Chart" picker is still open, the Save button is hidden behind it and cannot be clicked. Always close the picker (via Add or Cancel) before saving the dashboard.

Understanding the grid:

The layout uses a 12-column CSS Grid.

Examples:

Left half — bar chart:   col=1,  span=6
Right half — pie chart:  col=7,  span=6
Full width — pivot:      col=1,  span=12

7.3 Add Slicers

Slicers filter the entire dashboard. All charts in the dashboard respond simultaneously.

1. Click + Add Slicer
2. Choose a slicer type:

3. Specify the target data source and field
4. Place it on the grid

7.4 Publish a Dashboard

Newly created dashboards start in Draft state and are not visible in the viewer.

1. Click the Publish button on the dashboard card
2. The badge switches to "Published" — general users can now see the dashboard in the viewer
3. To hide it again, click Unpublish

Important: Clicking Save in the editor does not publish the dashboard. After saving, click Publish separately on the dashboard card.

7.5 Auto-Refresh Interval

Configure the cache refresh interval under the Settings tab → Auto Refresh:

7.6 Default Dashboard

Under the Settings tab → Default Dashboard, you can specify which dashboard opens automatically when the viewer starts.

Tip: If there is only one published dashboard, it opens automatically regardless of this setting.

8. Viewing Dashboards

8.1 Dashboard List

Opening the BI Hub app shows a list of all published dashboards.
Click any dashboard to open it.

Auto-open: If only one dashboard is published, or if a default dashboard is configured in the Settings tab, the dashboard opens directly — skipping the list screen.
To switch to a different dashboard, click the ← Dashboard List button.

8.2 Filtering with Slicers

Changing a slicer value instantly updates all charts in the dashboard.
No extra API calls are made — filtering runs on the in-memory cached data.

8.3 Per-Chart Filters

Click the ▽ icon in the top-right corner of a chart to open its filter panel.
Add conditions and click Apply. Filters reset when the page is closed.

8.4 Fullscreen Mode

Click the fullscreen button (⛶) in the dashboard toolbar to expand the dashboard to full screen.
Press Esc or click the button again to return to normal view.

8.5 Manual Data Refresh

Click the ⋮ menu in the top-right corner of a chart → Refresh Data to clear the cache and fetch the latest data.

8.6 Bookmarking a Dashboard

Individual dashboards can be bookmarked directly:

https://your-domain.cybozu.com/k/{appId}/#dashboard/{dashboardId}

9. Export (PNG / PDF)

9.1 Export an Individual Chart

From the ⋮ menu in the chart's top-right corner:

• Save as PNG: Downloads the chart as an image
• Save as PDF: Downloads the chart as a single-page PDF

9.2 Export the Entire Dashboard

From the dashboard toolbar's Export button:

• Export as PNG: Downloads the full dashboard as an image
• Export as PDF: Downloads the full dashboard as a PDF

Note: Animations are paused during export. Large dashboards may take a few seconds to process.

10. FAQ & Troubleshooting

Q. The dashboard is not displayed

• Verify that a Custom View is configured in the BI Hub app
• Verify that the custom view is set as the default view
• Verify that the custom view HTML contains <div id="bi-viewer-placeholder"></div>
• Verify that the plugin is installed in the BI Hub app

Q. A chart shows a "Permission Error"

• The current user does not have access to the app used as a data source
• Review and update the app's permission settings in kintone

Q. Data appears outdated

• Open the ⋮ menu on the chart → Refresh Data
• Or shorten the auto-refresh interval in dashboard settings

Q. Performance is slow with large datasets

• Add filter conditions to data sources to reduce the number of records fetched
• For JOINs, aim to keep both tables under 20,000 records
• Use a longer cache TTL (1 hour or more) to reduce API call frequency
• Use a virtual table row filter to exclude unnecessary records

Q. Exported images look broken

• Make sure charts have finished rendering before exporting
• Update your browser to the latest version of Google Chrome

Q. Can I use AND / OR in the row filter?

• Yes. Use the AND / OR keywords (case-insensitive) to write compound conditions.
• Example: amount > 10000 AND status == "Closed"
• Example: (type == "A" OR type == "B") AND amount > 0

Q. DISTINCT isn't removing duplicates as expected

• Make sure DISTINCT ON is enabled and the correct key fields are checked
• With no keys checked, only fully identical rows are removed
• Check only the fields that define "uniqueness" for your use case, and leave the rest unchecked

Q. Can I use multiple measures in a pivot table?

• Yes. Add as many measures as needed
• With multiple measures, the column header becomes two rows: the column dimension values on top, measure names below
• Totals are computed correctly per measure (AVG re-aggregates from raw data, not by averaging cell values)

Q. The "Add Dimension" button is grayed out

• Each chart type has a maximum number of dimensions
• Pie and donut charts allow only 1 dimension and 1 measure
• Bar, line, area, scatter, and bubble charts allow up to 2 dimensions
• Delete an existing dimension first, or switch to a chart type that supports more dimensions