docs.healthdata.beAt the end of each year, a number of steps need to be taken to ensure the monthly SLA reporting continues to run automatically and uninterrupted during the following year. These steps have already been completed to prepare for the year 2024 but will need to be followed for all subsequent years.
STEP ONE - Create a new folder for the new year
Create a new folder in the following location on Dropbox as shown below:
HD_Reports\HD_Service_Level_Report\HD_Service_Level_Report_MSA_Year_Month
The folder should have the same naming convention as for others.
For example, in 2025 the folder should be named: HD_Service_Level_Report_MSA_Monthly_2025
STEP TWO - Copy and rename Excel, Word and Powerpoint files
Navigate to the following folder on Dropbox:
HD_Reports\HD_Service_Level_Report\HD_Service_Level_Report_MSA_Year_Month\Calculations
Copy the Excel, Word and Powerpoint files for the current year.
At the end of 2024 these will be called: HD_Service_Level_Report_MSA_Monthly_2024
Rename these three files by adding a suffix with the following year as shown below.
STEP THREE - Update newly created Excel and Powerpoint files
We need to replace all references to the current year in these two files with references to the following year.
We also need to delete the 50 graphs in the Powerpoint that are linked to the current year's Excel, and replace them with graphs that are linked to the newly created Excel for the following year.
This process was detailed in Steps 3 to 7 of the equivalent documentation for the weekly SLA reporting that can be found here: https://docs.healthdata.be/documentation/weekly-automated-publication-summary-report-service-level-committed-master-service-55
Follow the same steps to update and link the monthly files together.
There is one difference to be aware of: the weekly Excel file contains only one graph whereas the monthly Excel file contains two graphs and a table.
The larger of the two graphs (found at the bottom) should be copied to the Powerpoint. The other graph and table is for the Word document.
STEP FOUR - Open Word document and break existing links
Open the newly created Word file. If prompted to update links, select "No".
We want to break all existing links as they point to the Excel file from the current year.
Click the "Edit Links to Files" button displayed under the taskbar. If this button is not immediately visible, you can right-click on the taskbar, click "Customize the Ribbon" and place it there yourself.
In this pop-up, you will see a list of all the external links - this represents every graph and table in the document.
All these links must be broken - approximately 100 in total.
Make sure all links are highlighted, then click "Break Link", and then click "Yes" - as shown below.
STEP FIVE - Copy graphs and tables from Excel to Word document as links
We need to delete the 52 graphs and 52 tables in the Word document which are currently linked to the Excel for the current year (e.g. 2024) - and replace them individually with graphs and tables that are linked to the Excel file we just created for the following year (e.g. 2025).
Each tab in the Excel corresponds to a section in the Word document. You can match them up by looking at the name of the Excel tab, and the header of the corresponding page in Word. The table of contents also lists these headers along with their page numbers.
We will go through the document and replace each graph/table in sequence. To ensure the document is easy to read, each new header should be moved to the top of the next page. In the example shown below we start with the first indicator - HDI00001.
FOR GRAPHS
There are two graphs contained in each Excel tab, for the Word document we must use the smallest graph.
Select the graph, right-click, and click "Copy" as shown below.
Then navigate to the appropriate section in the Word document. It should be pasted just below the header.
As we want to keep an automatic link between the two documents, we must use "Paste Special" (ctrl + alt + v).
Then select "Paste link", click "Microsoft Excel Chart Object" and click "OK" as shown below.
FOR TABLES
Highlight all cells in the table (3x13) as shown below, right-click and click "Copy".
Then navigate to the appropriate section of the Word document. It should be pasted just below the graph that was just created.
Again we use Paste Special, select "Paste link", select "Microsoft Excel Worksheet Object" and click "OK" as shown below.
STEP SIX - Check the newly created links
You can check that all the links have been created correctly by again going to the "Edit Links to Files" button.
There should be a total of 104 links - two for each HDI number. If everything was copied in the right order, the first two under "Item" should begin with HDI0001 and then proceed sequentially until HDI00052.
Also ensure the "Automatic update" option is checked.
If a link is missing or appears more than twice, go to the relevant page and check, repeating the steps above if necessary.
STEP SEVEN - Change all references to the current year
Use the replace function (ctrl + h) to replace all references to the current year (e.g. 2024) with references to the following year (e.g. 2025).
Then save the document and close.
STEP EIGHT - Update the Excel file with the Sciensano office closure days
If not done so already, update the Sciensano_office_closure_days Excel file.
Public holidays and bridge days for the following year need to be added to the document - following the instructions detailed in the first tab.
This file can be found at the following location: HD_Reports\HD_Service_Level_Report\Input_Files
