Personally I would do multiple searches and in each search I would also bring back the data pre calculated for example: (If your DB supports it, like Oracle, I would use a begin end block and put all the selects in one direct DBMS and return all the data in one shot)
begin; select col1, col2, col3, numberofRepairs, SUM(numberofRepairs) as totalRepairs from theInfoTable where someColumn = 'OEM'; select col1, col2, col3, numberofRepairs, SUM(numberofRepairs) as totalRepairs from theInfoTable where someColumn = 'OEM'; select col1, col2, col3, numberofRepairs, SUM(numberofRepairs) as totalRepairs from theInfoTable where someColumn = 'OEM'; end; Then you have an array with 5 fields the last one having the sum of all the repairs in the search. When you display it in using the array, I would sum all the rows in the summed up number of repairs like this: <@CALC EXPR="SUM(<@var local$resultSet[*,5]>)"> This will display the sum of all the column 5 items for the number of repairs. This way you don't use filters and stuff like that in Tango. It sounds like you have a lot of data to bring back, and I think that you should use selects, because beyond a certain point, Tango is actually MUCH slower at doing filters, sorts and that kind of stuff than just doing a simple quick select out of the DB which will only take is 0.1 ms. Of course now if we're talking MS Access or something like that, then I don't think it matters much, but if you have this data in Oracle, or MS SQL then this is definitely the way I would do it. Put MORE on the DB server and less on the App server, because your app server is likely to be much busier than the DB server, and the Tango app server's load can suffer if a lot of users are waiting for data to come back, and it's pretty much always busy serving other non-database driven pages. R On 7/11/02 12:45 PM, "Mark Bushaw" <[EMAIL PROTECTED]> wrote: > I think your solution depends on your resources. If you have a fast database > and > a fast connection to it, then you can get away with one search and manipulate > the > resultset with WiTango. If your database is slow with a fast connection, many > small searches will keep the customer wait time down. If your connection to > the > database is slow, it may not matter. > If the customer wants 400 rows in an itemized report, that is what you give > them. > But it could be that once they see what they are asking for they will decide a > more > condensed report will work. The time spent on planning the report display > will pay > off in reduced code changes. Maybe have the user select one of several > reports > to display, then do your search based on the customer request? I don't like > retrieving excess data from the database. > Mark Bushaw > > On 11 Jul 2002 at 12:01, Jose Kuhn wrote: > >> Here is how the reports supposed to work. There are nine (I lied) Repair >> Categories and 3 Repair Tracks (OEM, Broker, Direct). For each of the 27 >> items I need # of repairs and the total cost. I also want to total the >> columns and the rows as well as the grand totals. Our customers just want a >> general monthly report on how much they have spent, And they want it >> itemized. If I were to do a search for one month of repairs it could easily >> be 300-400 rows in my array!! >> >> I just want to do the most efficient search That does not task the server >> while not taking a lot of time to post the result. ( I can dream can't I ;) >> ) >> >> I hear what you are saying but our customers are not going to spend the time >> doing 9 separate searches they just want to know what they spent last month >> and where. As usual in order to make it simple for the end user there is >> going to be a lot of coding. >> >> >> Thanks for the input thus far!!!! >> >> Jose >> >> on 7/11/2002 11:19 AM, Len Wright at [EMAIL PROTECTED] wrote: >> >>> I have written several reports that sound similar in scope to what you >>> are doing. My strategy is to provide the user with several selection >>> fields to build the query based in the user's input, and then bring back >>> a resultset that matches the selection fields. >>> >>> if your categories and subcategories fields are indexed, then this >>> approach is very quick and you don't bring back a bunch data that >>> doesn't get used. >>> >>> >>> >>> Regards, >>> >>> Len Wright >>> Plus International Corp >>> www.plusinternational.com >>> >>> Direct: 604-415-2384 >>> Fax:604-415-0830 >>> >>> >>>>>> [EMAIL PROTECTED] 07/11/02 08:20AM >>> >>> Before, I waste a lot of time going down the wrong path I would like to >>> know >>> the most efficient way to handle generating reports from a huge array. >>> >>> Here is the deal. I need to generate a cost report of repair orders >>> that has >>> 7 categories and three sub categories. Is it better for me to do one >>> search >>> and generate a huge array to manipulate through filters, do a bunch of >>> little searches or put the Results array into a JavaScript array and >>> let >>> JavaScript handle the gory details? >>> >>> Thanks in advanced, >>> >>> Jose >> >> -- >> Webologies >> 150 Robinette Drive >> Waynesville, NC 28786 >> 828.627.1994 >> >> http://www.webologies.com >> >> ---------------------------------------------------------------------------- >> "You can't make an omelet without breaking eggs" Boris Badenov >> ---------------------------------------------------------------------------- >> >> > > > ________________________________________________________________________ > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body > ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
