4

I am using SP2010 and SRSS is installed on the farm. I built a report using report builder 3.0.

In my report, I have several datasets. Dataset A returns about 10,000 records and Dataset B 150,000 items. Both Dataset is querying SP2010 lists.

In the report, I have a lookup function like this:

Lookup(Fields!ReportNo.Value,Fields!ReportID.Value,Fields!CommitDateTime.Value, "DataSetB")

I found only the first some hundreds (~500) of results are returning lookup results. It should return more than 2,000 lookup results.

May I know is it a threshold I can adjust?

Improve this question
4
  • Are you using the sharepoint list data source or the xml data source?
    – Mike2500
    May 15, 2017 at 12:30
  • how much lookup fields are there? You may be over the 8 lookup query limit. technet.microsoft.com/en-us/library/cc262787(v=office.14).aspx
    – Mike
    May 15, 2017 at 16:02
  • @Mike2500 I think it is xml. I wrote query like <RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    – Mark L
    May 16, 2017 at 0:59
  • @Mike both list A and B are simple list do not have lookup field.
    – Mark L
    May 16, 2017 at 1:03

2 Answers 2

Reset to default
3
+25

First, as mentioned in the comment, check your data source. In SSRS, r-click on the data source, and select properties. In the properties dialog, check the "connection type". With a value of rsssharepointlist in the dataset, I'm guessing you're using a sharepoint list data source.

If you're using the SharePoint list data source, one consideration is that you can't select which view to use; it uses the default view. So if you list has thousands of items, but the default view for the SharePoint list has a filter to only show the 10 most recent items, then your report will show the 10 most recent items.

If you're using the xml data source, you're likely running into the fact that the SharePoint REST services use paging. I don't know what the solution in SSRS would be for this, other than to write a separate web service that does the call, iterating through each "skiptoken" that part of each batch of rows that points to the next set of rows. (the sharepoint rest service doesn't send all rows. It sends a batch of rows, along with a "skiptoken" field that has the url to retrieve the next batch.)

Also, perhaps check into joining the lists via a REST operation, instead of the lookup function.

Or, perhaps use something like SSIS to get the data to a database table. After all, 150,000 rows is a large list, but a small table. Trying to join that many rows via a function in an SSRS report is going to cause some sort of issue, timeout issues if nothing else.

Improve this answer
5
  • Mike, I checked I am using SharePoint list datasource. The default view contains 50000+ items. In my datasource I added <Where> tab to limit the item return around 2000 items but in lookup only around 500 is matching.
    – Mark L
    May 17, 2017 at 1:03
  • Can I use REST in report builder?
    – Mark L
    May 17, 2017 at 1:07
  • Please check your other data source. Remember, lookup isn't doing anything server-side. The report has to pull down both datasets and then do the lookup to find the matching row. (again, you may need to investigate other options, as running a report with 50000 items in just one of two datasets sounds like a headache to me)
    – Mike2500
    May 17, 2017 at 1:10
  • And yes, you can use rest, however that has the issue of the fact that the rest service only returns a batch of rows. You would need to have a loop of some sort to keep requesting another batch until there are no more rows to request. That's pretty simple in JavaScript or other programming languages, but I'm not sure how to do that in an SSRS dataset.
    – Mike2500
    May 17, 2017 at 1:22
  • After more test, I found if I change query <OrderBy> <FieldRef Name="ActionDateTime" Ascending="FALSE" /> </OrderBy> from false to true. All lookup match works! ... but I need a descending order...
    – Mark L
    May 17, 2017 at 3:59
2

You should try with LookUpSet instead of lookup,

  • Lookup - Returns the first matching value for the specified name from a dataset that contains name/value pairs.

     Lookup(source_expression, destination_expression, result_expression, dataset)

  • LookUpSet - Returns the set of matching values for the specified name from a dataset that contains name/value pairs.

     LookupSet(source_expression, destination_expression, result_expression, dataset)

  • MultiLookup - Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.

    Multilookup(source_expression, destination_expression, result_expression, dataset)
Improve this answer
2
  • In my report I only need the first matched value. Why I need lookupset?
    – Mark L
    May 17, 2017 at 1:13
  • May I know how to get the Last value within a LookupSet? I fail to find any reference.
    – Mark L
    May 17, 2017 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.