JoelBlogs - Joel Jeffery's Microsoft 365 Blog

Microsoft 365, SharePoint, Teams and Office 365 Architecture, Development, Administration and Training

  • Home
    • Sitemap
  • #SPThingADay
  • Articles
    • SharePoint Online
      • SharePoint Online – Drag and Drop and Large File Uploads
    • SharePoint 2016
    • SharePoint 2013
      • Content Database Changes to the AllUserData Table
    • SharePoint 2010
      • Administration
        • Disable CRL Checking
        • Excel 2010 & PowerPivot
        • Limits & Thresholds
        • PeoplePicker AD Errors
        • Recycle Bin Behaviour
        • Renaming a Server
        • Service Pack 1
        • Unattended Installs
        • Uninstall All SharePoint 2010 Solutions via PowerShell
        • User Alert Management
        • Virtualised SharePoint
        • Visio Stencils for Administrators
      • Development
        • Audience Membership Workflow Activity
        • Base Types, Lists & Content Types
        • BCS & Offline Sync
        • Debugger Skipping Lines
        • Development Laptop Spec
        • Enabling JavaScript IntelliSense
        • Event Receivers & Deployment Jobs
        • FavIcons & SPUrl
        • Google Maps Sandbox Web Part
        • Group By Content Type for List Views
        • Locale Stapler / Master or Default Locale
        • Removing Default Editor Parts
        • Sandbox Embedding Resources
        • Solution Sandbox Introduction
        • SPPersistedObject
        • Restoring Deleted SPSites in SP1
        • SPWebConfigModification 1
        • SPWebConfigModification 2
        • STSADM copyappbincontent vs. Install-SPApplicationContent
        • Workflows for Beginners
        • Workflow InitiationData Seralizer
    • SharePoint 2007
      • Alternate Access Mappings
      • Excel Services
      • Excel Services UDFs & Excel Client 2007
      • Experiences from the Field
      • InfoPath & Forms Server
      • Kerberos & SSRS
      • Records Management
      • Web Application Service
      • WSS vs MOSS
  • Training
    • SharePoint Admin Links
  • Downloads
    • Summary Slides for PowerPoint
    • CodePlex Projects
      • Audience Membership Workflow Activity
      • Google Maps Sandbox Web Part
      • Group By Content Type in List Views
      • Locale Stapler / Master or Default Locale
      • SharePoint Outlook Connector
  • Hire Me!
    • MCP Transcript
    • Résumé/CV

SharePoint 2010 Developer Courses and Business Intelligence in SharePoint 2010

July 22, 2010 by Joel Jeffery

Over the next few weeks I’m building the new SharePoint 2010 Developer Course for Firebrand Training. These are exciting times for SharePoint 2007 (MOSS and WSS 3.0) developers looking to acquire new skills.

The Microsoft SharePoint 2010 platform has really come of age. In my three recent video podcasts, I showed some of my favourite features in SharePoint 2010:

  • SharePoint 2010 Enabling Business Agility using Workflow
  • SharePoint 2010 Business Connectivity Services and Offline Synchronisation
  • SharePoint 2010 Sandbox Solutions

But there’s so much more for the SharePoint Developer to enjoy!

  • Visual Studio 2010 SharePoint Projects and Project Items
  • Client Object Model (for .NET, Sliverlight and JavaScript)
  • LINQ to SharePoint
  • Solution and Feature Lifecycle with the Versioning and Upgrade Framework
  • SharePoint Designer 2010 and Visio 2010 Integration
  • InfoPath 2010 Forms
  • Claims Based Security
  • SharePoint Powershell Integration
  • Lists and Libraries Enhancements
  • Branding Improvements
  • Enterprise Content Management Improvements, Taxonomies
  • Search Improvements
  • Excel Services, PerformancePoint Services, PowerPivot for SharePoint
  • Access Services
  • Office Web Access
  • …More! More! More!

To round off my week as the Firebrand Blog guest blogger, I’ve made a high level video overview of some of the new Business Intelligence features in SharePoint 2010. A quick look at the Business Intelligence Center site definition, a look at Excel Web Access and the Excel Web Access Web Part, and finally a peek at the REST API for Excel Services.

 

Don’t forget you can find all my videos as podcasts on iTunes! Just search for “joelblogs tv” and you’ll find them.

Filed Under: SharePoint, Training Tagged With: Business Intelligence, Charting, Excel Services, Excel Web Access, SharePoint 2010, SharePoint Videos, Training, Videos

WSS 3.0 and MOSS 2007 tips #1 – Popular Misconceptions – Excel Services

October 11, 2008 by Joel Jeffery

I don’t know if there’s enough of these to make a series, but I’m going to give it a go.

I’m going to start with a MOSS Excel Services tip. If you’re already using this in anger, then I’m preaching to the converted. If you’re considering using Excel Services, or you’re about to advise a client on Excel Services, there’s a few things you need to know.

First off, some stuff you’ll probably know. Excel Services is a great platform for consolidating spreadsheets and bringing order for clients that are heavy Excel users. Investment banks – we know who you are – you folks with tens or hundreds of thousands of complex spreadsheets that get emailed about between users. Some of these spreadsheets are used to make multi-billion dollar decisions for the larger banks. Often, the authors of these spreadsheets are long since departed. Frequently, the banks daren’t make material changes to the business logic therein, just in case it all goes a bit Pete Tong.

Similar stories exist even when there aren’t decisions being made with quite such a high dollar-value. Government departments, pharmaceutical companies, accountancy firms, consultancies – the list goes on. All places with the potential to generate tonnes (can I measure digital data by weight?) of spreadsheets copied from user to user.

Microsoft Office SharePoint Server provides check-in and check-out to help restrict this kind of proliferation, as you’d expect in common with the rest of the Document Library features of MOSS. Excel Services gives us much more on top of that

When you add an Excel spreadsheet to a suitably configured Document Library, you can use Excel Web Access – kind of like Outlook Web Access – to view and interact with it. In fact, viewing through Excel Web Access is the default action on Excel spreadsheets in such a Document Library. There is also programmatic and web service access to Excel Services, which I’ll try and come to in another blogging.

Which brings us to:

Popular Misconception #1 – “You can edit the spreadsheet through the browser. The spreadsheet experience is just like the real thing – just like Google Docs.”

Nope. No you can’t. No it’s not. Sorry.

It’s good though! And what it offers is valuable. Instead, you get 60-70% of the core Excel desktop experience through the browser. Graphs, colour bars, pivot tables. All good, all decently rendered. And you can interact with the sheet through settable parameters and filters.

You can’t edit the sheet and you can’t save it. If you want to do that, you can either open the sheet in Excel client on the desktop, or create a “snapshot” – once again, for use in Excel client.

Why is this good? If you expose the sheet *solely* through Excel Web Access, none of your hard earned intellectual property – none of your business logic – ends up in the hands of your audience. You can build dashboards in MOSS that show regions of multiple spreadsheets or charts side-by-side with Key Performance Indicators (KPIs) or any other information you want to display.

Popular Misconception #2 – “I can take one of our complex spreadsheets and just stick it in Excel Services and have it run server side.”

Nope. Sorry. You’ve more than likely got some work to do. If your spreadsheet uses:

  • VBA macros
  • COM add-ins
  • User Defined Functions

…you have some difficult decisions ahead of you.

If a lot of your business logic resides in VBA macros, you’ll need to consider recoding these as an Excel Services User Defined Function (UDF) in a .NET language. If your spreadsheet does lots of gnarly Monte Carlo analysis using a visual COM add-in, once again you’re left with a significant coding exercise.

If you’re lucky enough that your spreadsheet already uses client-side, C language User Defined Functions, you can either, once again, re-code them as an Excel Services Managed UDF – or write a managed wrapper for them.

There is a good list of unsupported and partially supported Excel client features in Excel Services here.

So, how do you do it? It can’t be easy, surely?

Popular Misconception #3 – “SharePoint Excel Services UDFs are difficult. It’s better just stick to simple spreadsheets and keep the complex ones as desktop spreadsheets.”

Very, very easy.

First, create a .NET 2.0/3.5 managed Class Library project in Visual Studio 2005/8.

Second, make a reference to Microsoft.Office.Excel.Server.UDF.dll, which is usually sitting here: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI on a server with MOSS installed.

Third, we’ll write our class and methods. Here’s a C# example.

   1: using System;
   2: using Microsoft.Office.Excel.Server.Udf;
   3:
   4: [UdfClass]
   5: public class MyUDFClass
   6: {
   7:     [Udfmethod]
   8: public double MyUDFMethod(double x, double y)
   9:     {
  10: return x * y + y;
  11:     }
  12: }
We tell the UDF framework that our class contains UDFs by decorating it with the UdfClass attribute – in line 4. Similarly, you mark up one or more methods as being a UDF by decorating then with the UdfMethod attribute – in line 7. You can read an in depth tutorial on msdn.com here.

That’s more or less it. You’re now at liberty to fill your class and methods with as complex business logic as you see fit. The final step is configuration of the Shared Service Provider (SSP) for Excel Services, and deployment to a MOSS server – which may well be the topic of a future blogging.

To use your new UDF in a spreadsheet couldn’t be easier. You don’t even need to deploy the UDF to the desktop machine you’ll use to create the spreadsheet. Simply open a new spreadsheet, and use your function in a formula as if it meant something to Excel, thus:

Using a Managed UDF in Excel Client
Figure 1 – Using a Managed UDF in Excel Client

Yes, when you hit return it will get resolved to “#NAME?” as if it doesn’t recognise it. This is, of course, because it really doesn’t recognise it, and it won’t be recognised until you deploy that spreadsheet to a correctly configured Document Library.

That’s it for this time! :)

If you’ve been evangelising Excel Services having never used it, I hope this has cleared up a couple of common misconceptions. If you’ve been tempted to, but never quite made the jump, I hope this has given you a push to just *try* Excel Services. They’re pretty cool and, as I hope you’ll see, offer a compelling business story for bringing Excel spreadsheets back under control.

Filed Under: SharePoint Tagged With: Excel Services, Excel Web Access, MOSS, SharePoint, UDF, User Defined Function, WSS

Joel is a full-stack cloud architect who codes. He is a Microsoft Certified SharePoint Online, SharePoint Server and Azure specialist and Microsoft Certified Trainer.
He has over 20 years' experience with SharePoint and the Microsoft .NET Framework.
He's also co-founder of Microsoft Gold Partner JFDI Consulting Ltd. Read More…

#SPThingADay

#spthingaday

View
Open
#spthingaday 003 - Information Architecture. Folde #spthingaday 003 - Information Architecture. Folders are evil. Use metadata.  #SharePoint, #Office365..
On a fileshare, all you have is path and filename. That's the only axis by which you can classify your stuff. So you nest folders, within folders, within folders. Then you pack the filename with extra bits of information - like the initials of the last editor, or the version number. 
You don't need to do this in SharePoint. Use metadata. 
Add columns to your libraries. Text columns, number columns, choice columns, managed metadata columns. Each file can have several of these values simultaneously. But a file can only live in one folder at a time. 
Use metadata.

#microsoft #microsoftoffice #microsoftsharepoint #microsofttraining #officetraining #freetraining #trainingvideos #contentmanagement #enterprisecontentmanagement

#spthingaday 003 - Information Architecture. Folders are evil. Use metadata. #SharePoint, #Office365..
On a fileshare, all you have is path and filename. That's the only axis by which you can classify your stuff. So you nest folders, within folders, within folders. Then you pack the filename with extra bits of information - like the initials of the last editor, or the version number.
You don't need to do this in SharePoint. Use metadata.
Add columns to your libraries. Text columns, number columns, choice columns, managed metadata columns. Each file can have several of these values simultaneously. But a file can only live in one folder at a time.
Use metadata.

#microsoft #microsoftoffice #microsoftsharepoint #microsofttraining #officetraining #freetraining #trainingvideos #contentmanagement #enterprisecontentmanagement
...

14 0

View
Open
#spthingaday 001 - Governance. Avoid death by file #spthingaday 001 - Governance. Avoid death by fileshare #SharePoint, Teams, Planner and #Office365 projects need executive sponsorship.

#microsoft #microsoftoffice #microsoftsharepoint #microsofttraining #officetraining #freetraining #trainingvideos #contentmanagement #enterprisecontentmanagement

#spthingaday 001 - Governance. Avoid death by fileshare #SharePoint, Teams, Planner and #Office365 projects need executive sponsorship.

#microsoft #microsoftoffice #microsoftsharepoint #microsofttraining #officetraining #freetraining #trainingvideos #contentmanagement #enterprisecontentmanagement
...

15 0

View
Open
#spthingaday 060 - SharePoint Search. Changing the #spthingaday 060 - SharePoint Search. Changing the Search Topology.  #SharePoint, #SharePointSearch.. The Search Service in SharePoint on-premise is made up of six components.

Crawl, Content Processing, Index, Analytics, Query and Administration.

Each of these components can be placed on one or more servers in your Farm.

To make any one Search component support high availability, you need to make sure there are at least two servers in your Farm running it.

SharePoint Server 2010 had only 3 types of component, and Microsoft let you manage their placement via Central Admin, in the browser.

From SharePoint 2013 onwards, this was deemed too difficult to express graphically.

For SharePoint 2013 and 2016, topology changes need to be made via PowerShell.

Tomorrow, we'll look at just what PowerShell cmd-lets you need to use.

#microsoft #microsoftoffice #microsoftsharepoint #microsofttraining #officetraining #freetraining #trainingvideos #contentmanagement #enterprisecontentmanagement

#spthingaday 060 - SharePoint Search. Changing the Search Topology. #SharePoint, #SharePointSearch.. The Search Service in SharePoint on-premise is made up of six components.

Crawl, Content Processing, Index, Analytics, Query and Administration.

Each of these components can be placed on one or more servers in your Farm.

To make any one Search component support high availability, you need to make sure there are at least two servers in your Farm running it.

SharePoint Server 2010 had only 3 types of component, and Microsoft let you manage their placement via Central Admin, in the browser.

From SharePoint 2013 onwards, this was deemed too difficult to express graphically.

For SharePoint 2013 and 2016, topology changes need to be made via PowerShell.

Tomorrow, we'll look at just what PowerShell cmd-lets you need to use.

#microsoft #microsoftoffice #microsoftsharepoint #microsofttraining #officetraining #freetraining #trainingvideos #contentmanagement #enterprisecontentmanagement
...

12 0

Recent Posts

  • Course: Microsoft 365 Certified Teamwork Administrator
  • Audience Targeted Searches in Modern SharePoint Online
  • SharePoint Thing a Day – 073 – SharePoint Information Architecture. Content Type Hub Gotchas
  • SharePoint Thing a Day – 072 – SharePoint Information Architecture. The Content Type Hub
  • SharePoint Thing a Day – 071 – SharePoint Information Architecture. Content Type Management Options.
MCT 2020-2021
Microsoft Teamwork Administrator Associate
Joel's Acclaim Profile
Joel's Microsoft Profile

Tags

Administration Architecture Certification Cloud Development Information Architecture intranets MCP Microsoft Microsoft Architecture Microsoft Azure migration Mobile Development MOSS MOSS 2007 office365 Office 365 Office 365 PowerShell SaaS SharePoint SharePoint 2010 SharePoint 2010 Training SharePoint 2013 SharePoint Administration SharePoint Administrator SharePoint Architecture SharePoint Designer 2010 SharePoint Developer SharePoint Development SharePoint Online sharepointonline SharePoint Search SharePoint Training SharePoint Videos Silverlight SOA Solution Sandbox SPThingADay TechEd 2007 Training Videos Visual Studio 2010 Windows Phone 7 WSS
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

Copyright © 2020 Joel Jeffery, SharePoint Architect