JWColby
jwcolby at colbyconsulting.com
Wed Feb 21 22:02:50 CST 2007
Shamil,
>And you could have bound your report to this stored procedure or UDF - on
open this report would have requested from/to dates in parameters input
dialog.
Well, when I say "report" that means something a little different here. My
client feeds data to mainframes. They give me a table of specs which looks
briefly like:
Field name Data Type Default Value Format Field width
Required
Field 1 Char Some Val 9
Yes
Field 2 Num Some Val2 000.00 10 No
Field 3
Field 4
.
.
.
Etc.
I then have to pull the data out into queries, and somehow get them out into
fixed width text files, named according to their naming spec etc.
In order to do this I use (what else) classes. I import their table into a
report field spec table (with a few fields of my own to make my life
easier). I then built a class to hold each field spec record plus code to
manipulate the data as required by their specs. Apply the correct
formatting, determine if the field has to be there or not, replace nulls
with default values if any, etc. The Field Spec class is responsible for
all formatting of a given field and handing back a fully formatted string,
regardless of whether the data is a date (YYYYMMDD? MMDDYYYY? MMYYYY?),
currency (00000.00?, 00000^00 - decimal shifted out), string (width? Pad
left / right? Pad char?). Each field class is responsible for error
reporting should any error occur in formatting, padding etc. since the field
class "knows" what the format should be, width of the final string should be
etc.
I build a report spec table which holds the query required to pull the data,
the name / path of the destination file and some other stuff.
In the end I have a system where I load a Report Spec supervisor class and
that class loads the field spec classes, loads the data, processes each
field of each data record through each field class, assembles the strings
returned by the field classes into a big string, and then uses text streams
to write the files out to disk. The Report spec class is responsible for
error reporting at the record level - errors from field classes, no data,
etc., with errors logged to files on the hard disk for attachment to email
to the person who can fix the problem.
These files can and often but not always do have header information which
goes in the same file, which may or may not be the same width as the detail
data, and can have trailer info (usually sums and counts) which are also
appended into the same file.
Thus a "report" will be at the very least a detail section, but may / may
not have a header and / or footer. Processed the way I do, the header and
footer becomes just another set of field specs in the Field Spec Table. The
Report Spec will have one to three records in it, one for a header if any,
one for the detail (they all have a detail) and one for the footer if any.
Thus a method of my report spec class is called passing the PKID of the
detail spec record in the Report Spec table and it takes over and loads
header and footer report spec classes as necessary. Each Report spec class
instance loads it's own data (has it's own query) and processes it's data
according to it's set of field spec records, and writes the data to the file
under the supervision of the detail Report Spec class (the "overall"
supervisor for the report).
I did not do it this way originally, in fact I originally hard coded
everything in the queries - format, conversion of nulls, padding etc. What
I discovered is that it was a MAJOR PITA to adjust a report as I had to go
find the right place in the right query. Done the way I do it now, it is
all "formatted" by entries in my field spec table. If I need to change the
width of a field, I just look up the field in the Field Spec Table and
change it. Same for format, data type, default value etc.
It works really well in fact once I got the classes working. I can
literally tweak a report in minutes and rerun. One might wonder why such
tweaking would be necessary if it was for a mainframe import, and the answer
of course is that the info I get from the mainframe people is always sketchy
- leading or trailing padding, spaces or zeros for padding, and I have to
submit a file for testing, then tweak, resubmit, tweak until they say it is
OK. And of course, it all has to be adjusted on MY END. By building this
into a table, I just change my PadDir column from (L)eading to (T)railing
and it pads the other way, change the PAdChar from 0 to ' ' and it changes
from a zero to a space (or anything else they can think up). I use VB
format strings where possible but sometimes I just have to brute force it.
So we (my client) selects filter data using combos and date text boxes, and
pushes a button and a report is built and placed on disk, in a specific
location, with a specific name (and a dated name for our internal tracking).
I can and do sometimes attach these reports to email or FTP them if that is
necessary.
So these are not "reports" in the Access sense. I now have reports going to
3 different companies, unknown number of different mainframes, all using the
same report generator system. They give me a field spec "table", I import
(or type) that in to my report / field spec table pair, build queries to
pull the data and plug that query name into the report spec table and push
the button. Tweak the query, then the field spec records, send, tweak,
send. After that it just runs.
In fact one issue that this whole class system handles very nicely is
"missing data" since it can just run the reports without writing the files
to disk, then see if any error reports are generated and if so email them to
the examiners. They can go correct the data errors. We can rerun until it
is right, then send.
All in all a royal PITA but that is after all why they pay us the big bucks,
right?
And then came the client that wanted it in an Excel spreadsheet.
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Wednesday, February 21, 2007 4:00 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server
Hello John,
Let's suppose you use NorthWind sample database and you have to calculate
total Freight for a Shipper given from/to dates.
I assume you do not use ADPs - if you have used them then you could have
created
- a stored procedure:
=====================
CREATE PROCEDURE usp_TotalFreightStats (@fromDate datetime, @toDate datetime
)
AS
BEGIN
select ShipName, Sum(Freight) as FreightTotal
from Orders
where Orders.OrderDate between @fromDate and @toDate
Group by ShipName
END
- or a UDF
===========
CREATE FUNCTION TotalFreightStats (@fromDate datetime, @toDate datetime )
RETURNS @TotalFreightStatsTable TABLE
(
ShipperName nvarchar(80),
TotalFreight money
)
AS
BEGIN
declare @fromDate1 dateTime
declare @toDate1 dateTime
INSERT @TotalFreightStatsTable
select ShipName, Sum(Freight) as FreightTotal
from Orders
where Orders.OrderDate between @fromDate and @toDate
Group by ShipName
RETURN
END
And you could have bound your report to this stored procedure or UDF - on
open this report would have requested from/to dates in parameters input
dialog.
I assume you use mdb and then you can:
- create table to keep parameters
==================================
CREATE TABLE [dbo].[ParametersForUDFs] (
[fromDate] [datetime] NULL ,
[toDate] [datetime] NULL
) ON [PRIMARY]
GO
- populate this table with parameters' values (clear in first)
=============================================================
delete from ParametersForUDFs
insert into ParametersForUDFs (fromDate, toDate) values ('1996-07-04',
'1996-07-12')
- create UDF
============
CREATE FUNCTION TotalFreightStats1 ( )
RETURNS @TotalFreightStatsTable TABLE
(
ShipperName nvarchar(80),
TotalFreight money
)
AS
BEGIN
INSERT @TotalFreightStatsTable
select ShipName, Sum(Freight) as FreightTotal
from Orders
where Orders.OrderDate
between (select top 1 fromDate from ParametersForUDFs)
and (select top 1 toDate from ParametersForUDFs)
Group by ShipName
RETURN
END
- use UDF in view
==================
CREATE VIEW dbo.vw_TotalFreightStats1
AS
SELECT ShipperName, TotalFreight
FROM dbo.TotalFreightStats1() TotalFreightStats1
++++++++++++++++++++++++++++++++++++
- link view to MS Access mdb
++++++++++++++++++++++++++++++++++++
Of course that approach will not work well in multi-user environment if
different users will need different from/to dates...
--
Shamil