Building no code solution to consolidate Issues data across Farm

Customizing Search Results on MSDN            Pointing up Search results XML Only

Help from Corey Roth                                                   CoreResultsWebPart configuration

Scenario & Management Requirement
SharePoint will soon be deployed across the company with Issues Management in place. All Issues that may affect the organization that need or may need Management visibility shall be entered, secured, updated, escalated, reported on, acted on and and eventually closed for all Departments and sub-departments. Each department is responsible for data integrity and control of who can read and write their data.

Proposed Solution
The IM folks have created an Issues List custom content type that is built into Site Templates. We have hundreds of Site Collections and Sub-sites across many different Web Apps in a Farm with Issues lists in use at Random. The IM folks built in certain Metadata fields and  Pick lists to allow for Issue escalation and reporting to various levels of Management. As the organization matures, more and more users will have to fill out issues lists on their collaboration site in order to communicate with Sr. Management. Each Department will get one site collection and the sub-departments with get one or more sub-sites. Security is controlled at the site collection level by Department owners. As the need to report issues arises, the Site Collection owner will implement and secure out the issues list for their departments.

How to consolidate issues data across site collections and across different web applications as this is not readily available out of the box. How to ensure the right managers have their issues bubble up at the right time for action. How can the issues rise up to the various levels of management so they are either resolved quickly before raising to Sr. Management, vetted and summarized for Sr. Management consumption.

Final Solution
Implement SharePoint Enterprise Search with a custom search scope. The Scope shall be configured to query the Search Index for all records and fields that IM implemented in the Issues List custom content type. Build a custom Search Page with the Out of Box (OOB) Data View Web part (DVWP) and custom XSLT. The page will implement the OOB DVWP column filtering with the custom Filter and Group By controls as a reporting mechanism at all levels of Management. Each Department Team Members shall use the OOB List controls on the site to mange data entry. Sr. Management will reserve the right to Edit core fields on any item across the company. Departmental Managers can edit core fields on items spawned from their own Department and not cross Departmental.

Here is how to do it and make it Scalable

Make a new Content Type called Issues and add the required Site Columns (of course you could use the OOB Issues Content Type, simply called “Issue”). To deploy the content type to all Site Collections in the Farm set up a Content Type Hub

Create and configure a new Search service application and content source and do a full crawl of your farm Technet

Make a new list based on the Issues Content Type see MSDN how to here

Add some sample data to the List, say 2 or 3 records, this will allow the custom scope to pull records to allow development of the DVWP. Then save the list as a Template (check off the include data box) and it will be saved in the List Template Gallery by default. Now you can add this list to a few Site Collections by creating a new list from the template. If you do not know how to do this by now, see here

Create a Search Scope that will reach into a Search Indexed Content Source that was created in step 2 above. You can create the scope from any Site Collection but you should create a Shared Scope in Central Admin so that Site Collection Owners can make use of if. Either way the results are the same. How to is found here but basically all you have to do is go to your Search Service Application from Step 2, click on Scopes in the left hand menu and click add New Scope > Enter a Title, leave defaults and click OK. You must add at least one rule, click new Rule and enter the values as shown in this graphic image The Add Property restrictions is key here, we want to filter all records in the Content Source where the data is based on our Issues content type. If the Content Type property is not in the list you will have to activate it by Clicking on Managed Meta data Properties link in left hand menu right above Scopes, from there find the words Content Type in the list and click on it and check off the box “Allow this property to be used in scopes” leave defaults as is and click ok.

The Search Scope job runs every 15 minutes so check back and see if it found your Issues sample records. Click on Scopes Link again from the left hand menu, refresh the page to see how many records were found under items column. Once complete we can start to build our user interface pointing at this scope as the data source.

Building the UI

Navigate to a page in your Farm that you would like to expose to users with the Issues data available. It must be a page that accepts inserting of Web Parts. Insert the Search Core Results Web Part onto the page, we will need to make several settings such as point it to our Scope, set up our issues Fields and add in our custom XSLT that consists of the DVWP.

On the properties of the Search Core Results Web Part, under Appearance set the title to Issues,
see graphic. image. Under Results Query Options enter Scope="Issues"
per this graphic image.  Click Apply on the web part, save the and refresh the page, you should see issues  results, if not troubleshoot and do not go to next steps. You can play with the Append Text to Query by adding in further refinement such as filtering out only Active Issues. Under display properties, uncheck the Use Local Visualization and this enables the Fetched Properties, XSL Editor and Parameters Editor. This is where the fun starts. Basically there are 4 main steps, 1. Configure Managed Properties in Search Admin Site 2. Change the fetched Properties XML to include our Issues Fields from the Issues Content Type. 3. Make a throw away xml file from the web part results of sample records for use in SharePoint Designer to allow us to build the DVWP. 4.  Build the DVWP and copy the resulting XSLT and Parameters from SharePoint Designer into the Web Part and that is it, start slicking and dicing.

1. Configure Managed Properties in Search Admin Site. Each field in the Issues Content Type must be mapped to a Managed Property in Search. From Search Admin Site, Click Managed Properties link on left hand  menu > New Managed Property > Enter Name and Data type and check off allow this property to be used in scopes. Click Add Mapping and map it back to a crawled property. Do this for all the custom fields (site columns) from the Issues Content Type. Now these properties can be set in set 2 next. The Property name can be any unique name you like, I make mine the same as the content type field name. A trick to obtaining internal field names is to copy and paste the content type field view into Excel and look at properties URL for the field name.

2. Change the fetched Properties XML to include our Issues Fields from the Issues Content Type. First copy the xml out of the Fetched Properties box per this graphic image click inside the box > click Ctl A to select all text then Ctl X to cut the text into clipboard and paste into SharePoint Designer empty page and reformat the xml so you can actually read the data per this graphic. image. You will need append the Issues field names (Internal name not display name) in xml format,  You must leave the default properties as is and only add new xml columns. Paste the xml back in and then click apply at bottom of web part. If you get a property error this means one of 3 things, you made a mistake on xml, the field is not a managed property or the field name is spelled wrong.

3. Make a throw away xml file from the web part results of sample records for use in SharePoint Designer (SPD) to allow us to build the DVWP. Copy and paste the xml per instructions in this document , to simplify, go directly to step six, copy that xml into the XML editor (clear XML editor when if first opens) on the Web Part, click apply and you should get non formatted XML output similar to this graphic  image Then copy the resulting xml and paste it into an empty file in SharePoint Designer into any folder of your favorite DEV SharePoint site. We will then use this file as a temporary data source to help us build our DVWP. See graphic of the data source image in SPD

4. Make the DVWP and copy the resulting XSLT and Parameters from SharePoint Designer into the Web Part for our final step, this will render the data as a typical SharePoint List look and feel. From SPD, in the same site you created the temporary data source xml file in, make a new XML Data Source connection per this graphic image . Now add an empty aspx page to the site, open it and insert the an empty DVWP into it see graphic image . Then click on Click Here to select data source and select our xml file as shown here image . Select some fields and insert them into the view, set properties and look and feel to suit like this. image Now go to web part properties and copy out the xml and parameters image and paste into the Search Web part image .

The finished Product