Create the Base Report in Report Painter
You always begin a financial report in Report Painter
and complete it in the Financial Report Painter.
Before the detour, you had specified CENTSTMT as the data source
you would be using and selected Report Painter as your tool. Report
Painter should be open on your screen.
- Begin by scrolling
down in the Object Inspector to verify that all of the fields from
CENTSTMT are available for your report.
the following fields to bring them into the Report Painter window: GL_ACCOUNT, ACTUAL_AMT,
and BUDGET_AMT. (Since descriptive names
are assigned to each of these fields in the Master File, those names
appear as column titles.) They are all Detail fields by default.
- Change the title from Budget to Planned.
the column title and choose Column Title from
The Title dialog box opens with Budget displayed.
- Type Planned,
as shown below, and click OK.
- Select Ledger
Account and click the By button,
then select Actual and click the Sum button.
Notice that Planned automatically changes to a summary column as
well. These changes will enable you to sort Actual and Planned data
- Insert a title
for the report.
- Click Insert in
the toolbar menu and select Report Heading.
In the Report Heading object, type
2010 Income Statement
followed by a blank line to
leave space between the heading and the report.
- Highlight the
heading text, right-click and choose Justify,
and select Center.
Report Painter appears,
as shown in the following image.
- Click the Save icon,
then click Run to see what you have accomplished
As expected, the output shows
Actual and Planned data for each account in the Chart of Accounts.
Accounts are listed in low to high order. In the full report, accounts run
Next, you will create a column to reflect the
difference between Actual and Planned amounts. This information
will help Century Corporation determine which products the Sales
force should be encouraged to push and where efforts should be made
to reduce spending.
- Place the cursor
after the last column in the Report. Click the Options button,
then select the Computes tab from the Report
The Report Options dialog box opens.
- Name the field
you are computing Variance and change the
decimal format to D12.2B. (B will enclose
any negative numbers in parentheses in the output.)
- Click the Fields button
(bottom-right) to open the fields list. Double-click ACTUAL_AMT,
click the minus sign (-) on the calculator, then double-click BUDGET_AMT.
Click Close to close the Insert Field window.
Your expression is reflected in the input window.
- Click Apply to
make the calculated value available in the fields list. Click OK to
add the column to Report Painter.
- Click the Variance column
and make sure that the Sum button is selected.
- To give the
new column a more descriptive title, right-click Variance and
choose Column Title. In the Title dialog
- Save your work, then click the Run icon.
output shows the increase or decrease of each Actual value in relation
to the corresponding Planned amount.
- Although shown
in parentheses, the negative values are still a little hard to see. Make
them bold and red. The value of this information will be more evident
when you have completed the income statement, where red, bold values
will quickly draw attention to less than expected sales on the revenue
side, but also to less than expected spending on the expense side.
you will define the condition you want to highlight and then the
style you want to apply when that condition is met.
- Right-click Increase/Decrease and
The Field Properties dialog
box opens at the Style tab.
- Confirm that
the active object field is set to Column
- Click the Edit
Conditions button (lower-left).
The Condition List
dialog box opens.
- Click New.
Edit Condition dialog box opens.
- In the Condition
input box, assign the name DECREASE. Then create the following expression
to reflect the negative values.
- Choose Variance from
the Field list, is less than from the Relation
list, and type 0 in the Value input box.
- Click OK.
Condition List dialog box opens, displaying the completed expression.
- Click OK to
return to the Style tab where you will define a style to apply when
this condition is met.
- With DECREASE
selected in the Apply to Condition list, click the Select
Font button under Graphical.
The Font dialog box opens.
- Click the Color button,
select RED from the color palette and click OK.
- Then select Bold from
the Font Style list and click OK.
- Click OK again
to return to Report Painter.
- Click the Save icon
followed by the Run icon.
The output shows
all negative values in bold and RED.
You have created a project and within it, you have completed
the column-based setup for a report that examines Actual and Planned
data for a chart of accounts.
But your goal is to create an Income Statement that tracks revenue,
cost of goods sold, and expenditures.
You will do that in the next exercise.
Begin with a quick review of the differences between a standard
report, like the one you just created, and the financial report
you will turn it into.
In a standard tabular report:
- Report rows
are displayed in sort order (high-to-low or low-to-high).
- Rows are displayed
only for values retrieved from the data source.
- Text rows can
only be inserted at sort breaks.
- Inter-row calculations
can only be performed at sort breaks.
In a financial report:
- Specific rows
can be placed anywhere in the report.
- Rows can be
displayed for selected values in the data source, or values calculated from
that data, as well as for values that are inserted directly or picked
up from another file.
- Text and blank
rows can be inserted at any point in the report.
- Inter-row calculations
can be performed at any point in the report.
The income statement you will create
demonstrates many of these features.