Import Values From SQL Queries

Video manual Check out video guide about SQL indicators

BSC Designer can be integrated with almost any business system, such as CRM, ERP, etc. In this manner, customers can access data from their business systems and use these data as values for the scorecard.

BSC Designer uses ADO technology to link to external data sources. For the end user, this means the ability to import indicator values as inputted in MS Excel or other popular programs.

Your first imported SQL indicator


Here are steps you can follow to create your first scorecard using external data as values:

  1. Launch BSC Designer
  2. Create an indicator
  3. Click Data source button "KPI Data Source" button
  4. Select "Link to SQL Database"
  5. Select a database with local or web access
  6. The first required step is to specify a data source. Please note: the number of available data sources depends on the ADO drivers installed on your system.
  7. Click the "Build" button.
  8. As an example, you can select ODBC Drivers and click "Next"
  9. On the next tab, you will see "1. Data source." Use the radio button there to select "Use connection string." Please note: this dialog is a Windows dialog, not ours, so it may be different in other versions. Click the button to the right of this radio box (in Windows XP it is "Build").
  10. In the new dialog, go to the "Computer's data source" tab. Select there "Databases of MS Access." Double-click on it. Then click the "Database" button and select an MS Access file (*.mdb); you will find some in the BSC Designer installation. Click OK, and then OK again.

Now you will need to create an SQL query. A simple query might be:

SELECT Table1.Value
FROM `sample_db1`.Table1 Table1

In this case, the indicator will use the returned value. If there are some time points in the balanced scorecard, then you might use the current date selected in the program as a parameter for your query:

SELECT Table1.Value
FROM `sample_db1`.Table1 Table1
WHERE (table1.Date=:Date)

Parameters in request

To view a full list of supported parameters, move your mouse over the SQL text box and check the hint text.

Additional settings