Formatting Financial Reports

Modified on Sun, 26 Jul at 3:50 AM

Overview

QQube makes it straightforward to group accounts by hierarchy level. The more difficult part is formatting the group names so the report behaves like QuickBooks.

The Crystal Reports formulas in this guide address three requirements:

Display Subtotal when an account contains subaccounts.
Display - Other when activity is posted directly to a parent account that also contains subaccounts.
Suppress unused hierarchy levels so the report does not contain unnecessary blank space.

Crystal Reports account hierarchy showing subtotals and parent account activity

1. Create the Account-Level Count Formulas

Create the following five formulas and place them in the Details section.

@Count_Level_0

if {vd_Account.Account Level} >= 0 then 1
else
0.00

@Count_Level_1

if {vd_Account.Account Level} >= 1 then 1
else
0.00

@Count_Level_2

if {vd_Account.Account Level} >= 2 then 1
else
0.00

@Count_Level_3

if {vd_Account.Account Level} >= 3 then 1
else
0.00

@Count_Level_4

if {vd_Account.Account Level} >= 4 then 1
else
0.00

2. Configure Each Account Group

Each account hierarchy level requires the following report elements:

Group field.
Group Header name.
Group Header suppression condition.
Group Footer formula.
Group Footer name.
Group Footer suppression condition.

Level 0

Group	{vd_Account.Account NAME Grouping Mechanism - 1}
Header Name	{vd_Account.Account NAME Grouping Mechanism - 1}
Footer Contains	{@Count_Level_1}
Suppress Footer	None

Suppress Header

Sum ({@Count_Level_1}, {vd_Account.Account NUMBER Grouping Mechanism - 1}) = 0.00

Footer Name

if Sum ({@Count_Level_1}, {vd_Account.Account NUMBER Grouping Mechanism - 1}) = 0 then
{vd_Account.Account NAME Grouping Mechanism - 1}
else
'Subtotal ' & {vd_Account.Account NAME Grouping Mechanism - 1}

Level 1

Group	{vd_Account.Account NAME Grouping Mechanism - 2}
Header Name	{vd_Account.Account NAME Grouping Mechanism - 2}
Footer Contains	{@Count_Level_2}

Suppress Header

Sum ({@Count_Level_2}, {vd_Account.Account NUMBER Grouping Mechanism - 2}) = 0.00

Footer Name

if Sum ({@Count_Level_2}, {vd_Account.Account NUMBER Grouping Mechanism - 2}) > 0 then
'Subtotal ' & {vd_Account.Account NAME Grouping Mechanism - 2}
else
(if
{vd_Account.Account NAME Grouping Mechanism - 2} = ''
then
{vd_Account.Account NAME Grouping Mechanism - 1} & ' - Other'
else
{vd_Account.Account NAME Grouping Mechanism - 2})

Suppress Footer

Sum ({@Count_Level_1}, {vd_Account.Account NUMBER Grouping Mechanism - 1}) = 0.00

Level 2

Group	{vd_Account.Account NAME Grouping Mechanism - 3}
Header Name	{vd_Account.Account NAME Grouping Mechanism - 3}
Footer Contains	{@Count_Level_3}

Suppress Header

Sum ({@Count_Level_3}, {vd_Account.Account NUMBER Grouping Mechanism - 3}) = 0.00

Footer Name

if Sum ({@Count_Level_3}, {vd_Account.Account NUMBER Grouping Mechanism - 3}) > 0 then
'Subtotal ' & {vd_Account.Account NAME Grouping Mechanism - 3}
else
(if
{vd_Account.Account NAME Grouping Mechanism - 3} = ''
then
{vd_Account.Account NAME Grouping Mechanism - 2} & ' - Other'
else
{vd_Account.Account NAME Grouping Mechanism - 3})

Suppress Footer

Sum ({@Count_Level_2}, {vd_Account.Account NUMBER Grouping Mechanism - 2}) = 0.00

Level 3

Group	{vd_Account.Account NAME Grouping Mechanism - 4}
Header Name	{vd_Account.Account NAME Grouping Mechanism - 4}
Footer Contains	{@Count_Level_4}

Suppress Header

Sum ({@Count_Level_4}, {vd_Account.Account NUMBER Grouping Mechanism - 4}) = 0.00

Footer Name

if Sum ({@Count_Level_4}, {vd_Account.Account NUMBER Grouping Mechanism - 4}) > 0 then
'Subtotal ' & {vd_Account.Account NAME Grouping Mechanism - 4}
else
(if
{vd_Account.Account NAME Grouping Mechanism - 4} = ''
then
{vd_Account.Account NAME Grouping Mechanism - 3} & ' - Other'
else
{vd_Account.Account NAME Grouping Mechanism - 4})

Suppress Footer

Sum ({@Count_Level_3}, {vd_Account.Account NUMBER Grouping Mechanism - 3}) = 0.00

Level 4

Group	{vd_Account.Account NAME Grouping Mechanism - 5}
Header Name	{vd_Account.Account NAME Grouping Mechanism - 5}
Footer	Blank
Footer Name	{vd_Account.Account NAME Grouping Mechanism - 5}

Suppress Header

{vd_Account.Account NAME Grouping Mechanism - 5} = ''

Suppress Footer

{vd_Account.Account NAME Grouping Mechanism - 5} = ''

Expected Result

The Crystal report displays account subtotals, identifies amounts posted directly to parent accounts with - Other, and suppresses unused hierarchy levels.