Office Business Scorecard Manager 2005

Office Business Scorecard Manager 2005: "SharePoint Lists as Data Sources in Business Scorecard Manager
posted Wednesday, December 07, 2005 1:42 PM by Patrick Husting
SharePoint runs on SQL, SQL supports ODBC so of course it makes sense to be able to use SharePoint lists as data sources in Business Scorecard Manager as soon as you have ramped up on the SharePoint schema.

Here's an example connection string:

Driver={SQL Server};Server=localhost;Database=STS_MACHINENAME_1;Trusted_Connection=yes;

Here's a sample query that does a count:

select count(*)
from userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'SOME_LIST_TITLE' where nvarchar3 = 'SOME_COLUMN_HEADER_1' and nvarchar2 = 'SOME_COLUMN_HEADER_2'

Here's one that does a ratio:

declare @nApples decimal
declare @nOranges decimal
declare @ratio decimal

set @nApples = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'RED') or(nvarchar3='REDISH')))

set @nOranges = (
SELECT count(*)
FROM userdata
inner join lists on lists.tp_id = userdata.tp_listid
and lists.tp_title = 'Fruit'
and nvarchar2='FruitBasket'
and nvarchar1 <> 'UNRIPE'
and ((nvarchar3 = 'ORANGE') or(nvarchar3= 'ORANGEISH')))
"