![]() ![]() In the drop down list on the Show tab, select the option called “Items in the Workbook”. Select the “Excel Services Options” button. Give to spreadsheet a name, but don’t click save yet, there are some additional options that need to be configured. The spreadsheet will automatically attempt to save back to the document library from where it was initiated. This will open the “Save As” dialogue box with some additional options. ![]() ![]() To do this, select the “Pearl” in to the top left hand corner of Excel, navigate down the list to an option called “Publish” and then select “Excel Services”. Now that the Pivot Table is complete, we need to publish it to Excel Services running on Sharepoint. Once you are done, it should look something like this: I am assuming that you know how to do this, otherwise why are you reading this blog. Using the “Pivot Table” wizard in Excel, build a Pivot table to display some data. In this case we want a “Pivot Chart and Report” and the location can be any cell in the current sheet. Step 5: The final part of the wizard is used to determine how and where you would like to display the data retrieved from the database. Select “OK” to close this dialogue box and then select “Finish”. “None” can only be used if the data is already in the spreadsheet itself. The default option is NTLM, but the SSO (Single Sign-on Service) can also be used if it has been configured. Step 4: Once the Pivot Table is published to Excel Service in Sharepoint the authentication will be done server side. Select the button at the bottom of the screen called “Authentication Settings”. What we need to do now is look at how the users will be authenticated. ![]() Step 3: Note that the connection string will be stored as a ODC file and will be automatically uploaded to Sharepoint when we publish. Step 2: Select the database of your choice from the drop down list and then select the table or view that will be the source. Step1: Specify the SQL Server name that you will be connecting too and the Log-on credentials. Once you selected the option, a wizard will start the will guide you through the rest of the process. I will therefore be using a more realistic example like a SQL Server database. There is a whole range of data source options to choose from and in a perfect world everyone would be running some form of BI cube on “Analysis Services”, but unfortunately that is not the case. Once the spreadsheet has opened in MS Excel, navigate to the data tab on the ribbon and select “From Other Sources”. To simulate a proper use of a “Pivot Table” in an enterprise environment I am going to use a back end database as the source of the spreadsheet. Create a “New” spreadsheet from the document library Create a Document Library to store the spreadsheetģ. The initial steps would therefore be as follows:Ģ. With the advent of “Excel Services” in MOSS 2007 Enterprise edition, we can now use pivot tables in Sharepoint, running server side.īefore we can view a pivot table embedded as a web part in a Sharepoint site, we first have to create one in MS Excel 2007 and then save the spreadsheet into a document library. Initially it only formed part of Excel, but due to it’s popularity it was eventually converted to a “Service” that could be used in Word, Access and many other applications. Pivot Tables are one of the most powerful features that Microsoft has ever developed for MS Office. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |