Beginning balance 2

[Caren]

What is the best way to add beginning and ending balances to an activity report?<br><br>I have a report that gives me the general ledger activity for a specified account for a&nbsp;&nbsp;period based on beginning date parameter and ending date parameter.&nbsp;&nbsp;I have a group total for that activity period. <br><br>I now want to add a beginning balance for each account (group) so I can sum the beginning balance and the activity balance to get the ending balance.<br><br>I have been trying to arrive at this using running total but I am not doing something right and it is not totaling. I am studying this option further.&nbsp;&nbsp;However, upon thinking further, I wonder if I would be better off to calculate the beginning balance in a subreport instead and insert it in the group header suppressing all but the report total.&nbsp;&nbsp;Am I barking up the wrong tree?&nbsp;&nbsp;I don't want to create performace issues. <br><br>I am using CR7.0 with Epicor SQL 7.0 Database using ODBC driver <br><br>

 

[MalcolmW]

You could also use a Insert¦SQL Expression Field to get your beginning balance - kind of a simpler form of a subreport, for those cases where you only want one value returned.&nbsp;&nbsp;I haven't used them much, but can only presume they can't be worse than subreports in terms of performance.<br>The running total field in v7 are not to be trusted completely, as you may have found out.&nbsp;&nbsp;They do work in V8.<br>You can roll your own running totals, see help re running totals using formulas.<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[MalcolmW]

P.S.<br>Thanks for putting your version number, database type, and data access method in your post.&nbsp;&nbsp;That is useful info - wish everyone did it. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Ina]

Word of caution: you would be safer not using a sub report but the grass is not greener with Running Totals (RT) anywhere.&nbsp;&nbsp;Even with manual running totals, there are chances that you will get errors in your running totals at page breaks so be careful about checking ALL your numbers.&nbsp;&nbsp;Another problem that occurs when you supress groups for RT is it does not reset the formula because it by passes the supressed groups.&nbsp;&nbsp;To cirumvent this, add a sub section below your RT group calulation and place the reset RT formula in that subsection.&nbsp;&nbsp;GOOD LUCK!&nbsp;&nbsp;

 

[MalcolmW]

Ina,<br>I would be interested in hearing how manual running totals can provide errors at page breaks.&nbsp;&nbsp;I have never had problems with this, or at least never noticed any problems.<br>Ignorance can be bliss <br>Also, while I have found subreports to be slow, they generally deliver the goods.&nbsp;&nbsp;I am curious as to why you say they are risky. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Ina]

Malcolm,<br><br>We have a huge dw and it killed our server when I used subreports to perfom some functionality.&nbsp;&nbsp;We were on v6 at that time and the general advise from Seagate has also been to stay away.&nbsp;&nbsp;Therefore, I do.<br><br>On the nightmare called RT: we have reports that use upto 15 RT formulas and they've always been a pain.&nbsp;&nbsp;When we went to v7, the RT functions were not included and therefore, all reports failed.&nbsp;&nbsp;after we installed the dll's, they worked but still were very slow.&nbsp;&nbsp;Of course Seagate suggested we should use the expert.&nbsp;&nbsp;That resulted in incorrect RT and some of the conditional ones could not be implemented in the expert at all.&nbsp;&nbsp;So of course they (S) changed their story and said to continue using RT functions and manual RT.&nbsp;&nbsp;<br><br>Recently, I had to recreates reports in v7 to remove info view.&nbsp;&nbsp;I copied and pasted all formulas from report created in v6.&nbsp;&nbsp;This report should display Summary (Group 1 only) or Detail (Group 3, 2, & 1) based on parameter.&nbsp;&nbsp;The Detail works fine but the Summary, which supresses Detail and Groups 3 and 2 either resets at every page or counts Group 3 twice.&nbsp;&nbsp;I have this issue open with S and it has still not been resolved though it is over 2 months now.&nbsp;&nbsp;&nbsp;S says that &quot;it should work&quot; and that they've never seen this before.&nbsp;&nbsp;<br><br>It's good to know you don't have nightmares with your RT's - there's hope! (or your data mart is designed better than mine )<br><br>

 

[MalcolmW]

If you have a formula in a group header, and the header is formatted on print on every page, the formula will get fired on every page, not just once in the original group header.&nbsp;&nbsp;This may not be your problem (Seagate should be aware of this, so it likely isn't).&nbsp;&nbsp;<br>My experience with Seagate is that followup of problems is not their strong suit.&nbsp;&nbsp;I'd resubmit it as a fresh problem, and you might get lucky and get a good tech.<br>As for subreports, I spent many hours writing SQL so that I don't have to use subreports - they are inherently slow - but I have found that using them with stored procs helps, both to reduce network traffic, client memory requirements, and speed.<br>As for RT's, I never used the built in ones until v8 - they were just too flaky.&nbsp;&nbsp;It is a ritual for me with a new version - try out the RT's and see if they work, find a bug, and stop using them.&nbsp;&nbsp;As of yet, I haven't had to quit using them in v8. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>My pet peeve:<br>
To provide a solution, someone else must also understand the problem. To understand the problem, it must be described. The tools for describing are things like error messages, product names, versions, and what the problem is.

 

[Caren]

How do I

 

[Ina]

How do you stop using RT's, you mean there is an alternative to it??

 

[MalcolmW]

I can't stop using running totals until people stop asking for them, but there are different ways I use to meet the need.&nbsp;&nbsp;<br>I have 3 methods I commonly use, in order of increasing reliability, and time required to do.<br>1)&nbsp;&nbsp;Use the Running Total functionality built into Crystal<br>2)&nbsp;&nbsp;Build running totals with one or more formulas (usually three).<br>3)&nbsp;&nbsp;Calculate the running totals in SQL and return it to crystal as a field value along with the data set.&nbsp;&nbsp;This is a good method when performance is an issue.<br>Don't know whether that answered your question, or whether you to know how to persuade people not to ask for running totals.&nbsp;&nbsp;If you know how to do that, please let me know

 

[Ina]

Until I learn how...<br>Know any good documentation on 3? how about writing FAQ?<br>or any good documentation on &quot;Insert SQL Expression&quot;?&nbsp;&nbsp;<br>If you do, will share with you my future FAQ on SI alternatives

 

[MalcolmW]

Option 3 is more of a SQL thing rather than a Crystal thing.<br>Two examples from Joe Celko's SQL For Smarties:<br>These are for a bank account, where the running total is the balance of the account after each transaction.<br><br>SELECT B0.transaction, B0.transdate, SUM(B1.amount) AS balance<br>FROM BankAccount AS B0, BankAccount AS B1<br>WHERE B1.transdate &lt;= B0.transdate<br><br>or using a scalar subquery (great terminology to confuse and intimidate)<br><br>SELECT B0.transaction, B0.transdate, <br>&nbsp;&nbsp;(SELECT SUM(B1.amount)<br>&nbsp;&nbsp;&nbsp;FROM BankAccount AS B1<br>&nbsp;&nbsp;&nbsp;WHERE B1.transdate &lt;= B0.transdate) AS balance<br>FROM BankAccount AS B0<br><br>Celko has several well written books on SQL.&nbsp;&nbsp;In return for stealing his examples, I will offer him a plug for his books.<br>

 

[Ina]

Thanks Malcolm!&nbsp;&nbsp;

 

[Gyver]

Hey MalcolmW.
I have a question about the first responce you made to this thread. You said that &quot;You could also use a Insert¦SQL Expression Field to get your beginning balance&quot; (I'm assuming you meant SQL instead of ¦SQL). What version was that fuctionality added in? I'm using version 6 and I can't find it. If it is available in version 6, where would I find it?

Ryan Morton
U of Waterloo.