Building Finance Reports

Building Finance Reports

Building Finance Reports

Lab Instruction Document

Requisition Approval Report

Multi-Year Budget Report

Accounts Payable Aging Report

Requisition Approval Report

Beginning a New Report Document (alio Finance)

  1. Log in the alio Intelligence (BI Launchpad).
  2. From the Applications menu, select Web Intelligence.
  3. Click the New button to create a new document.
  4. From the Select a data source screen, select Universe and click OK.
  5. Select the alio Finance universe and click the Select button.

After completing the above steps, the Query Panel should open. Continue to the next section to begin building the report.

Creating the Initial Query

  1. Expand the Purchasing folder and then expand Requisitions.
  1. Drag the following objects to the Result Objects pane:
  2. Requisition No
  3. Date Entered
  4. Vendor Name
  5. Item Adjusted Extended Cost–This includes discounting, freight, etc.
  6. Expand the Req Current Approvalfolder and add the following objects to Result Objects:
  7. Approval Code
  8. Approval Code Description
  9. Drag Approval Code to the Query Filters pane and set it up to prevent inclusion of requisitions that have already been made into purchase orders.
  10. Click the Run Query button to load data and show the initial report layout.

Initial Formatting

  1. Double click in the Report 1 textbox and enter a new title “Requisitions Needing Approval”.
  1. From the Format, Style tab, click the Underline button to remove the underline from the title.
  2. Position the mouse pointer between the columns of the table to click and drag the width of each column so that the text fits. You can also double click on the column separators to set the column to automatically grow based on the contents.
  3. Add a page number cell to the page header using these steps:
  4. From the Report Element, Cell tab, select the Pre-Defined menu and choose Page Number/Total Pages. Then click in the page header area to insert the new cell.
  5. From the Format, Border tab, select the option for no borders .
  6. From the Format, Style tab, click the Bold button to remove the bold formatting.
  7. Modify the formula of the field to add “Page: “ to the beginning of the cell:
    Old Value:=Page()+ "/" + NumberOfPages()
    New Value:="Page: " + Page()+ "/" + NumberOfPages()
  8. Click on the Item Adjusted Extended Cost header cell. Then from the Formatting, Alignment tab, click the Align Right button so the header alignment will match the column values.

  1. Save the document to a location under Public Folders and name it “Requisitions Needing Approval”.

Adding Sections for Approval Code

  1. Right click on one of the values in the Approval Code column and select Set as Section.
  2. Change the formula for the newly created section cell to include the Approval Code Description.
    Old Value: =[Approval Code]
    New Value:=[Approval Code] + “ – “ + [Approval Code Description]
    Press Enter after entering the new formula.
  3. Position your mouse at the right side of the cell until you see the left/right arrow pointer. Then click and drag to widen the cell until it is large enough to display the full description. Again, you can double click on the right side to have the cell automatically set the width based on the contents.

Adjusting Page Layout

  1. Under the Page Layout, Page tab, change the orientation from Portrait to Landscape.
  2. In the same section, change the paper size from A4 to Letter.
  3. At the bottom of the screen, select the Page button so that you can see how the report will render on paper.
  4. Readjust the column widths so that all of them will fit on the page. You can change the column headers by double clicking on the cell and typing new text to replace what is there. For example, you might want to shorten Item “Adjusted Extended Cost” to “Total”.
  5. Save the report document again.
  6. Now you will be able to print or export the report to PDF file. Click the Print button in the upper left corner to open the report as a PDF file. Once open, you can save the report, email it to someone, or print it.

Creating‘My’ Approvals Copy

  1. Use the pulldown next to the Save button to select Save As. Insert the word ‘my’ into the name and save the document with the name “Requisitions Needing My Approval”.
  2. Double click on the title cell again to change the heading of the report to “Requisitions Needing My Approval”.
  3. Click the Edit Data Provider button to open the Query Panel again.
  4. Drill down to the Purchasing Requisitions folder.
  5. Click and drag the Pending Approval by Cur User predefined filter into the Query Filters panel.
    This filter will cause the query to only return requisitions that the currently logged in user can approve. It does this by comparing the approval codes in the requisition’s current approval chain line to the approval codes assigned to the current user.
  6. Click Run Query button to display the report with the new filter.
  7. Save the report. You can then close the report by clicking the X in the corner of the tab for this report.

Creating a Publication

  1. Click on the Documents tab and browse to the folder where you saved the “Requisitions Needing My Approval” report document.
  2. From the New menu select Publication.
  3. For the Title, enter ‘Requisition Approval Email’.
  4. Click the Source Documents page at the left.
  5. Click the Add button. Browse to and select the “Requisitions Needing My Approval” report and click OK.
  6. Click on the Enterprise Recipients page at the left. Use the arrows to move any users you would like to receive this report into the Selected box.
    Users can also subscribe to this publication themselves after you finish creating it. They can control this by right clicking on the publication and selecting Subscribe or Unsubscribe.
  7. Choose the Formats page from the left side. Uncheck the checkbox next toWeb Intelligence and select Adobe Acrobatinstead.
    Note: Since we are delivering to email, the Web Intelligence format would not be usable unless the user has the Web Intelligence Rich Client installed.
  8. Select the Destinations page from the left. Unselect Default Enterprise Location and choose Email instead.
  9. Enter a valid email address in theFrom: field.
  10. Enter ‘Requisitions for Approval’ for the Subject:.
  11. Type a short message in the Message: field. This will be the body of the email.

As an alternative, you can choose the BI Inbox as the destination. This will cause the report to be delivered into the users’ alio Intelligence inbox in the BI Launchpad.

  1. At the left again, expand the Additional Options and select the Advanced page. Then under Report Bursting Method, choose the option One database fetch per recipient.
    This option forces the report to be run for each user. That way they will see only those requisitions that they are able to approve.
  2. Click the Save Close button.
  3. Right click on the newly created “Requisition Approval Email” publication and choose Schedule.
  4. Review the options under the Run object: pulldown. For this example leave it set to Now.
  5. Click the Schedule button.
  6. You can close the History screen that comes up or wait to watch the status complete. You can return to the History screen by right clicking on the publication and selecting History.
  7. Now check the email for the users who are subscribed to the publication. They should have a message with the “Requisitions Pending My Approval” report attached as a PDF file.

Multi-Year Budget Report

Beginning a New Report Document (alio Finance)

  1. Log in the alio Intelligence (BI Launchpad).
  1. From the Applications menu, select Web Intelligence.
  2. Click the New button to create a new document.
  3. From the Select a data source screen, select Universe and click OK.
  4. Select the alio Finance universe and click the Select button.

After completing the above steps, the Query Panel should open. Continue to the next section to begin building the report.

Creating the Initial Query

  1. From the GeneralLedgerAccountsDimensions folder, drag the following objects to the Result Objects pane:
  2. Fund
  3. From the General LedgerAccounts, drag the following to the Result Objects pane:
  1. Account with Description
  1. From the General LedgerAccountsMulti-Year ReportingCurrent Year Columns folder, drag the following objects to the Result Objects pane:
  2. YtdOrig Bud
  3. YtdAdj Bud
  4. Ytd Expended
  5. From the General LedgerAccountsMulti-Year ReportingPrevioius Year 1 Columns folder, drag the following objects to the Result Objects pane:
  6. Prev 1 Ytd Bud
  7. Prev 1 YtdAdj Bud
  8. Prev 1 YtdExp
  9. From the General Ledger folder, drag the following predefined filters to the Query Filters pane:
  10. Default Account Year (Based on the account year specified on the User screen in alio)
  11. Expenditures (Using the Revenues filter would turn this report into a review budget report)
  1. Click the Run Query button to load data and show the initial report layout.
    Because we are using measures from the Multi-Year Reporting folder, we will automatically be prompted for Begin Period, End Period, and whether or not to use Debit / Credit Flag. The period range will apply to all years reported. The debit / credit flag option will determine whether your figures display as positive or negative values. Respond ‘Y’ to see expenses as positive numbers.

Initial Formatting

  1. Double click on the “Report“ title to change it to “Expenditure Budget Comparison”.
  1. Resize the first two columns so that the Fund and Account with Description are fully visible.
  2. Double click the column headers to change them to shorter abbreviations.
  3. Select each of the column header cells and right align them from the Formatting, Alignment tab.
    Tip: Once you have the formatting set for a specific cell. You can easily apply it to other cells using the Format Painter. Just highlight the cell you want to copy, click the Format Painter button , and then click the new cell that you want to have use the format. If you double click on the Format Painter button, you can keep “painting” the format until you go back and click the Format Painter button again.
  1. Save the report as “Expenditure Budget Comparison”.

Creating a Fund Summary Tab

  1. Right click on the “Report 1” tab under the report and choose Rename Report. Change the tab name to “Account Detail”.
  2. Right click on the newly named “Account Detail” report and choose Duplicate Report. A copy named “Account detail (1)” will be created.
  3. Now rename the new tab to “Fund Summary”.
  4. Right click on one of the values in the Account with Description column and choose Delete.
    The totals for the budget and expenditure columns will automatically be rolled up to the Fund level. You may need to resize some of the columns to support the larger numbers. If there is not enough room you will see “#########” instead of the numerical value.

Adding a Difference Column

  1. Right click on a cell in the LY Expcolumn and choose InsertColumns on Right.
  2. Double click on the new column header and change the text to LY Diff.
  3. Click on one of the cells where the values for LYDiff will be and then click on the Edit the Formulabutton .
  4. Create the formula to find the difference between last year’s adjusted budget and last year’s expenditures.
    =[Prev1 YtdAdj Bud] - [Prev1 YtdExp]
    You can double click on the objects under Available Objects to insert the item in your formula in the location of your cursor.
  5. Click OK when you are finished.
  6. You can create similar formulas for:
  7. CY Diff=[YtdAdj Bud] - [Ytd Expended]
  8. CY vs LY Bud=[YtdAdj Bud] - [Prev1 YtdAdj Bud]
  9. CY vs LY Exp=[Ytd Expended]- [Prev1 YtdExp]

Adding Totals

  1. Select one of the values in the CY Orig column. Then on the Analysis, Functions tab, click on the Sum button .
  2. Repeat this process for each of the numerical columns.
  3. Double click on the cell with “Sum:” and change it to “Totals:”.
  4. Save the report document again.

Accounts Payable Aging Report

Beginning a New Report Document (alio Finance)

  1. Log in the alio Intelligence (BI Launchpad).
  1. From the Applications menu, select Web Intelligence.
  2. Click the New button to create a new document.
  3. From the Select a data source screen, select Universe and click OK.
  4. Select the alio Finance universe and click the Select button.

After completing the above steps, the Query Panel should open. Continue to the next section to begin building the report.

Creating the Initial Query

  1. From the Accounts PayableA/P Payment Headers folder, drag the following objects to the Result Objects pane:
  2. Vendor Name
  1. From the Accounts PayableA/P Payment HeadersA/P Invoices folder, drag the following objects to the Result Objects pane:
  2. Invoice Date
  3. Invoice No
  4. Amount To Pay
  5. From the Accounts PayableA/P Payment HeadersA/P Payment Details folder, drag the following objects to the Result Objects pane:
  6. PO No
  7. Payment Extended Cost
  1. Click the Run Query button to load data and show the initial report layout.

Initial Formatting

  1. Double click on the “Report 1” title cell and change the text to “Accounts Payable Aging”.
  2. From the Formatting, Style tab, remove the underline for the cell.
  3. Click to drag one of the cells for the Invoice Date valuesover top of one of the Vendor Namevalue cells. This will reverse the position of these two columns.
  4. Resize the Vendor Name column so that the data fits completely.
  5. Double click the “Amount To Pay” column header and change the text to “Invoice Amt”. Also set the cell to use right alignment from the Formatting, Alignment tab.
  6. Double click the “Payment Extended Cost” column header and change the text to “Amt Paid”. Also set the cell to use right alignment from the Formatting, Alignment tab.
  1. Save the report as “Accounts Payable Aging”.

Filtering Out Paid Invoices

  1. From the Data Access, Data Objects tab, click the New Variablebutton .
  2. Set the Name: to “Paid”.
  3. Set the Formula to the following:
    =If [Amount To Pay] <= [Payment Extended Cost] Then "Yes" Else "No"

If the total of our payments is equal to or larger than the invoice total, Paid will equal “Yes”. Otherwise, Paid will equal “No”.

  1. Click on the very edge of the invoice listing table to select the entire table.
  2. From the Analysis, Filters tab, click on the Filterbutton .
  3. Make sure Vertical Table : Block 1is selected at the left and click the Add Filter button.
  4. Select Paid under the Variables folder and click OK.
  5. Change the operator from In ListtoEqualto.
  6. Select No and use the arrow button to add it to the Paid Equal to box on the right.
  7. Click the OK button.

Creating a Timeframe Group

  1. From the Data Access, Data Objects tab, click the New Variablebutton .
  1. Set the Name: to “Aging Group”.
  2. Set the Formula to the following:

= If DaysBetween([Invoice Date]; CurrentDate()) >= 90 Then "90 days or more"

Else If DaysBetween([Invoice Date]; CurrentDate()) >= 60 Then "60 - 89 days"

Else If DaysBetween([Invoice Date]; CurrentDate()) >= 30 Then "30 - 59 days"

Else "0 - 29 days"

  1. Click the OK button.
  1. At the left hand side click the Available Objects button to display the result objects from our query and the new variables we created.
  2. Click and drag the Aging Group variable to insert before the Invoice Date column in the table. Watch the highlighting as you drag the object. Be sure that is shows a small box in front of the Invoice Date column so that it is inserted instead of replacing the Invoice Date column.
  3. Right click on a value cell in the Aging Group column and select Set as section.

Adding Totals

  1. Select one of the values in the Invoice Amt column. Then on the Analysis, Functions tab, click on the Sum button .
  2. Repeat this process for the Amt Paid column.
  3. Double click on the cell with “Sum:” and change it to “Totals:”.
  4. Save the report document again.
Top View