Thursday, March 12, 2015

ListView Day Filter in SharePoint

There was a query at technet forum , "Requirement to show "Daily" stats on a custom list.  Criteria are to show items modified between 3pm yesterday to 3pm today. "

Solution -  

1- Create a Calculated column in list. The formula should be  -
  • Name - ModifiedHrs
  • Type - Calculated
  • Formula:     =HOUR(Modified)
  • Format -Number (1, 1.0, 100)
    Number of decimal places:  0
2- Create a Tabular View ( e.g. DayState.aspx) for List. (not Calendar View), through browser with filter ( Modified equal to [Today]).
3. Now Open the view page (DayState.aspx) in SharePoint Designer code view [Edit in Advance Mode]
4. Locate the CAML query associated with the filter (Modified = [Today]), you can find something like

<WebPartPages:WebPartZone ---
<WebPartPages:XsltListViewWebPart ----
<View --->
<FieldRef Name="Modified"/>
<Value Type="DateTime"><Today/></Value>

5. Replace the <Where>----</Where> Section with this one

<Or><And><Eq><FieldRef Name="Modified"/>
<Value Type="DateTime"><Today OffsetDays="-1"/>
</Value></Eq><Gt><FieldRef Name="ModifiedHrs"/>
<Value Type="Number">14</Value></Gt></And>
<And><Eq><FieldRef Name="Modified"/>
<Value Type="DateTime"><Today/></Value></Eq><Lt>
<FieldRef Name="ModifiedHrs"/>
<Value Type="Number">16</Value></Lt></And></Or>
6. Save the page , and test in browser

1 comment:

Unknown said...

Very informative post... Thanks...