Balance sheet report comparison column 2

[namas]

CR 8.5, SQL server 2005 tables using ODBC connection.

I need to display General Ledger Balance sheet for two periods on two separate columns for comparison purpose. How can I achieve this?

eg. SQL produces following results.
How to display period3 in one column and period 12 in another in CR? Thanks.

fiscal_yr period Balance_amt Assets
---------------------------------------------------------------------------------
2005 3 ($474.08) Beneficial interest in perpetual trusts
2005 3 $228899.39 Cash and cash equivalents
2005 3 ($691.46) Charitable remainder trusts receivable
2005 3 ($248.95) Furniture, fixtures, and equipment (less) depreciation
2005 3 $1515.94 Interest, dividends, other receivables
2005 3 ($28392.64) Investment collateral
2005 3 ($15977.00) Investments loaned to broker
2005 3 ($174250.67) Investments, at market
2005 3 $279.41 Notes receivable
2005 12 ($13485.81) Beneficial interest in perpetual trusts

2005 12 $8559.55 Cash and cash equivalents
2005 12 $1831.44 Charitable remainder trusts receivable
2005 12 $0.00 Due from net assets on notes
2005 12 ($882.35) Furniture, fixtures, and equipment (less) depreciation
2005 12 ($201.48) Interest, dividends, other receivables
2005 12 $10574.73 Investment collateral
2005 12 $8304.98 Investments loaned to broker
2005 12 $8300.29 Investments, at market
2005 12 ($993.98) Notes receivable

 

[GJParker]

Create 2 formulas

//@Period3
If {MyTable.Period} = 3 Then
{MyTable.Balance_Amt}
Else
0

//@Period12
If {MyTable.Period} = 12 Then
{MyTable.Balance_Amt}
Else
0



Gary Parker
MIS Data Analyst
Manchester, England

 

[namas]

Used the formula, it's close, comes out like follow.
How to make description appear only once?

Description period1 period2

Beneficial interest in perpetual 1.08 0.00
Cash and cash equivalents 2.39 0.00
Charitable remainder trusts recei 7.46 0.00
Furniture, fixtures, and equipmen 9.95 0.00
Interest, dividends, other receiv 0.94 0.00
Investment collateral 8.64 0.00
Investments loaned to broker 2.00 0.00
Investments, at market 4.67 0.00
Notes receivable 0.41 0.00

Beneficial interest in perpetual 0.00 2.81
Cash and cash equivalents 0.00 7.55
Charitable remainder trusts recei 0.00 3.44
Due from net assets on notes with 0.00 0.00
Furniture, fixtures, and equipmen 0.00 7.35
Interest, dividends, other receiv 0.00 9.48
Investment collateral 0.00 8.73
Investments loaned to broker 0.00 1.98
Investments, at market 0.00 2.29
Notes receivable 0.00 9.98

should be like:

Description period1 period2

Beneficial interest in perpetual 1.08 2.81
Cash and cash equivalents 2.39 7.55
Charitable remainder trusts recei 7.46 3.44
Furniture, fixtures, and equipmen 9.95 7.35
Interest, dividends, other receiv 0.94 9.48
Investment collateral 8.64 8.73
Investments loaned to broker 2.00 1.98
Investments, at market 4.67 2.29
Notes receivable 0.41 9.98

 

[GJParker]

insert a group for description and insert a summary at this level for each of the formulas. then suppress the details

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

It looks like you inserted a group on period. You need to remove that before following Gary's last instruction.

-LB

 

[namas]

It's getting closer. How to calculate Net Assets (Total Assets - Total Liabilities) for period1 and period 2? It's grouped by account type (assets and liabilities), balance sheet currently shows total assets and total liabilities like follow.

Period 1 Period 2 Inc(Dec) % Inc(Dec)

Assets

Beneficial interest $12,1.88 348,5.81)$60,720 -100.90
in perpetual trusts
Cash and cash 738.83) 55,917.55 ($594,31 -653.60
equivalents
Charitable remainder 118.05) 83,143.44 ($301,326. -164.53
trusts receivable
Due from net assets $0.00 $0.00 $0.00 0.00
on notes with
below-market interest
rates
Furniture, fixtures, $22,322.48 8,827.35) ($13,495.1 152.88
and equipment (less)
accumulated

Total assets 26,403.98 766,114.3$29,692,51 1.16

Liabilities

Accounts Payable and $37,255.79 5,466.55 $51,789.2 645.62
accrued liabilities
Amounts due $0.00 6,469.85) $256,469.8 -100.00
beneficiaries
Charitable funds held $0.00 10,040.79)$2,310,040 -100.00
for the benefit of
others

Total liabilities $84,234.92 ,48,007.70($3,632,24 1.23

NET ASSETS:

 

[lbass]

Create four running totals, one for assets and one for liabilities, for each period. Use a sum of amount, evaluate using a formula:

{table.period} = 1 and
{table.accttype} = "Assets"

Reset never. Change the evaluation formula for each running total. Then create a formla like this to get the net:

{#AssetsPeriod1}-{#Liabilitiesperiod1}

Repeat for period 2, using the period 2 running totals. Place these in the report footer.

-LB

 

[namas]

Almost there with little snafu. The last column %Inc (Column C/Column B) is not computed correctly in the report. Column C is difference b/w column A and column B (PeriodA-PeriodB). A formula computes the difference:

difference = {@periodA}-{@periodB}

Column %Inc is computed by dividing column C by column B.
I have another formula called 'percent':

if {@periodB} <> 0 then
{@difference} * 100/{@periodB}

'percent' is then place in detail area that is suppresed. Summary of percent is grouped by description and accountType. It gives following incorrect Percentage calculation. How to fix it so that %Inc column is calculated correctly? Thanks.


PeriodA PeriodB Inc(Dec) % Inc(Dec)

Assets

Beneficial interest $12,167.88 (1,348,552.81)$1,360,720 -100.90
in perpetual trusts
Cash and cash (4,738,393.83) 855,917.55 ($5,594,311.38) -653.60
equivalents
Charitable remainder (118,183.05) 183,143.44 ($,301,326.49) -164.53
trusts receivable
Due from net assets $0.00 $0.00 $0.00 0.00
on notes with
below-market interest
rates
Furniture, fixtures, ($22,322.48) (8,827.35) ($13,495.13) 152.88
and equipment (less)
accumulated
depreciation


Total assets 026,403.98) 5,666,114.3$29,692,51 1.16

Liabilities

Accounts Payable and $637,255.79 85,466.55 $551,789.2 645.62
accrued liabilities
Amounts due $0.00 56,469.85) $256,469.8 -100.00
beneficiaries

Total liabilities $684,234.92 ,948,007.70($3,632,24 1.23

NET ASSETS:

 

[namas]

Pls ignore above posting.

Just like calculating net assets for period2 and period2, I want to calculate net assets for %Inc column. CR won't let create running total for this column like the previous two columns.
Total assets and Total liabilities for column %Inc (column C/ColumnB) is calculated as follow:

if Sum ({@periodB}, {qryBalanceSheet.account_type}) <> 0 then

Sum ({@difference}, {qryBalanceSheet.account_type}) /
Sum ({@periodB}, {qryBalanceSheet.account_type})


Net Assets In %Inc = Total Assets in %Inc (a) - Total Liabilities in %Inc (b)

How to calculate a, b?

 

[namas]

Pls ignore my last two postings

 

[namas]

1. I would like to plot a chart for the Balance sheet.
The chart will display account description (all assets, liabilities account description) and compare period1 and period2 values. How to do this?

2) I have parameter fields (period1, period2, fiscalYear1, fiscalYear2) as number data type. When I enter values,
it displays with decimal (1 as 1.00) and commas (2005 as 2,005). How to fix this other than changing the parameter type to String? Thanks.

 

[synapsevampire]

Curious whether anyone's colutions have helped you along the way, I see that you haven't selected the thank anyone for their assistance, so was the initial point of this thread scrapped?

1) You should be able to use the same types of formulas in your chart, though comparing values is somewhat anbiguous, do you mean display the values, or is there some sort of math involved?

2) The parameters observe the default setting for numerics in the report when entering them, not sure why you care that it allows for decimals and commas druing data entry, you don't need to enter those.

-k

 

[dgillz]

A little off topic, but I am curious what your source software package is. Most Accounting or ERP packages have at least one financial report writer built in, such as FRx or F9 which both work with a package I specialize in. F9 in particulat works with multiple packages.

These tools are about 100 times better than crystal, IMHO, to build financial statements. Thats why they exist - they are easy for accountants to use and work great.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown