I have 2 lists I need to create reports out of including pivot like reports. What's your recommendation for doing so and still present it on a SharePoint site?

look up list

Sample List A
1  X
2  Y
3  Z

items list

Sample List B
1  John   X
2  John   Y
3  Jay    Z
4  Jay    X
5  Ipsum  Y

SQL table

John  9
Jay   5

Expedted result

Name         x          y        z       SQL Column
John         *          *                9   
Jay          *                   *       5
Ipsum                   *

If I had this data in SQL, I would be done with it. But it's unfortunately/fortunately in SharePoint. Are you using Microsoft Reporting Services, Excel, etc? What has been the experience when having to do inner joins? I know you can do some joining with XSLT, but it's more troublesome than what it should be at times.


There are a number of variables that go into a decision like this. I would probably start with a Data View Web Part (DVWP). Can you explain more about what type of reports you want?

UPDATE 2010-04-21: Based on the additional information you posted above, I'd probably still stick with the DVWP suggestion. If the number of values [X, Y, Z] are fixed, then it's even easier.

  • Clarified the question and added a caveat, a SQL table. – Anonymous Apr 21 '10 at 13:39

Simple and easy - DVWP as Marc suggested.

More elaborate - I would look to something like Nintex or SQL Server Reporting Services (SSRS). I've used SSRS on a number of projects.

If it is a unique request and doesn't justify the configuration of a full reporting solution then you can create a custom solution in managed code.

  • What if I need to join a SQL table also? – Anonymous Apr 21 '10 at 13:40
  • You should be able to join to a SQL table in any of the three paths as long as the data types are consistent. – Mike Oryszak Apr 21 '10 at 15:32

Enysys Software has a great product for 2007 that I've used several times with great success if you can do a 3rd party solution:


Your Answer
