Poorman Excel Services: Excel query files

The Steps

If you don’t have SharePoint Excel Services and want to do more charting, pivots, or heavy excel-related functions than WSS or MOSS Standard allows, try “Exporting to Spreadsheet” from a list.  It works well since the data can more easily be maintained in a SharePoint list but shared and presented in Excel.  What I’ve outlined below, really just leverages the Excel query functionality.  This works with all versions of Excel and SharePoint. Here are my quick steps:

1. Go the SharePoint List you want to link to 
2. Create a view with all the fields you want to capture.  I recommend putting them in the order you want to report on as well.  I unusually call the view "zReporting".
3. Go To the Actions button and select “Export to Spreadsheet”.
4. Click Save, to the File Download box prompting to open the *.iqy file 
5. Rename and save the query (iqy) file to a network location where everyone has access.  Usually on the same SharePoint site make sense. 
6. Open the saved query (iqy) file and excel will open.
7. With the Excel file open, on Sheet1, right click in data and select Data Range Properties
8. Check off “Refresh Data on file open” and click Ok.
9. Create the formatting, pivots and charts as need in new worksheet. 
10. You cannot edit the data on Sheet1 as it is being pulled from SharePoint.   
11. Then just share a link to your excel doc.

Benefits

• Data still available in SharePoint 
• Allows multiple users to edit/add data 
• All the functionality within SharePoint can be used such views, calculated columns, etc.
• With SharePoint 2010, you can synchronize between Excel and SharePoint

For Example

Since the SharePoint Gantt view is not useful for annual representations, I pull a SharePoint list into excel and use the networkdays() function and formatting to present the data.  See below for example.

Supported with:
- Excel 2003, Excel 2007, and Excel 2010
- WSS, SharePoint 2003, SharePoint 2007, and SharePoint 2010

Read More

SharePoint 2010 Failover Database: How am I going to use that?

After reading Mike Watson and Todd Klint's take on SQL Mirroring in SharePoint 2010 and the new Failover Database Server option, it got me thinking how I would do disaster recovery in our environment.  SharePoint used to just be for collaboration but now is a critical part of daily operations (ie storing DR process docs), incident response workflows, and the list goes on. With this new functionality and the DR options of visualization platforms, there are new and cheaper ways to provide recovery.  I still haven't see a good fit for virtualizing SQL unless it is a small or non-prod farm so we typically still use physical database servers.  The typical recovery process requires finding a few servers, building a SQL server and SharePoint farm, then mounting and attaching databases just isn't an option to meet the SLA's users demand these days.  Here is an option that I though of which could provide an RPO/RTO from a few minutes to a few hours depending on your infrastructure. 

High-Level Design

• Virtualize your web front ends (wfe's) and application server
• Setup SQL mirroring or use a product like AvePoint HA to get your content to the second database server/datacenter
• Use VMware SRM or Hyper-V Multisite functionality to bring up your SharePoint farm in the second datacenter
• With the Failover Database Server configured, the farm will be all set.

Benefits

• Leverages infrastructure you may already have
• Microsoft licensing can transfer reduce software costs

Things to consider

• Network latency and bandwidth between datacenters for SQL Mirroring
• DR testing in your environment may differ and you'll need to determine how would would test failing over AND failing back.
• This design is for actual recoveries and not a weekend failover so you can avoid an outage.

While mocking this up in Visio doesn't prove it will work, this is a viable option and one to consider for larger, critical SharePoint 2010 implementations.

Read More

Step by Step: Adding Search Box to a SharePoint page

I found very few articles on how to use the out of the box search web parts for SharePoint.  There is an MSDN article here to provide some background but here is a few steps to get your started.  This one basically provide a direct search to a specific list not on the page using the Search Box web part. Here goes:

1. Go to your page > Site Actions > Edit Page 
2. Click Add a Web Part, select the “Search Box” and click Add 
3. Locate the web part you added and select Edit > Modify Shared Web Part
4. Navigate to the far right to start configuring the web part; I’ve highlighted a few options below.

Scopes Dropdown:

·   Dropdown Mode: Select the one that works for you. 

·   Dropdown label: This adds test to the left of the scope and can be used to clarify search parameters.

Query Text Box:

·   Query text box label: This place a label to the left of the query text box.

·   Query text box width (in pixels): I like to change the Query Text box width to 400-500 pixels depending on the page as it give users more room type their search.

·   Additional query terms:  This is where you really get to tweak or focus the search. You can add in options such as these; This will only pull back results with this filter. Make sure to check off “Append additional terms to query”

o   contenttype:"Project Documents"

o   scope:”This List: Project Directory”

o   site:”http://yoursite.com”

·   Additional query description label: This places a label below the query text box.

·   Query box prompt string: This temporarily places text in the query text box to prompt the users to type in their query such as “Type your search here”

 Miscellaneous, Appearance, Layout, and Advanced: Set these like you would any other webpart.

Here is an example of the webpart with all options filled in:

If you have trouble setting your Additional query terms, test the query directly in your searchcenter to tune your query before entering it in your search box web part.

Good luck

Read More

MSFT's Prod Hub 2010 released

For those moving to Microsoft Office 2010 products and you have a SharePoint environment, the Productivity Hub 2010 could be a helpful tool in your migration plan. It was release on June 28th but expect additional content to be added as more upgrade to 2010 products. 

It doesn't replace the PR campaigns, communications and help desk scripts but gives end-users a good place to go internally for some extra documentation as well as videos. The challenge is it is designed for SharePoint 2010 but install the 2007 version and then add the 2010 content.

Read More

Don't email me an Excel Spreadsheet

To often people send me an email with an excel document attached. Then ask to update it, I send it back, and then they look for additional edits and before you know it there are multiple versions floating around.  I'm sure this has happened to you. While it isn't the answer for every spreadsheet, in most case I just want to tell them to put it in SharePoint and create a list!

• Import Spreadsheet option: An often overlooked perk of SharePoint is the import spreadsheet option for creating a list. Sometimes I'll start creating a spreadsheet and then pull it into SharePoint, or just start a list from scratch or even export a previous list and use as a template. Starting with Excel does give you a little flexibility if you don't know exactly how you want to layout your columns and fields. You can even use the Excel spreadsheet for a few days then import it because SharePoint will use the field data as potential drops.  Importing is easy:

1. Go to Site Settings > Create 
2. Under Custom Lists > Import spreadsheet
3. Name you list, browse to your existing spreadsheet, and click import. 
4. Excel will launch with "Import to Windows SharePoint Services list" (See image at right.)
5. Change the Range Type to "Range of Cells:" 
6. Place cursor on "Select Range:" then place your cursor on the little box above row 1 and to the left of column A.  This will highlight the sheet.
7. Click Import and your list will now appear in SharePoint.
8. In the new list, go to Settings > List Settings to tweak field columns or change version and advanced settings.

Now that you have the list created you can use jquery to create pie charts , use the Export to Spreadsheet feature, or improve your list with calculated fields or coloring.  A SharePoint list doesn't replace spreadsheets, especially those with with heavy calculations or macros, but for 80% of spreadsheets this could be a great improvement allow for multiple users to access. Bottom-line is list are not just for Tasks, Action items or project related data and it is all out of the box.

How do you use lists?

Read More