SQL indicators with BSC Designer – step by step
One of the success factor of using the Balanced Scorecard concept is making it easy to access data from company databases.
Use “SQL Indicator” in BSC Designer for this purpose. With SQL Indicator user can query external data source and get the date-specific results.
What data can BSC Designer access?
It is possible to access any database with SQL query and SQL indicator. Normally, no additional development is needed. The process of designing appropriate query might be complicated, so the help of professional from your IT might be required.
Creating SQL indicator step-by-step
Create SQL Indicator
- Run BSC Designer Pro or open existent project.
- Select any category
- Select “New SQL indicator” in Strategy Tree menu.
Learn more about SQL Indicators in BSC Designer manual.
Modify existent SQL Indicator
- If you already have some SQL Indicator in your project you can change its settings. Right-click on this indicator and select “Edit SQL Properties” command.
Creating SQL Indicator step-by-step
Follow the algorithm below:
In this example we will show how to access data from MS Excel file.
- If you need to access another data source your steps might be different. For instance, on Step 2 you might need to select something different than “ODBC”. You might need to contact your IT specialist with this question.
- In order to make the data in Excel file accessible some changed are needed. The step-by-step instruction on how to make these changes together with original Excel file are located below.
Learn about all possible parameters for SQL Indicators in BSC Designer manual.
How to make MS Excel file accessible for SQL indicators
Follow these steps to prepare Excel file:
- Open MS Office Excel and input “Date” into “A1” cell and “Value” into “B1” cell.
- Fill the column “Date” by dates (in this example the format DD.MM.YYYY is used, but you can use your local date format).
- Fill the Value column by some test values.
- Name the data range by selecting all the cells (including “Date” and “Name”) and inputting “all” into “Name” (check the screenshot below).
- Save the file and close MS Excel.
Count values in the source file
What if it is necessary to count values in the source file and return them as a value for the indicator? As long as it is possible to do with SQL query, it is possible to do this with BSC Designer.
For instance, the source Excel file (or any other database) contains data in the format:
Our goal is to return 3 for the 03.06.2011 (as there are 3 items for this date) and 2 for 04.06.2011 (as there are 2 items for this date).
We will use the following SQL request to get necessary information (assuming the you have named the data range as explained above):
SELECT COUNT(`Value`) as “ITEMS”, `Date`
( `Date` = :Date )
GROUP BY `Date`;
as a result if we will select this SQL indicator and select 03.06.2011 in the BSC Designer’s calendar, we will see 3 in the indicator’s value field. As for 04.06.2011 we see 2.