SQL Server to Web Service to PerformancePoint to SharePoint

Question

Ok I have a sql server database view with a list of bookings (employee, date, charge). What i need to do is create a PerformancePoint chart that basically lists the employees and totals all their charges for a particular period. e.g.

Karen 01/01/2010 £50
Jim 01/01/2010 £20
Karen 02/01/2010 £30
Tim 03/01/2010 £70

Chart for January
Karen £75
Jim £60
Tim £70

The data would then be used in various graphs etc..
How would i go about doing this? I have PerformancePoint raring to go, and I can enter sql statements etc...but its the totalling the individuals and only returning results for a specific period (that can change) that I am struggling with. Would i need to use a web service for this? I know how to make them but no idea how to integrate with PerformancePoint.

Many thanks to all who contribute

Answer 1

You can do this many different ways. Here is one path:

1. Use Reporting Services to display your matrix, like so:

         Jan Feb Mar  
   Karen $75 $50 $40  
   Jim   $60 $65 $70  
   Tim   $70 $75 $80  
   

2. Then you create a report shell (Other Reports --> SQL Server Report) in Dashboard Designer. Link to your report you just created. You will need to either link to your deployed report in Report Server or your SharePoint integrated report.
3. Next create a dashboard in Dashboard Designer. In this dashboard, place the report you create the report shell you created in step 2.
4. Deploy your dashboard to a report library in MOSS.
5. Publish a major version of your dashboard report in MOSS.

that's about the gist of it. You should realize that using PerformancePoint for this isolated incident is kinda overkill. However, if you have a bunch of metrics that you want to incorporate into your BI presentation, then PerfPoint will work just fine.