WebQuery Access

Step 1 - Generate Your Web Report

The first step to creating a report that you wish too share with others starts with running the report for yourself. Put together and run the report you wish to share with others. A typical report looks like the screenshot below:

Notice in the top right corner that there are a few options for what to do with the report once you have run it. Click the option called "Create WebQuery." You will be presented with the option to create either a temporary or a permanent webquery, as shown below.  In this example, we want a permanent webquery, so we just choose the "Create" button and are presented with the link in green text:

If you just want to send this report to others so they can view the same data, simply click the copy button and paste the link into an email.  Anyone can view the contents of this report. They cannot change the parameters of the report or drill through to more data, but it's a great way to quickly share information with people. Keep in mind the link we created is a permanent link that exists until you delete it. Sometimes you just want to share information with someone temporarily, perhaps for security reasons. If this is the case, instead of choosing the "Permanent" link creation, you would choose "Temporary," and then input how many days the link should persist before it expires.

Step 2 - Acquiring Your Permanent WebQuery Link for Excel

The nice thing about the permanent webqueries is that they're really great for embedding in Excel. The benefit of this is that you can refresh the data right from Excel whenever you like - eliminating the need to re-export anything each week and saving you a bunch of time! To do this, navigate to the main Avantalytics website, open the Reporting menu, find the option entitled Web Query Access, and click on it. You'll see something similar to below:

Notice that there is an entry for each webquery as well as several options, including the ability to deactivate the webquery link. In order to embed a webquery into Excel, we need to click on the "Copy Excel URL" button.

Step 3 - Open a New Excel Workbook & Embed the WebQuery

Open Excel to a new workbook and then click on the "Data" menu as shown below. Your version of Excel may differ slightly:

You may be tempted to choose "From Web." DO NOT DO THIS. Instead, choose "From Text." A dialog box labeled "File Name," will appear, similar to the one seen below. In the "File Name" dialog box, paste the Webquery URL, then click "Open."

In a few moments, the report will be pulled into Excel, and you'll see an import wizard similar to the one below. If you are familiar with Excel, this process is much like importing any normal CSV data.

From this wizard, choose "Delimited" and then press "Next." On the following screen, you will de-select "Tab" and select "Comma," then skip to the "Finish" button.

Next, you will see an Import Data dialog box in Excel, similar to the below image. This box is asking you where to put the data, and allowing you to do a few other things as well, such as choose properties. Let's select the Properties button to see what options we need to change.

You'll see the screen below on the left - make sure to adjust it so it looks like the screen below right.  We've highlighted the changes for you.

Once you've made the changes, hit "OK," and Excel will begin to retrieve the report. When the report is ready, the data will populate into the workbook. Your last step is to simply save this workbook onto your desktop. You can now refresh the data anytime you like by right-clicking in the data in Excel and choosing "Refresh." You can also refresh multiple webqueries in Excel simply by clicking the Data tab and choosing "Refresh All."