Contents 

BSC Designer - Online Manual

Prev Page Next Page

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

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 \BSC 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
  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.

  • :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

Additional settings

  • Use "Document Properties" command in "File" menu to access "SQL" tab. You will find there additional settings that specify how the BSC Designer will access data using SQL indicators.
   
Published by BSC Designer: Home Page | Download | Buy Here | Support