Note: This cannot be done on the DBeaver Community edition. You need to use DBeaver Enterprise Edition. Before starting, make sure you are logged in to your BOX drive.
- Copy the code for the query that you want to use to generate the report.
- On DBeaver, on the tabs at the top, select Database – Tasks – Create new task
3. On the screen that opens, enter a name for the task (and a description, if you like), select ‘Data Export’ and then click on ‘Next’.
4. On the Next screen, click on ‘Add Query’
5. Choose a source table (in this example, it is ldp_cornell) and click on ‘OK’. It’s also good to check the box for ‘Show connected databases only’ so that you can confirm you are connected to the database you are specifying.
6. The next screen that shows up will be a blank one that says ‘SQL Preview’ at the top. Paste your query into the space and click on ‘OK’.
7. For the Data export screen that follows, click on ‘Next’. You can also edit your query from this screen.
8. On the ‘Export target’ screen that follows, make sure you choose XLSX format, and then click on ‘Next’.
9. On the ‘Extraction settings screen’ that follows, the ‘extract type’ should be ‘Single query’, and then click on ‘Next’.
10. On the Format settings screen that follows, click on ‘Next’, unless you want to change some settings.
11. The ‘Output’ screen is where things can go WRONG, so please check your settings carefully.
a. Directory – this is your BOX folder where the report will be sent
b. File name pattern – the default is the last file name, so make sure you update this each time
c. File name conflict behavior settings – this setting is almost hidden. Make sure you choose ‘Overwrite’, else each day’s updated report will land in your folder with a different filename, in the form of numbers appended to the end of the file name. (Do not select ‘Overwrite’ if you need to keep individual daily files.) Overwriting is vital if you are using these EXCEL files as input for a Tableau dashboard.
12. On the following ‘Confirm’ screen, click on ‘Proceed’.
13. Now you will be brought back to the main DBeaver screen from which you run queries, and on the bottom right, you should see the ‘success’ message.
Your report has been created and sent to your BOX folder, please make sure it is there.
14. Next, you need to set up a daily update of this task. On your main Dbeaver page (from where you run queries), right-click on the task you just created. (If you can’t see your tasks, click on the ‘Window’ tab on the top panel in DBeaver, and select ‘Database Tasks’.)
If you cannot access Scheduler, please ask Desktop Services to enable this functionality.
Right-click on your task and select ‘Scheduler’ –‘Schedule task’.
15. Choose the frequency and start time and click ‘Schedule’.
16. Right-click AGAIN on the task, and this time you will see a somewhat different menu. Select ‘Scheduler’ – Open scheduler settings’.
17. Now you will see the Task Scheduler view. Click on the drop-down under Task Scheduler (left pane), and click on ‘DBeaver’. In the Middle pane, you will see a list of DBeaver tasks. Right-click on the task you want to schedule, and choose ‘Properties’.
18. Now choose ‘Run whether user is logged on or not’ and click on ‘OK’.
By doing this, your task will run as long as the computer is running, whether you are logged on or not. Remember that the computer has to be ON.
19. You will be prompted for your netid password, which you need to enter.
20. Once you click ‘OK’ you are all done. The screen will revert back to the previous one, and you will see a greyed-out option that shows it will run whether you are logged on or not.
21. You can now close the scheduler window.
22. Some useful checks to make on a regular basis:
- Make sure you are logged into BOX from the computer that hosts your DBeaverEE.
- Check your BOX folder to make sure the files are being updated according to your schedule.
23. You can DELETE or EDIT your task anytime from the Database Tasks pane, by right-clicking on the task.