November 31, 1996 - Adventures in Development

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

November 31, 1996 - Adventures in Development

Author

Topic

grrr223
Starting Member

30 Posts

Posted - 2004-02-21 : 01:32:51

To overcome the limitations in the ERP software we use at work, we use LOTS of Excel spreadsheets. Since modifying the software itself to do what we want is not a simple thing to do, I have begun developing an Access Data Project to replace the spreadsheets. It's all going very well, thanks to awesome sites like this one.

My Day's Experience with Running Totals

My top priority at the moment is to generate PRESENTABLE customer statements. We use Crystal Reports for some reports, and I have become quite skilled with it. However, I figured I'd try out the reporting capabilities in Access since we're going to be using it to enter data as well.

The customer statements we had in Crystal used a running total to get a beginning balance for the period, so I did a Google search, and came up with this link:
[url]http://www.databasejournal.com/features/mssql/article.php/3112381[/url]

and this code:

select OrderId, OrderDate, O.OrderAmt
        ,(select sum(OrderAmt) from Orders 
                          where OrderID <= O.OrderID)
          'Running Total'
from Orders O

Which worked great on the 10 record example table in the demonstration, and even on the 1,000 record table in one of the databases I was developing on. However, our other two databases contain 56,000 and 113,000 rows. Let's just say that that procedure doesn't scale well!!! Running that procedure on the 113,000 rows requires 326 MILLION rows according to the query analyzer and took 25 minutes to run.

I ended up dropping the running total for each line item on the statement, and just running the Sum() subquery for the beginning date which doesn't require millions of rows and things are going well...for now. I think I'll add the running total as some sort of VB function in the actual report.

My Day's Experience with the 31st of November

Our wonderful ERP software stores all dates as integers. They provide a function to convert them to date format in Crystal Reports, but I had to create my own scalar function to convert them in my ADP. So, I learned how to write a function, and everything worked fine on the 1,000 record database, and even on the 113,000 record database. However...our illustrious software somehow managed to enter a date of 19961131 in 2 of the 56,000 rows in the third database.

Now, obviously, this is something I should have been looking for?!?? So, after finding out wtf was even causing the error after all of my tests for Is Numeric, IS Null, and checking that the range was 19930101 to 20041231 provided no clues as to what the problem was, I got to change the formula to change that one special case from 1131 to 1201 because I need that record.

What a day! Although I do have to admit that this is all a lot of fun. This place is awesome, and I promise I'll post more constructive messages next time.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-21 : 17:29:23

FYI --

Both crystal and Access can calculate running totals for you, without having to do it in the SQL query. YOu don't need VB or anythign fancy, let me know if you need any specific instructions on how to do this. but the HELP files for both products explain it pretty well. Unless you need running totals for further SQL processing (updating tables, doing calculations or whatever) always try to do all totalling at the presentation layer.

also -- I personally find MS Access to be the best report writing tool out there, because it includes a query processor (the JET engine) and VB, along with event processing and all that. you can do basically anything that Crystal can do, and then much more. of course, this is in terms of printing a report to paper using the actual application (Access or Crystal), not an interactive web report or anything like that.

- Jeff

grrr223
Starting Member

30 Posts

Posted - 2004-02-22 : 01:16:19

Our accounting software has a ton of ugly looking reports written in COBOL, and just recently they re-wrote a bunch of them in Crystal, so half my company just went through Crystal Reports training and all that, but the more I learn about Access, I agree with you. It can do everything that Crystal does, but since it also lets you modify the data, AND has VB capabilities, it kicks ass.

I know that Crystal and Access can both do running totals for me. Unfortunately, I have a few complications such as, Crystal can't do running totals on certain fields that are formulas. So I had to create my own, which actually wasn't that big a deal. Apparently it's a pretty standard method in Crystal, works well and allows a lot of control of ther results, it's just a lot of work to get the one number. The method can be found here: http://support.businessobjects.com/communityCS/TechnicalPapers/scr_running_total.zip.asp

My problem with the Access running totals is that they only perform a running total on the fields that are being displayed. However, I need a running total for all of the records in the query. Hmm, let me try to explain that better. I have a table AROPNFIL that contains records for all open accounts recievable items (invoice, payments, credits, etc.) and the running total needs to be for all records in that table, but for a monthly statement, I only want to show an opening balance, the current month's activity, and a closing balance. If I use the Access running total, it starts the running total at 0, but I need it to start at the opening balance. Any suggestions would be GREATLY appreciated.

Since you seem to have experience with both Crystal and Access, do you have any suggestions on how to replicate Crystal's multiple sections feature. Crystal lets you have say, 2 or 3 separate details sections, and then you can turn them on or off using conditional formulas. It lets you have separate formats for different types of records. The only thing I could think of to replicate this is to use VB to rearrange stuff for each record, but that requires a bit more work.

Anyway, thanks :).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-22 : 09:45:16

for multiple sections: just add a new group, with the same expression. i.e., if you want to group by customer, invoice and date, and you wish for multiple "customer" sections, just group by "Customer" twice! it works quite well.

For running totals over all rows, not just the one's the report is returning, I assume that in your report's criteria, you must have a date range of what to display. then, you can do it at least three ways:

1) in your report query, return all rows up to your end dateand only DISPLAY rows in your date range. you can do this with vb on the each section's Print property. check the date, if it is > startDate, show it, otherwise hide it. but running totals should continue to accumulate. this works well with Crystal reports, and is how I wrote a "billing statement" report to work one time.

2) or, you can UNION your report with 1 row giving the totals BEFORE the start date in your date range, and make sure it sorts to the beginning. then, that row is your "previous balance" and from there you can do running totals.

3) you can also use VB and Dlookup's to get the previous balance and go from there, but make sure you set a variable in the beginning of the report with the prev balance and use it again and again, instead of the running the DLookup for each line in the report. you can do something like this:

dim prevBal as currency

private sub Report_Open()
prevBal = DSUM("Amount","YourTable","Date < #" & startdate & "#")
end sub

private function GetBal() as currency
getBal = prevBal
End function

and then on your report, in the "Balance" text box, you use a formula like this for the data source:

Amount - GetBal()

and set that to be a running total. you can do all kinds of tricks with VB and formulas as you've stated. even dynamic cross tabs are possible with vb functions embedded in your report, along with changing column headers and the like.

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-02-23 : 09:06:36

Just to add my 2 cents in, I am with Jeff.

Access reports is much easyer to use than Crystal Reports.

And since you can create tables, S.P.s, Views,forms,Triggers ext. all within Access this is a boon for fast devlopment.

The only thing I use EM for is to set Permisions.

Jim
Users <> Logic

grrr223
Starting Member

30 Posts

Posted - 2004-02-23 : 13:18:16

I really appreciate your help, this is the jump start I needed to progress further on this, I hope to soon be able to answer other people's questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources