Create Financial Detail Spreadsheet with Running Fund Balance

This process uses the Report Direct Link to populate USAS data into an Excel spreadsheet.  The steps below outline the procedure using a spreadsheet template for the SSDT Financial Detail Report July 1 Cash Balances. The Report Direct Link can be utilized on any custom or template USAS report to pull data into Excel. Use the HTML-Tables or HTML-FieldNames format when using the Report Direct Link with Excel.

Create Spreadsheet with Report Link

1. Click to download the spreadsheet template for Financial Detail Template with Running Fund Balance. This template will support approx. 50,000 entries. The formulas used to calculate totals can be modified if more entries are needed.
   1. NOTE: If opening the template spreadsheet directly from this web page, the spreadsheet may open in Protected View. Click 'Enable Editing' on the yellow bar across the top of the spreadsheet.
2. In USAS, navigate to the Report > Financial Detail Report on the drop down.
3. Enter Report Parameters
   1. Enter Start/End dates for the fiscal year
   2. Format: HTML-Table
   3. Include specific account parameters or a filter
      
4. Save the report parameters using the Save and Recall option.
   1. Select the blank option and enter a save name. 
      
      
      
      
   2. Once you tab off the field, the 'save' icon will be active.  Click on  to save your report and parameter settings. 
      
      
   3. Your saved report name will now be included in the 'save and recall' drop down menu. 
5. Once the report parameters have been saved, the link icon will become available. Click the link icon to open the Report Direct Link pop up window. 
   

   1. Right click on the URL link that appears in the pop up window. Choose to copy the link address.
      
      
      
      

      Do not check the box for 'Include parameters?' when using the Report Direct Link with Excel. This option adds to the length of the link that could cause issues with the Excel character limit.

      
      

6. Open the Financial Detail Template with Running Fund Balance spreadsheet in Excel. Navigate to the Data Tab. Click 'Get External Data from Web.'
   
   
   
   
   1. Paste the Link into the Web Address field. Click Go. 
      
      
      

   2. Enter username and password for USAS. 

      If you are not prompted for credentials after you click 'Go' then Excel has not started loading the data. If this happens, close the pop up window and click on 'Get External Data from Web' again.
      It is common for this step to take time to load - specifically when pulling a large set of data (full Fiscal Year). The HTML Table to show in the preview window and the 'Import' option to become available when Excel has finished loading.
   3. Once data from link loads, click Import.

   4. Click on cell A3 to select where to put the imported data.
      
      
      

      It is common for this step to take time to load - specifically when pulling a large set of data (full Fiscal Year). Excel will indicate in the bottom left corner of the window that it is running a query in the background. 
7. Enter the Beginning Fund Balance in cell E2.
   
   
   
   
   1. The Beginning Fund Balance can be found on the Fund Account page or the SSDT Cash Summary report can be generated for the same parameters used when generating SSDT Financial Detail July 1 Cash Balances (fund, posting period, etc.). Once the Beginning Fund balance is entered, the SSDT Cash Summary report is also useful for balancing the other calculated fields on the Financial Detail spreadsheet. 
   2. When the Beginning Fund Balances has been entered Column J will populate with the running Fund Balance for each transaction.
8. Save the Excel spreadsheet to your computer.

Refresh Spreadsheet

Now that the spreadsheet has been saved with the Report link, it can be opened at a later time and the data can be refreshed to match the current USAS database information for the saved report parameters. For example, this spreadsheet for the Financial Detail report can be created at the beginning of the fiscal year and then refreshed throughout the year to view the current FYTD figures.

1. Open the saved Excel spreadsheet
2. Navigate to the Data tab and click 'Refresh'
   OR
   Right click any cell containing data from USAS and click 'Refresh'
   
   
   
   
   
   

   1. Enter username and password for USAS. 

      It is common for this step to take time to load - specifically when pulling a large set of data (full Fiscal Year). Excel will indicate in the bottom left corner of the window that it is running a query in the background.
      It is also important to note that the report parameters saved using Save and Recall must remain active and unchanged in USAS. Any changes to the saved report parameters used for the report link will impact the data in the spreadsheet.

Share with Other Users

The Excel spreadsheet can also be shared with other USAS users in the district. That user will then be able to log in with their own USAS credentials when refreshing the data. In order to access this report, users must have at least USAS_RO access.

1. The Excel spreadsheet can be emailed as an attachment or saved in a shared folder.
2. The recipient will see the previously saved data when the spreadsheet is opened. To refresh the spreadsheet to show the current data, they will navigate to the Data tab and click 'Refresh.'
   OR
   Right click any cell containing data from USAS and click 'Refresh'
   
   
   
   
   
   
   1. The recipient will be prompted to enter their username and password for USAS.

Add the From Web (Legacy)

IF the standard "Get Data From Web" option is not allowing credentials to be entered for the link, the the "From Web (Legacy)" option can be used to import data from the Report Direct Link. Perform the following steps to add this option to the Data ribbon.

1. In Excel, Go to File-> Options-> Customize Ribbon-> select All Commands-> From Web(Legacy)- and add it to “New Group” under the Data tab.  

2. OR add the From Web (Legacy) can be added to the group for "Get & Transfer Data"

3. Once saved, the From Web (Legacy) option will be available on the Data ribbon

4. When utilizing the Report Direct Link in excel, utilize the From Web (Legacy) option when the 'Get Data from Web' option is indicated in the instructions.