Preformatted Report File
Hello All,
I have a proc whose final result is from the below query:
Please let me know.
I have a proc whose final result is from the below query:
select REPLICATE(' ',26)+
REPLICATE(' ',9 - LEN(residentnumber)) +
residentnumber
+ REPLICATE(' ',29)+
TransactionID+ REPLICATE(' ',16-LEN(TransactionID)) + REPLICATE(' ',30)
+ cast(Quantity as varchar)
+REPLICATE(' ',12-LEN (Quantity))
+ cast(sum(UnitPrice) as varchar) + REPLICATE(' ',12-LEN (sum(UNITPRICE))) +
REPLICATE(' ',13)
+ DATE + REPLICATE(' ',8-LEN(DATE))+
REPLICATE(' ',37) AS RECORD
from @t group by ResidentNumber,TransactionID,date,QuantityPlease let me know.
ASKER
hello mlmcc,
I have used the same query to return the data and exported the report to text file.I have attached the text file, but I see a char 'o' in the first line and again couple of lines below appearing 5 times at the beginning of a line in the file. Not sure how is that coming.If i could remove that char, the file is all set for production.Please help.
test.txt
I have used the same query to return the data and exported the report to text file.I have attached the text file, but I see a char 'o' in the first line and again couple of lines below appearing 5 times at the beginning of a line in the file. Not sure how is that coming.If i could remove that char, the file is all set for production.Please help.
test.txt
What is the query you are using for the report?
What formula are you using to show the fields on the report?
mlmcc
What formula are you using to show the fields on the report?
mlmcc
ASKER
the query that iam using is in the original post
and Iam not using any formula. Just dropped the concatenated resultset into the report and exported it to text file.
and Iam not using any formula. Just dropped the concatenated resultset into the report and exported it to text file.
Your query must be adding it for some reason. Crystal wouldn't add it. WHen I viewed the text file it showed a weird character at the start and between some records.
mlmcc
mlmcc
ASKER
Yes mlmcc , thats what iam trying to get rid of.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I suspected the special character was a new page but didn't know how to get rid of it.
mlmcc
mlmcc
FWIW, the character wasn't actually an 'o' (the letter). It was a small rectangle. I didn't check, but it was presumably a Form Feed, which you will get in an exported text file if CR tries to add page breaks.
If that was your only problem, then I guess you can accept your own post as the solution.
James
If that was your only problem, then I guess you can accept your own post as the solution.
James
ASKER
my answer gives the solution for the problem
In Crystal you can use Left and Right (depending on where the padding is) to get what you want.
For instance
This converts the residentnumber to a string and adds spaces to the left.
Right(Space(9) & CStr({residentnumber},0,""
To add trailing spaces
Left(CStr({residentnumber}
There are problems with Crystal if you are trying to export this to a fixed format. Crystal tends to strip the leading and trailing spaces from the export.
mlmcc