BI Query Tool

The BI Query Tool allows administrators to visually build SELECT queries, generate SQL, save reusable BI queries, create stored procedures, schedule automatic execution, and review execution results.

This tool is designed for administrators and developers who need to create reusable reports or scheduled database queries without writing every SELECT statement manually from scratch.

Overview

The BI Query Tool is divided into several working areas. The left side displays database tables, stored procedures, and saved BI queries. The right side provides the visual query builder, generated SQL editor, stored procedure options, schedule settings, and execution result tabs.

Area	Description
Tables	Displays available database tables. You can search and add tables to the query builder.
Stored Procedures	Displays existing stored procedures related to report or query execution.
Saved BI Queries	Displays reusable saved queries and scheduled query definitions.
Visual Builder	Allows you to select tables, fields, joins, filters, sorting, and limits.
Generated SQL	Displays the generated SELECT SQL. You can edit the SQL before saving or running it.
Stored Procedure	Creates or updates a stored procedure from the current SELECT SQL.
Schedule	Allows a saved query or stored procedure to run automatically based on a schedule.
Execution Result	Displays manual query results and execution logs.

Top Action Buttons

Button	Purpose
Clear	Clears the current visual builder selections and generated SQL.
Generate SQL	Creates a SELECT SQL statement based on the selected tables, fields, joins, filters, sorting, and limit.
Run	Runs the current query and displays the result in the Execution Result area.

Tables Panel

The Tables panel lists database tables that can be added to the visual builder. You can search for a table name and click Add to include it in the query.

How to add a table

Open the Tables tab.
Search for the table name if needed.
Click Add next to the table.
The table becomes available in the Visual Builder area.

Double-clicking a table may also add it to the builder depending on the page configuration.

Stored Procedures Tab

The Stored Procedures tab displays available stored procedures. Use this area when you want to review, reuse, or execute stored procedures related to reporting or BI queries.

Saved BI Queries

The Saved BI Queries panel contains reusable query definitions. These may include schedule settings and previously saved SQL statements.

Common actions

Search saved query definitions.
Load an existing saved query.
Review schedule information.
Delete saved queries that are no longer needed.

Visual Builder

The Visual Builder helps you create SQL without writing the full query manually. After adding tables, select fields and configure joins, filters, sorting, and record limits.

Add Join

Use Add Join when the query requires data from multiple related tables. Join rules should define how tables are connected.

Example:
Customer.Id = Order.CustomerId

Select All

Selects all available fields from the added table or tables.

Unselect All

Clears selected fields so you can choose only the fields required for the query.

Join Rules

The Join Rules area defines how selected tables are connected. This is required when your SELECT query includes multiple tables.

WHERE

The WHERE field is used to filter records.

Example:
t1.IsActive = 1
t1.Status = 'READY'
t1.CreatedDate >= '2026-01-01'

ORDER BY

The ORDER BY field controls the sorting of query results.

Example:
t1.CreatedDate DESC
t1.Name ASC

TOP / LIMIT

The TOP / LIMIT field controls the maximum number of records returned by the query.

Example:
100

Generated SQL

The Generated SQL area displays the SQL created by the Visual Builder. You can review and edit this SQL before saving, creating a stored procedure, or running it.

Save Query

Saves the current SQL as a reusable BI query. Saved queries can be loaded again later and can also be scheduled.

Run SQL

Executes the current SQL and displays the result in the Query Result tab.

Always review the generated SQL before running it, especially if the query includes joins or filters.

Stored Procedure

The Stored Procedure section allows you to create a reusable report procedure from the current SELECT SQL.

Procedure Name

Enter the name of the stored procedure. Use a clear naming convention.

Example:
dbo.BI_ReportName
dbo.BI_SalesSummary
dbo.BI_CustomerActivity

Use CREATE OR ALTER PROCEDURE

Enable this option if you want the generated script to use CREATE OR ALTER PROCEDURE. This is useful when the procedure may already exist.

Generate SP Script

Generates a stored procedure script from the current SELECT SQL.

Apply SP Script

Applies the generated stored procedure script to the database.

Applying a stored procedure script changes the database. Confirm the procedure name and SQL logic before applying it.

Schedule

The Schedule section allows saved queries or stored procedures to run automatically.

Field	Description
Enable Schedule	Turns scheduling on or off for the saved query.
Schedule Type	Defines how the query should be scheduled, such as once, interval, daily, weekly, or monthly depending on system configuration.
Schedule Value	Stores the value required by the selected schedule type.
Time UTC	Defines the scheduled execution time in UTC.
Weekly Days	Defines selected weekdays for weekly schedules, such as MON,TUE,WED.

Save Query / Schedule

Saves the query and its schedule settings. If scheduling is enabled, the system can execute the query automatically.

Execution Result

The Execution Result area contains two tabs: Query Result and Execution Logs.

Query Result

Displays the result of a manually executed query.

Execution Logs

Displays execution history, including scheduled runs and manual runs depending on system configuration.

Recommended Workflow

Open Admin > Site > Query Builder.
Search for a table in the Tables panel.
Click Add to add the table to the Visual Builder.
Select the fields required for the report.
Add join rules if multiple tables are used.
Enter WHERE conditions if filtering is required.
Enter ORDER BY rules if sorting is required.
Set TOP / LIMIT to control the maximum number of returned rows.
Click Generate SQL.
Review the generated SQL carefully.
Click Run SQL to test the result.
Click Save Query if the query should be reused.
Create a stored procedure or schedule only after the query has been tested.

Common Use Cases

Use Case	Recommended Action
Create a simple report	Add one table, select fields, generate SQL, and run the query.
Create a joined report	Add multiple tables and define join rules before generating SQL.
Save reusable SQL	Generate and test the SQL, then click Save Query.
Create a stored procedure	Generate SQL first, then generate and apply a stored procedure script.
Run a report automatically	Save the query, enable schedule, configure schedule type and time, then save the schedule.

Important Notes

This tool is intended for SELECT-based reporting queries.
Always test generated SQL before saving or scheduling it.
Use TOP / LIMIT to prevent very large result sets.
Be careful when joining large tables, as performance may be affected.
Use clear names for saved queries and stored procedures.
Scheduled execution time is based on UTC.
Only trusted administrators should have access to this tool.

Recommended Naming Rules

Saved Query:
Customer Activity Report
Monthly Sales Summary
Unpaid Invoice List

Stored Procedure:
dbo.BI_CustomerActivity
dbo.BI_MonthlySalesSummary
dbo.BI_UnpaidInvoiceList

Troubleshooting

Problem	Possible Cause	Solution
No tables are displayed	The database connection may not be configured correctly, or the user may not have permission.	Check the database connection string and database user permissions.
Generated SQL does not run	The selected fields, join rules, or filter conditions may be invalid.	Review the generated SQL and correct table aliases, columns, joins, and WHERE conditions.
Query result is empty	The WHERE condition may be too restrictive.	Remove or simplify filters and run the query again.
Stored procedure cannot be created	The database user may not have permission to create or alter procedures.	Grant the required database permissions or ask the database administrator.
Schedule does not run	The schedule may not be enabled, or the schedule value may be invalid.	Confirm that Enable Schedule is checked and review schedule type, value, and UTC time.

Best Practices

Build and test the query manually before scheduling it.
Use clear WHERE conditions to reduce unnecessary data processing.
Limit result size when testing large tables.
Use consistent stored procedure names starting with dbo.BI_.
Review execution logs regularly for scheduled queries.
Do not expose this tool to non-technical or unauthorized users.

The BI Query Tool can directly access database tables and generate executable SQL. Use it carefully and only grant access to trusted administrators.