Formatting Financial Reports in Crystal Reports | CLEARIFY

Formatting Accounts in Financial Reports

Grouping accounts is relatively easy in QQube.  What isn't easy, is formatting the account names to do the following:

• Show the word 'Subtotal' when there are sub accounts
• Adding ' - other' when something has been posted to a parent account when there are sub-accounts (A QuickBooks proprietary operation)
• Hiding group 'space' when you have some sub accounts for some levels, and not for others

Here is a foolproof method for handling all of these in SAP Crystal Reports so you don't have to re-invent the wheel.

Step 1

First create 5 formulas and put them on the Details Line:

{@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

Step 2

For each Account Grouping Mechanism you have in the report you will have options for

• HEADER NAME
• Conditions on when to SUPPRESS the HEADER NAME
• FOOTER Formula
• FOOTER NAME
• Conditions on when to SUPPRESS the FOOTER NAME

***LEVEL_0***
GROUP: {vd_Account.Account NAME Grouping Mechanism - 1}
HEADER NAME: {vd_Account.Account NAME Grouping Mechanism - 1}
SUPPRESS HEADER: Sum ({@Count_Level_1}, {vd_Account.Account NUMBER Grouping Mechanism - 1}) = 0.00
FOOTER: Contains {@Count_Level_1}
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}
SUPPRESS FOOTER: None


***LEVEL_1***
GROUP: {vd_Account.Account NAME Grouping Mechanism - 2}
HEADER NAME: {vd_Account.Account NAME Grouping Mechanism - 2}
SUPPRESS HEADER: Sum ({@Count_Level_2}, {vd_Account.Account NUMBER Grouping Mechanism - 2}) = 0.00
FOOTER: Contains {@Count_Level_2}
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}
SUPPRESS HEADER: Sum ({@Count_Level_3}, {vd_Account.Account NUMBER Grouping Mechanism - 3}) = 0.00
FOOTER: Contains {@Count_Level_3}
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}
SUPPRESS HEADER: Sum ({@Count_Level_4}, {vd_Account.Account NUMBER Grouping Mechanism - 4}) = 0.00
FOOTER: Contains {@Count_Level_4}
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}
SUPPRESS HEADER: {vd_Account.Account NAME Grouping Mechanism - 5} = ''
FOOTER: Blank
FOOTER NAME: {vd_Account.Account NAME Grouping Mechanism - 5}
SUPPRESS FOOTER: {vd_Account.Account NAME Grouping Mechanism - 5} = ''