Printing Reports
-
Hello,
We have several reports on the web that require printing. Now as I am sure all of you know it can be a pain to write all the code required to format the lines correctly so they dont wrap.
There is one report that I am thinking of that has caused me much pain in the past. It prints out all the operations and instructions to create a part. Well the operation descriptions can be quite long so I needed a way to accuratly print and count lines of text. I had written a class that would formatt the information breaking at the correct length for printing but the problem was the ammount of time it was costing me when the report would render. So I thought to my self.. Self.. wouldn't it be great if I had a function that would formatt the information for me so when I pulled it from the database all I had to do was display and count?
So I wrote a little function that does just that:
---------------------------------------------------------------------------
CREATE FUNCTION F_FormatRevisionDescForPrint(@Desc VARCHAR(1000), @DisplayLenghOfString INT)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE
@CharacterCounter INT
,@DescLen INT
,@WhenToBreak INT
,@FormattedDesc VARCHAR(2000)
,@NewLine VARCHAR(2)
,@Space VARCHAR(1)
,@NextCharacter VARCHAR(1)
,@PreviousCharacter VARCHAR(1)
,@CurrentCharacter VARCHAR(1)
,@NewString VARCHAR(2000)
,@NumberOfCharactersToCut INT
--
SELECT @CharacterCounter = 0
,@DescLen = LEN(@Desc)
,@WhenToBreak = 0
,@FormattedDesc = ''
,@NewString = ''
,@NewLine = CHAR(13) + CHAR(10)
,@Space = ' '
--
IF (@DescLen > 0) BEGIN
WHILE @CharacterCounter <= @DescLen BEGIN
--
SELECT @FormattedDesc = @FormattedDesc + SUBSTRING(@Desc,@CharacterCounter,1)
,@CurrentCharacter = SUBSTRING(@Desc,@CharacterCounter,1)
,@NextCharacter = SUBSTRING(@Desc,(@CharacterCounter+1),1)
,@PreviousCharacter = SUBSTRING(@Desc,(@CharacterCounter-1),1)
,@WhenToBreak = @WhenToBreak + 1
--
IF (@WhenToBreak = @DisplayLenghOfString) BEGIN
-- I need to check and make sure that I am not breaking on
-- a word.
IF (@CurrentCharacter = @Space) BEGIN
-- There is a space here so it is ok to break
-- I know that I am not in the middle of a word
SELECT @FormattedDesc = @FormattedDesc + '<BAR />'
,@WhenToBreak = 0
,@CharacterCounter = @CharacterCounter + 1
--
END ELSE IF (@CurrentCharacter != @Space) BEGIN
-- houston we have a problem I am somewhere in a word
-- I have to go back in the string till I find a space.
-- When I find a space I am going to remove the rest
-- of the characters from the string.
-- EX: String: A T<BR>OOL New String: A<BR> The word tool will be cut off.
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
-- reverse the string that way it will be
-- easier to find the space
SET @NewString = REVERSE(@FormattedDesc)
-- get the first index of a space
SET @NumberOfCharactersToCut = PATINDEX('% %',@NewString)-1
-- now reverse the string again so it is no longer backwords
SET @NewString = REVERSE(@NewString)
--
-- get the new substring
SELECT @FormattedDesc = '' + SUBSTRING(@NewString,0,(LEN(@NewString)-@NumberOfCharactersToCut)) + '<BAR />'
,@WhenToBreak = 0
,@CharacterCounter = @CharacterCounter-@NumberOfCharactersToCut
END
END ELSE BEGIN
--
SELECT @CharacterCounter = @CharacterCounter + 1
--
END
END
END ELSE BEGIN
SET @FormattedDesc = ''
END
RETURN @FormattedDesc
END
-------------------------------------------------------------------------
Example of use:
SELECT REPLACE(LTRIM(RTRIM(dbo.F_FormatRevisionDescForPrint(Routing_Revisions_Edit.[Description],47))),'<BAR />','<BR>') AS Description
FROM MyTable
--------------------------------------------------------------------------
Well any thoughts are welcome.
Thanks
Will
-
After you asked yourself
"Self.. wouldn't it be great if I had a function that would formatt the information for me so when I pulled it from the database all I had to do was display and count?"
you should have told yourself
"no, no, Formatting of data is the job of the presentation layer, Sql server does not effeciently format and or manipulate text."
Just make sure you don't argue with yourself, Because thats a sure sign that your Insane.
But seriously. You have to really try to make formatting data a function of the presentation layer.
I did not really evaluate any of the code you posted. Sorry
-
Pretty sharp, Womalley... sometimes ya gotta do what ya gotta do.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I just hope I won't be one of those guys
. -
... and then again there is always SQL Reporting Services
--------------------
Colt 45 - the original point and click interface
-
Thanks for the reply's
Ok...
SQL Reporting services is a great tool and I have used it for some reports but it has limits and would not work for what I need to do.
--------------------------------------------
After you asked yourself
"Self.. wouldn't it be great if I had a function that would formatt the information for me so when I pulled it from the database all I had to do was display and count?"
you should have told yourself
"no, no, Formatting of data is the job of the presentation layer, Sql server does not effeciently format and or manipulate text."
Just make sure you don't argue with yourself, Because thats a sure sign that your Insane.
But seriously. You have to really try to make formatting data a function of the presentation layer.
I did not really evaluate any of the code you posted. Sorry
----------------------------------------------
This is a VERY big mistake. Why take the time on a slower machine and cause a timeout?
Why not use the power of SQL Server to make my life easier?
I understand that when formatting imformation for presentation it is much easier to do that after you have pulled the data. But to go through all the loops and ... well... pain of getting this text to do what I want is just not worth it.
Example:
1 report that I can think of took 1 minute to render (50 pages) of instructions. That is not acceptable.
After taking the 30minutes it took to write this function it took a few seconds. I really could not believe the ammount of time it cut. But then again when I though of the hundreds of lines of code I didnt need to run anymore.
This may not be the best practice but for this situation it works wonders and ... no more timeouts
Thank you all again.
Will
-
One question comes to mind... I know that the server is probabely more powerfull than the user's pc. But still 60 times faster???? This sounds to me like code optimization could be done on the client code.
Also I agree that on rare occasions it's faster on the Server, but it's should really be RARE and on last resort.
-
Believe me I tried to look at the code and make it as fast as I can.
But the more loops and complex string minipulation you have to do the slower it is.
It was rare most of the reports printed fine. It was the ones that had operation instrictions that took 2 pages to explain where you would run into problems. Also you have to deal with how the uses put in the data. Most of the time you would find no new line characters only text strings that were thousands of characters long. Now I am sure that I could have changed the entry screen to force a new line after a specific ammount of characters but then what would happen if the ammount of room I have to display the data changes? I still need to format it.
this way I can pass in the length I want for that specific report and not worry about anything else.
Thanks again this is a great exchange I look forward to more!
Will
-
HTH.
-
huh?
-
Happy to help.
-
I am almost certain that it is possible to perform that formating client side without coding on most report writers!!!
* Noel -
there are always several answers to questions. The answer presented here was the best fix at the time with out needing to learn and understand some reporting software.
To create the report using BI could be done... but there are always limits!
The report in question is very complex now I am sure that there is software that would suit my needs, I mean you can buy an answer for anything if you have the money and in house experience for implementation.
I wanted to post this code for technical review and suggestions on how to make it better. The function is in use and has worked wonders.
Thank you again for the replies. I have lots more code to post and talk about as this is the best forum I have found for SQL in a long time.
William O'Malley
-
HTH means "Hope this helps". Here's a great site for deciphering acronyms:
-
thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply