Subscribe to the SSDT Newsletter by clicking here.  Your email will be added to a distribution list.        

The April newsletter contains feature articles on certification reports and the creation of query parameters in template reports.  Because these articles are pretty detailed, we provided links below so you can easily click on a specific section of the article.   So much good information, we wouldn't want you to miss a section of it! 

• Certificate Reports
  • Appropriation Resolution
  • Certificate of Available Balances
  • Amended Official Certificate of Estimated Resources 
• Creation of Query Parameters in a Template Report
  • Operations Table                                                   

Certification Reports Available in USAS-R

Classic's USACERT programs are available in the Redesign.  They are all located under the Periodic menu and include the following: (click on the sample report to view a PDF version of each)

Prior to running one of the above reports. fund levels for reporting can be set up under CORE>Accounts' Fund tab.  This is a one-time setup.  The 'Resolution Levels' and 'Include in Resolution' checkbox pertain to the Appropriation Resolution report.  By default,  all funds and all resolution levels are included of which you can modify.  The 'Certificate Reporting' pertains to the Certification reports.  You can choose between Fund/SCC or Fund reporting for each Fund.

Appropriation Resolution

You can choose one of the following 'amounts to use' depending on whether a temporary resolution or permanent resolution report is needed.   The selected amounts will appear on the report.

• Beginning Balances Only (July 1st Cash balance)
• Next Year Proposed
• FTD Appropriation, Carryover, and Totals
• FTD Appropriation minus Carryover Encumbrances

The Appropriation Resolution narrative from Classic's APPRES program, which includes the the date/time/location and the listing of Board members adopting the Resolution, is not available within the Redesign.  Instead, a sample narrative (in Word format) is available in the documentation which you can then save and customize with your district information. 

Certificate of Available Balances and the Amended Official Certificate of Estimated Resources

Separate summary and detail report options are available for both reports.   Like the Appropriation Resolution report, the Certificate and Amended Certificate programs have sample narratives available in the documentation you can save and customize.

Certificate of Available Balances Options and Calculations

Fiscal Year: Enter the fiscal year to report on.  

Exclude Fund/SCC: enter Fund/SCCs you want to exclude from the report. 

Principal Amounts for Permanent Fund: enter the Fund/SCC along with the associated principal amount for any permanent funds. Reporting requirements state that only interest earnings are supposed to be considered available for expenditure when certifying year-end balances of a permanent fund to the county auditor.  The principal amount entered for each Fund/SCC will be subtracted from the beginning cash balance listed on the report.

Advances Not Repaid: enter advances that have not been repaid as of July 1st.  A positive amount should be entered for the fund that made the advance and a negative amount entered for the fund that will be making the reimbursement.

The resulting report contains the following columns.  Included is how the amounts are calculated:

• Cash Balance June 30: Cash Balance as of June 30 of the prior year
• Encumbrances June 30: all outstanding encumbrances as of June 30 which have been carried over into the new year
• Advances not Repaid: Advances not repaid that were entered in the program
• Carryover Balance Available for Appropriation: Column 1 – Column 2 +/- Column 3.
• Total Amount From all Sources Available for Expenditures: Pulled from the receivable amounts stored on the revenue accounts.  For example, the total in this column for the general fund should equal the total sum of all receivable amounts for general fund revenue accounts  If you did not enter temporary or permanent appropriations to your revenue accounts, this amount will be zero.
• Total Amount Available Plus Balances: Column 4 + 5

Amended Official Certificate of Estimates Resources Options and Calculations

Fiscal Year: enter the fiscal year to report on.  

Tax Options: By default, values listed will be included in the "Taxes" column on the report unless you check them. NOTE: Income Tax is receipt code 1130 and Other Taxes is receipt code 1190.   

Exclude Fund/SCC: enter Fund/SCCs you wish to exclude from the report. 

Principal Amounts for Permanent Funds: enter the FUND/SCC along with the associated principal amount for any permanent funds. (Same as Certificate option) 

Advances Not Repaid: enter advances that have not been repaid as of July 1st. (same as Certificate option)

The resulting report contains the following columns. Included is how the amounts are calculated

• Unencumbered Balance July 1: calculated for each fund by taking the July 1 cash balance - Prior year carryover encumbered + Advances not repaid - Principal entered in program
• Taxes and Other Sources Columns: are the receivable amounts; these are the final receivables at the time which would include any additions or deductions made.  If you did not enter temporary or permanent appropriations to your revenue accounts, this amount will be zero.
• Total:  total of Column 1 + Column 2 + Column 3

How are the Query Parameters created when generating a report? 

With Classic reports, you are stuck with a few options under the 'Selection Criteria' prompts to filter your report by.  Not the case with Redesign.  When customizing a template report, the 'Configure Filters' tab is the behind-the scenes mastermind that allows you to create filterable prompts that display as 'Query Parameters' when generating a report.  Most template reports provide default Query Parameters.  However,  if you want to create your own query parameter prompt, please refer to the following steps:

1. Open the Report Definition in the Report Manager Grid  
2. Click on   .  On the far left is a list of Properties to filter from.  Select a property which will be pulled into 'Display Name' 
3. Select the desired Operation from the drop-down. 
4. In the Filter Value, enter the following function:  param("name","default value","label")
   1. The "name" must be unique within the query. Most template reports use the (string) value which is displayed when hovering over a display name property
   2. The "default value" is optional. 
      1. Is there a default value you want displayed in the Query Parameter?  Example: creating a date range parameter and you want it to default to current period.  Please refer to Greater or equal/Less or equal example under Operations Table.
      2. NOTE: If leaving the default value blank, all but the 'One of' operation require double quotations "" for a blank default value.   
   3. The "label" is the prompting value shown to the user when generating the report.  This is optional.  If left blank, it will display the 'Display Name'.  I would recommend creating a label to further define the parameter.

Operations Table

The table below summarizes the more popular 'Operations' you can use and how to create a Query Parameter.  For more detailed information about the various 'Operations' and how to to create them, click here.