Flat Files
-
And another thing...
If you really want formatted results, then use Business Objects' Crystal Reports or Microsoft Reporting Services.
These are much closer to Oracle Reports in functionality but with modern designers and much prettier output.
I use DTS for the simple reports that someone else processes with Excel macros to pretty up or further modify. The PDFs for upper management come from Crystal Reports. In some cases, the same sprocs are used by both.
-
Hi,
I really want formatted results, but just in text format. I know what you're saying about Crystal or Reporting Services (or Oracle Reports, for that matter), but they're just too hard to work with in a dynamic manner.
The delimiters work just fine with Excel, I've used the ` grave frequently. These are characters that have no lexical value (i.e., no language that uses the Roman alphabet ever uses the ` or ~ by itself), so the common problems of commas and quotes are avoided. You really don't ever need to quote anything.
I've never tried 0x7F with Excel, though I did once use 0xFF in another way. The system we were using displayed 0xFF as a blank (but it wasn't). The system was outputing a .CSV file, but they didn't like the leading 0's being removed, which happens automatically when you read in a .CSV into Excel; they didn't want to format the columns, and I didn't want to teach them how. So, I appended a 0xFF character to the beginning of the offending number strings, no truncation of the leading 0's occurred and they were all none-the-wiser to the strange character in the cell.
Steve
-
Habving just started Oracle DB support (oh about a year ago) I have some idea of what you are looking for from SQL2000 from an Oracle perspective. Unfortunately at present a comparison of Oracle SQL Plus versus SQL2000 osql/bcp/dts, SQL0000 fails miserably. However, when SQL2005 rolls out to GA (Q4 this year ?) the osql replacement SQLCMD will definitely equal the functinality you presently enjoy with SQL Plus.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hi,
Thanks Rudy. Your post definitely makes up my mind, unless someone makes me an offer I can't refuse
. I'll start with Oracle, then, if there's a demand for it, I can implement a SQL2005 version if and when it becomes available.Steve
-
By the way Steve, I've been s MS/SQL DBA since v4.21, Sybase since v4.9.2, DB2 since v1.0 and Oracle since v8. So there's no bias in my suggestion.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hi Stephen,
T-SQL, for that any Microsoft product, is well know for its libraries, and with regard to you, stored procedures and functions. So to answer your question, yes you can definitely duplicate the SQL*Plus script in a stored procedure using T-SQL, of course though not as straight forward as Oracle does, and also it gets saved as a stored procedure on the server so that solves the problem of persistence and locating the same.
Hope this helps.
Tony John.
-
BCP and DTS do not pretend to have reporting capabilities, they are just ways to dump query results to a text file. I might be tempted to use a script task in DTS and write the report in VBScript.
The basic functionality T-SQL provides is using SELECT ... GROUP BY ... WITH ROLLUP to generate grouped results with subtotals and grand totals. After that it is up to you to decorate the group and page breaks.
Just for fun I tried to duplicate your example output with some code that puts the grouped output in a temp table and massages it. This will produce a plain-text, 66-line-per-page report with extra lines in department breaks, page headers, and footers. This would work in a stored procedure, or in DTS it could be used as the source query for a data pump task to a text file destination.
(I haven't done this kind of thing since my Fortran days. Are you sure you can't scrounge up an open-source version of RPG?
) (If you don't get the RPG joke, you're probably better off for not knowing. 
)-- Create table for basic report
-- Identity field incremented by 100 to allow room to add extra rows
create table #rpt1 (
linenum int identity(100,100) not null primary key clustered,
dept char(10),
lname char(25),
salary char(16),
GrandTotal bit not null default(0),
DeptTotal bit not null default(0))
-- Insert rows with department subtotals and grand total
insert into #rpt1
select dept,
case when grouping(lname)=0 and grouping(dept)=0 then lname
when grouping(dept)=1 then 'Grand Total'
else 'Department Subtotal' end as lname,
left(right(space(19) + '$' + convert(varchar, sum(salary), 1), 19), 16) as salary,
grouping(dept) as GrandTotal,
grouping(lname) as DeptTotal
from #salaries
group by dept, lname with rollup
-- -- To put dept summaries first:
-- order by GrandTotal, dept, DeptTotal desc, lname
-- Blank out department field after first row of each group
update #rpt1 set dept = ' '
where linenum not in
(select min(linenum) from #rpt1
where dept is not null group by dept)
-- Add additional break lines
set identity_insert #rpt1 on
-- Department breaks: Bars before, empty line after subtotals
insert into #rpt1 (linenum, dept, lname, salary)
select r.linenum + d.offset, d.dept, d.lname, d.salary
from #rpt1 r
cross join (
select -1 as offset, ' ' as dept, replicate('*',25) as lname, replicate('-',16) as salary
union all
select 1 as offset, ' ' as dept, ' ' as lname, ' ' as salary
) d
where r.depttotal = 1 and r.grandtotal = 0
-- Add bars before grand total
insert into #rpt1 (linenum, dept, lname, salary)
select r.linenum + d.offset, d.dept, d.lname, d.salary
from #rpt1 r
cross join (
select -1 as offset, ' ' as dept, replicate('*',25) as lname, replicate('-',16) as salary
) d
where r.grandtotal = 1
set identity_insert #rpt1 off
create table #rpt2 (
id int identity not null primary key clustered,
line varchar(255)
)
declare @page smallint
declare @L1 int, @L2 int, @Lmax int
declare @pad smallint
set @page = 1
set @L1 = 0
select @Lmax = max(linenum) from #rpt1
while @L1 <= @Lmax BEGIN
insert into #rpt2 values ('A C M E W I D G E T')
insert into #rpt2 values (' ')
insert into #rpt2 values ('EMPLOYEE REPORT PAGE: ' + cast(@page as char(2)))
insert into #rpt2 values (' ')
insert into #rpt2 values ('DEPARTMENT LAST NAME MONTHLY SALARY')
insert into #rpt2 values ('---------- ------------------------- ----------------')
select @L2 = MAX(linenum) from (
select top 58 linenum from #rpt1 where linenum >= @L1 order by linenum) x
insert into #rpt2 (line)
select dept + ' ' + lname + ' ' + salary
from #rpt1 where linenum between @L1 and @L2
order by linenum
-- Add extra blank lines on the last page
set @pad = 58 - @@rowcount
while @pad > 0 begin
insert into #rpt2 values (' ')
set @pad = @pad - 1
end
insert into #rpt2 values (' ')
insert into #rpt2 values ('COMPANY CONFIDENTIAL')
set @L1 = @L2 + 1
set @page = @page + 1
END
select line from #rpt2 order by id
drop table #rpt2
drop table #rpt1
-
Steve
For your basic output needs, I'd take a look a creating a stored procedure that provides the output in the correct layout. Then just use the OSQL command line tool to output the results of the stored procedure to a text file.
Another option if you want to produce HTML output is sp_makewebtask. With this system stored procedure you can provide basic template files that are populated with a resultset. Although, the catch with sp_makewebtask is that it requires sysadmin privelages.
--------------------
Colt 45 - the original point and click interface
-
Wow Scott!
RPG, a blast from the past...hardcore! Blinking green letters on a black background. You know, the pharma industry really has never moved past that, still Unix/Oracle/SAS. Their one step into thin client, electronic data capture, is just taking baby steps, and really isn't any cheaper than paper.
In SQL+, to produce that report would take about 10 lines of code, give or take. I'm waiting for SS2005!
Steve
-
Scott's code is great, but I would suggest dumping the pure report content (details and aggregates) to a file and then use a separate program to process the data into pages (phillcart's suggestion?). That would provide for better maintenance and more flexibility.
I did something similir for a report that had large numbers of repeating fields due to many JOINs in the query. I wrote a C# program to remove the repeating fields, much like Access or Crystal do for in their GROUPING effect. I've since used that program for other similar reports.
I don't have any experience with RPG, but I was surprised a year or so ago to find out that it was still taught in some college programs and is still the basis for a large number of business systems. Don't knock it too much.
-
Hi,
Not knocking RPG at all. It did what it was designed to do, which was a very necessary task, and it did it well. People trusted (and still do, I believe) their money to RPG, which says a lot. That fact that it's not very sophisticated, who cares?
King Solomon said it best: Ayn call chodesh tachas hashemesh (There's nothing new under the Sun).
Bottom line, it seems that it would take a good chunk of complicated logic in SQL Server to duplicate a few lines of SQL+. For my app, since I want to optimize for sw development efficiency, the choice is currently pretty clear.
Of course, if I could get some development capital, I certainly wouldn't mind creating a SQL Server version
!Like I said in an earlier post, there's probably good money to be made for someone to duplicate SQL+ for SQL Server.
Steve
-
How about the best of both worlds? Create teh sql to grab the correct data/summary and shoot it out as an XML file. A quick DTD to format it and VOILA! Instant web report!
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
Hi David,
Thanks, but this isn't going onto the web, it's not going into a browser at all.
Also, don't you mean XSL to format it? Isn't the DTD where the data model is defined? And from my experience, it's the DTD/schema that's really hard to do well.
Steve
-
Ok, here's a way I would probably end up doing it. This is pretty rough, I did do a bit a fiddling around to get some of the data to line up. If alignment isn't an issue, you could simplify it a bit. You could also modify the stored procedure to accept parameters etc...
1) Create stored procedure to produce required output. NOTE: the use of the rollup on the GROUP BY which produces the totals automagically.
Also,
- you wouldn't have a use for the first temp table (green highlight) if you were querying the data directly.
- you could derive/format the static text details information (blue highlight) via other tables, etc...
(In fact you could probably do the whole lot with Union queries and do away with the temp table)
CREATE PROCEDURE txtrpt AS BEGIN SET NOCOUNT ON
-- Create and populate test data CREATE TABLE #dpt( deptID char(10) , lastname varchar(50) , Salary int )
INSERT INTO #dpt VALUES(20, 'Hartstein', 13000) INSERT INTO #dpt VALUES(80, 'Russell', 14000) INSERT INTO #dpt VALUES(80, 'Partners', 13500) INSERT INTO #dpt VALUES(90, 'King', 24000) INSERT INTO #dpt VALUES(90, 'Kochhar', 17000) INSERT INTO #dpt VALUES(90, 'De Haan', 17000)
-- create and populate report table CREATE TABLE #Rpt ( RptLine char(45) )
INSERT INTO #Rpt VALUES('A C M E W I D G E T ') INSERT INTO #Rpt VALUES('') INSERT INTO #Rpt VALUES('EMPLOYEE REPORT PAGE: 1') INSERT INTO #Rpt VALUES('') INSERT INTO #Rpt VALUES('DEPARTMENT LAST NAME MONTHLY SALARY') INSERT INTO #Rpt VALUES('---------- --------------- --------------')INSERT INTO #Rpt SELECT CASE WHEN ISNULL(lastname, '') = '' THEN CASE WHEN ISNULL(deptID, '') = '' THEN CHAR(10) + '******* ----------' + CHAR(10) + 'Total: ' + RIGHT(REPLICATE(' ',14) + CAST(SUM(Salary) as varchar(14)), 14) + CHAR(10) ELSE '******** -----------' + CHAR(10) + 'Sum: ' + RIGHT(REPLICATE(' ',14) + CAST(SUM(Salary) as varchar(14)), 14) + CHAR(10) END ELSE CAST(deptID as CHAR(10)) + ' ' + CAST(lastname as CHAR(15)) + ' ' + RIGHT(REPLICATE(' ',14) + CAST(SUM(Salary) as varchar(14)), 14) END FROM #dpt GROUP BY deptID, lastname WITH ROLLUPINSERT INTO #Rpt VALUES('')INSERT INTO #Rpt VALUES('COMPANY CONFIDENTIAL')-- ouput report data SELECT * FROM #Rpt -- clean up DROP TABLE #dpt DROP TABLE #Rpt END GO
2) Now that you have the data being presented in the format you want, use the OSQL command line utility to execute the stored procedure and dump the results to a file.
osql -E -S <<Server>> -d <<database>> -h-1 -Q "EXEC txtrpt" -o txtrpt.txt
3) This produces a text file that looks like this,
A C M E W I D G E T EMPLOYEE REPORT PAGE: 1 DEPARTMENT LAST NAME MONTHLY SALARY ---------- --------------- -------------- 20 Hartstein 13000 ******** ----------- Sum: 13000 80 Partners 13500 80 Russell 14000 ******** ----------- Sum: 27500 90 De Haan 17000 90 King 24000 90 Kochhar 17000 ******** ----------- Sum: 58000 ******* ---------- Total: 98500 COMPANY CONFIDENTIAL
--------------------
Colt 45 - the original point and click interface -
Hi Phill,
I've done similar things in different circumstances. It's probably how I would implement it were I to use the current version of SQL Server.
I've never had any doubt that it could be done in SQL Server. It's mostly just a question of how easy it would be to do, and how easy it would be to abstract.
Steve
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply