Tell me more ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.
up vote 0 down vote favorite

RowLimit seems not working. I'm using it in CAML query to run a SSRSreport. I have to pick the Max date for each user. i sorted the user as descending and set the RowLimit element as 1. But that does not work. Any suggestions? Thanks

Edit:

<Query>
  <Where>
    <And>
      <IsNull>
        <FieldRef Name="Meeting_Type" />
      </IsNull>
      <Geq>
        <FieldRef Name="Meeting_Date" />
        <Value IncludeTimeValue="TRUE" Type="DateTime">
           <Today OffsetDays="-1095"/>
        </Value>
      </Geq>
    </And>
  </Where>
  <OrderBy>
    <FieldRef Name="FIRM" Ascending="False" />
  </OrderBy>
  <RowLimit Paged="TRUE">1</RowLimit>
</Query>
share|improve this question
1  
Why don't you paste your CAML query into the post...may help someone help you diagnose the problem further. – Rob D'Oria Jul 28 '11 at 15:40
Can you post the CAML query and explain what results you are seeing? Sorting in descending order and taking the top item should give you the max. – Laurie Jul 28 '11 at 15:47
If your query is malformed it can return the entire list contents, depending on what object is storing the CAML query. Post it here, we'll probably spot it in seconds. – James Love Jul 28 '11 at 15:47
1  
Thanks for the quick response.Here is the query<Query><Where><And><IsNull><FieldRef Name="Meeting_Type" /></IsNull><Geq><FieldRef Name="Meeting_Date" /><Value IncludeTimeValue="TRUE" Type="DateTime"><Today OffsetDays="-1095"/></Value></Geq></And></Where><OrderBy><FieldRef Name="FIRM" Ascending="False" /></OrderBy><RowLimit Paged="TRUE">1</RowLimit></Query>. I want to select the MAX date for the firm if the firm have 2 dates. Thanks – uzn Jul 28 '11 at 16:10
1  
Like I said in my answer below, I don't think you'll be able to accomplish this in CAML. I'll also note that the RowLimit is not part of the Query node...it's part of the outer View node. <View><Query></Query><RowLimit></RowLimit></View> – Rob D'Oria Jul 28 '11 at 16:17

3 Answers

up vote 1 down vote

I just re-read you question and assuming I understand what you're trying to do I don't think you'll be able to do what you want to do with a CAML query.

It sounds like you want to return a result that contains a row for each user based on the last (MAX) date of one of the colums in the list. You'd need to do an OrderBy on the date column and a GroupBy on the user to even come close to this type of result...setting the RowLimit in this case wouldn't help you because it would be acting on the entire result, not the per-user result.

share|improve this answer
Thanks for the clarification. I'm creating a SSRS report basically. Is there any different approach other than CAML query? Thanks. – uzn Jul 28 '11 at 16:19
This is against a SharePoint list, correct? – Rob D'Oria Jul 28 '11 at 16:21
yes, thats right. – uzn Jul 28 '11 at 16:24
so from a pseudo perspective: select Firm, Max(Date) from Firms where Meeting Type is null ...definitely don't see this in CAML...maybe Linq, but that's not a topic where I'm comfortable making a recommendation. – Rob D'Oria Jul 28 '11 at 16:31
I tried doing in filters in report but it wont allow me to use MAX function. it throws error. Any other solution for this? Do i have to write VB code? – uzn Jul 28 '11 at 16:42
up vote 0 down vote

If you're using SSRS, you can use the SharePoint web services to connect to the list and fetch the data, then use what ever method you'd like to in BIDS to display the results.

Here are a couple of good references.
David Wise's Blog post.
MSDN

share|improve this answer
I'm using SSRS 08 and sharepoint 2010. Will this work for 2010? I'm able to select Sharepoint list from the data source list. My question is still do i have to use web services to do this task?Correct me if I'm missing some point here. – uzn Jul 28 '11 at 18:22
Yes that is my understanding. We're not on 2010 yet, but I have used the 2008 BIDS software to connect to a Sharepoint 2007 list. You just have to pass in some paramters like the list GUID and stuff which Dave's Blog details. – PirateEric Jul 28 '11 at 18:51
Optionally, you could create a view in the list that achieves this and use that too by setting the number of items to return to 1 and using the fields you'd like as filters. – PirateEric Jul 28 '11 at 18:54
up vote 0 down vote

Another possibility if you have the option of writing code is to return the list items as a collection (SPListItemCollection) and then move all of these items to a DataTable using SPListItemCollection.GetDataTable(). Once in a datatable, you create your own sql statement to use as a view for the data.

From here, I'm would think you could then bind this view to the SSRS report datasource.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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