This article explains how to create a reports in Excel which can update automatically from the live data over the web.
"Live-linked" reports can be based on reports you have created in Analytics or based on Views or pivot reports you have created in the Backoffice.
In steps 1 and 2, this article deals with the detail of with creating reports from Backoffice views. However, the same principles apply to creating live-linked reports from pivot reports and from Analytics, so Step 3 is the common to all.
- In Analytics: just right-click on the Live link menu option button at the bottom of your report and choose the option to Copy link address/shortcut to obtain the link you need
- For a Pivot report in the Backoffice: right click on the Excel icon in the pivot report list and choose the option to Copy link address/shortcut to obtain the link you need
- From Backoffice views: See steps 1-2 below
Step 1 - Create the view you want to export
If your security settings permit, there is an option to customise the columns of data on both the Vacancies and the Candidates page.
Select the Create view...option available at the bottom left right corner of the page (under Views).
If you do not have rights to create views, contact Advorto support or your super user.
Create a view with all the data you need for your report.
Step 2 - Select additional parameters to display the relevant data
Now that you've got the columns you want, you need to make sure that you're looking at the data you're interested in. You can use some or all of the options below to adjust the data returned:
- Select a preference
Select the relevant preference from the top-right drop-down list. The eventual report will only included data that is available to view within the selected preference. You may need to set up a new preference if the existing options are not what you require. If you do not have access to do this, contact your superuser or Advorto support.
- Select a candidate type
For candidate-based reports, you can select Unsubmitted, Submitted, Archived or All. N.B. All will display both Submitted and Archived candidates, but not Unsubmitted.
- Select a folder
Open the folder bar on the Vacancies or the Candidates page and select a specific folder if you're only interested in vacancies/candidates at a particular status. If you want all vacancies/candidates, select the relevant All option. N.B. For candidate-based reports, multiple folders can be selected by creating a search (see below).
- Select a search
On the candidates page, you can also select or create a specific search to filter the candidates by one or more specific parameters. The saved searches appear below the list of folders. If you do not have access to create saved searches, contact your superuser or Advorto support.
Click the Excel icon located next to the global search in the top right corner on Candidates or Vacancies page. The pop-up should open with a link.
Right click on the link and select Copy link address. You can also highlight the link and select Copy from the menu.
Step 3 - Export the data to Excel
Once you've copied the link to your clipboard, open your (new) Excel document and in the main menu, go:
Excel 2003: Data > Import External Data > New Web Query
Excel 2010 - Office 365 Data > From Web
Paste the shortcut you copied into the Address field and click Go.
IMPORTANT: You will be prompted to enter log in details - please use the same credentials you use for the back office. You might be asked to enter the log in twice - please follow the instructions.
You should see the data from your view in the window as indicated below:
Click the second yellow arrow to select the data (in the Ref column heading cell above) - the yellow arrow will change to a green tick.
Click Import to import the data.
Note that you may see some message and warning screens appear, e.g. whether you want to view the certificate or whether you want to display only the secure data, etc.
Once past these screens, you will be asked where you want the data to be displayed. Choose cell A1 in your Existing worksheet, unless you specifically don't want the data to be entered here.
You should now see the data in your Excel worksheet and can Save and Close if you wish.
Step 4 - Updating the data
Open the Excel report and just right click anywhere in the data.
Select the Refresh Data (red exclamation mark icon) option at the bottom to update the data.
Note that you must be connected to the Internet to be able to update the data.
Alternatively, if you turn on the External Data toolbar in Excel (View > Toolbars > External Data), you can also click the Refresh or Refresh All icons in the toolbar to update the data.
1. Refreshing data on file open
You can also set the report to refresh automatically each time you open it.
To do this, higlight a cell within your data and go back to the Data menu in the main menu bar and go: Data > Import External Data > Data Range Properties. (See screenshot below.)
Check the Refresh data on file open box if you want the report to update automatically each time you open it.
2. Add additional columns of data calculated from the columns in the raw data.
e.g. for a time vacancy open calculation, you might want to do Closing minus Opening date.
Tick the Fill down formulas in columns adjacent to data box on the Data Range Properties screen.
Now you can enter a new column heading in the top cell of the column to the right of your raw data and enter a formula in the cell below, e.g. =C2-B2.
Note that you only need to add the column heading and the first calculation.
The next time you refresh the raw data, the additional calculated column will also fill down with the relevant value in each cell for each row.
This function can be used to calculate or display a wide variety of additional information, based on your raw data.
Some useful examples are given below.
- Subtract two dates to get the number of days difference between the dates:
- Divide the above by 7 to get the number of weeks:
- Display a column showing only the month, e.g. Oct, from a full date field:
- Join two cells into one cell - e.g. to get Full name from First name and Surname:
= B2&" "&C2 or =CONCATENATE(B2," ",C2)
N.B. " " puts a space.
- Display the first part of a value from another cell, e.g. where a | character is used to split a hierarchical list, such as Division X | Sub-division Y and you want to display just Division X:
= LEFT(A2, SEARCH(" | ", A2)-1)
Display True or False if a date is within a certain range, e.g. where date1 and date2 are your date range:
N.B. date1 and date2 could also be references to other cells.
Display a date-time stamp (eg 13/10/2009 00:00:00) as a date in another column:
Calculation to identify duplicate entries or to count unique entries only (e.g. in a cumulative report)
A is the data you want to use as the unique identifier (e.g. candidate email, vacancy id)
B is the column you want to pick up duplicate entries in (e.g. folder move, vacancy status change).
N.B. This formula assumes that your raw data is ordered by the column you are using as the unique identifier and that a duplicate entry is unlikely to occur in more than 100 rows below the first occurence.
Care when editing views and reports with associated live-linked reports
When you edit reports or amend views that are used for reports in this way, take care, as you may end up breaking your reports, particularly where you have used the Fill down formulas in columns adjacent to data feature.
You may need to amend your Excel report to reflect the change you made to your view/report in the system.