How are you creating reports out of lists?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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:

http://www.enesyssoftware.com/