External Excel Query via API (Office 365 and Other Supported Excel Versions)
Overview
Paradigm gives you the freedom to import your report to an external data visual analytics platform such as Excel to help your business or institution make more meaning and see data from your perspective. This is also very helpful in complying with your reporting requirements as an institution and to the government. This knowledge article will help you generate External Excel reports with data from Paradigm via an external connection.
Assumptions
The Excel report has been saved and tested in Report Builder;
Report parameters have been pre-determined in a report builder report;
An understanding of how these reports work in Excel; and
Supported version: Microsoft Office Professional 2016/2019, Microsoft Office Professional Plus 2016/2019, Microsoft Office 365.
Key Terms and Concepts
The report connection will by default use the logic as defined by the filter clauses within Report Builder. For example, if there is a check on a specific program ID then that check will be applied to the results of the report data returned to Excel.
Excel - Excel is a software program from Microsoft that is part of the Microsoft Office suite of productivity software developed by Microsoft. Excel is capable of creating and editing spreadsheets.
Pivot tables - Pivot tables are tables of statistics that summarise the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program). This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.
Query - A request to a server (typically a database) for information.
Parameter - A single part of the information is sent as part of a query to differentiate it from other requests. If a query is “Please supply me with a student” a parameter might be “named Robert”
Domain name - In a URL the domain name is what is immediately after HTTPS:// and before the next forward slash /. For example, in https://example.edu.net.au/php/student_summary.php. The domain name is example.edu.net.au.
Workflow
Step 1: Get the Report in Paradigm
Load the existing Saved Report in Paradigm. Navigate to Reports > Report Builder and go to the SAVED REPORT DESCRIPTIONS section. Select the report and click the LOAD REPORT button.
Once the required report has been loaded, scroll down to the bottom of the page and look for the SAVE REPORT section.
Copy the entire content of the Excel Web Query URL field. In the example below, copy: https://example.edu.net.au/php/external_report_builder_call_httpauth.php?report_id=10000.
Step 2: Get the Data to Excel
Open the MS EXCEL application. Choose the Data menu from the Ribbon Bar.
If you don’t see the From Web menu in the ribbon, click the Get Data menu > From Other Sources > From Web.
If you are using MS EXCEL 2019, other standalone versions higher than 2016, and MS OFFICE 365, after enabling the From Web (Legacy) menu, you should be using it instead of the From Web menu found in the ribbon by default. (The same as the sample screenshot below which is an Excel 2019 Version).
(3). Paste into the address field the content / API URL of the Excel Web Query URL field sourced from Report Builder, and click Go. After clicking the Go button on the right of the address bar, with the new link on it, a Windows Security window will appear prompting you to enter your Paradigm Username and Password.
(4). Enter your Paradigm credentials [Username and Password].
There are instances where some script error window will appear after logging in (which are caused by Excel attempting to download and display some web libraries used within Paradigm), just click the Yes button for every error that will come out.
(5). Click the Import button, once the page has been loaded.
After clicking the Import button, Excel will ask you where to put the data, you have the option to use the Existing worksheet or to create a New worksheet.
If you are generating a report that has parameters (the RED coloured content in the example below) on the link, an example would be a unit enrolment report link below:
Sample Excel Web Query URL with parameters: https://example.edu.net/php/external_report_builder_call_httpauth.php?report_id=emsUnitEnrol&start_date_from=["start_date_from"]&start_date_to=["start_date_to"]
If this is the case, Excel will ask you to input the needed parameters. Enter the required data or parameter value (Refer to your guide when it comes to the data format of the possible parameter values needed to generate a report), Excel will then load the report that you specified.
MS Excel Helpful Guide Links
Enable the From Web (Legacy) menu in Excel
Excel 365, and other standalone Excel versions higher than 2016 require the legacy “From Web” command to authorise login with Paradigm, and it also provides the most output compatibility.
Excel 365, and other standalone Excel versions higher than 2016 require the legacy “From Web” command to authorise login with Paradigm, and it also provides the most output compatibility.
Choose File > Options > Data > Show legacy data import wizards section > Check From Web (Legacy) > Click OK.
However, if you want to add the From Web (Legacy) menu as a new group in the ribbon bar, for easy access, you can follow the steps below in customising your ribbon bar with the legacy menu on it:
(1) Choose File > Options > Customize Ribbon.
(2) Look for the From Web (Legacy) command, and select it.
(3) Select which Tab you want it to appear, ideally, you should select the Data Tab on the right.
(4) To create a new section in the ribbon where the legacy menu will be added, you need to create a new group first, and in creating a new group, you either right-click on Data or click the New Group button.
(5) Select your newly created group.
(6) Click the Add>> button.
(7) Click OK.
Last updated
