• 1: Application Script
• 2: Tabular Report
• 3: Summary Report
• 4: Pivot Report
• 5: Number Report
• 6: Pie Chart
• 7: Column Chart
• 8: Application
• 9: Application Form
• 10: Bar Chart
• 11: Category
• 12: Form Library
• 13: Navigation Features
• 14: Parameters
• 15: Table Editor
• 16: YAML Script
• 17: Line Chart
• 18: Scatter Plot
• 19: Area Chart
• 20: Bar Line Chart
• 21: Word Cloud
• 22: Funnel widget
• 23: Treemap chart
• 24: Radar Chart
• 25: Gauge widget
• 26: Google Map
• 27: Query Snapshot
• 28: Calendar application
• 29: SQL Edit Application
• 30: HTML Report
• 31: PHP Application
• 32: Chain Application
• 33: Story report
• 34: Column based Dashboard Application
• 35: Gallery application
• 36: Predefined Script Application
• 37: Query Application

The Application builder workspace is where you build new applications and modify existing applications.

With the easiness Drag & Drop interface, you can build tabular report, summary reports, line chart, pie chart and many other application types in just some mouse clicks.

This section will show you how easy it is to build your own queries and charts using our intuitive drag & drop interface.

Basic Information

• Connection:
  The database connection that the application used. All applications require a valid database connection.

• Name:
  The application name, which is displayed in the main menu.

• New:
  Click the “New” button, redirect to “Setup new database connection” page.

Publish

• Save Draft:
  Save this application as a draft, draft application will not be shown in the left menu and can not be used in dashboard applications.

• Preview:
  Preview the current application.

• Publish:
  After you fixed all application issues, click the “Publish” button to set the application to published status. Published applications will be shown in the main menu.

• Revision

  DbFace will save all your application changes as revision, if you made a wrong change, and want to revert to the latest changes, click “Revision” button to open the change logs, find the right revision, and click the “Apply” button.

  

Form Builder

DbFace Form builder helps you generate user interface that produce query parameters. You can attach one form to any DbFace applications, including dashboard and storyboard applications.

To learn how to make a DbFace form, please follow the Form Builder section.

Visualization

DbFace provides many application types and counting. DbFace provides HTML report and PHP application, that you can define your own application templates.

• Visualization Type:
  Tap the corresponding application type button, the builder will be changed to corresponding application builder interface.

• SQL Mode Checkbox:
  SQL mode allows you to write custom SQL against your database. SQL Mode can be useful for complex queries that aren’t feasible in Drag & Drop Mode due to complex joins, subqueries, etc.

If you want to switch to SQL mode, click to check the “SQL Mode” checkbox, then you can write your SQL query in the SQL script editor.

Data Source

Select a table from the list, you can also join multiple tables to make a more complex data source.

Data source section only available in Drag & Drop mode, if you switch to Script mode, this section will be hidden.

Drag-drop Report Builder

Application Script

If you want to build application from scripts, Check the “Scripts” checkbox at the top corner of “Visualization”.

Additional Parameters

Additional Parameters option allow us to provide additional variables to current application script by an ini file.

Click the Additional Parameters button, DbFace will open an ini file editor, we input

; Additional variables for this application script
event_token = iap_purchased
package_name = com.flamingo.games

Now we can use event_token and package_name in the application script

select created_at_date, sum(revenue) from event where event_token='{$event_token}' 
and package_name='{$package_name}' group by created_at_date

Options

• Title:
  The title will be displayed as application title. This is optional, if not defined, the application name will be used.

• Description:
  The application description, describe the application more detail. This is optional.

• Custom Css:
  You can add a CSS style to the application, You can define css class should be defined in Customization section.

• Script:
  Script allow you to add your own code to DbFace application. The javascript will run when application opened. !!! Tip Do not use the script tag in the Script text area; just enter your JavaScript.

• ** Chart Options:**
  For chart report, you can define the chart height, the default value is 300px.

• ** Drilled In:**
  For chart report and number report, you can define the application drilled in application. Only sub application can be drilled in.

• Confirm Message：
  Confirm Message only available for SQL Edit Application. It will be shown before the actural queries executed.

Category

The DbFace applications are organized by categories. If no category set, the Default category will be used.

** Sub Application** category is a special category. Sub-applications can not be opened directly, but you can embed them in container applications(dashboard, story, gallery).

Permissions

Application user permissions section displays all sub-accounts under your account (developer accounts have permissions to all applications).

HTML Report Builder

HTML Report Builder provides a flexible way to make HTML report template as a DbFace application. You can embed Global parameters or Form variables in the HTML report template.

PHP Application Builder

PHP Application only available on Enterprise or On-premise installation.

If the default report types not work for you. You can define your own reports by PHP coding. DbFace provides a raw PHP online editor, you can write your own PHP code there.

Dashboard Builder

Dashboard application allows you to have a instant view on your selected data. You can also make dashboard application using the Gallery Builder.

Story Builder

Story report allow you navigate DbFace applications page by page.

Gallery Builder

Gallery builder provides a flexible way to build gallery pages and dashboard applications.

Free Form Application Builder

Freeform display application is an advanced dashboard application type, Freeform display allows you combine applications, widgets(Text, Web page, Image) into one page, and combine pages in tabs.

Application comments

Comments are notes you attach to dbface applications that can be viewed by anyone who has access to the application.

Comments you’ve written in the past can be edited or deleted at any time.

1 - Application Script

DbFace script is purpose-built query language based on SQL, powered by smarty template engine (with YAML front-matter supported).

---
event_token: 'iap_purchased'
date: '2020-10-06'
---
select count() from event where event_token='{$event_token}' and created_at_date = '{$date}';
sql-query-2;
sql-query-3

DbFace will simply split the queries by ;, and run theses queries one by one, and combine (join) all the result set into one result set.

If the big SQL query contains ;, and you want to force DbFace run the query by one batch, please add {config-one-query} tag, or use one_query front matter.

---
event_token: 'iap_purchased'
date: '2020-10-06'
one_query: true
---
{config-one-query}
select count() from event where event_token='{$event_token}' and created_at_date = '{$date}';
sql-query-2;
sql-query-3

Script Features

• Accept GET, POST, SESSION parameters as variables
• Accept YAML front-matter
• Auto join multiple scripts query result, and combine them as one result

Using smarty template engine to extend SQL script achieves the function that user form variables can be dynamically inserted in SQL scripts and the scripts can be dynamically rewritten according to the input.

Before all the scripts submitted to the database, all the variables will be replaced with the value of the current form.

Form variable value ​​can be applied to the script.

For example, a tabular report query application script:

select OrderData, Status, ColumnName from Orders where Status = '{$status}'

Considering a form that has a form field named “status”, if user input “Shipped”, the final query should be

select OrderData, Status, ColumnName from Orders where Status = 'Shipped'

The final statement may vary depending on the the choice of different data sources.

{$status}

For example:

About advanced usage of the script, please refer to Smarty template.

Form script

Form variables used in the script

Depending on the control, form controls may output two types of data: Single value and a value list.

• Single value

Single Line, Multi Line, Drop down, and Radio Group submit only one value.

If it’s single value, please insert it into the scripts in the form** {$Field Name}** . Considering a Single Line named Status, you can insert it into the filter condition or the scripts in the form {$status}. Such as:

select orderDate, status where status = '{$status}'

Please note: when this variable is included in the script, you need to add quotes. When parsing script, Dbface will not automatically determine the type of the field.

• Multiple value

Checkbox and Multi Select will submit multiple value.

Multi-value control used in the filter condition is the same with Single value control, meaning that: a match to meet the conditons.

Multi-value control used in script mode is different from Single value control, such as the search condition.

... Where status = '{$status}'

Dbface will not retrieve the data, because {$status} is an array so that it will be forced to be converted to a string ‘Array’. The correct usage should be:

... Where status in ({$status|join})

{$status | join} Syntax will automatically expand and split this value.

Data Source script

Considering some form controls having limited value, such as DropDown, Multi Select, Checkbox, RadioGroup, you can use data source scripts to specify their limited value. When executing, DbFace will dynamically execute data source script to get the value of form controls.

Data source script can output one or two fields, the remaining fields will be ignored.

If there are two fields: the contents of the first field as submitted value ​​(data is applied in the script), the second field as the displayed value ​​(data is only for display).

As

select value from table where ...

Or

select key, value from table where ...

To use the data source scripts, click the “Edit” button in Forms controls having limited value. Click the “Script” button in the edit box of pop-up window field, the input textbox “Data source script” will be displayed, where you can enter the data source script and save it.

** Do not forget to use the data source script generator, click on the generator icon behind the “script” button, all tables of current links and their fields will pop up. Check the required fields to generate queries.**

Form field onchange script

After editing the form field, you can set a query script. When the input is completed and the focus is lost, the script will automatically execute and fetch data form the final database. The data obtained will be mapped by name to the current form control one by one.

To change the script, click on the “Edit” button, the field property edit box pops up, click on the “Onchange Scripts” button, enter the script in the expanded textbox, then save it.

You can use the current form variable to change form field script.

##　Form loading script

Users can specify a loading script (query) for user form to initialize the form data value.

After the application opened, DbFace will detect whether the current form has the loading script, if so, Dbface will automatically execute this loading script and map the query result to the current form one by one. If the query returns multiple data, Dbface will provide the “Browse” button, then you can select the required data for the form control value.

To create a form loading script, click the Form Editor “Properties” button, then the property editbox pops up, where you can enter the loading script and save it.

Tips

Script Plugins

DbFace provides many script plugins that help you to build complex application source.

config

You can use config function to assign variables to current application script.

{config key1=value1 key2=value2}

Now, you can use {$key1}, {$key2} in the application script.

For loading multiple fixed variables, you can also create ini file

{config file="your-ini-file-which-located-in-dbface-system-folder"}

DbFace will try to load the ini file and assigned all variables in the ini file. To create ini file, please open Settings menu and click Cloud Code link, then click “Start Development Workspace”, then click “New File” in system folder.

create_variable

As you already known, you can create Global Variables in Parameters section. All available variables will be assigned in every appliction script.

You can also create local variable that only available in current application script.

{create_variables name="local-variable-name" value="value" sql=""  func="" url=""}

value, sql, func and url should be (and only one) assigned.

• value: assigned fixed value to {$local-variable-name}

• sql: DbFace wil try to use the sql to query application database, and assigne the result set to {$local-variable-name}

• func: DbFace will execute the function and assign the function result to {$local-variable-name}

• url: DbFace will try to call the URL and assign the http response body to {$local-variable-name}

snippet

snippet plugin helps you refer tagged application script into current application script.

{snippet name='your-tagged-sql-query'}

purge_cache

Purge DbFace cache data.

{purge_cache}

Front-matter

Front matter allows you to keep metadata attached to an application. i.e., embedded inside a variable. DbFace application script can contains a YAML front matter block.

---
event_token: 'iap_purchased'
---
select count() from event where event_token='{$event_token}'

This script will be compiled to

select count() from event where event_token='iap_purchased'

You can also define yaml files in user directory, and import them into current application script.

import: config/my-yaml-file.yaml

or

import:
  - config/my-yaml-file1.yaml
  - config/my-yaml-file2.yaml

DbFace will try to load these yaml files and merge variables into current application script.

DbFace supports four formats for front matter, each with their own identifying tokens.

TOML identified by opening and closing +++.

YAML identified by opening and closing —.

Quick Filter Options

Quick Filter Options is an inline form attached to the DbFace application.

You can define quick filter options in Front-matter, DbFace support select, date and daterange as quick filter options.

quick_filters: 
  country:
    type: select (date or daterange)
    multiple: true (only valid for select)
    selected: usa 
    list: (select control value list)
      - USA (or usa:USA)
      - UK
      - CN
      - RU
  search_date:
    type: date
    value: today

We can use quick filter options in current application script by smarty manner.

We can also use !script tag to make DbFace query the select value

quick_filters: 
  country:
    type: select (date or daterange)
    multiple: true (only valid for select)
    selected: usa 
    list: !script select distinct country_name from country
  search_date:
    type: date
    value: today

DbFace will query the target database

select distinct country_name from country

and use the result to render the country drop list.

---
quick_filters: 
  search_date:
    type: date
    value: today
---
WITH (
  select sum(revenue) from event where package_name='com.test.package' and event_token='iap_purchased' and 
  created_at_date='{$search_date}'
) as total_payment

select roleId,count() as "Order Numbers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test.package' and event_token='iap_purchased' and created_at_date='{$search_date}'
group by roleId

---
quick_filters: 
  search_date:
    type: daterange
    value: last7days
---
WITH (
  select sum(revenue) from event where package_name='com.test.package' and event_token='iap_purchased' and 
  created_at_date>='{$search_date|from}' and created_at_date<='{$search_date|to}'
) as total_payment

select roleId,count() as "Order Numbers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test.package' and event_token='iap_purchased' and created_at_date='{$search_date}'
group by roleId

Chart Total Widget Script

We can attach a total widget at DbFace chart reports. If we do not specify Total Widget Script, DbFace will collect all series data and caculate the summary value for this widget. If the total widget script specified, DbFace will use the script to query series summary value.

Chart Detail Table

At Chart Options settings, check “Display Detail Table”, DbFace will display the original chart data source using tabular.

Table Calculated Fields

We can append new fields for table report using Caculated Fields in application script.

---
calculated_fields:
  ARPPU: row['Payment'] / row['Payers']
---

WITH (
  select sum(revenue) from event where package_name='com.flamingogames.gok' and event_token='iap_purchased' and 
  created_at_date='{$date_range|from}'
) as total_payment

select visitParamExtractString(params, 'sid') as sid,count() as "Order Numbers", count(distinct roleId) as "Payers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Total Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test' and event_token='iap_purchased' and created_at_date='{$date_range|from}'
group by sid

The table will append a caculated ARPPU field, that value

row['Payment'] / row['Payers']

We can use row, data, and row_index in the expression

• row: current row data
• data: the whole result set
• row_index: current row index (start with 0)

Table Sort Fields

We can use sort_fields in script front matter to re-sort the result table columns.

---
calculated_fields:
  ARPPU: row['Payment'] / row['Payers']

sort_fields: ['sid', 'Payers', 'Order Numbers', 'Payment', 'Total Revenue', 'Percent', 'ARPPU']
---

WITH (
  select sum(revenue) from event where package_name='com.flamingogames.gok' and event_token='iap_purchased' and 
  created_at_date='{$date_range|from}'
) as total_payment

select visitParamExtractString(params, 'sid') as sid,count() as "Order Numbers", count(distinct roleId) as "Payers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Total Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test' and event_token='iap_purchased' and created_at_date='{$date_range|from}'
group by sid

The result table will display fields using the sort_fields in front matter.

2 - Tabular Report

Tabular Report (Table View Report) displays the data in rows and columns, which is the easiest way to show the data. We offer many different options to customize how they appear.

Use a tabular report when you want to see the data in rows and columns.

You can also attach a Form to the tabular report.

Table format accepted

The tabular report accepts an unlimited number of measures and dimensions.No formatting restrictions.

STEP 1: choose “Tabular Report” in “Visualization” section

STEP 2: Select or Join tables to make data sources

Select a table or join tables to make tabular data source.

STEP 3: Drag Select Columns from Field List

• Select Columns:
  The tabular report header titles
• Order Columns:
  The default order conditions, this field is optional.
• Label Column:
  You can click the field to label the column.

• Make Custom Column
  Click the dropdown of a column, and select “Custom” to make custom column.

STEP 4: Apply filter conditions

You can also use form variables or global parameters in filter condition, e.g. if the tabular has a form that contains a input field that named status, you can use {$status} value in the filter condition.

STEP 5: Preview the tabular report

Click “Preview” button to preview the tabular report, if there are errors, you need to correct them, and preview again.

STEP 6: Save the tabular report

Click “Publish” button to publish the tabular report. Click “Draft” button to save the current tabular report and you can edit later.

S Mode

Script mode provides you more flexible way to build tabular report.

Check the “Script Mode” checkbox to switch to Code mode to build tabular report. DbFace will try to generate correct SQL query(or JSON query for Mongodb) from the Report builder, but you still need to check the SQL query.

Here is a sample tabular report script:

SELECT
  `licensecode`,
  `clientcode`,
  `email`,
  `version`,
  `ip`,
  `httprefer`,
  min(`date`) as FirstVisit,
  max(`date`) as lastvisit
FROM
  `dbfacephp_license`
WHERE
  licensecode != ''
GROUP BY
  licensecode

!!! tip “Tips & Tricks” You can find more tips and tricks for tabular report at our knowledge base.

Tabular Options

DbFace provides several options to help you customize the table report appearance.

Table Header

Optional Scripts

Series Options

Drilled In Applications

Table Row Action

3 - Summary Report

Summary reports are similar to tabular reports, but also allow users to group rows of data,

Summary Report adds up the corresponding cell data of multiple reports which have the same structure and different data.

How to build Summary Report?

Like the tabular report, you can also build a summary report in Drag & Drop mode or Script mode.

Drag & Drop mode

STEP 1: Select “Summary Report” visualization type

STEP 2: Deside the Group and Summarized fields

You can summarized multiple fields.

STEP 3: Label fields and apply filter conditions

Choose appropriate label for fields, these label names will show as summary report header title.

STEP 4: Preview, correct issues and publish Summary Report

Click the “Preview” button to see what the summary report looks.

Script mode

Script mode provides a more flexible and advanced way to build summary report.

Check the “Script” checkbox to switch to Script mode, DbFace will try to generate SQL query that you already build, but you still need to correct it if contains issues.

Here is a sample summary report script:

SELECT
  YEAR(requiredDate) as `Year`,
  COUNT(orderNumber) as `Order Number`,
  COUNT(DISTINCT customerNumber) as `Customer Number`
FROM `orders`
GROUP BY `Year`

4 - Pivot Report

Many results are presented in tables that can be pivoted interactively. That is, you can rearrange the rows, columns, and layers.

Pivot reports(Crosstab reports) provide another perspective on the same data you see in the list report.

• Columns
• Rows
• Data

5 - Number Report

DbFace provides “Number Report” to highlight a single data value, like total payment of all the orders, total customers in a quarter.

Use a Number Report visualization when you want to see the total for a measure or the count for a categorical column.

Table format accepted

Number report supports One column with one row, other rows and columns of the result set will be ignored.

Number report also supports Two columns with one row, it allows you to compare your Single Value against another value. It adds an up or down arrow next to the single value and shows the percent change, based on the comparison value.

How to build Number Report?

Interactive Mode

STEP 1: select “Number Report” visualization type

STEP 2: Choose field you want to highlight

Drag filed that you want to highlight to the “Select Columns” field. Only the first column will be used to summarized, other columns you dropped will be ignored.

Click the caret button, you can apply “Count”, “Sum”, “Avg” function to the column.

Click the selected column to change the label name of the field.

STEP 3: Preview to test and save.

After you finished your application, you can click the “Preview” button to see what the number report looks, correct any issues, and publish the number report.

SQL Mode

You can also build number report from SQL Query directly.

Click the “Script” checkbox at the top-right corner of “Visualization” section to switch to Script mode. DbFace will try to generate SQL query that you already built. But you might also need to correct issues.

Option: Script

For Number Report, you can define

function __format_label(label) {
  var newlabel = label;
  // TODO: apply format to the label
  return newlabel;
}

and

function __format_value(value) {
  var newvalue = value;
  // TODO: apply format to the value
  return newvalue;
}

function in the Script code area to format label and value of the number report.

Here is an example that bold the label and makes the value color to blue:

function __format_label(label) {
    return "<b style='color:red'>" + label + "</b>";
}

function __format_value(value) {
    return "<span style='color:blue'>" + value+"</span>";
}

6 - Pie Chart

A pie chart (or a circle chart) is a circular statistical graphic, which is divided into sectors to illustrate numerical proportion. Displays tooltips when hovering over slices

Data format

Pie chart accepts two or more columns. The second through the last column must be numeric.

7 - Column Chart

A column chart is a graphic representation of data. Column charts display vertical bars going across the chart horizontally, with the values axis being displayed on the left side of the chart.

Data format

Bar chart accepts two or more columns. The second through the last column must be numeric.

8 - Application

The DbFace application is an available execution unit, could be a database query / operation, a form report, a summary report, a line chart,or a pie chart,etc.

DbFace applications include a user input form (optional), one or more execution scripts. When users enter data, click the “submit” button, DbFace submits them to the target database to perform, then outputs corresponding data (such as data tables, summary tables, pie charts, line graph, etc.). Dashboard and Story are special types of application. They can display more than one applications in one page. DbFace supports the following application types:

Login as administrator or developer , the browser will display the application list, where you can edit or delete applications.

Edit application

In the application list, select the application you want to edit, click the “Edit” button to enter the edit page. When editing the application, you aren’t allowed to modify the data source. How to edit application, please refer to “Application Builder” above.

Clone application

Click the “Clone” button to make a copy of the selected application.

Delete application

In the application list, select the application you want to delete, click the “delete” button, after confirmation the application will be deleted.

Share application

In the dashboard, tap the “Embed” button, DbFace will open the “Widgetize the application in your websites in an IFRAME” dialog, in this dialog, you can get the public URL of this application, please check the “Embed” button, copy the Direct Link or Embed Iframe code and click the “Confirm” button the save the settings.

Do not forget check the “Embed” checkbox, otherwise the Direct Link will not become active.

9 - Application Form

DbFace support to specify a form for the application to accept user’s input.

You can build DbFace form from Design Mode or Code Mode.

Drag & Drop Builder

DbFace has two major types of form controls: layout control and form control.

All form controls must be placed in layout controls.

Form fields

Single Line

Single Line allows the user to enter a single line of string data, the user can specify the data type in the property editor. If you specify a data type, before form submission, Dbface will check the input items whether the input items are the specified type, if not, the form will not be submitted.

Date Range

You can input 2020-05-01 ~ 2020-06-30 to initialize the date range control. As default, Date Range control will use the last 30 days.

Multi Line

Multi Line allows the user to enter multiple lines of text.

Drop Down

Drop Down allows the user to select a data in a value list. Drop-down box needs to be specified the data source.

Radio Group

Radio Group displays a list of all the values, and allows the user to select one value as the form data to submit.

Checkbox

Checkbox displays a list of all the values, and allows the user to check multiple data to submit.

Multi Select

Multi Select displays a list of all the values, and allows the user to select multiple values to ??submit.

Static Label

Static label displays static data that can not be edited.

Text

The text control can be used as the form description. DbFace integrates CKEditor as rich text editor.

Form field Properties

To edit the form field properties, click on the “Edit” button, and open the property editor.

Label

Labels ar text items in front of form field, which can be used for user to identify this form field.

Name

Form field script name can be used as variables in application scripts by Smarty manner. Such as: {$status}

Required

This form field must be required. Before submitting, Dbface will check whether the value of this field is empty, if it is, the form will not be allowed to submit.

Disable

This item can not be edited. Users can not edit this item.

Data Sources

Only limited value form fields (such as Drop Down, Multi Select, Checkbox, etc.) have datasource options. DbFace supports two kinds of ways to use the datasource: Enter value directly and mapping script. (“Unrestricted” is generally used to preview the script, in fact, generally do not use this.)

Enter value directly:

the user input datasource directly, one value per line. The value supports “key: value” division format. If this format is detected, Dbface will automatically take the previous value as the form value,the succeeding value as data displayed in the form. Value as a form before submitting value, the latter value data as displayed in the form inside.

!!! Tip Do not forget to use the data picker to help choose the data from the database.

Mapping Script:

Users can use a query to set the data source.

Onchange Script

Onchange script is general SQL query script.

When the user completes the input and focus are lost, the scripts will be executed and the result will be mapped to the current form control.

Build Form from Code

If you need to build a form where Checkbox C is revealed If Checkbox B is Checked “No” or any combination of javascript actions and behaviors normally used in form building to make the Form “Smart”.

Diving into source code, you can build logic embedded into options & choices for Forms.

10 - Bar Chart

A bar chart or bar graph is a chart that presents grouped data with rectangular bars with lengths proportional to the values that they represent.

Like all DbFace charts, bar charts display tooltips when the user hovers over the data.

Data format

Bar chart accepts two or more columns. The second through the last column must be numeric.

11 - Category

DbFace organizes all the applications by categories. The default category name is “Default”. All the applications that do not have a category are automatically attributed to the default category.

Create category

Log in as Administrator, find “Category” in the Dashboard. You will find all categories in “Category” section.

On this page, enter a category name, cofirm “Create a new directory” button to create a category. Category names are not allowed to be duplicate.

For convenience, when creating or editing an application, you can also make a new category name for it.

Edit category

In the category list, click the category name, a small window will pop up, where you can modify the category name. Enter a new category name, then confirm.

You can also specify or update the icon of the cateogry.

Delete category

To delete a category, click the “Delete” button in the category list. Don’t worry, delete the category does not lead to delete the applications in the category. All applications in the deleted category will be automatically moved to the default category(Application).

Edit category permission

DbFace categories are virtual folders that organize applications. Edit category permission does mean bulk edit application permissions that in the category.

Tap the “Edit user permissions” button to open a dialog that list all user accounts under your account.

12 - Form Library

Form library tool helps you fetch other application forms into the current application, so you do not need to build a form again and again.

• Open Form Library
  Click the “Form Library” button to open Form Library dialog. Form Library dialog will display all forms you have created (draft or published).

• Preview Form
  Click the Eye button to preview the form

• Choose Form
  Click the Correct button to fetch the form into current application

The Form library only displays forms that in the same database connection.

13 - Navigation Features

14 - Parameters

Parameters are used to store values that can be obtained in applications.

Parameters (Variables) are reusable pieces of information that can be created once and used in multiple places, such as filter conditions, SQL queries, and HTML applications. DbFace store includes a large number of predefined attributes, and also gives you the ability to create your own custom variables.

You can define fixed or dynamic variable(by SQL query or Code) in this section, all variables can be used in all applications.

If you bind a connection to the parameters, these parameters will be only available in applications that using the connection.

Define Global parameters

Tap the “Parameters” menu to open the “Global Parameters” page.

Create new parameter

Tap “Create new parameter” button to open the “Create new parameter” dialog. DbFace supports fixed and dynamic parameters.

Fixed Value

Dynamic Value

Please notice that DbFace only supports single value. DbFace will execute the SQL query, and make the dynamic value equal the first row and first field value.

If SQL query only return 1 field and 1 row, the column data will treated as the value, other then, the resultset associate array will be the dynamic value.

• TTL
  You can specified a TTL (time to live) value for dynamic value, DbFace will refresh the value when the time expired automatically.

If you want to force build the dynamic value, you can click the “Refresh” button at the parameters list page.

Public global parameter

You can access “Public Global Parameter” in external webpages without having to log in DbFace. Each public paramater has a public URL. i.e.

http://localhost/dbface/user/jsding/value/now

You can load the value in your webpage:

$('#span_now').load("http://localhost/dbface/user/jsding/value/now");

Edit Global Parameter

Tap the “Edit” button to open the “Update Parameter” dialog. In the dialog, you can edit the parameter settings.

Remove Global Parameter

Tap the “Delete” button to remove the global parameters.

Refresh Dynamic Parameter Value

You can tap the “Refresh” button to force DbFace to refresh dynamic parameter value. Since fixed value never changes, this function is not available for “Fixed Parameter”.

Use Global parameters

You use “Global Parameter” in DbFace applications just like the form field.

Use dynamic array value

Predefined Variables

DbFace also store several predefined variables that you can use directly.

You can also predefined variables on on-premises version, open config/config.inc.php and edit predefined_variables entry

$config['predefined_variables'] = array(
  'status_deleted' => 100
);

You might also want to define dynamic predefined variables in hooks.php, find api_get_predefined_variables function in config/hooks.php

数据字典

15 - Table Editor

!!! note “Depreciate” We generated Table Editor Applications for every tables automatically when the database connected. So you do not need create table editor application anymore.

Table editor provides the functions such as CRUD (creating, updating, deleting), filtering, paging the original database table, etc.

DbFace built-in table editor is powerful:

• Navigate data by page
• Sort by field(multi-field)
• Delete the line
• Open line editor
• Insert line: the user selects a single row of data, then open the line editor, Dbface will copy the seclected row data to the editor automatically. Very convenient.
• Filter data by field. Click the “Filter” button to open the filter condition editor.

DbFace will generate Table Editor Application for every tables in the database connection. Please refer Data Module section to get more information about Table Editor Application

16 - YAML Script

To build complex dbface applications, you might need to do not only one query to the target databases, some of them to fetch values, and use this values in the result application script.

---
vars:
    var1: !script execute-sql-query-to-get-value
    var2: !func execute-php-function-to-get-value
    var3: !url http://your-rest-url-to-fetch-this-value
    var4: !var var
    var5: !get get-value
    var6: !post post-value
    var7: !input get-or-post-value
    var8: direct-value
process: your-php-process-functions
app: script

---
data: php_function_name

17 - Line Chart

A line chart or line graph is a type of chart which displays information as a series of data points called ‘markers’ connected by straight line segments.

Here is a video that showing you how to build a chart report:

Data format

Line chart accepts two or more columns. The second through the last column must be numeric.

18 - Scatter Plot

A scatter plot, scatterplot, or scattergraph is a type of mathematical diagram using cartesian coordinates to display values for two variables for a set of data.

Data format

Scatter plot chart accepts two or more columns. The second through the last column must be numeric.

19 - Area Chart

An area chart or area graph displays graphically quantitive data. It is based on the line chart. The area between axis and line are commonly emphasized with colors, textures and hatchings.

Data format

Area chart accepts two or more columns. The second through the last column must be numeric.

20 - Bar Line Chart

Bar Line Charts use a bar and a line to visualize a data set with both a continuous and a categorical metric.

Data format

Bar line accepts three or more columns. The second through the last column must be numeric. The last column will be considered as line chart.

21 - Word Cloud

Use a word cloud visualization when you want to see a text-based visualization of a given column. The text height represents the scale. The name itself is the different members of the column.

Data format

Word Cloud accepts two columns with multiple rows. The second column must be numeric.

22 - Funnel widget

Funnel charts are a type of chart, often used to represent stages in a sales process and show the amount of potential revenue for each stage. This type of chart can also be useful in identifying potential problem areas in an organization’s sales processes. A funnel chart is similar to a stacked percent bar chart.

Data format

Funnel widget accepts two columns. The second column must be numeric.

23 - Treemap chart

Use a treemap visualization to identify patterns and exceptions in a large, complex data asset.

Data format

Treemap chart accepts two columns with multiple rows. The second column must be numeric.

24 - Radar Chart

As known as: Spider Chart, Web Chart, Polar Chart, Star Plots.

Radar Charts are a way of comparing multiple quantitative variables. This makes them useful for seeing which variables have similar values or if there are any outliers amongst each variable. Radar Charts are also useful for seeing which variables are scoring high or low within a dataset, making them ideal for displaying performance.

Each variable is provided an axis that starts from the center. All axes are arranged radially, with equal distances between each other, while maintaining the same scale between all axes. Grid lines that connect from axis-to-axis are often used as a guide. Each variable value is plotted along its individual axis and all the variables in a dataset and connected together to form a polygon.

25 - Gauge widget

A Gauge widget is a simple status indicator that displays a needle that moves within a range of numbers displayed around its edge.

Data format

Gauge widget supports One column with one row, other rows and columns of the result set will be ignored.

26 - Google Map

Google Map report allows you to create markers on google map report.

Data format

Google Map report accepts two or more columns.

DbFace uses Google Maps Geocoding API service to decode the address field to real GEO locations. If the query result contains “lat” and “lng” fields, DbFace will use these two fields as geo location for each data.

You can also use JSON string in address field:

or

How to build a Google Map Report?

Follow the steps below to create a Google Map report.

• Click the “Create New Application in Admin Console
  Login in DbFace with Administrator or Developer accounts. and click the “Create New Report” button in the application list section.

• Select “Google Map Report”
  In the report builder page, find the “Visualization” section, and click the “Google Map” icon to start building Google Map report.

• Select Google Map Report Type
  Drag “Address” Fields Drag the map address fields to “Address” fields, you can drag 1 or more fields to create detail map address Drag “Markers” Fields Address and Markers fields for Google Map Report in DbFacePHP The settings will create the following Google Map Report:

You can also build Google Map Report in SQL Query Mode.

The first column will treat as the “Address” field and other columns treat as markers fields.

Google Map Options

__get_map_options
DbFace uses this function to override the default Google Map Options.

Please refer Google Map Options to get information about Google Map Options.

Here is an example that configure the initial Map zoom level to 16.

function __get_map_options() {
  var result = {zoom: 16};
  return result;
}

get_marker_icon(address, content)
Define this function to create your own markers.

function get_marker_icon(address, content) {
  return "http://maps.google.com/mapfiles/ms/icons/green-dot.png";
}

27 - Query Snapshot

What is Query Snapshot Application?

Query Snapshot Application helps you save current query resultset snapshot. You can view all snapshots of query result sets by date time.

You can monitor your Query result sets time by time using Query Snapshot Application.

• Flat List View
  

• Calendar View
  

!!! warning The snapshot size is limited to 1KB, please double check your requirement. If you want to use larger snapshot size, please contact us.

How to build a Query Snapshot Application?

This section will show you how to build a Query Snapshot Application to monitor your query result sets.

Step 1: Choose the “Query Snapshot Application”

Step 2: Build SQL Query

Build SQL Query using the Drag&Drop wizard

Write SQL Query directly

You can also build your SQL Query directly in Application Script Editor.

Step 3: Publish

Input the “Application Name”, and click “Publish” button to publish the application. Now, the new created Query Snapshop Application should be available on the dashboard page.

Using Query Snapshot Application

Capture a Snapshot

Tap the “Capture Snapshot” button to capture the result sets of the query.

Remove a Snapshot

Tap the “Delete” button to remove the snapshot.

Flat List View and Calendar View

You can view snapshots in flat list view and calendar view mode.

28 - Calendar application

Calendar application displays query data in a full calendar.

How to build Calendar Application?

You can create calendar application in Drag & Drop mode and Script mode.

STEP 1: select “Calendar Application” in Visualization section

Please click the “Calendar Application” button to open the Calendar Application Builder.

STEP 2: Choose a base date field.

Drag the date field from the left field list top the Date field. If your fields do not contain “Date” field, you can also format string field to “YYYY-mm-dd HH:ii:ss” to get correct result.

You can only drag one date column to the Date field.

STEP 3: Choose the data fields

Drag the data fields from the left field list.

Script mode

You can also build calendar applications in Script mode.

Tap the “Script” checkbox to switch to Script mode, DbFace will try to generate correct query you already built.

Your SQL query’s first column should be Date field or date formatted field.

e.g.

SELECT
  DATE_FORMAT(orderDate, '%Y-%m-%d') as `Date_of_orderDate`,
  `orderNumber`,
  `status`,
  `customerNumber`,
  `comments`
FROM
  `orders`

29 - SQL Edit Application

What is SQL Edit Application?

SQL Edit Application allows you execute one or more SQL queries one by one. These SQL queries usually used to update table data.

Typical SQL Edit Application are composed of

• User input form (optional)
• Confirm message
• SQL queries

When opened an SQL Edit application, DbFace displays the user input form. The user fills the form, click on “Submit” button, the confirmation message (input data can be contained) will be displayed. Click on the “Confirm” button, DbFace will execute the scripts line by line, and show the final execution result.

DbFace tries to execute all scripts in a transaction. Only all the scripts are executed, all the queries will be committed, otherwise the queries will all be rolled back. (The database should support transaction feature.)

How to build an SQL Edit Application?

This section will show you how to build an SQL Edit Application in DbFace.

Step1. Choose the “SQL Edit Application”

At the “Visualization” section, choose the “SQL Edit Application” type

Step2. Build a form

Build a Form, which allows application users input required information. Here, we build a form that contains a textbox named “licensecode”.

Step3. Write SQL Edit queries

Now, you can edit the SQL Edit Application queries, you can use the form fields by Smarty template manner.

Step4. Input Confirm Message

Enter a confirm message for the SQL Edit application, you can also use form fields’ variables by Smarty template manner.

Now, you have built an SQL Edit Application in DbFace.

30 - HTML Report

An HTML Report lets you create a custom report using HTML, inline CSS, and one or more series of data.

DbFace provides a full-featured HTML editor, users can edit the content on the fly. HTML also supports dynamic contents by Smarty template engine.

You can define variables in the “Parameters” section, and use these variables in HTML reports to build dynamic pages.

31 - PHP Application

If the other report types not work for you. You can define your own reports by PHP coding.

DbFace’s PHP development environment is based on CodeIgniter Framework, so you can benefit from all CodeIgniter features especially the Database Active Record.

You can get more information from CodeIgniter Documentation.

Build PHP Application

Sign in DbFace with Administrator or Developer account. Click the “PHP Application” button in the “Visualization” section. DbFace workspace will show you PHP code editor.

Input your PHP code, and press CTRL + Enter or click Preview button to preview the PHP application.

Controller-View Application

DbFace already integrates Smarty template engine.

Create smarty template

Tap Settings -> Cloud Code to enter cloud code page, click the “Create Template” button to create new smarty template:

Here, we build a simple sample Smarty template, we use [{}] as the variable delimiter:

Use smarty template in PHP Application

<?php
  $smarty->assign('variable', 'Hello world!');
  $smarty->display('sample.tpl');
?>

The application result:

32 - Chain Application

What is chain application?

DbFace is a general platform to visualize any data from any data source, including application data. Chain application allows you create json response in your existing application system (which might writen JAVA, Python, PHP, etc.) and visulize them in DbFace.

Chain Application Settings

At the Application Builder page, click the “Chain Application”, DbFace will display Chain Application Settings section:

• Application URL

DbFace will post (or get) application data from the Application URL. You can use http:// or https:// schema to get your remote application. You can also use file:// schema to get static files in DbFace public directory.

You can use variables in this area. You can define applicationURL variable in the Global Parameters, and input {$applicationURL}.

• Form Query

Additional form data.DbFace will post the form data to the Application URL. Global parameters are also accepted in this input field.

• Callback

If leave this setting empty, DbFace will try to show the application type depends the response data.

How to make chain application?

33 - Story report

Story report helps you navigate sub reports page by page smoothly via carousel component.

34 - Column based Dashboard Application

Dashboard application allows you to have an instant view on your selected data. Dashboards are a simple way to organize together and manage multiple charts that share the same underlying data.

Dashboard application can seamlessly integrate the above-mentioned three types of applications into a single page. DbFace provides full-featured layout editor that allows users to drag and drop existing applications to the layout.

DbFace uses a simple clean way to arrange the applications in a dashboard. It provides 9 kinds of layouts(and counting), you just need to choose one layout for your dashboard, and drag & drop reports in the dashboard to build new dashboard.

!!! Tip Gallery Builder also provides a flexible way to build dashboard applications.

To build a dashboard application:

• check “Dashboard Application” in the Visualization section

• Change dashboard layout

Tap the “Change dashboard layout” button to choose a appropriate layout template for your dashboard.

• Place reports into the dashboard

Tap the “Add a widget” button to explore all existing applications

Here is a sample dashboard:

35 - Gallery application

Gallery builder provides a flexible way to build gallery pages and dashboard applications.

Gallery application provides a fluid container that you can place applications, menu items in sections.

Gallery Application: Application Menu Links

Gallery Application: Dashboard

Gallery Builder

• 1. App/Link
  Displaying applications directly or displaying application menu link.
• 2. Change background Color
  Change the menu link background color, this option does not affected if displaying application directly
• 3. Choose application icon
  Change the application icon, only available displaying menu link.
• 4. Expand the size
  Expand the section size
• 5. Reduce the size
  Reduce the section width
• 6. Remove the application
  Remove the application from the container
• 7. Add new application
  Add new existing application into the container. The new placed application will append into the container.
• 8. Open the application in new page or dialog
  Click to swith the application opened style.

36 - Predefined Script Application

Build DbFace application by TOML scripts.

37 - Query Application

Query Application

Query Application is the most simple workflow to build application by SQL in DbFace. We type SQL and choose an application type, then all set down.