Creating KPI's from BDC data
Hi,
I've recently started using Sharepoint 2007 and I'm trying to use the KPI feature with some data we have in an SQL server 2005 database. I've managed to setup BDC so that I can display the table pretty much as it is in the database in a Business Data List.
What I'd like to have is a KPI with the value, goal and warning values all coming from this Business Data List. IE, not from totalling the number of rows in the table but actually take values from cells in the table. Does anyone know if this is possible or if I'm going about this the right way or not?
Kind regards
Ross
I had a quick look at the Business ScoreCard manager but couldn't see how that would help us.
We've decided to use Excel to pull the data out of the SQL server 2k5 database and then the KPI references the relevant fields in the spreadsheet. This seems a bit of a long winded way of doing things but it seems to be the easiest when we only need to refer to a few values held in a sql database and we don't want to utilise the complex and power analysis services (because they're complicated 
)
If you've got any ideas how to simplify this, i'm all ears 
Hi
You can use Office Data Connection files(odc) to pull out data from the SQL Server database into your Excel Workbook and then apply conditional formatting to display it in the KPI format.However the one disadvantage with the above method is that since odc represents a view or a table from the database, you cannot have parameterized queries. What you can do is write a query in the odc file in such a way that you can pull out the data into the Excel workbook and then create a pivot table with report parameters to filter and then apply conditional formatting on the pivot table range. I hope this answers your query
Thanks
Guruprasad
I have the same question as the original poster...I have created a BDC list with all the information I need to use for KPIs...how can I set up the KPIs to reference specific data in this list? In my table for example, I display "Sale Type", "Goal", and "Value"...I want the KPI to show when the value is approaching or has passed the goal for each sale type - I do not want to use Business Scorecard Manager, Excel or Analysis Services since the data is already there and just needs to be referenced properly by the KPI...how can I accomplish this?
Thanks!
You can achieve this by Opening the web part page in SharePoint Designer 2007 and editing the XSLT using the XPath builder in it. Instead of a BDC web part you can also use a DataForm view web part,give the data source as the BDC and appl conditional formatting through XSLT to the web part. E.g. In your case, you can make the rows appear green which have met the sales target and appear red which have not.
