Re: theory and practice: ying and yang

From: mountain man <hobbit_at_southern_seaweed.com.op>
Date: Tue, 07 Jun 2005 13:48:51 GMT
Message-ID: <7Bhpe.7186$F7.1591_at_news-server.bigpond.net.au>


"Paul" <paul_at_test.com> wrote in message news:42a56d90$0$8716$ed2619ec_at_ptn-nntp-reader02.plus.net...
> mountain man wrote:
>>>But when the external code writes a report, how does it know what the
>>>column titles should be? How wide the columns are?
>>
>> There is no external code. The report is written by a DBA
>> (or nominated party) in SQL (from report specifications
>> that have been prepared in consultation with the client
>> organisation) and saved as a stored procedure, column titles,
>> column widths and default sort being specified in the sproc.
>
> What if a report with subtotals at various levels is required?

Use a union statement.

>Or other
> fancy stuff like running totals, subreports etc?

Drill-down into sub-reports by chaining further stored procedures to the upper level report.

Running totals can be written in temp tables if the coding is too intensive in one step.

>Or crosstab reports?

Easy to do in SQL.

> This kind of thing, which is basically presentation rather than data
> extraction is better done by a client tool, is it not?

It is more efficient to allow all processing of data to occur within the (R)DBMS, IMO. There is no reason to use a client tool if SQL can do it.

Optimum performance, and
rapid development.

> You mentioned before about people having to cut & paste things into
> Excel if they want to play around with the data, but this sounds like a
> recipe for disaster, knowing some people's technical abilities. They
> want to click a button and have a report come up just how they want.

Well, this is organised by writing the report just how they want it using SQL. However, often people examine a report in order to perform some analysis on the data presented, such as sorting the data set (of the report) by any column in the report, or filtering the data set by any element, or both.

By determining in advance the underlying data required to be graphed, one may write a stored procedure to generate that specific data set. The user can export this to excel, highlight the data in excel and click the chart wizard to create a graph.

Yes, I agree some people will not be able to perform this function. But that is the nature of the territory. There will be someone who can do this step, and the "hard" part of assembling the appropriate data elements (which could be very very complex) is handled by the sproc.

-- 
Pete Brown
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au
Received on Tue Jun 07 2005 - 15:48:51 CEST

Original text of this message