SQL Imported Indicators in Balanced Scorecard

Introduction - Functions Overview - Purchase Program

Import values from SQL queries

• SQL indicators are available only in the PRO version
• Online step-by-step guide on how to create SQL indicators

Balanced Scorecard Designer can be easily 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.

Starting with version 1.8, Balanced Scorecard Designer has used 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

Note:

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

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

1. Launch BSC Designer. Create category. Select the category; you can now create an indicator, imported indicator or SQL indicator.
2. Select "New SQL Indicator" in the Edit menu.
3. 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.
4. Click the "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 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").
7. 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.

• :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 for making an SQL query;
• :Max - passes the MAX value of an indicator;
• :Min - passes the MIN value of an indicator.