Using SQL database as a data source for the indicators

With BSC Designer Online you can use an external database as a data source for your KPIs. SQL Indicators work in both - BSC Designer Online and BSC Designer Server.

Video tutorial: Use of the SQL indicators

Setup SQL indicator

1. Open a scorecard, go to the "KPIs" tab.

2. Select "New WebSQL Indicator" in "Add..." menu.

3. Enter server name. For a locally installed BSC Designer the server name will most likely be localhost

4. Enter port number. For a locally installed MySQL database the default port number is 3306.

Setting up SQL indicator in BSC Designer Online

5. Enter database name, access login, and password. Click the "Next" button.

6. Formulate required SQL query (see some examples below).

7. Click on the "Next" button to preview the results. Click on the "Finish" button.

Examples of SQL queries

BSC Designer expects to receive one value as a response to the SQL query. The received data will be used for the Value field of the indicator.

Supported parameters:

Case 1: Indicator is updated daily. There is one value for each date.

select value from datatable where date = %%date%%

Case 2 (most used): Indicator is updated monthly. There are several values for this period in the database that need to be summarized.

select sum(value) from datatable where date between %%UpdatePeriodStart%% and %%UpdatePeriodEnd%%

Updating data

There are several ways to update data for SQL indicators: