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.
Title: Refreshable Web Queries
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
Step 1 - 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.
Run the desired report, and click on “Save Shared Criteria” (or just "Save Criteria" if you don't need to share with others).
Provide the desired name and click on Save.
Add the Shared (or Saved) Report to one of your dashboards. This is required because by doing so, you create a unique URL. Excel requires a unique URL for the RWQ to work.
From the dashboard where you saved it, run the Shared (or Saved) Report. You will notice that when you run the saved report, the URL will note a report number (). The report number denotes what type of saved report it is (R is User Report, A is Adhoc, S is Shared).
Step 2 - Import Data From Legacy Web wizard
Note: If the legacy web wizard is not available, see the section titled "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 "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 on the From Web button in the Get External Data section.Clicking this button will open the New Web Query form shown in Step 4.
Step 3 - Paste the URL into the Web Query browser
Excel will open up a copy of Microsoft Internet Explorer that it will use to identify tabular data. Paste the saved query from Unanet in the Address field of the browser, access it and log in with your Unanet credentials.
Step 4 - Select the section of the report you want to import
By clicking on the arrow next to the selection you would like imported, you can pick and choose which Unanet data will be part of your excel workbook.
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.
Step 5 - Modify the import options
Before hitting the import button, click on the Options button in the top right corner and change the Formatting from none to Full HTML formatting. This ensures that you experience no formula breaks in the transition from HTML to Excel.
Step 6 - Import and modify connection options
Click the Import button on the browser. This will give you options of where to import the data and some other settings. From there click the Properties button.
In the External Data Range Properties options enable the Fill down formulas in columns adjacent to data box. 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).
Step 7 - 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. Either right click on the spreadsheet and click "Edit Query" as in the first screenshot below, or click on Data/Connections/Properties/Definition/Edit Query as in the second screenshot below.
- 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 in section above). Ensure that you have clicked the yellow box with black arrow to change it to green (as in the screenshot above).
- 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 like the screenshot below "Invalid managerPath specified", it is likely you have not run the report from it's saved location on a dashboard. Notice how the URL does not contain at the end "runfrom=R" or "runfrom=S", which 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.
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". The need for the legacy wizards appeared in a recent update of Excel. It used to be the case that you could go to the Data tab of the Excel ribbon, then select "Get External Data From Web" (or something similar) to open up the "Edit Query" dialog box.
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 websites that require the type of credential authentication that Unanet requires. Instead, you must access the legacy wizards. To do that, these are the steps:
- In Excel, go to the File tab in the Excel ribbon and select Options. Under Options, select Data, and the bottom of the Data section will have a heading that reads "Show legacy data import wizards." Select "From Web (Legacy)" to activate this wizard.
- After doing so, you will now see the Legacy Wizards option in the Get Data dropdown of the Get & Transform section on the Data tab in the Excel ribbon. Screenshot below.
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 visit the KC - Quick Topic - Unanet-To-Excel (UTE) add-in.
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 that 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.