Unanet would like to thank Max Patin and AEgis Technologies for this Knowledge Center entry, which demonstrates how our customers are maximizing their use of Unanet.
Note: This is functionality outside the scope of Unanet and not supported by Unanet Customer Support as it relates to third party tools.
Note: This functionality applies to Microsoft Windows versions of Excel. Microsoft Excel for Mac is incapable of connecting to Unanet via this method. You can work around this by installing a virtual instance of Windows on your Mac and using Excel for Windows within the virtual environment.
Refreshable Web Queries (RWQ) allow you to use default Excel capabilities to capture saved, shared, reference, or Adhoc report content from Unanet as well as incorporate non-Unanet data sources (if you so desire) for combined reporting. Data can be refreshed at any time (honoring Unanet password authentication and access controls) without having to re-copy and paste into excel. This will allow users to leverage native Excel capabilities to create combined reports with conditional formatting that can be sorted, filtered, and grouped.
What’s covered in this document:
How To Link Unanet Reports Using Excel Web Refreshable Query Functionality
Save And Run Your Unanet Report
- You must save the report prior to creating the web refreshable query. You can use any saved report; however, if you are allowing other users access to your Excel file, you may want to consider using a Saved Shared report so that they will have access via authentication.
- Select the desired report, and click Save Shared Criteria (or Save Criteria if you don't need to share with others).
- Provide the desired name and click Save Shared/Save. Add the report to one of your dashboards. This step is required because it will create a unique URL for the report. Excel requires a unique URL for the RWQ to execute properly.
- From the dashboard where you saved it, run the report.
- When executed, the URL will have a report number appended at the end (report?runFrom=S_76). The report number denotes the type of saved report (R = User Report, A = Adhoc, S = Shared).
Import Data From Legacy Wizard
Note: If the Legacy Wizard is not available, see Adding Legacy Wizards below. This is the most reliable method of importing your data from Unanet into Excel due to the need to supply your log in credentials.
If you do not have access to the Legacy Wizards and cannot add them when following the instructions in the Adding Legacy Wizards section, you may have an older version of Excel where creating a RWQ report was not considered a legacy option.
In this case, simply navigate to the Data tab in the Excel ribbon and click From Web in the Get External Data section. This will open the From Web query form. Choose Basic or Advanced option as needed.
Adding Legacy Wizards
If you are not able to use the copy and paste method to get a web query into Excel, you may want to access the Legacy Wizard called From Web (Legacy). The need for the legacy wizards appeared in a recent update of Excel.
Excel 2013 and later has a From Web button in the Get & Transform Data section of the Data tab, but it does not bring up the dialog box needed to bring in data from web sites that require the type of credential authentication that Unanet requires.
Instead, you must access the Legacy Wizards. To do that, the steps are:
- If you need to add the Legacy Wizards, select the File tab in the ribbon and select Options. Under Options, select Data, and see the "Show legacy data import wizards" section at the bottom.
- Select the check box for From Web (Legacy). Click OK.
- After doing so, you will now see the Legacy Wizards option in the Get Data dropdown menu.
Paste The URL Into The Web Query Browser
- In Excel, from the Data tab, select Get Data > Legacy Wizards > From Web (Legacy).
- Excel will open up a copy of Microsoft Internet Explorer that it will use to identify tabular data. Paste the report URL from Unanet into the Address field of the browser and click Go.
- Clear the script dialog messages.
- Expand the window as needed and enter your Unanet credentials. Click Login.
- Clear the script dialog messages.
- The report is displayed but may contain no detail.
With the release of Unanet's Tabular Interactive Data Views feature, a number of Lists and time-related reports may not fully display here and will require an addition to the URL. (
Reports with the Tabular Interactive Data Views feature will have the icon shown below in the far left column.
Normal URL example:
Appended URL example:
Note: It is necessary to have the latest UTE version for this process. To check your version, select the UTE - Main tab, then select About Me.
- Click Options (upper right) and select Full HTML formatting in the first section of the Web Query Options dialog. Click OK.
- Click in Address bar, append the URL with
reportViewis case sensitive), Click Go.
- Clear script dialog popups and report data should display.
Select The Section Of The Report You Want To Import
To choose which data will be part of your Excel workbook:
- Click the yellow arrow next to the selection you would like to import.
- The arrow will change to blue with a check mark, and the area will be encompassed with a blue box.
- Click Import.
Note: When there is a table inside of another table the wizard will often place the identification arrow of the inner table on top of the outer table. Make sure you select the outer table arrow if your intention is to get the whole table rather than just a part of it.
Modify The Import Options
Before hitting the import button, click the Options button in the top right corner and change the Formatting selection from None to Full HTML formatting. This ensures that you experience no formula breaks in the transition from HTML to Excel.
Import And Modify Connection Options
Click the Import button on the browser. The Import Data dialog displays. This will give you options of where to import the data and some other settings. From here, click Properties.
On the External Data Range Properties dialog, select the check box for Fill down formulas in columns adjacent to data. This ensures that if you add formulas next to the query, Excel will add or remove the appropriate amount of rows (i.e., if after a refresh, your report goes from 100 to 120 rows, it will fill down. If it goes to 80 rows, it will delete the extra rows).
Use The Data
By placing multiple Web Refreshable Queries in one Excel workbook, you can now combine data from multiple built-in Unanet reports into one consolidated report that can be sorted and filtered.
Edit The Query Without Re-Pasting
You can edit the query without having to re-paste by using Excel's native query edit. Right-click on the spreadsheet and click Edit Query.
An alternative method is:
From the Data tab, click Queries & Connections. Right-click the desired connection in the right pane. Click Definition, then click Edit Query at the bottom.
- Save each report as separate sheet in Excel Workbook; it will keep expanding data from colliding.
- Select "Edit Query" first when opening the spreadsheet to save Unanet credentials.
- Change your browser settings. This link may be of use to you: http://java.com/en/download/help/enable_browser.xml.
- Alternatively, open the Excel spreadsheet you saved with the refreshable web query in it.
- Right-click on the spreadsheet and select "Edit Query" (screenshot above). Ensure that you have clicked the yellow box with the arrow to change it to blue.
- On the data tab, click the "Refresh All" button.
- Here is a sample screenshot of the error:
Occasionally, updates to the Unanet software will results in changes to reports. An example of these changes may be a report modification to add another column. If this occurs, you may notice the data displaying in your Excel workbook unexpectedly, and you will need to update the RWQ.
- If you get an error such as "Invalid managerPath specified" or similar, it is likely you have not run the report from it's saved location on a dashboard. Notice how the URL does not contain "runfrom=R" or "runfrom=S" at the end, as it would if the report had been run from the saved location on a dashboard.
- If you are running Unanet 11 you will experience the following error message several times when attempting to create/refresh a RWQ-based reporting solution. Simply click through these error messages to continue. We recommend utilizing Unanet-To-Excel (UTE) to refresh your RWQ-based reporting solutions. UTE will silence these error messages when refreshing a report. UTE cannot silence these messages when creating a new RWQ, however.
Additional Options to Streamline Credentials
Option 1 - Unanet-To-Excel (UTE)
Unanet has developed an add-in to Microsoft Excel that enhances the refresh process associated with refreshable web queries, by eliminating the need for the user to provide their credentials each time they attempt to refresh a report. In UTE, the user is prompted to enter their login credentials. After submitting that information, UTE attempts to log in to Unanet in a secure manner. UTE also includes additional features that add functionality to Excel.
For more information, contact your Customer Success Manager, or see the links in the Additional Information section below.
Option 2 - Append Username/Password
Note - this is not a secure option.
Appending the username and password (
username=XYZ&password=ZYX) to the URL will prevent the login screen from being required. For example, after following the steps outlined in establishing the RWQ, if you then go to "Edit Query" and append
&username=XYZ&password=ZYX to the web address displayed in the URL banner and then reselect the table and click Import, the new web address with the appended username and password will be saved. When you reopen the file and refresh the RWQ it will populate as normal. This is extremely helpful if you are sharing Excel files with other users.
Note: The URL is encrypted once the DNS is resolved, so anything after
unanet.com would be encrypted once the TCP connection is established if you are using HTTPS. HTTP on the other hand is NOT encrypted and you would be exposing your username and password to anyone listening in.