FAQ about Datasets

Custom SQL errors

If you encounter errors while executing or saving your custom SQL, please check for the following common issues.

1. Incorrect custom SQL syntax

Quick BI partially processes custom SQL before it is executed by the database. This preprocessing includes parsing placeholders, applying row limits with limit statements, and inserting SQL comments. Consequently, errors from the underlying database may occur if the custom SQL syntax is incorrect.

To troubleshoot custom SQL errors, try executing the SQL statement directly in the database to identify any syntax issues. You can review previously executed SQL statements in the "history".

2. Other statements written before the SELECT statement

To prevent performance issues due to large data volumes, Quick BI appends a 'limit 200' clause to custom SQL during execution and saving. Therefore, custom SQL must begin with a SELECT statement. HINT statements cannot be directly written before the SELECT statement, as this will result in errors.

To use HINT statements, please set them through the [Set HINT Statement] option provided.

For a list of data sources that support HINT statements, see: Data Source Function Item List.

3. Placeholder used without a default value

Quick BI's custom SQL allows for parameter passing via placeholders. When viewing reports, placeholders enable dynamic data analysis by passing values to the underlying SQL through query controls. For usage scenarios and methods, see the document: Placeholder.

A default value must be set for any placeholder used in the SELECT statement to avoid incomplete SQL and execution errors.

For instance, in the example below, if the placeholder $val{profit_range} lacks a default value, the "order level" cannot be calculated, leading to custom SQL errors. By assigning a default value of 100 to the placeholder, the SQL executes correctly and displays the data.

To prevent report errors due to unassigned placeholders, it is advisable to set a "global effective" default value for the placeholder after the SELECT statement, rather than a "dataset only" effective default value.

SELECT report_date, order_level, shipping_type,price,order_number,area,
   case when profit_amt< $val{profit_range} then'Loss' 
   when profit_amt> $val{profit_range} then'Profit'
   else 'Flat'
   end 'order_level'
from company_sales_record
where $expr{report_date :report_date}
and $expr{order_level :order_level}
and $expr{order_number :order_number}

4. Others

Standardizing the use of comments and aliases in custom SQL is recommended to avoid errors that may arise from special field names returned by the underlying database or placeholder parsing issues.

Also, it is unnecessary to add a semicolon ";" at the end of custom SQL.

Common causes of errors in calculated fields

1. Calculated fields reference physical fields. To convert field types, you must use the appropriate conversion function. Manually changing the field type does not alter the underlying physical field type, which can lead to errors due to type mismatches.

2. Dimension calculated fields cannot utilize aggregate functions such as SUM and AVG. To perform aggregation calculations, you should save them as measures.

3. Text-type fields are limited to COUNT and COUNTD for aggregation. They are incompatible with aggregate functions like SUM and MAX. Convert these fields to numeric types prior to performing aggregation calculations.

How to convert Unix timestamp type fields to standard date-time type fields?

How to set null or empty values displayed on the dashboard?

1. Configure the display style for empty values within the dataset.

2. Configure the display style for empty values on the dashboard itself.

How to achieve flexible calculations of proportions using parameter SQL?

Quick BI currently lacks native support for direct proportion calculations. However, you can utilize parameterized SQL to construct a dataset for this purpose.

Consider a table with the following structure: date, province, city, sales amount. To calculate the sales amount ratio by city within its respective province, you must: 1. aggregate by the city dimension; 2. allow selection of any date range and enable multi-selection of provinces and cities.

select a.city,sum(fenzi)/sum(fenmu) as ratio
from
(select province,city,sum(order_amt) fenzi
 from  zhanbi_test
 where  $expr{date:date_para}
 and    $expr{province:province_para}
 and    $expr{city:city_para}
 group by province,city
)a
left  join
(select province,sum(order_amt) fenmu
 from  zhanbi_test
 where  $expr{date:date_para}
 and    $expr{province:province_para}
 and    $expr{city:city_para}
 group by province
)b on a.province=b.province

The provided SQL example aggregates data by the city field, but you can apply similar aggregation to other fields. When using SQL in this parameterized form, ensure to convert the date field to a date type within the dataset's parameter settings to successfully create a dataset and visualize it on the dashboard.

How to achieve cumulative calculations using parameter SQL?

select  a.mon_date,avg(a.order_num) order_num,sum(b.order_num) add_num
from (
select date_format(report_date,'%Y/%m') mon_date,count(distinct order_id) order_num,max(date_format(report_date,'%Y/%m')) max_mon_date
from  company_sales_record_copy
where  $expr{report_date:month_date}
group by date_format(report_date,'%Y/%m')
)a
left join(
select date_format(report_date,'%Y/%m') mon_date,count(distinct order_id) order_num
from  company_sales_record_copy
where  $expr{report_date:month_date}
group by date_format(report_date,'%Y/%m')
)b on a.max_mon_date>=b.mon_date
group by a.mon_date        

With this setup, as long as the dashboard query conditions are linked to parameter fields, you can retrieve the cumulative value for each month starting from the selected month by filtering different month ranges.

How to query data for the last n days by entering a single date using parameter SQL?

select   report_date,area,product_type,count(distinct order_id) order_num
from   company_sales_record
where  area in ('Southwest','Northwest','North')
and    ( $expr{dateadd(report_date,1,'dd'):date1}
or      $expr{dateadd(report_date,2,'dd'):date1}
or      $expr{dateadd(report_date,3,'dd'):date1})
group by area,product_type,report_date

Is row-level permission supported?

Yes, it does. For more information, see Row-level Permissions.

How many rows of data does the dataset display by default?

By default, the dataset displays 100 rows of data.

Does the dataset support paging?

No, the dataset does not support paging.

Why can't the new field be displayed on the right?

How to apply geographic data to map charts?

On the dataset editing page, you can transform geographic data into the corresponding geographic information by using the dimension type switch feature.

For more information, see Create and Manage Datasets.

How to set the use of descriptions as field names?

In your workspace settings, you can opt to use either table names or descriptions as field names when creating datasets.

Additionally, you can select multiple fields and choose 'Use Description as Field Name' in the Batch Configuration tab during dataset creation.

How to quickly update the dataset when fields in the physical table change?

If a physical field is missing, it might still be utilized for analysis or displayed on the dashboard. Quick BI does not automatically remove fields. You can select the table on the canvas, examine the field modifications in the right-side panel, and promptly delete any invalid fields with a single click.

How to copy a dataset to another workspace?

You can copy a dataset to a different workspace using the cross-space dataset copy feature. For more information, see Cross-space Copy Dataset.

How to configure association models to achieve multi-table association analysis?

For more information, see Build a Model.

Configuring association models is akin to using the Join statement in SQL. You can set up association models on the dataset editing page. For more information, see Build a Model.

What is the use of enabling query result caching for datasets?

Enabling caching for a dataset can speed up report access and minimize database queries. For instance, when caching is activated for a dataset and a user runs a query on a report derived from that dataset, the resulting data is stored in the cache. Subsequently, if other users access the same report within the cache's specified duration, Quick BI can instantly present the data without re-querying the database.

How to optimize when SQL dataset query time is too long?

1. Refine SQL logic to utilize indexes effectively, or establish views within the database to bypass complex joins and accelerate query execution.

2. Set global default values for SQL placeholders to prevent full table scans when dealing with extensive datasets.