Several sets of queries are available to be downloaded from this website. These queries are designed to access the BIS Data Warehouse and generate standard financial reports using Brio software. We believe most users will find the queries effective in their current form.
However, the queries can be modified and saved once downloaded. We’ve posted instructions on how to make several common modifications. Please note that several of the queries involve complex "joins" (links between multiple tables). Thus, significant modifications to the queries should only be done by someone familiar with Brio. We suggest that you choose "Save As" to give the downloaded query a new name before making any changes. That way, if you encounter any problems, you can revert to the original query. Of course, you can always download the queries again should the need arise.
The queries fall into the four categories listed below. The links below will allow you to read descriptions of the queries before you download them.
To use these queries as they have been designed, simply open each query using Brio and click "Process" on the toolbar. You will be prompted to login. When the results appear, switch to the "Detail" tab (along the bottom of the screen) and print. That’s it!
CONTRACTS &
GRANTS - Click here for financial
reporting for contract & grant funds
OTHER FUNDS - Click here for financial reporting for all other funds
REVENUE SUMMARIES - Click here for financial reporting for revenue funds
OTHER SUMMARIES - Click here for fund and/or account balance summaries
Download All 6 EFA Queries - For
Mac - For
Windows
(Requires the most recent version of the free Expander
software from Aladdin
Systems to decompress)
All reports are sorted by account-fund with a page break after each account-fund.
For complete financial statements for each account-fund on your last cumulative ledger (GLX0650), you need to run 4 queries: the EFA Summary, the EFA Outstanding Transactions, and two detail queries. For the two detail queries, you should select either the Fiscal Year To Date or Current Month queries for both payroll and non-payroll. The Fiscal Year To Date queries pull detail for the entire fiscal year. The Current Month queries provide a summary balance forward for previous months and detail for only the most recently completed month.
The EFA Summary and the EFA Outstanding Transactions queries work with both the Fiscal Year To Date and the Current Month detail queries.
We recommend that you run the Fiscal Year To Date queries the first time you report and the Current Month queries each month thereafter. This will avoid wasting paper.
We recommend collating the reports in the following order: 1) summary, 2) non-payroll detail, 3) payroll detail, 4) outstanding transactions.
Download EFA Summary Query - For Mac - For Windows
This query will produce a summary financial report with subtotals by sub for all extramural funds which appeared in your last cumulative ledger (GLX0650). This will contain appropriations, expenditures to date from the ledgers and outstanding expenses from the BFS data extracts.
Click here to view a sample
Download EFA Outstanding Transactions Query - For Mac - For Windows
This query pulls detail voucher, encumbrance, and pre-encumbrance data from the most recent BFS data extract (i.e. activity since the last ledger run).
Click here to view a sample
Download EFA NonPayroll Detail FY TD Query - For Mac - For Windows
This query pulls the non-payroll expense detail for the fiscal year to date for all extramural funds which appeared in your last cumulative ledger (GLX0650). This report will also contain any existing facilities encumbrances (PRR liens) from the most recent general ledger. This query should be used with the EFA Payroll Detail FY TD query.
Click here to view a sample
Download EFA Payroll Detail FY TD Query - For Mac - For Windows
This query pulls the payroll detail for the fiscal year to date for all extramural funds which appeared in your last cumulative ledger (GLX0650). This query should be used with the EFA NonPayroll Detail FY TD query.
Click here to view a sample
Download EFA NonPayroll Detail Monthly Query - For Mac - For Windows
This query pulls the non-payroll expense detail for the month most recently posted to BIS for all extramural funds which appeared in your last cumulative ledger (GLX0650). This report will also contain any existing facilities encumbrances (PRR liens) from the most recent general ledger. This query should be used with the EFA Monthly Payroll Detail query.
Click here to view a sample
Download EFA Payroll Detail Monthly Query - For Mac - For Windows
This query pulls the payroll detail for the month most recently posted to BIS for all extramural funds which appeared in your last cumulative ledger (GLX0650). This query should be used with the EFA Monthly Non-Payroll Detail query.
Click here to view a sample
Download All 6 Other Fund Queries - For
Mac - For Windows
(Requires the most recent version of the free Expander
software from Aladdin
Systems to decompress)
All reports are sorted by account-fund with a page break after each account-fund.
For complete financial statements for each account-fund on your last GL which is NOT a contract or grant, you need to run 4 queries: the Other Funds Summary, the Other Funds Outstanding Transactions, and two detail queries. For the two detail queries, you should select either the Fiscal Year To Date or Current Month queries for both payroll and non-payroll. The Fiscal Year To Date queries pull detail for the entire fiscal year. The Current Month queries provide a summary balance forward for previous months and detail for only the most recently completed month.
The Other Fund Summary and the Other Fund Outstanding Transactions queries work with both the Fiscal Year To Date and the Current Month detail queries.
We recommend that you run the Fiscal Year To Date queries the first time you report and the Current Month queries each month thereafter. This will avoid wasting paper.
We recommend collating the reports in the following order: 1) summary, 2) non-payroll detail, 3) payroll detail, 4) outstanding transactions.
Download Other Funds Summary Query - For Mac - For Windows
This query will produce a summary financial report with subtotals by sub for all non-contract/grant account-funds which appeared in your last GL. This will contain appropriations, expenditures to date from the ledgers and outstanding expenses from the BFS data extracts.
Click here to view a sample
Download Other Funds Outstanding Transactions Query - For Mac - For Windows
This query pulls detail voucher, encumbrance, and pre-encumbrance data from the most recent BFS data extract (i.e. activity since the last ledger run).
Click here to view a sample
Download Other Funds NonPayroll Detail FY TD Query - For Mac - For Windows
This query pulls the non-payroll expense detail for the fiscal year to date for all non-contract/grant account-funds which appeared in your last GL. This report will also contain any existing facilities encumbrances (PRR liens) from the most recent general ledger. This query should be used with the Other Funds Payroll Detail FY TD query.
Click here to view a sample
Download Other Funds Payroll Detail FY TD Query - For Mac - For Windows
This query pulls the payroll detail for the fiscal year to date for all non-contract/grant account-funds which appeared in your last GL. This query should be used with the Other Funds Non-Payroll Detail FY TD query.
Click here to view a sample
Download Other Funds NonPayroll Detail Monthly Query - For Mac - For Windows
This query pulls the non-payroll expense detail for the month most recently posted to BIS for all non-contract/grant account-funds which appeared in your GL. This report will also contain any existing facilities encumbrances (PRR liens) from the most recent general ledger. This query should be used with the Other Funds Payroll Detail Monthly query.
Click here to view a sample
Download Other Funds Payroll Detail Monthly Query - For Mac - For Windows
This query pulls the payroll detail for the month most recently posted to BIS for all non-contract/grant account-funds which appeared in your last GL. This query should be used with the Other Funds Non-Payroll Detail Monthly query.
Click here to view a sample
Download Revenue Fund Balance Query - For Mac - For Windows
This query provides a summary of income, expenses and balances for each revenue fund in your department. Each fund is summarized by the accounts (both revenue and expenditure) that are associated with it. This query does not include appropriations, except for the balance forward from the previous fiscal year.
Click here to view a sample
Download Monthly Revenue Summary Query - For Mac - For Windows
This query summarizes revenue to date for each fund by account and then by month with subtotals for each account and a grand total for each fund.
Click here to view a sample
Download Fund Balance by Account Query - For Mac - For Windows
If you have funds for which you have activity on multiple accounts, this query will summarize the appropriations, expenditures, and encumbrances for each of those funds by account. You may want to check out the information on Changing the Queries before processing these so that you can limit your results to only those funds for which you have multiple accounts.
Click here to view a sample
Download Account Balance by Sub Query - For Mac - For Windows
This query will summarize appropriations, expenditures, and encumbrances for each account by sub. (No fund is involved.)
Click here to view a sample
Note: These queries were developed with the bis_dept.oce Open Catalog. This is one of the security mechanisms for accessing BIS with Brio. To use the queries, you must be using the same Open Catalog. If you are a departmental user, you should have it installed as your default Open Catalog. To do so, download the file appropriate to your platform following the directions at http://bis-main.berkeley.edu/ (Click on "Technical Matters").
Problem: How do I change the page layout from portrait to landscape?
Solution: Page Setup from the File menu brings up a window in which you can change this setting.
Problem: My department recently received a new grant, and it is not showing up when I run the Contract & Grant queries.
Solution: The queries will only pull information based on the account-funds which were in the last ledger cycle, so new awards will not appear in the reports until they have appeared on a ledger.
Problem: I get a blank page at the end of my print job for each query.
Solution: Many of the queries are designed to produce a page break after each account-fund to facilitate reporting to principal investigators and unit managers. The blank page at the end results from the page break after the last account-fund. If you really don't want this extra piece of paper, you can always switch to Print Preview (use the icon of the paper with the magnifying glass) before you print. In the bottom right of your screen Brio will tell you the total number of pages. For example, it will read 1 of 18. Exit Print Preview (click the Print Preview icon again). Select print and put in the range from 1 to the total number of pages minus one (in our example the range would be 1 to 17).
Problem: When I open the query I downloaded, I see either a blank page or another department's data in the Detail screen.
Solution: You must click the Process button (top Toolbar) to run the query and generate your results. The payroll queries were deliberately saved with no data to protect sensitive information. Most of the other queries were saved with minimal sample data to provide a sense of the report's formatting.
How do I limit my query to particular accounts or funds?
To specify a range of values, select "Between" from the pulldown menu which defaults to "Equal". Then enter the first number in the range you want, click the green check, and then enter the last number in the range, click the green check, and click OK.
To specify multiple values, enter each number, clicking the green check after each value. When you've entered all the values you wish to find, click "Select All" and then OK. Brio will only find the values in your limit which are highlighted, so the "Select All" is critical.
How do I change the page breaks?
Brio can set page breaks before or after parts of the layout (headers, footers). To add a page break, select the part around which you wish to build the break (click in the left margin of the page across from the layout part in question). Select Page Break Before/After from the Insert menu.
To remove an existing Page Break, select the layout part around which it is built. Then choose Remove Page Break from the Insert menu.
How do I remove a column from the reports?
Click on the field you want to remove, and hit delete. You may also have to click on the Labels and any subtotals or totals and delete those as well.