Start Free Trial

asked on

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.

What is the AccountHeaderNumber for AccountNumber2?

mlmcc

ASKER

AccountNumber2's AccountHeaderNumber == AccountHeader1,
hmm.. i wonder where's my attached screenshot.

i'll attach it again.

Clipboard01.png

Is this multiple tables?

How are they linked?

mlmcc

ASKER

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.

How are they linked?

mlmcc

ASKER

Left outer joined, the master account is on the left.

ASKER

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.

Crystal doesn't handle a recursive table unless you define it in the report.

An account can be in more than 1 AccountHeader?

mlmcc

ASKER

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.

ASKER

@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

What is the raw data?

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

mlmcc

ASKER

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,

Are they always 1 digit numbers or can you have say

11.22.33.44

mlmcc

ASKER

The format for the account number is,
x.x.xx.xx

The first 2 group is always a single digit.

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

ASKER

Okay, ill try that. Will let you know the result.

ASKER

@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

ASKER

@mlmcc
I finally used the builtin hierarchical function in CR to produce the report, with some group summary tweaking to produce the result.

ASKER

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 ?

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

ASKER

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).

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

ASKER

That's the accountheader (parent account) for that account.

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I reverted back to my own solution using hierarchical grouping,
but i'd like to give you 250 points for replying to my question.

ASKER

@mod
thanks for the help,

If my comments didn't lead to a solution there is no need to award any points.

mlmcc