SP 2010 – Content Query Web Part filter items by both date and time

Environment:

SharePoint Server 2010

Problem:

We have a list which stores company news added by internal staff. We use the content query web part on the home page to display the last added news item. Sometimes we have news that needs to be published at a certain time of a day, e.g. the CEO just announced something at 10 am and we would want that news to be displayed right after on the home page. Ideally, the announcement would be pre-entered to the list and set a time to be displayed.

The content query web part only has filters that can be used to filter a date, for example, it can filter the publish date to be less than today. For my scenario, I need it to filter the current time as well.

Solution:

After some searches on the net, I found two approaches. One is to use a workflow to move list items from list A to list B on a certain time. List A has all the pre-entered items and List B has the items to be displayed on home page. The other approach is to modify the .webpart file and override the existing filters.

I took the latter approach since it’s simpler for me to do.

    1. Open the page which has the content query web part, check it out and edit it.
    2. Export the content query web part to local desktop.
    3. Make a copy of it and rename it to something new, e.g. CQWP_Updated.webpart
    4. Open the CQWP_Updated.webpart in a text editor, e.g. Notepad
    5. Search for QueryOverride, you should be able to find <property name=”QueryOverride” type=”string”/>, remove that line.
    6. Move to the bottom of the file.
    7. Add the  following code  before the closing </properties>
      <property name="QueryOverride" type="string">
        <![CDATA[
                      <Where>
                       <Leq>
                         <FieldRef Name="Start_x0020_Date" Nullable="True" Type="DateTime"/>
                         <Value IncludeTimeValue='TRUE' Type="DateTime"><Today /></Value>
                       </Leq>
                     </Where>
                   <OrderBy>
                      <FieldRef Name="Start_x0020_Date" Nullable="True" Type="DateTime"
                      Ascending="FALSE"/>
                   </OrderBy>]]>
       </property>

      Note: The code basically specifies that filter the list by Start Date, display the items that has the Start Date less or equal to now and order the list in decending order of Start Date.

    8. Save the file and go back to the browser.
    9. Remove the original Content Query web part and add the modified one to the page. When you open the “Edit Web Part” panel, you’ll see a message: “Some properties in this Web Part are not available because they are configured to have fixed values.” Also you’ll notice the Additional Filters, Grouping and Sorting sections are grayed out. That means you’ll need to modify the .webpart file to change filtering, grouping and sorting.

References:

Customize the SharePoint Content By Query Web Part by Using Custom Properties: http://msdn.microsoft.com/en-us/library/aa981241.aspx

Filter a CQWP on time portion of datetime field

http://go4answers.webhost4life.com/Example/cant-customized-content-query-web-part-22681.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s