Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
The Excel Team Blog » All Tags » Excel Server (RSS)

Browse by Tags

  • Using Excel & Excel Services with SQL Server Analysis Services 2008

    Today's author, Pej Javaheri, a Product Manager on the SharePoint team, talks about configuring Excel Services to work with the just-released SQL Server 2008. With the recent announcement of SharePoint Server 2007 supporting SQL Server 2008 , like you, I was excited to setup my Excel / Excel Services environment to take advantage of the great new capabilities available, and there are many. I encourage you to take a look at how the new SQL environment will benefit your SharePoint deployment, and...

  • Building an Asset Tracking Application in Excel Services – Part 5 of 5

    Today's author, Dan Parish, concludes his discussion of building an Excel Services solution. In Part 4 , I walked through creating the web part pages for the solution. At this point, you should have a fully functional asset tracking application up and running. The last piece, that we'll build today, is a simple web part that will allow your admin to archive all of the information they have collected. Overview At this point we now have the entire application up and running. We can collect...
    Filed under: ,

  • Building an Asset Tracking Application in Excel Services – Part 4 of 5

    Today's author, Dan Parish, continues on his discussion of building an Excel Services solution. In Part 3b I walked through creating a User Defined Function (UDF) that pulls information from a SharePoint List into Excel Services. In today's post, I will describe how to create the two web part pages that make up the actual application: the page that end user's see, and the page that the admin uses. Overview Now that we have our spreadsheet running on the server and displaying data entered...
    Filed under: ,

  • Building an Asset Tracking Application in Excel Services – Part 3b of 5

    Today's author, Dan Parish, continues his discussion on building an Excel Services solution. In Part 3a I walked through how to create the SharePoint List that stores the data users enter. In this post, I will walk through how to create the Excel Services User Defined Function (UDF) that will pull this information from the SharePoint List into Excel Services. Overview Out of the box, Excel Services does not support pulling in data stored in SharePoint Lists. However, you can add this functionality...
    Filed under: ,

  • Building an Asset Tracking Application in Excel Services – Part 3a of 5

    Today's author, Dan Parish, continues his discussion on building an Excel Services solution. Part 3 turned out to be a bit longer than we had anticipated so we've split it up into two parts: 3a and 3b. Thus far in this series, I have given an overview of the solution , and detailed how to build the Excel workbook that is the core of how it works. In today's post, I will walk through building the SharePoint List that users will input their machine information into. Overview In order to...
    Filed under: ,

  • Building an Asset Tracking Application in Excel Services – Part 2 of 5

    Today's author, Dan Parish, a Program Manager on the Excel Services team, continues on with his multi-part post about building an application using Excel Services. In my last post I gave an overview of the asset tracking application that I am going to spend the next four posts describing how to build. The core of the application is the workbook it is based on. The workbook is what really makes this application work, since it is what pulls in the data users entered, figures out who has and hasn't...
    Filed under: ,

  • Building an Asset Tracking Application in Excel Services – Part 1 of 5

    Today's author, Dan Parish, a Program Manager on the Excel Services team, will discuss over the next few posts how he built an asset tracking application using Excel Services. At the SharePoint Conference in Seattle in March, I did a presentation entitled "Leveraging Excel Services to Build Rich SharePoint Applications". The talk included a demonstration of how to build a SharePoint application that used Excel Services to solve a data collection scenario in which group administrators...
    Filed under: ,

  • Ten Tips for Using Excel Services

    A new MSDN article about Excel Services was just released. If you do any kind of work with Excel Services, whether you are just getting started with it or trying to do something a bit more advanced, this article is a pretty good read because it covers some of the more common pitfalls that we hear from customers. The following topics are discussed: Making an Excel Workbook Compatible with Excel Services Configuring External Data Connections Configuring Excel Services to Work with Kerberos Saving an...

  • New Technical Article on Excel Services

    There's a new article up on MSDN that describes a custom solution built by the IT department here at Microsoft that uses Excel Services. Rather than re-paraphrase, I'll "borrow" liberally from Shahar's description : The solution is probably one of the most advanced implementation of Excel Services I have seen to date and includes lots of ingenious customizations - the white paper, specifically, talks about how to use Excel Services as a landing pad for data and then get the...

  • Setting up Excel Services to Refresh Workbooks When Opened.

    Today's author, Pej Javaheri, a Product Manager on the Excel Services team, talks about one of the more common configuration questions we hear about from customers. One of the challenges in sharing Excel workbooks is ensuring that the right people see the right data. With Excel Services, this has become a lot easier ensuring user's only see the information they are authorized to view and not sharing information that is privileged or out of their scope. With a few simple configuration steps, workbook authors can quickly setup their reports to only display the information that is authorized by user's security privileges. This document describes how to setup SharePoint SharedServices for Excel Services, and how to setup your ODC connection to refresh workbooks on open. Ensure the server has the right permissions to access data connections, and run Excel workbooks on the server. To do this, go to the Shared Administration page of your Shared Services. First, modify the Trusted File Locations to include the locations that you will allow Excel workbooks to be accessed to display on Excel Services. In the Address field, enter the location of where the workbooks are stored. Most likely this will be in a SharePoint Document library. If you want to enable the whole SharePoint site, enter the root address of the SharePoint site, such as http://SharePoint . Under Location , ensure you select Children Trusted if you will be storing workbooks in folders from this location, especially if you are going to enable the whole site. Under External Data, select either "Trusted data connection libraries only", or "Trusted data connection libraries and embedded" to allow Excel Services to refresh data from workbooks that access data through ODC files. If you will be calling User Defined Functions (UDF), then check the box under User-Defined Functions. You may also want to disable the Warn on Refresh option under External Data Click OK to return to the Excel Services Trusted File Locations screen and apply all the changes otherwise a warning dialog box will require users to okay the action before refreshing the workbook in Excel Services. You should have something like this, which identifies the location of the workbooks that will be accessed by Excel Services. You can add as many locations as required for each shared service environment you setup. Next, we need to identify where the data connection libraries are stored for Excel Services to access and refresh data. Go back to the SharedServices administration page, and click Excel Services Trusted Data Connection Libraries, and then click Add Trusted Data Connection Library. Enter the location of the document library that the ODC files are saved in. In this example, I have added the default location under the Reports document library Creating the ODC file in Excel In Excel, select From Other Sources under the Data tab. Select the data source type and then enter the required credentials in the Data Connection Wizard. In the File Name field, select Browse and then type the location of the Trusted Data Connection Library (alternatively, you can save the ODC file locally, and the upload it to the Data Connection Library via SharePoint). If the connection requires specific authentication requirements, or single-sign on select the Authentication Settings and set the appropriate item. Now that you have the connection created, you need to modify the properties of the connection so that it is set to refresh the workbook when it is opened every time. Still in the Data tab, select Properties and then check Refresh data when opening the file. Create your Excel workbook like normal, and then publish to Excel Services. If data is coming from a relational source, publish it in a Pivot Table. And there you have it. Whenever users open the workbook, the data is automatically refreshed using their credentials to access the data source. Read More...

  • Handling Workbook Dependencies using Excel Services

    Today's author, Sanjay Kulkarni, a Program Manager on the Excel Services team. We have heard from several customers about scenarios where they have workbook dependencies. So the output of one workbook is used in other workbooks for calculations. The dependencies could be a result of the business model meaning different people in the organization being responsible for the workbooks or it could simply be a way of isolating related logic in different workbooks. In this blog post we will look at a sample way of how these workbook dependencies can be handled by writing a little code using Excel Services Web Services API and SharePoint object model. Note that you will need MOSS 2007 with Excel Services configured to use what is described here. To simplify things let us look at a scenario with two workbooks. At a high level workbook one (Rates.xlsx) has mortgage rates that are refreshed from the database. This workbook also adds the overhead and calculates the retail rate that can be offered to customers. The second workbook (Payments.xlsx) has a list of potential customers and information about them. The idea is to calculate the first workbook to get the retail rates and then use these rates to calculate the mortgage payment for the customers in the second workbook. We will use document properties to pass values from the Rates workbook to the Payment workbook. The Rates workbook has a Pivot Table that has interest rates and a cell that stores the margin. To make the sample work independently we have removed the connection to the external database and instead we get the data from another sheet in the workbook however that can be easily changed. Here is what the Rates workbook looks like. 0.75 Margin above the wholesale rates Values Row Labels Interest Rates Retail Rates 1 Year 4.5 5.25 3 Year 5 5.75 30 Year 5.875 6.625 5 Year 5.25 6 7 Year 5.5 6.25 The Payments workbook has information about the clients, their mortgage as well as cells where the retail mortgage rates can be updated. Mortgage Type Interest Rate 1 Year ARM 5 3 Year ARM 5.5 5 Year ARM 5.75 7 Year ARM 6 30 Year 6.375 Client Name Email Id Loan Type Interest Rate Loan Term in Years Principal Monthly Payment Jack jack@contoso.com 3 Year ARM 5.5 30 $350,000 $1,987.26 Judy judy@contoso.com 5 Year ARM 5.75 30 $400,000 $2,334.29 The following sections describe the steps involved with some code snippets. The complete code and the workbooks can be downloaded at the bottom of this post (see attachments). This is a console application and accepts the margin rate added for overhead as a parameter. We use a fixed constant in the workbook if no margin is specified. To simplify things I have omitted the error handling for the most part. You will definitely need to add robust error handling if you are planning to deploy it for a practical application. First print the margin rate and some initialization if (args.Length > 0) Console .WriteLine( "Margin rate is = " + args[0] + "%" ); // Instantiate the Web service and create a status array object. ExcelService es = new ExcelService(); Status[] outStatus; string ratesWorkbook = "http://server-name/Site/doclib//Rates.xlsx" ; // Set credentials for requests es.Credentials = System.Net.CredentialCache.DefaultCredentials; Now we open the workbook and set the margin rate in the Rates workbook, refresh data and calculate the retail rates. // Open the Rates workbook, get new rates by refreshing the external data and // calculate. string sessionId = es.OpenWorkbook(ratesWorkbook, "" , "" , out outStatus); //Set the input overhead margin rate if specified omcommand line. if (args.Length > 0) { es.SetCellA1(sessionId, "RatesSheet" , "Margin" , args[0]); } // Refresh data and recalculate the workbook. es.Refresh(sessionId, "" ); es.CalculateWorkbook(sessionId, CalculateType.CalculateFull); Now that the retail rates are calculated; we get the retail rates and set them as document properties on the Payments workbook. The advantages of using the document properties for storing values are firstly the properties are associated with the document so if you update the workbook they will still be preserved. Secondly since they are stored by SharePoint the process of calculating rates and payments can be done at different times if needed by the business process. //Get the calculated rates from the workbook double rate1Year, rate3Year, rate5Year, rate30Year; valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate1Year" , false , out outStatus); rate1Year = Convert .ToDouble(valObj); valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate3Year" , false , out outStatus); rate3Year = Convert .ToDouble(valObj); valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate5Year" , false , out outStatus); rate5Year = Convert .ToDouble(valObj); valObj = es.GetCellA1(sessionId, "RatesSheet" , "Rate30Year"...

  • Example file for PivotTable / Data Validation Trick

    Recently I posted an article discussing how PivotTables can be used on Excel Services to mimick the Data Validation feature . The author, Dany Hoter, sent me a copy of the file he used in the example screen shots and I never got around to posting it for our readers. Here's a short description of the example file from Dany: The example implements a scenario of calculating the pricing for car insurance based on the details entered in a form. The form can be collected on paper and entered manually into Excel or collected online and fed into Excel services to perform the calculation. Each value is connected to a numeric value. High values means less risk to insure this car to this driver. More driving experience, safer car, better neighborhood etc. Of course all data is completely fake and does not represent any real data. Each field might have a different number of possible values, some have only Yes/No and some might have 10 different values. All list of values where extended to the last row so to avoid blank appearing as an option for input. We can’t really prevent the user from choosing multiple options or the all option so an error message is displayed when the user does that. The file can be downloaded from here . Read More...

  • A PivotTable Trick That Brings Data Validation to Excel Services

    Excel has a feature called Data Validation that controls the possible values a user might enter into a cell or a range of cells. As you can see there are many options for validating data entry. The most popular is probably validating against a list of values. The list can be included in the validation definition or can be a region in the sheet referenced from the dialog. Recently I saw an example created by a customer that validates input using a PivotTable instead of data validation. The idea is to use only the report filter area of the PivotTable and to use the values selected in multiple filter fields as inputs for a model: In the example you see multiple input fields and when clicking the filter icon the user will see a list of values and can choose one. The advantage of this method is easier maintenance of multiple lists of values used for validating multiple fields. The range that the PivotTable is based on looks a bit odd because usually PivotTables are based on rectangular ranges...

  • Replacing OWC Reporting with Excel Services

    Today's author: John Campbell, a program manager who works on the Excel Services teams. Replacing OWC Reporting with Excel Services As a Program Manager on the Excel Services team, I often field emails from people who have a thin reporting solution that is based on Office Web Components (OWC). They would like to migrate that solution to Excel Services, but aren't sure how or what the pitfalls are. In this entry I will talk about what OWC functionality Excel Services can be used to replace...

  • Integrating Excel (Services) and PowerPoint with the Microsoft SDK for Open XML Formats

    Today's author: Danny Khen, a program manager who works on both the Excel and Excel Services teams. What is this about? Office 2007 introduced new default file formats – Open XML. Excel, Word and PowerPoint take advantage of them. One of the nicest things about those file formats is that they enable easy manipulation of Office documents without needing the Office applications up and running – by simply modifying XML “parts” within the files. In June 2007 we published an SDK that facilitates this...
1 2 Next >
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.