Re: theory and practice: ying and yang
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?
>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?
> This kind of thing, which is basically presentation rather than data
> extraction is better done by a client tool, is it not?
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.auReceived on Tue Jun 07 2005 - 15:48:51 CEST