Crystal Report 10, balance sheet report design

I need to create a balance sheet report using crystal report 10 & vb6 (yes it's a bit antique), heheh.

my current recordset is like this :
AccountHeaderNumber AccountNumber AccountName Debit Credit

And i'm trying to build a report like this,

--snip--
ASSETS
    AccountHeader1
            AccountNumber1    AccountName1    Account1Total (debit - credit)
            AccountNumber2    AccountName2    Account2Total (debit - credit)

                                                                         TOTAL AccountHeader1 (sum)

    AccountHeader2
            AccountNumber3    AccountName3    Account3Total (debit - credit)
            AccountNumber4    AccountName4    Account4Total (debit - credit)

                                                                         TOTAL AccountHeader2 (sum)

--snip--

The problem is,
AccountHeader2 value == AccountNumber2 value (and i group them using accountheader field), is there a way to join them together instead separate it in a different group ? Or should i redesign the report in a different way ?

Attached is the screenshot of the report.
carmalegnoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What is the AccountHeaderNumber for AccountNumber2?

mlmcc
0
carmalegnoAuthor Commented:
AccountNumber2's AccountHeaderNumber == AccountHeader1,
hmm.. i wonder where's my attached screenshot.

i'll attach it again.

Clipboard01.png
0
mlmccCommented:
Is this multiple tables?

How are they linked?

mlmcc
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

carmalegnoAuthor Commented:
2 tables,
One is the account master table, and the other is the transaction journal table.

For the debit and credit field I used a subquery totalling all the transaction under that account number.

The master table is used to get the account names.
0
mlmccCommented:
How are they linked?

mlmcc
0
carmalegnoAuthor Commented:
Left outer joined, the master account is on the left.
0
carmalegnoAuthor Commented:
Maybe I didn't get it detailed enough,

The master table :
AccountHeaderNumber
AccountNumber
AccountName

It's a recursive table, an account can point to another account on the same table as it's parent.

The transaction table is self explanatory, usual stuff. TransactionId,Date,AccountNumber,Debit,Credit

Let me know if I can help you more, or if there is another solution for creating a balance sheet scenario.
0
mlmccCommented:
Crystal doesn't handle a recursive table unless you define it in the report.

An account can be in more than 1 AccountHeader?

mlmcc
0
carmalegnoAuthor Commented:
No, the account master's primary key is AccountNumber.

I've tried using CR's hierarchical grouping option, I can't put the total value underneath the account header (like above). Since CR put every account in their own group.

Or maybe I set it wrong, ill attach the hierarchical sample later.
0
carmalegnoAuthor Commented:
@mlmcc
This is the report when i use the hierarchical grouping option.

I need to sum the total of account
1.1.01.00 (KAS)
1.1.02.00 (POS SILANG)
1.1.03.00 (BANK)

and their sub account, directly below
1.1.00.00 (AKTIVA LANCAR)

like this,
--snip--
ASSETS
    AccountHeader1
            AccountNumber1    AccountName1    Account1Total (debit - credit)
            AccountNumber2    AccountName2    Account2Total (debit - credit)

                                                                         TOTAL AccountHeader1 (sum)
--snip--

CR hierarchical grouping place every account on their own group., if i use group summary on the group footer, every account has their own footer.

any ideas ?


Clipboard02.png
0
mlmccCommented:
What is the raw data?

Can you provide several records even just dummy data for the master table

mlmcc
0
carmalegnoAuthor Commented:
Here's the raw data for the Account Master's table.

AccountNumber AccountHeader AccountName
1.1.00.00                  1.0.00.00            AKTIVA LANCAR
1.1.01.00                  1.1.00.00            KAS
1.1.01.01                  1.1.01.00            Kas Rupiah

Let me know if you need anything,      
0
mlmccCommented:
Are they always 1 digit numbers or can you have say

11.22.33.44

mlmcc
0
carmalegnoAuthor Commented:
The format for the account number is,
x.x.xx.xx

The first 2 group is always a single digit.
0
mlmccCommented:
Try this idea
Create several formulas and group on them in turn

FirstDigitGroup - suppress the header and footer
Split({AccountHeaderField},'.')[1]

SecondLevelGroup - suppress the header and footer
Split({AccountHeaderField},'.')[1] & '.' & Split({AccountHeaderField},'.')[2]

ThirdLevelGroup - suppress the header and footer
Split({AccountHeaderField},'.')[1] & '.' & Split({AccountHeaderField},'.')[2] & '.' & Split({AccountHeaderField},'.')[3]  

Then group by the full Account header field

mlmcc
0
carmalegnoAuthor Commented:
Okay, ill try that. Will let you know the result.
0
carmalegnoAuthor Commented:
@mlmcc
I made the formula groupings as you said,
but the result is still different from the hierarchical grouping using CR builtin function.

Attached is the report,

the 1st unbolded column is the accountcode
the 2nd unbolded column is the accountname
the 3rd unbolded column is the accountheader for that account

ps. i'm increasing the point to 500..
=)

Clipboard03.png
Clipboard02.png
0
carmalegnoAuthor Commented:
@mlmcc
I finally used the builtin hierarchical function in CR to produce the report, with some group summary tweaking to produce the result.
0
carmalegnoAuthor Commented:
Is there a way to give you half the points (250) for helping me pointing in the right direction although i revert back to my own solution ?
0
mlmccCommented:
I can reduce the points.

It looks like you may be using the wrong field or the tables are linked wrong.

IN the report with the formulas what is the right most column?

mlmcc
0
carmalegnoAuthor Commented:
The right most field is the sum of all debit - credit transaction for that account.

If I use the builtin hierarchical grouping, the account showed correctly using accountheader field as it's parent (clipboard2.png).
0
mlmccCommented:
Sorry meant the one just left of that.  it is 1.1.00.00 for the first group then 1.1.01.00
The formulas may be using the wrong field

mlmcc
0
carmalegnoAuthor Commented:
That's the accountheader (parent account) for that account.
0
mlmccCommented:
Just out of curiousity try changing the 3rd and 4th formulas to use the account number

ThirdLevelGroup - suppress the header and footer
Split({AccountNumberField},'.')[1] & '.' & Split({AccountNumberField},'.')[2] & '.' & Split({AccountNumberField},'.')[3]  

Then group by the full AccountNumberField

mlmcc
0
carmalegnoAuthor Commented:
@mlmcc
Could you please reduce the points to 250 ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Can you give a reason why the points should be lowered.

EE Admin will handle.

mlmcc
0
carmalegnoAuthor Commented:
I reverted back to my own solution using hierarchical grouping,
but i'd like to give you 250 points for replying to my question.
0
carmalegnoAuthor Commented:
@mod
thanks for the help,
0
mlmccCommented:
If my comments didn't lead to a solution there is no need to award any points.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.