In many cases you find yourself writing yet another report against a SharePoint list.
while you know and preach the Microsft guidelines of not accessing the Content database directly , I find that performing a CAML query and paging through it is very slow and not efficient at all.
I have researched this ove and over and looks that you can get better performance if you directly connect to the SQL Content database directly with only one condition ( Please please please , use the the NO LOCK option on all the SELECT statements)
as if you don’t it will slow down you Content DB a bit ( not noticable at run time ) but will be noticable if you have large Lists.
General concept on this solution is INNER JOIN of your AllUserData Table on the UserInfo Data table .
You can also use the views but I am not that good to know how to use them yet.
In General here is the Sample code.
Select AllUserData.[tp_ID] AS Id
,[nvarchar1] As Title
,[nvarchar2] As Author
,[nvarchar4] AS AI_Type
,[nvarchar5] As Functional_Area
,[nvarchar6] As Assigned_By
,[nvarchar7] As Priority
,[ntext2] As Disposition
,[int1] AS INT01
,[int2] AS INT02
,isnull([float1],0) As FL01
,isnull([float2],0) As FL02
,isnull([float3],0) As FL03
,isnull([float4],0) As FL04
,isnull([float5],0) As FL05
,[datetime1] As DT01
,[datetime2] As DT02
,[datetime3] As DT03
,[datetime4] As DT04
,[datetime5] As DT05
FROM AllUserData WITH (NOLOCK)
UserInfo WITH (NOLOCK)
(AllUserData.tp_SiteId = UserInfo.tp_SiteID
AllUserData.tp_Author = UserInfo.tp_ID)
/*Place your List Guid here instead of my sample */
Order By AlluserData.tp_ID
This dataset will be similar to the All Items View and you should be able to do all sort of Reporting on the dataset as with any SSRS report.
Thank you and Good luck.