SQL Imported Indicators

 

Introduction - Functions Overview - Purchase Program

Import values from SQL query

Balanced Scorecard Designer can be easily integrated with almost any business system, such as CRM, ERP, etc. In this way customer can access data from their business systems and use this data as a value for the scorecard.

Starting version 1.8 Balanced Scorecard Designer uses ADO technology to link to external data sources. For end-user this means the ability to import indicator value as it is released in MS Excel or other popular programs.

The first SQL imported indicator

Note:

  • Creating SQL requests might require additional knowledge, so if you face some difficulties, please consult with some IT professional who is experienced with SQL/ADO technologies.
  • We have put an example of how to use scorecard in \Balanced Scorecard Designer\Samples\SQL Scorecard, check readme-sql.txt in this folder before opening samples.

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

  1. Run BSC Designer. Create category. Select the category, you can now create indicator, imported indicator or SQL indicator.
  2. Select "New SQL Indicator" in Edit menu.
  3. The first required step is specifying data source. Please, note: the number of available data sources depends on installed ADO drivers in your system.
  4. Click "Build" button.
  5. As an example you can select ODBC Drivers and click "Next"
  6. On the next tab you will see "1. Data source". Use radio button there and select "Use connection string". Please, note: this dialog is Windows' dialog, not ours, so it might be different in other versions. Click the button right to this radio box (in WinXP it is "Build").
  7. In the new dialog go to "Computer's data source" tab. Select there "Databases of MS Access". Double click on it. Then click "Database" button and select some MS Access's file (*.mdb), you will find some in BSC Designer installation. Click then OK, again OK.

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

SELECT Table1.Value
FROM `sample_db1`.Table1 Table1

In this case the indicator will use the returned value. In there are some time points in balanced scorecard, then you might use 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 have a full list of supported parameters move over the SQL text box and check hint text.

  • :FileName - will pass the file name of the current .bsc project
  • :Date - will pass the current selected date in BSC Designer
  • :ItemName - will pass the name of SQL indicator, which make a SQL query
  • :Max - passes MAX value of indicator
  • :Min - passes MIN value of indicator