SharePoint Lists as Data Sources in Business Scorecard Manager

Note: In a production scenario you would ideally use the SharePoint object model and bring the data into SQL Server through Integration Services and then into Business Scorecard Manager through Analysis Services, this is article is about helping people build prototypes and proof-of-concepts ahead of the complete implementation.

 

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’)))
 
set @ratio=@nApples/@nOranges * 100
 
select @ratio

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s