Beginning SharePoint With Excel - From Novice To Professional (2006)
.pdf
x ■C O N T E N T S
Creating Interactive Web Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Publishing and Using the Spreadsheet Component . . . . . . . . . . . . . 112
Publishing and Using the Chart Component . . . . . . . . . . . . . . . . . . . 114
Publishing and Using the PivotTable Component . . . . . . . . . . . . . . . 116
Appending to an Existing Web Page . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Automatically Republishing Web Pages . . . . . . . . . . . . . . . . . . . . . . . 118
Displaying HTML Pages in SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Creating a Web Part Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Adding and Modifying the Page Viewer Web Part. . . . . . . . . . . . . . . 121
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
■CHAPTER 7 Building Out-of-the-Box Business Solutions. . . . . . . . . . . . . 127
Business Solutions Using Lists and Views . . . . . . . . . . . . . . . . . . . . . . . . . 127
The Project Issue Tracking Scenario. . . . . . . . . . . . . . . . . . . . . . . . . . 128
Building the SharePoint Issue Tracking Solution . . . . . . . . . . . . . . . 129
Extending the Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Business Solutions Using Charts and Tables . . . . . . . . . . . . . . . . . . . . . . . 139
The Sales Performance Scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Building the SharePoint Sales Performance Dashboard . . . . . . . . . 142
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
■CHAPTER 8 Using Excel to Query SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Creating a Static Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Creating a Refreshable Web Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Refreshing Query Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Modifying a Web Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Saving the Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Using Web Queries to Manage SharePoint Site Users . . . . . . . . . . . 160
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
■CHAPTER 9 Using SharePoint’s Office Web Parts . . . . . . . . . . . . . . . . . . . . . |
167 |
Using the Office Spreadsheet Web Part . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Creating a Web Part Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Adding the Office Spreadsheet Web Part to a Page . . . . . . . . . . . . . 171
Working with the Office Spreadsheet Web Part . . . . . . . . . . . . . . . . 173
■C O N T E N T S xi
Working with the Office PivotTable Web Parts . . . . . . . . . . . . . . . . . . . . . . 178
Connecting Web Parts to Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Using the Office Datasheet Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 184
Using the Office PivotTable Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 185
Using the Office PivotChart Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 187
Using the Office PivotView Web Part. . . . . . . . . . . . . . . . . . . . . . . . . . 189
Modifying Office Web Parts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Appearance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Advanced. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Spreadsheet or PivotView. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
■CHAPTER 10 Building Excel Spreadsheet Web Parts . . . . . . . . . . . . . . . . . . . |
195 |
Installing the Spreadsheet Web Part Add-In . . . . . . . . . . . . . . . . . . . . . . . . 196
Custom Web Parts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Creating a Simple Custom Web Part . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Setting up the Excel Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Using the Spreadsheet Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Protecting Your Web Part . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Adding the Web Part to a Web Part Page. . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Adding Your SharePoint Site to My Network Places. . . . . . . . . . . . . 205
Importing the Custom Web Part. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Creating a Web Part That Returns a Data Set . . . . . . . . . . . . . . . . . . . . . . . 209
Creating the Data Retrieval Service Connections File . . . . . . . . . . . 210
Formatting and Saving the XML Spreadsheet. . . . . . . . . . . . . . . . . . 214
Creating and Importing the Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 214
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
■APPENDIX A Creating and Using Excel Lists. . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Creating a New List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Entering Data into a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Redefining the Columns in a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Deleting List Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
xii ■C O N T E N T S
Using Database Features and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Sorting and Filtering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Adding Totals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
When Lists Don’t Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Converting a List to a Normal Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
■APPENDIX B Mapping Excel Spreadsheets for XML . . . . . . . . . . . . . . . . . . . . 225
XML Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Opening XML Files in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Mapping an XSD in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Adding an XSD File to a Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Adding Fields to the Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Importing XML Data Using the Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Exporting XML Data Using the Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
■APPENDIX C Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
General SharePoint Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
MSD2D. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
The Boiler Room—Mark Kruger, SharePoint MVP . . . . . . . . . . . . . 236
SharePoint Portal Server Frequently Asked Questions . . . . . . . . . . 236
SharePoint Hosting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
SharePoint Training Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
MindSharp. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
SharePoint Experts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Microsoft SharePoint Portal Server 2003 Training Kit . . . . . . . . . . . 237
Excel and SharePoint Add-Ins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Spreadsheet Web Part Add-In for Microsoft Excel . . . . . . . . . . . . . . 238
Microsoft Excel XML Tools Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Microsoft SharePoint Sites Worth Noting. . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Microsoft SharePoint Products and Technologies . . . . . . . . . . . . . . 238
Microsoft Applications for Windows SharePoint Services. . . . . . . . 238
Microsoft SharePoint Products and Technologies Team Blog. . . . . 239
■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
About the Authors
■GINI COURTER is a partner in TRIAD Consulting, a technology consultation and training company. Gini’s expertise in technology planning, information architecture, and database design, and her ability to synthesize and communicate this expertise, is invaluable to the varied clients she serves. From Fortune 1,000 companies to small businesses and not-for-profit organizations, Gini has proven over and over again that technology can effectively solve business problems as long as the humans designing it understand the needs of the business.
■ANNETTE MARQUIS is also a partner in TRIAD Consulting. Annette has more than 15 years of executive management experience in health care and nonprofit organizations, and 10 years of experience providing software consultation, training, and technical writing to a wide variety of businesses. She offers expertise in the practical uses of computers in business settings and in designing and providing effective, customized staff training and documentation of software applications and solutions.
Gini and Annette are coauthors of more than 25 books on Microsoft Office and related software topics, including Mastering
Microsoft Office 2003 for Business Professionals (Sybex, 2004) and Mastering Microsoft Office XP (Sybex, 2001). This is their first book with Apress.
xiii
About the Technical Reviewer
■ALEXZANDER NEPOMNJASHIY works as a Microsoft SQL Server DBA with NeoSystems North-West Inc., an ISO 9001:2000–certified software company. As a DBA, he’s responsible for drafting design specifications for solutions and building database-related projects based on these specs. As an IT professional, Alexzander has more than 11 years of experience in DBMS planning, designing, securing, troubleshooting, and performance optimizing.
xv
Acknowledgments
For making this book happen, our hats go off to Jim Sumser. Jim was the very first acquisitions editor we ever had—Gini worked with him on her first book, The Learning Guide to Windows 95 (Sybex 1995)—but soon after that, he went on to find fame and fortune in other circles. We are thrilled to be working with him again after all these years. Thanks, Jim, for agreeing to put up with us again!
We would also like to thank all the great people who worked on this book. Our special thanks to senior project manager Kylie Johnston, who stayed with us in the dark days between submissions. We appreciate your support and patience. Susannah Pfalzer, our copy editor, is responsible for making our meaning clearer and our sentences smoother. Thanks for all your attention to detail. Speaking of detail, we want to thank Alexzander Nepomnjashiy for his attention to technical detail. He helped keep us on target and made sure we didn’t miss a critical step along the way. Thanks, Alexzander.
We know that we have just scratched the surface here in terms of the number of people who have been involved with this book. We extend our heartfelt thanks to them all.
We would be remiss if we didn’t thank Leonardo Brito, senior IT analyst for Valassis, Inc., for all his help as we learned the ins and outs of SharePoint. He was invaluable as we worked with him on implementing a corporate-wide SharePoint portal and innumerable Windows SharePoint Services sites to departments and teams throughout the company. Muchas gracias, Leo!
xvii
Introduction
Whether you work in a large multinational corporation or in a tiny family-owned company, you have at least one thing in common: it’s a challenge to secure IT resources to solve common everyday business problems. In a large corporation, resources are typically spent on the big projects that have a significant cost benefit to the company. When you make a request for assistance, you’re probably called upon to justify the business value of your request. If you aren’t planning to save the company millions of dollars, you’re out of luck. At the other end of the spectrum, all a small company might know about IT is that it was a member of the cast of the Addams Family TV show.
Although initial installation of Microsoft SharePoint Portal Server requires administrative expertise, even a company with no technical staff can buy hosting from a SharePoint hosting company (see Appendix C), and in minutes have a Windows SharePoint Services site for its team. As we’ve delved deeper and deeper into the possibilities that SharePoint offers, one thing has become crystal clear: a high-end user within a department can develop highly sophisticated SharePoint sites, business dashboard, and data sharing systems without a lick of programming. This puts tremendous power in the hands of end users and IT business analysts who want quick, easy-to-implement solutions for their users.
Who This Book Is For
This book is intended for IT professionals and for business users who see the benefits of electronic collaboration and want to maximize the tools they already have available. Most books of this type are written for programmers and require a high level of technical knowledge. Because there’s so much you can do with SharePoint and Excel without programming, we intentionally excluded techniques and solutions that require programming. Our intention was to make this book accessible to those high-end users who aren’t programmers but who, with the right level of knowledge, can still make a big difference in solving business problems in their organizations. In other words, even if you don’t know the difference between XML, SQL, and .NET, you can create powerful team collaboration solutions with SharePoint and Excel.
How This Book Is Structured
To work with SharePoint and Excel effectively, you’ll sometimes be working primarily in |
|
Excel and other times primarily in SharePoint. Deciding what make sense given what you |
xix |