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

Run a report on the Reports Module to include the data, or type of data that you want to use

The example that we are using a very basic Scoring Event form that is updated live by someone in the system and it is entered for the "Team" athlete.

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"

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

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

Remember the name of your report as you will need this to create an import using this exact name in the live excel report

Download the template file for your excel reports- ask your distributor for more details on this

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

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

1. Click on Data

2. Click on From Web

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".

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):

e.g. https://my.smartabase.co.uk/fusionallstars/live?report=livematchstats

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

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.

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 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.

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!!!

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

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".

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".

 

Next, you need to define a name for the data. In the Formulas Tab, click on Define Name

Next, you need to define a name for the data. In the Formulas Tab, click on Define Name

 

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))

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

=OFFSET(Sheet1!R3C1,0,0,COUNTA(Sheet1!C1)-1, COUNTA(Sheet1!R3))

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

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)

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)

4: Click on Formulas

5: Click on "Use in Formula"

6: Click on the name that you had in the define name section (e.g. MatchData)

7: Click on the cell selection drop down box when the name of the cells appears in the "Create Pivot Table" text box.

You will see that your cells are now selected as part of the pivot table, click OK

You will see that your cells are now selected as part of the pivot table, click OK

 

You can now create you pivot table/chart according to the columns/layout that you require

You can now create you pivot table/chart according to the columns/layout that you require

 

Now design your pivot tables and charts accordingly.

Now design your pivot tables and charts accordingly.

 

When ever any new data is entered into the system, this will get pulled through automatically every minute

When ever any new data is entered into the system, this will get pulled through automatically every minute

Even if you log off the actual website (e.g. smartabase.co.uk/fusionallstars), the data will still be imported into this report and any data entered into it will pull through every 1 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.

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.

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.

When you open the specific excel file that you have saved you will need to login again to the application to access the data.

 

Security Notice

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.

The Bug where the live reports would not pick up any data point if it was saved using a report set to Included All athletes has been resolved

The Bug where the live reports would not pick up any data point if it was saved using a report set to Included All athletes has been resolved

Previously, an excel live report would not pull through any data from the report if it referenced a Report that was saved using the setting "Include All" in the Users selection. This has been resolved and the live reports can be built from reports saved like this.

N.B. If the Groups button is ticked to be included in the live report it will not be pulled through.

N.B. If the Groups button is ticked to be included in the live report it will not be pulled through.

Because an athlete may be in multiple groups, the Groups are not pulled through into the live report. The workaround would be to add the groups to a profile / athlete information event form and linking this data into any live report.

0 Comments

Add your comment

E-Mail me when someone replies to this comment