Hi, I have three sharepoint lists in my site: jobs, clients, and client types. Each job has a column for the assigned client and each client has a column for client type. In my report I want to be able to list all the jobs (grouped by client) for a given client type. The report works great without client types being involved, but I cannot figure out how to work in the client types. I used the lookup function but it cannot be used in a dataset filter so that didnt work. I suppose I could add a parent row group for client type and conditionally show only the given client type row group but I dont know how to approach this. Does anyone have any suggestions for me on how to achieve my goals with this report?J. Duke Rogers Communicore Technologies & Triangle Forensics

Hi Duke, Here's what I am thinking you are wanting to achieve: Display a report that lets the user select Client Types and display client records for only the selected client types. Let's assume that you have three datasets in your report, Jobs, Clients and ClientType each corresponding to its SharePoint list. Below are the steps you would want to do (please ignore the steps you might have already done): 1. Assuming you need to provide the user with the ability to select only one / few client types, you would need to setup a ClientType filter in your report. 2. Once your parameter is setup, you could add this to your "Client" dataset (client dataset has the clienttype field in it). SELECT * FROM Client WHERE ClientType In (@ClientType) 3. Now, whenever the user selects any client types from the parameter, only those records would be shown on the report. Please let me know if I havent understood your question correctly. HTH. Cheers, IceQB Please mark correct answers :)

I can use T-SQL in a sharepoint dataset?J. Duke Rogers Communicore Technologies & Triangle Forensics

I can use T-SQL in a sharepoint dataset?J. Duke Rogers Communicore Technologies & Triangle Forensics

IceQB, I cannot figure out what you mean by SELECT * FROM Client WHERE ClientType In (@ClientType)J. Duke Rogers Communicore Technologies & Triangle Forensics

Hi jdroger2, Thanks for your posting. From your description, you have two SharePoint lists: one shows the relationship between Job and Client, the other shows the relationship between Client and ClientType. Now, you want to group the data on ClientType and return the Job information for each ClientType. Right? If so, you can achieve your goal by using the Lookup function. Besides, I am a little confused about why you use the Lookup function in the dataset filter. If you just want to add a parameter "ClientType" to the report, you can configure the parameter to retrieve values from the proper dataset and it's no need to get the parameter values from the other dataset by using the Lookup function. The following steps are for yoru reference: Create two datasets for the two SharePoint lists. Set the DataSet1 to include the Job and Client fields, and DataSet2 includes the Client and ClientType fields.Drag a table into the Design surface, drat the Job field to the first column of the Details row.Add a parent group for the Details group, set the parent group to group on the following expression: =Lookup(Fields!Client.Value, Fields!Client.Value, Fields!ClientType.Value, "DataSet2")Add a multi-value parameter ClientType from the Reprot Data pane other than in the SharePoint list query, set the parameter to retrieve values from the query of Dataset2.Add a filter to the table or the parent group like below: Expression: [ClientType] Operator: In Value: [@ClientType] If you have any questions, please feel free to let me know. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Mike Yin TechNet Community Support

Hi jdroger2, Thanks for your posting. From your description, you have two SharePoint lists: one shows the relationship between Job and Client, the other shows the relationship between Client and ClientType. Now, you want to group the data on ClientType and return the Job information for each ClientType. Right? If so, you can achieve your goal by using the Lookup function. Besides, I am a little confused about why you use the Lookup function in the dataset filter. If you just want to add a parameter "ClientType" to the report, you can configure the parameter to retrieve values from the proper dataset and it's no need to get the parameter values from the other dataset by using the Lookup function. The following steps are for yoru reference: Create two datasets for the two SharePoint lists. Set the DataSet1 to include the Job and Client fields, and DataSet2 includes the Client and ClientType fields.Drag a table into the Design surface, drat the Job field to the first column of the Details row.Add a parent group for the Details group, set the parent group to group on the following expression: =Lookup(Fields!Client.Value, Fields!Client.Value, Fields!ClientType.Value, "DataSet2")Add a multi-value parameter ClientType from the Reprot Data pane other than in the SharePoint list query, set the parameter to retrieve values from the query of Dataset2.Add a filter to the table or the parent group like below: Expression: [ClientType] Operator: In Value: [@ClientType] If you have any questions, please feel free to let me know. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Mike Yin TechNet Community Support

Thanks Mike! I have completed step 1 through 4; however, when I get to step 5 (which I am doing on the Table filter) I don't understand how to achieve the Expression: [ClientType] because there is no field ClientType in the Table's dataset, which is DataSet1. Where am I going wrong? Thanks,DukeJ. Duke Rogers Communicore Technologies & Triangle Forensics

Thanks Mike! I have completed step 1 through 4; however, when I get to step 5 (which I am doing on the Table filter) I don't understand how to achieve the Expression: [ClientType] because there is no field ClientType in the Table's dataset, which is DataSet1. Where am I going wrong? Thanks,DukeJ. Duke Rogers Communicore Technologies & Triangle Forensics

I keep looking at it and I'm completely stumpedJ. Duke Rogers Communicore Technologies & Triangle Forensics

Hi Duke, Thanks for posting. Sorry for the mistake, we should have set the DataSet2 as the main dataset to design the report and used the expression "=Lookup(Fields!Client.Value, Fields!Client.Value, Fields!Job.Value, "DataSet1")" to get the values for Job. The modified steps are as follows: Create two datasets for the two SharePoint lists. Set the DataSet1 to include the Job and Client fields, and DataSet2 includes the Client and ClientType fields.Drag a table into the Design surface, bind the table to the DataSet2.In the first column of the Details row, input the following expression: =Lookup(Fields!Client.Value, Fields!Client.Value, Fields!Job.Value, "DataSet1")Add a parent group for the Details group, set the parent group to group on "ClientType".Add a multi-value parameter ClientType from the Reprot Data pane other than in the SharePoint list query, set the parameter to retrieve values from the query of Dataset2.Add a filter to the table or the parent group like below: Expression: [ClientType] Operator: In Value: [@ClientType] Regards, Mike YinMike Yin TechNet Community Support

Hi Mike, Thank you so much for all the help. I'm making some progress. The Lookup is working but I'm only getting one row per client, even though the clients have more than 1 job. So that we are clear what I'm trying to do is something like this: New Clients ACME Plumbing ACME Job 1 ACME Job 2 ACME Job 3 Contuso Painting Contuso Job 1 Contuso Job 2 Contuso Job 3 Old Clients AAA Masonry AAA Job 1 AAA Job 2 BBB Carpentry BBB Job 1 BBB Job 2 Does this make sense? J. Duke Rogers Communicore Technologies & Triangle Forensics

I continued to play with it today with no success. I started anew with your directions and still had the same issue. It must be something fundamental that I am missing :)J. Duke Rogers Communicore Technologies & Triangle Forensics

Hi Mike, Do you think what I am trying to do may be impossible? If so, any ideas for alternatives? thanks!J. Duke Rogers Communicore Technologies & Triangle Forensics

One really good alternative (the best in my opinion) to pull data from SharePoint lists/libraries in real time is to write CLR procedures and deploy them to an instance of SQL Server somewhere.. I found the below sample project a great starting point for CLR functions that pull directly from SharePoint lists in real time. It's written for 2007 but I found no trouble getting it to work with SharePoint 2010 SP1. SQL Server CLR SharePoint Interface You can then join in the database to your hearts content and do things way beyond what simple lookup functions provide in SSRS. There's also the method of using SSIS to extract the list data first to a database somewhere before running a report over it, but the CLR method gives real time data. http://sqlsrvintegrationsrv.codeplex.com/ (SharePoint List source and destination component for SSIS) Josh Ash

Thanks Josh It looks like I'll have to do what you mention. That is very disappointing though because I moved us from a SQL based system to SharePoint because I "believed the hype". It turns out that SharePoint doesn't do much of anything OOB and the lack of reporting integration for such a simple report is just another in a list of gotchas I've run into. Oh well, thanks again.J. Duke Rogers Communicore Technologies & Triangle Forensics

Hi Duke No problems - seems like there's a growing trend to store data within SharePoint, mostly because it's easy to create a list and have users entering data in no time at all. But from what I've seen it's a bad idea, especially when you get into lists that relate to each other, or have a decent number of items. The External List type is not much better unfortunately - the performance is atrocious... at least the data is stored in the database but the interface is very poor. I've turned to using custom asp.net pages that are displayed within SharePoint and expose a database for data entry. Works well but requires a small of amount of coding. It's a much better solution. Having said that I do like the ability of SharePoint to store documents with associated metadata, I think it does that reasonably well from what I've seen (list thresholds and other issues excepted :| ) Have a good one. Josh Ash

Josh, did you have to modify the code in that sample to get it to work right for SQL 2008 R2? I'm getting a Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method error when I try to call the functions from SQL. Google seemed to indicate that this happens on 2008 but doesn't on 2005?J. Duke Rogers Communicore Technologies & Triangle Forensics

Sorry mate I forgot to respond to you. This may be way too late to be of use to you but you have to mark the function like so [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)] From memory, the original project had some of the functions marked DataAccessKind.None I had the same problem and marking the method like so fixed it. Josh Ash