Hi,
Can aybody help me in the below requirement.
I have a report like below:
<<Name Of Compnay>>
<<Name Of Report>>
<<Todays Date>>
<<ReportPeriod>> From Date - To date
The above is header and then the data in the report should come as the <<Data>> and # to be used as separator for columns.
163 #GXXX #ABC Comp Ltd. #DTDC#PPPP ABC XYZ ETC #111
*End Of Report*
How can I get this exported as it is to the .txt file?
Currently I have acheived this with SSIS, where I have kept the header template, footer template in my folder. The SSIS generates the .txt file and then I use the execute process transformation to merge the files and data. I am getting the exact o/p as I want. But in this I am intorducing lot of IO in the package by every time creating a new header for the data to be replaced as todays date and the period to be replaced by the user selected Report Period.
How can this be achived using SSRS? Is any custome code required?
Thanks in advacne for your help.
Regards,
Virendra
Note that I have designed the report int he above fashion and I can see this in the browser. I want this to be stored as txt file in the same format as the report is displayed. CSV will not do.
Any help will be appreciated in this problem.
Virendra
|||
There are a couple of different ways to do this, none perfect. The hardest one is the most perfect, as is usually the case <g>, but every case what you want to do is subject to certain layout limitations, which I am sure you already recognized.
OK:
* -- hardest: write your own delivery extension. Now you're in charge of what comes out to the text file and there is nothing in the way.
* -- not actually hard at all but most people don't want to do it, and I wish I understood why <sigh>: write an XSLT that takes your data from the XML export version of the report and converts it to text. Not very hard to make generic, FWIW. To get your custom headers, if you want to do this with no code at all and just attach the XSLT to the data properties of the report, you probably need have another little table that exposes your parameters in the report body. If you apply the XSLT externally (very little code) you can parameterize the XSLT with these values instead.
* -- do the same thing as above but use the DOM instead of XSLT. Can't be attached to the report and is not as performant, but as I said, people don't like it when I suggest XSLT so ... <g>
* -- write some code, or a different XSLT, to grab the HTML version instead of the XML, and strip the tables down to what you're looking for. I don't like to do it this way but if you need the page headers for some reason you will probably need the "complete" output rather than the data-xml output as your source.
* -- the easiest way, and surprisingly good, although wacky <g>: print to file using the generic text printer driver. You may think this is crazy, and you're not likely to hear this suggestion from anybody but me <g>. But remember that the people who built these drivers (which come with the OS) have already faced the problem you're facing and made sensible compromises for the various layout problems. In some ways, everything else we do to re-solve this problem is re-inventing the wheel.
HTH,
>L<
|||
Hi,
Thanks for your reply. It will definitely help me to decide on my approach to develop this functionality.
I think the way I have done this in SSIS is very easy than the way this can be done in SSRS. Do you think this will cause any issues for any reasons like Performace, maintenenace etc?
BTW, can you provide some link to see how the rendering functionality can be extended?
Regards,
Virendra
|||
I have no idea what issues of performance or maintenance will be caused by your SSIS approach -- and if you're happy with it you shouldn't change. However, it really is easy to use the generic printer driver <s>. Have you tried that?
As far as "extending the rendering functionality", you don't so much "extend" the existing rendering as provide a new extension output type. RS provides several, you're writing a new one. Start here
http://msdn2.microsoft.com/it-it/library/ms154606.aspx
.. but I"m trying to tell you that you will be doing something that the generic printer driver already does <s>.
>L<
|||
Hi,
I tried the priter option and it is very easy to do this. I have two problems though. Can you futher help in this?
1. When I keep some space between two rows in my report, it prits the garbage character like three samll squares (Which I do not see if I open it using worpad). Can this be avoided?
2. How can I directly write the report to harddisk through my .NET code?
Regards,
Virendra
|||
1. the "garbarge" is line break characters (CR's, LF's, or some combination of those characters) that your viewing application does not understand. The way to fix is:
* -- analyze the characters to figure out exactly what ones are causing the problem
* -- translate those characters to a sequence your viewing application is happer with, before saving to disk, or
* -- fix the viewing application <s>.
2. You will find a printer delivery extension sample as part of the SQL Server sample set. You will also find something similar (printing from a console app) here: http://gotreportviewer.com. In both cases you will probably need to do a bit of revision, but you will see that you can specify printer or device settings as part of the process. In this case, one of the settings you want to specify is the filename to go to, typically a temporary/generated one. (Don't just set up your printer setup to an explicit filename, IOW, that wouldn't be thread safe.)
HTH,
>L<
|||
Thanks for your help. I will try the options mentioned by you.
The application, I am trying to use for viewing the file is notepad. I don't know why the problem is with the notepad but not with the wordpad.
Thanks again for your reply. I will try those options to compare against my SSIS approach.
Virendra
|||
In Notepad the results will often be different depending on the font. If you want to send me a text file to check the characters -- if for some reason you can't figure out what they are -- you can email to me (site is in my sig, contact details are on my site) or post somewhere where I can d/l an example.
>L<
|||
Hi Lisa,
I have sent you the text file generated from the report using the text generic printer.
Do you know what this character is and how to avoid this ?
Virendra
|||
hi Virenda,
The character in your text file (I assume that you are talking about the one at the end of line 20?) is an FF (form feed), chr(12) in ASCII.
some helpful background on how to do this yourself:
When you have characters like this, and they come out as plain squares, you can usually copy the character and interrogate it in an application that will give you the ascii value -- use whatever you like <s>.
Sometimes the Windows clipboard buffer will not take the character in question, in which case you can still find its ascii value without copying and pasting. Read the file, or a truncated copy of it, into a string variable programmatically and interrogate its characters instead of using the clipboard.
It also often helps to change the font in notepad to an OEM/DOS font or other script that will change the character to something more recognizable, too. In this case, you might see a little "female symbol" (circle with plus underneath) for chr(12).
Please refer to http://www.asciitable.com/ or similar charts for other whitespace characters that may be interpreted differently in different environments...
The thing you need to do, for your uses, to get the right result, depends on how you would like to represent that formfeed or other whitespace character in your text file. Usually you represent a formfeed by one or more extra blank lines. In your file hard returns appear to be represented by CRLF combinations (CHR(13) + CHR(10)), so you replace the FF by as many CRLFs as you like to represent your "extra space" in the text file version of your output.
I hope this helps,
>L<
|||
Hi Virendra,
Modify the config file '<Extension Name="CSV" ' node to
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration><DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter>some delimiter (unfortunately Tab char doesn't work)</FieldDelimiter>
<Extension>TXT</Extension>
</DeviceInfo></Configuration>
</Extension>
After modifying, restart the report server and navigate to report via Reportserver. Open the report. Now download the report by selecting CSV file type. This will save the file or open the file in text directly.
Let me know if this solve the problem.
Thanks,
S Suresh
|||
Suresh and other folks reading (so nobody gets confused),
The text file is not created by the CSV extension -- because Virenda wants to preserve the physical layout of his elements in his text file <s>.
The mechanism is a generic text printer driver and the character in question is not a field delimiter; it is a form feed.
If one wants to do this with an intermediate format it is, frankly, easier to do it with the XML export than the CSV export -- but the generic text printer driver really does most of the work for you. Whether parsing the CSV as an intermediate format *or* using XML-XSLT to do the same thing, one needs to reference the RDL metadata to achieve V's desired result (preserving his physical layout on the page with appropriate white space between elements).
I hope this helps,
>L<
>L<
|||
Lisa and All (who contributed to this post),
After checking all the possibilities, it looks like that after getting the report in the text format as I want, I need to write another piece of code. This will be for getting read of the FF,CR,LF......(squares) characters from the notepad.
With SSIS, I don't have to do that. I was thinking of not to use SSIS as there was some IO involved for each file generation. If the same way I have to do the IO using this SSRS option, then I think it is better to either extending the rendering functionality or use SSIS.
I tired in SSRS different ways to get clean new line or line feed, but it alwyas shows me the squares.
Do let me know if you have any thoughts on this.
Virendra
|||
Sorry for the delay in reply -- I have been away --
They are not "squares" and they are not "from SSRS". They are from the printer driver and they represent formfeeds <s>. The only way to get rid of them is to tell the printer driver that you want a page size which eliminates the formfeeds -- and as I think you said something about "when I have put extra space in my RDL" -- meaning explicit page breaks -- this really won't work for you <s>.
Myself, I don't really see any problems with doing a simple Replace for the FF character. I also don't really understand your concern about IO -- whether in SSIS or other code -- here.
Basically even when it is behind the scenes in streams or whatever there is IO if the classes handling your file deem that temporary files are required to perform an operation. It often depends on the file size and memory available, and it is often beyond your control. Since there is clearly something you are worried about here I will assume that your file sizes are large -- but that makes me wonder in what way you think you would be avoiding IO even if there were no FFs involved.
As a case in point: I don't actually have an preference for doing this one way or another, unless it happens to be my preference for using XML/XSLT for this type of thing. I offered that solution for you but you did not seem interested at all -- and many developers are not, so I don't like to push that on people even though I do like it <s>. My point in bringing it up now is simply that there could be IO here also. I don't think that this fact would be my deciding factor in doing something using one method or another, because it is always potentially present.
Virendra -- honestly -- maybe we should be talking about why you are hesitating over the IO required for your current (SSIS) solution so that we can weigh this type of thing more accurately. If you don't want to get more specific about what you are doing, that's okay, but there is no way we can talk about performance and efficiency with the information we have so far...
Good luck,
>L<
