Live Data Reports
You can now generate reports that are updated live from data that you enter into your system. To do this follow the steps below:
Run a report on the Reports Module to include the data, or type of data that you want to use
Once you run your report, click on the "Save" button and then name the Report and click OK. For this example the report name is "livematchstats"
When you click on the Clear button, or when you first come back to the Reports page you will see that the report is saved
Download the template file for your excel reports- ask your distributor for more details on this
The template file is available from your distributor and it needs to be setup to make sure that all data will automatically refresh when you use your data from Smartabase in the live excel report. You need to use THIS template to create your new live reports.
N.B. Even though we can continually send new data to the excel sheet, Excel doesn't update the pivot tables and charts automatically; we use an updated excel sheet to enable you to be able to updated pivot tables and pivot charts. Excel by default doesn't do this so if you would like auto-update your charts you must use this template.
Now open the live template worksheet and click on the "Data" Tab along the top of the page and then click on the "From Web" button
Type in the name of the site that you are logged into e.g. https://my.smartabase.co.uk/fusionallstars and you MUST add in "/live?report=reportname" and click "Go".
1. Type the name of your site address and add in a "/live?report=report name" (the exact spelling and text case of the report that you created in the reports page):
2. See the information in the step below before you Click on "Go"
Having the ability to pull through additional athletes' data when they are added into the group can be achieved by adding in &updategroup=true
For example, if the url above was written as https://my.smartabase.co.uk/fusionallstars/live?report=livematchstats&updategroup=true then any new athletes added into the group that you are referencing would be pulled through into the report. This means you are always getting a full list of data from the group.
This means the report doesn't have to be resaved every time a new athlete is added into the group, which was how it used to function.
Once you have typed in the site and report name correctly, you will be asked for your username and password for the Smartabase site. Type this into the username and password section.
When you type your username and password into this section, you will be able to access the data directly from Smartabase. This means you don't have to be logged into the actual smartabase application to access the live data for this report, it is pulled directly from the Smartabase server.
N.B. For security purposes, never select "Remember my credentials"; if you do and you lose your computer this report could be opened!.
You may be asked about accessing the data from that Report. If this message below appears, click on "Yes"
You will see any data from that report being pulled through into the window (as shown in the image here), then click Import.
An "Import Data" box will appear. You MUST select the "Properties" button and set the data to refresh button according to you needs (see the step below). DON'T CLICK ON THE OK BUTTON YET!!!
In the External Data Range Properties, Set the Refresh Control Properties to Refresh according to your needs
Make sure that:
- Ensure background refresh is ticked (this enables you to use excel while the query runs)
- Set the refresh rate according to your needs; this is a balance between the amount of data you will accessing and the type of report you will be running
- If the refresh data when opening the file is ticked, this mean each time a user opens this report, it will run. Please consider how much data is being pulled through and how much load it will put on your server.
- For additional data security, you can also select to remove external data from worksheet before closing.
N.B. The only time to use a refresh of 5 minutes or less is if the report needs to be constantly refreshed for a small group of players; for example, a morning wellness, screening tests, bodyweight, hydration, or if a match is going on, some data might be getting entered to Smartabase in real time and report refreshes itself. Or if testing with multiple testing stations with data being entered and a coach needs the ability to see the results as they are happening. For most other scenarios, a constant refresh is not needed and the Refresh button should be ticked at the top using the manual refresh button.
Now you can select where you want the data to import to (e.g. the sheet and page location) and you need to import them to Sheet 1 and click "OK".
Name the cells (you will use this name for your pivot table and charts), and add in the following formula into the Refers to: text box =OFFSET(Sheet1!R3C1,0,0,COUNTA(Sheet1!C1)-1, COUNTA(Sheet1!R3))
A New Name window will appear for you to name you data. Most importantly:
1: Rename your cells if you need to (names cannot have spaces in them). This will be the name that you will use to generate your pivot tables and/or charts
2: you MUST copy and paste in the following formula* into the Refers to: text box
3: Click OK
Note that this formula assumes that your source data is in a tab named "Sheet1". Change the references from "Sheet1" to the name of the tab your data is in if you choose a different sheet.
This formula is creating a "range" of data that automatically resizes to the number of rows and columns in the report, so that if new data comes in, your pivot table automatically refreshes.
As an alternative, you can use the following cell reference
Some customers also use the following reference instead in the "Refers to:" cell selection area.
=OFFSET(Sheet1!$A$3,0,0, COUNTA(Sheet1!$A:$A)-1, COUNTA(Sheet1!$3:$3))
Now create you pivot chart by clicking on the Insert Pivot Table/chart. Now click on the select cells icon in the Table/Range box and follow the steps below
Click on "Formulas" and then select the "Use in Formula" button and you will see that the name that you wrote before appears for selection (e.g. MatchData)
When ever any new data is entered into the system, this will get pulled through automatically every minute
Password protect the excel file and then Save and Close it so that others cannot access the data. This will also log you off your connection to the Smartabase server.
When you open the file again from your documents, you will be asked for your username and password to access any new data that has been entered on the Smartabase site.
Please consider the type information that you are importing into your excel sheet and always password protect it in case you lose your computer, or if it is stolen.
Do NOT set up live reports for data which could break data protection acts requirements such as Medical Data.