This guide shows you how to change a data source in an Excel report. This guide is of great use if you have received a report with a colleague's connection.
This guide is only of relevance if you have an Azure POS or Promotion cube.
In this guide we will go through how to change your data connection in Excel:
🔴OBS! If you are changing from an iPOS to an Azure cube, you cannot use this guide, as the two cubes are structured differently. This can only be used to change a data source on azure cubes.
If you have received a report in Excel with a connection to your Azure cube, you can change the connection string to your own. Making it easier for you to update your PivotTables. Then you do not need to Sign in with another account each time you refresh your report.
Before you can change the connection string in the PivotTable, you must make sure, that you have established a connection to your Azure cube.
If you need a guide that shows you how to connect to an Azure Cube, please see our guide How to connect to an Azure cube in Excel
👍Pro tip: Due to the security of Azure cubes, you can only refresh PivotTabels if you have been created on the Azure cube. If you are not created on the Azure cube, please contact support@effectmanager.com to be created as a user.
Changing a single data source in Excel
If you only have one or a few PivotTables in Excel, you can use this method to change your data source in the PivotTable.
Open the Excel file that you want to change the connection in and press the PivotTable, so the Toolbar will show the tabs PivotTable Analyze and Design.
1. In the Tab list press PivotTable Analyze.
2. In the ribbon, press Change Data Source, then select Change Data Source.
3. In the pop-up window, select Choose Connection.
4. In the next pop-up window, select the existing data connection from the list and press Open.
If you have not made a connection to your Azure Cube it will not be shown on the list, please see our guide How to connect to an Azure cube in Excel
5. You are sent back to the previous pop-up window. The selected connection name is shown. Finish by pressing OK.
Save your file, and your connection is now saved in the file for the next time you need to update the file.
Change multiple data connection in Excel
If you have multiple tabs and multiple PivotTables in Excel, you can use this way of changing your data source connection in the workbook.
🔴OBS! Be aware to only make one connection in each Excel Workbook to use this way of changing the data source connection.
Add new PivotTables by using Existing Connections in the Data Tab in Excel instead of creating a new connection for each PivotTable.
Open the Excel file that you want to change the connection in and in the Toolbar press Data.
1.In the ribbon, press Queries & Connections, to open to the sidebar.
2. In the sidebar you can see the connection made in the Excel Workbook. Right-click on the connection and then click on Properties.
3. In the Connection Properties pop-up, go to the Definitions tab and click Browse to open your existing connection sources.
👍Pro-tip: In the Used In tab, you can see which PivotTables in the workbook the connection is used in. Make sure to change the connection for all PivotTables if there is more than one connection in the Excel workbook.
4. Clicking Browse, will open the Data Sources made on your computer. Select your data connection by double-clicking the source or selecting it and clicking Open.
NB! If you have not made an Azure connection with your user, you will need to do this. See our guide How to connect to an Azure cube in Excel
5. When you have selected your connection to the Azure cube, finish by clicking OK. In the pop-up, you will be asked to confirm that you are changing the data source, do this by clicking Yes.
Your connection will update and is now changed. Remember to save the Excel file to save your data connection in the file.
Download our printer-friendly guide here