Live Excel Reports

A live Excel report is useful for maintaining awareness of updates on real time situations, such as daily monitoring records, testing sessions, competitions and matches or ongoing experiments. Live Excel reports use Microsoft Excel to periodically download saved Smartabase data to an Excel file.

Excel has a web connection tool that allows this to work; however, the ability to fetch external data works quite differently depending on whether you are using Microsoft Windows or MacOS. When you create a live Excel report, it's important to set up web connections to only retrieve the data you need and ensure that the refresh period is set to a suitable frequency. If your report is fetching large datasets too frequently, this can cause your report to perform poorly and/or impact performance of the server that runs your Smartabase site.

This example from Microsoft Excel demonstrates a live Excel report that was created to display the gym check in details for the Smartabase All Stars team. As each player arrives in the gym in the morning, they use Smartabase to record their arrival time, weight and heart rate.

The live report fetches their gym check in details from Smartabase as well as their latest wellness event form. The report tab of the Excel file summarizes this information as we can see in this screenshot.

From this screenshot we can see that the most recent player to arrive at the gym is Lucas Martin at 6:35am; however, Riley Jones has not yet checked in for the day.

Steps to view a live Excel report

1. Navigate to the Excel file on your computer.
2. Open the file.
3. Enter your Smartabase username and password when prompted.
4. View the report in Excel.
5. Use the report as required, including refreshing the data if necessary.

Steps to build a live Excel report for Windows users

1. Open a blank worksheet in Microsoft Excel.
2. Go to the Data tab.
3. Select From Web.
4. Using the Basic option, enter the URL as follows, replacing the capitalized content with the details of your Smartabase site and event form:

https://SERVERNAME.smartabase.com/SITENAME/externalcsvreports?app=SITENAME&formName=FORMNAME&filter=FIELDNAME|VARIABLENAME&filter=FIELDNAME|VARIABLENAME&startTime=STARTDATE&finishTime=FINISHDATE

Where:

• SERVERNAME is the name of the server your Smartabase site is located on. 
• SITENAME is the name of your site. 
• FORMNAME is the exact title of the event form you want to fetch a dataset from. This must be capitalized in the same way as the event form name. If there are spaces in the event form name, you must replace them with the + symbol, so if the form name was Training Load you would type formName=Training+Load.
• FIELDNAME is the exact name of a field or calculation within the event form, while VARIABLENAME is the exact text or number of a possible result for that field. These can be used in combination with the filter parameter to restrict the dataset to specific information. For example, if we wanted to fetch a dataset from the Training Load form but only for cases where the type of training (field name = Session type) was skills training (variable name = Skill), we could type filter=Session+type|Skill.
• STARTDATE and FINISHDATE are the beginning and end dates of the records you want to include in your dataset formatted as ddmmyyyy, so 14 May 2017 would become14052017 and 2 December 2016 would become 02122016.

formName, startTime and finishTime need to be capitalized as they are here. Using filters and start and end dates are optional; if these are not required your URL request would end with the form name. There should be no spaces in your URL request, field names and variable names should be separated by a vertical bar ( | ) and parameters should be added using an ampersand ( & ).

5. Click OK.
6. On the next screen, change the view from Anonymous to Basic in order to enter your Smartabase username and password.
7. Click Connect.
8. The next screen will show you a preview of the data that will be added to your worksheet. If this is correct, click Load and carry on. If it is not correct, check that your URL references the correct event form and that, in Smartabase, you're logged in and viewing the correct group. 
9. Configure the Excel worksheet according to how you would like to view the data.
10. Save and close the file – you will need to enter your username and password to re-open.

Steps to build a live Excel report for MacOS users

1. Create a new text document in TextEdit, Word or similar.
2. Paste the following text, ensuring each line is on a new line and replacing the site and report names as necessary:

   WEB
   
   1
   
   https://SERVERNAME.smartabase.com/SITENAME/externalcsvreports?app=SITENAME&formName=FORMNAME&filter=FIELDNAME|VARIABLENAME&filter=FIELDNAME|VARIABLENAME&startTime=STARTDATE&finishTime=FINISHDATE

3. See above for instructions on what details should replace the capitalized terms.
4. Save this document using text format.
5. Using Finder, replace the file extension with .iqy
6. Create a new Excel worksheet:
   1. Go to the Data menu along the top toolbar of your Excel workbook.
   2. Select Get External Data from the drop-down menu.
   3. Select Run Web Query.
   4. Choose the IQY file you created earlier.
   5. When prompted to choose the data location, select Properties.
      • Enable Refresh Data on File Open.
      • Enable Background Refresh.
      • Click OK.
   6. Click OK.
7. Format the Excel worksheet as necessary and save.