Creating DB from scripts: delimited

Showing posts with label delimited. Show all posts

Thursday, March 22, 2012

Creating A tab delimited export

Hello all,

I am trying customize the CSV rendering extension with different device information settings. I would loke to change the CSV extension to a Tab delimited output. However, whenever I add a new extension line to the RSReportServer.config file I run into problems.

1. What is the correct character to use in the delimiter to specify a Tab character? I seem to be unable to get this correct

2. Why when I override the name in the rendering extension does it still show up as the CSV (comma delimited) name in the export dropdown.

Here is my customized rendering extension.

<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name>TXT (Tab Delimited Text File)></Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter><![CDATA[#x9]]></FieldDelimiter>
<NoHeader>true</NoHeader>
</DeviceInfo>
</Configuration>
</Extension>

Thanks

Justin

Justin,

1. For tab-delimited report, use

2. For name override, you must specify language attribute:

<Name Language="en-US">TXT (Tab Delimited Text File)</Name>

(more info on http://msdn2.microsoft.com/en-us/library/ms156281.aspx)

Thanks!

|||

Denis,

Thanks for your response. This got me halfway there. The Language attribute worked beautifully but the Field Delimiter didn't seem to make any difference.

The ouput defaulted back to a comma delimiter.

Here is my revised config file

<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name Language="en-US">TXT (Tab Delimited Text File)></Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<FieldDelimiter>	</FieldDelimiter>
<Extension>TXT</Extension>
<Encoding>ASCII</Encoding>
<NoHeader>true</NoHeader>
</DeviceInfo>
</Configuration>
</Extension>

and the only line in the log files that seemed to relate to this request.

aspnet_wp!cache!1!9/5/2006-22:33:30:: v VERBOSE: Key not found in cache: __RenderedSnapshotResult__:/AdvancedReports/AscribeExportReport&0a14be41-8afb-4c74-b8ce-6a8d80aade69&&Format:TXT&Extension:TXTEncoding:ASCIINoHeader:true:__PrimaryStream__

Thanks again prompt reply,

Justin

|||

Justin,

Unfortunately, Reporting Services currently trims all special characters from configuration file device info values. So, single TAB character got trimmed out when configuration file got parsed.

The work around to get a tab-separated file is to pass &rc:FieldDelimiter=%09 via URL access.

Thanks!

|||does this include encoding this in a cdata section?|||This is not really a viable workaround for anyone using the Reporting Services Interface. Yeah we could put a link on a report pointing to the URL, but this is inconsistent and users won't understand why. So is it our only option then to go and write our own rendering extension to accomplish something that's already done?

Creating A tab delimited export

Hello all,

1. What is the correct character to use in the delimiter to specify a Tab character? I seem to be unable to get this correct

2. Why when I override the name in the rendering extension does it still show up as the CSV (comma delimited) name in the export dropdown.

Here is my customized rendering extension.

Thanks

Justin

Justin,

1. For tab-delimited report, use

2. For name override, you must specify language attribute:

<Name Language="en-US">TXT (Tab Delimited Text File)</Name>

(more info on http://msdn2.microsoft.com/en-us/library/ms156281.aspx)

Thanks!

|||

Denis,

Thanks for your response. This got me halfway there. The Language attribute worked beautifully but the Field Delimiter didn't seem to make any difference.

The ouput defaulted back to a comma delimiter.

Here is my revised config file

and the only line in the log files that seemed to relate to this request.

Thanks again prompt reply,

Justin

|||

Justin,

Unfortunately, Reporting Services currently trims all special characters from configuration file device info values. So, single TAB character got trimmed out when configuration file got parsed.

The work around to get a tab-separated file is to pass &rc:FieldDelimiter=%09 via URL access.

Thanks!

Creating A tab delimited export

Hello all,

1. What is the correct character to use in the delimiter to specify a Tab character? I seem to be unable to get this correct

2. Why when I override the name in the rendering extension does it still show up as the CSV (comma delimited) name in the export dropdown.

Here is my customized rendering extension.

Thanks

Justin

Justin,

1. For tab-delimited report, use

2. For name override, you must specify language attribute:

<Name Language="en-US">TXT (Tab Delimited Text File)</Name>

(more info on http://msdn2.microsoft.com/en-us/library/ms156281.aspx)

Thanks!

|||

Denis,

Thanks for your response. This got me halfway there. The Language attribute worked beautifully but the Field Delimiter didn't seem to make any difference.

The ouput defaulted back to a comma delimiter.

Here is my revised config file

and the only line in the log files that seemed to relate to this request.

Thanks again prompt reply,

Justin

|||

Justin,

Unfortunately, Reporting Services currently trims all special characters from configuration file device info values. So, single TAB character got trimmed out when configuration file got parsed.

The work around to get a tab-separated file is to pass &rc:FieldDelimiter=%09 via URL access.

Thanks!

Friday, February 24, 2012

Creating a comma delimited list

Hi,
I have a complex query where each row in the final dataset is a product.
However each product has a number of authors associated with it. What I
would like to do is have a query/subroutine join the authors to the product,
as a string:

ProductID Title Authors
1 The Sacred and the Profane John Rieggle, George Alexi
2 Dancingin the Dark DanBrown, Peter Kay, Paul
Dwebinski
Products Table
==============
ProductID
Title
Authors Table
=============
AuthorID
Name
Product Authors Table
=====================
AuthorID
ProductID
Is this at all possible?
Thanks
jr.
It is possible with the use of a UDF. However, I highly suggestthat you do this work on the front end. Preparing the data foroutput is not something the database engine should be doing, and theUDF method is fairly intensive as it results in additional queries.
Here's a link showing the UDF method:Joining Strings into Delimiter Separated Lists.

.
|||Terri,
Thanks for your reply. I understand that this is a UI rendering issue, and I have been toying with how best to proceed. Currently my paging routine returns one resultset, were each row represents a product. To return the authors, I would need to return a second resultset, and join them at the middle tier:
Pseudo steps:
1. Do complex paging query
2. Based on items in paging query, join them to the authors table and return that resultset too.
This would solve the problem, but sends more data across the wire which I was thinknig could be avoided, even though I know that the DB shouldnt be doing ui rendering steps. Other than "best practice" I see no other reason to put this code in the ui, since it is just a dumb string without much meaning when rendered in a listing. As you mention though, this maybe putting too much stress on the DB when using a UDF...I am relatively new to this so forgive my naivete...
Thanks Terri,
jr.|||

This might not be very efficient if you have a large sum of data, but here is how to do it.

create table #product (
productID int,
productTitle varchar(50)
)
go

create table #author (
authorID int,
name varchar(100)
)
go

create table #productAuthor (
productID int,
authorID int
)
go

insert into #product values (1,'product1')
insert into #product values (2,'product2')

insert into #author values (1,'author1')
insert into #author values (2,'author2')
insert into #author values (3,'author3')
insert into #author values (4,'author4')

insert into #productAuthor values (1,1)
insert into #productAuthor values (1,2)
insert into #productAuthor values (2,3)
insert into #productAuthor values (2,4)

create table #nameList (
productid int,
names varchar(1000)
)
go

select distinct p.productid into #tmp
from #product p
inner join #productAuthor pa on p.productid = pa.productid

declare @.productid int,
@.names varchar(1000)

while exists (select null from #tmp)
begin
set @.names = ''
select top 1 @.productid = productid from #tmp

select @.names = CASE WHEN @.names = '' THEN name ELSE @.names + ',' + name END
from #product p
inner join #productAuthor pa on p.productid = pa.productid
inner join #author a on pa.authorid = a.authorid
where p.productid = @.productid

insert into #nameList values (@.productid, @.names)

delete from #tmp where productid = @.productid
end

select p.productID, productTitle, nl.names
from #product p
inner join #nameList nl ON p.productid = nl.productID

Let me know if you have any questions.

Nick

Thursday, March 22, 2012

Creating A tab delimited export

Creating A tab delimited export

Creating A tab delimited export

Friday, February 24, 2012

Creating a comma delimited list

Creating DB from scripts

Blog Archive

About Me