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

Ghostable VS. GhostableInLibrary

What does Ghost and UnGhost mean?

Ghosted means the file is shared across sites and each site has a pointer to a file on the file system.  Unghosted implies that the file has been edited and is now stored with the particular site.

 

What’s the difference between Ghostable and GhostableInLibrary?

The decision between Ghostable and GhostableInLibrary really is based on the type of file that you are deploying and whether you want users to view and access the document view a list or a library.  If you desire users to view the file within a library then you would select the InLibrary option.  Both Ghostable / GhostableInLibrary implies that the file will be cached in memory.  GhostableInLibrary however specifies that the file be cached as part of a list whose base type is Document Library and that you can work with the file as you would with any other file in the library (check-in, check-out, version history).

 

Reference: http://www.davidyardy.com/davidyardy/blog/post/2010/09/15/SharePointe28093-Ghostable-and-GhostableInLibrary.aspx