Freeform report formulas

[sandora]

v11, accessdb. Not sure if this is the correct forum for this one. I'm building a Balance Sheet. I've summed each of the account catagories with a db query. For instance:

cash
1231 sum of debits and credits
1234 sum of debits and credits

Fixed assets
2234 sum of debits and credits
2335 sum of debits and credits
etc...

I've built formulas for each account catagory (cash, fixed assets) giving me the sum of each.

I was trying to build sort of a freeform report in the RH, I can place one formula in and I can see it just fine, but as soon as I try to place another one in the section they both go blank. Any ideas on why or is there a better way to create a freeform report?

 

[synapsevampire]

Please define what a freeform report is, I don't know it as a technical term.

If you need help with formulas, try posting the formulas.

If the data is grouped by the account category, it would make sense that you'd want a account category in the report, and you would place the formulas in the group header or footer.

Successful posts generally include technical information:

Example data
Expected output

Another approach might be to create an Access Query as the datasource and do all of the summaries within that. If the data is present in Access, it should be within Crystal as well.

-k

 

[sandora]

When I say free form, I mean not using the detail line and being able to put things where I want them. I've done the grouping on the access side.
I've tried a couple different things, I've moved my data to different sections, I've tried creating a text box (like a form letter) with my account names and the formulas, but every time I try to insert the second formula, any second formula, both go blank. Here are a couple of the formulas:

{@ARSum}
sum({AR.Expr1})
{@CashSum}
sum({Cash.Expr1})

The Expr1 comes from the query in access that creates the sum for each account number. Is is because they are all called Expr1 or am I just way off base.

 

[synapsevampire]

Those formulas are summaries, yet you state that you've already done the summaries.

Think of Crystal as a visual representation of a SQL Statement.

If you've already performed summaries in your query, then those are the details, and you can increase the size of details section to whatever level you like, however data is returned as rows, and should be thought of in that way. You shouldn't have to create anyformulas within Crystal if I understand you.

I fear that your dilema is that you don't understand how Crystal works.

Perhaps you'll heed my suggestion and post example data and expected output instead of continuing to use non-technical terms to describe something you don't understand.

-k

 

[sandora]

You may be right, I am very new to Crystal and I'm trying to be as clear as possible. It is possible that I am making this harder that it needs to be. Here is what the output should like except with more totals. I'm not sure if this is the data you are asking for.

Assets

Cash $644,807
Accounts Receivable

Inventory
Trailers
Used
Freightliners
Utility & Cargo
Usage
Parts
LIFO Reserve
Total Inventory
Prepaid Expense
Total Current Assets

These are all text fields except the number which is the formula {@CashSum}, which returns the correct amount. However, as soon as I insert the next formula, they are blank. If this is not the data you are referring to, please let me know.

 

[synapsevampire]

I understand the output now, thanks, however I don't understand the data (the fields).

Do you have just one row of data being returned?

Have you verified that your Access query is returning the expected results?

-k

 

[sandora]

Here is the result of the query for cash in access

GL Account SumOfCredit SumOfDebit Expr1
1000-10 $45,327,884.36 $45,783,379.26 $455,494.90
1000-13 $14,400,144.25 $14,504,781.79 $104,637.54
1000-15 $4,518,399.24 $4,529,255.61 $10,856.37
1000-16 $236,672.26 $253,055.67 $16,383.41
1000-17 $356,575.71 $369,241.92 $12,666.21
1001-11 C$10.00 $21,413.09 $21,403.09
1001-12 $0.00 $8,732.35 $8,732.35
1003-11 $263,351.52 $105,867.06 ($107.40)
1003-13 $966,025.75 $972,998.47 $6,972.72
1003-14 $2,883.22 $2,883.22 $0.00
1003-15 $339,259.09 $341,213.90 $1,954.81
1010-11 $939.22 $1,290.09 $350.87
1010-12 $1,708.70 $1,577.93 ($130.77)
1010-13 $2,900.00 $3,236.50 $336.50
1010-15 $323.57 $853.01 $529.44

then the formula in crystal, {@CashSum}=sum({Cash.Expr1}), is to sum up all of these accounts, which works, as do the rest of the formulas, I'm getting the correct result as long as I only try to put one formula on the page.

 

[synapsevampire]

Got it.

You can propably see now why understanding the data is essential.

Since the fields are all being returned under one datasource, why do you reference different data souces as AR vs. Cash?

{@ARSum}
sum({AR.Expr1})
{@CashSum}
sum({Cash.Expr1})

I'd guess that you're returning m,utlipel datasets to Crystal, which isn't allowed.

One datasource per report.

I'd have to see the query, but I'll just guess that you should be returning the data using a Union All or some such.

select 'AR' as Datasource, GL Account, SumOfCredit, SumOfDebit, Expr1 from AR
UNION ALL
select 'CASH' as Datasource, GL Account, SumOfCredit, SumOfDebit, Expr1 from CASH
...etc...

Your original post states that you've summed these as a db query, not queries, so I'm still confused.

There's nothing in your example data which shows the source (Cash vs. AR), so how can you differentiate them in the output?

Remember, you can't just drop in numerous datasources into Crystal and reference them as one.

If this post doesn't clarify for you, the last step would be to paste in your SQL from your Access query.

Or if you've omitted fields from the example data, that might explain it.

-k

 

[sandora]

I'm not sure how to get SQL from Access so I'm going to try to explain my source data. I have a db called Data5. It contains a table called GL_Accounts and GL_Trans_Archive (it contains others but these are the two I've used). The GL_Accounts contains account numbers and descriptions, etc. The archive account contains the debits and credits.
I've created queries for eash catagory (Cash, AR, etc)
I've used both tables linked by the account number. This is what the queries look like:

Table Acct# Arch Arch
Field Acct# Debit Credit Exp:[sumofdebit]-[sumofcredit]
Total Sum Sum Expression
Criter Like "10*"

I'm thinking this may be where my problem lies, in using all these queries. Let me know if this is not enough info.