Before we move any further, we need to verify the following prerequisites to support this demo:
- Secure Store Service Application should be created and configured properly.
- Required Application ID should be created in Secure Store Service, which allows the authentication for Excel Service over SQL Server Databases.
- Excel Service Application should be created and configured properly.
There must be at least one Data Cube hosted on SQL Server Analysis Server (SSAS) for consuming the data. Before proceeding any further we need to ensure that all the prerequisites are in place-
- Validate Secure Store Service & Application ID
Go to Central Admin => Manage Service Application.
Click on “Secure Store Service”.
![]()
Check for Application ID that we want to use for Excel Services.
In this case, we have created Application ID by the name “Excel Services”.
- Validate Excel Service Application
Now Go to Central Admin => Manage Services on Server.
Look for “Excel Calculation Services” and make sure it is also in started mode.
Again Go to Central Admin = > Manage Service Application.
Look for “Excel Services” and make sure that it is in Started mode.
Click on Excel Services Application.
Then, click on Global Settings.
Scroll Down to the the bottom and look for Target Application ID. It should be set to the same Secure Store Application ID (Excel Services) that we created in earlier steps.
- Validate SQL Server Analysis Server
Login to SQL Server Analysis Server instance and make sure that we have at least one Data Cube hosted on SQL Server Analysis Server that can serve the data to Excel Service.
A valid Data Cube should look like the following.
Demo:
Launch the Excel Client Application and choose Blank Workbook.
Under Data Tab, select “From Other Sources”. Then, select “From Analysis Service”.
On the Data Connection Wizard Dialog, enter SQL Server Analysis Server instance name.
Click Next.
On the next screen of Data Connection Wizard, choose the required Cube.
Click Next.
On the next screen of Data Connection Wizard, click on Authentication Settings.
On Excel Services Authentication Settings Dialog, choose None and click OK.![]()
On the Data Connection Wizard, click Finish to end the Connection Wizard.
On the Import Data Dialog, choose any option you like. Here, we are choosing “PivotTable Report”.
Choose Pivot Table Fields that suit the report you need.
In this report, we are presenting Internet Sales that can be filtered for State. So select “Internet Sales Amount” as aggregator. It shows the sum of values in “Internet Sales Amount” column.
In order to filter the values, we can add Slicers that can help to filter out the records based on the column selected as Slicer.
From the Top Ribbon, click on Insert Slicer.
On the Insert Slicer Dialog, select State Province and click OK.
Now, we can select any value of State Province from the Slicer and can see the filtered value of Internet Sales Amount, corresponding to the selected State Province.
Now let’s save the sheet to SharePoint.
Go to File Menu => Save As. Choose SharePoint Document Library and specify the name of the File and click Save.
Now, browse the SharePoint Document Library.
Click on the file to render it in browser, using Excel Services.
Click Yes on “Query and Refresh Data” Pop Up. This option allows the sheet to re-query the updated data based on the connection we set-up within the Excel, and ensures the data freshness all the time.
Hope you find this helpful.