Showing posts with label zip. Show all posts
Showing posts with label zip. Show all posts

Thursday, March 29, 2012

Concatenate list of values under a group

I have the following dataset:

State ZIP Homes Schools

WA 98007 2000 4

WA 98052 3000 5

WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as “[98007, 98052, 98079]” how can I accomplish this.

My report needs to show:

State Homes Schools

[98007, 98052, 98079] 7000 12

Any help will be greatly appreciated

SPJ11, Why dont you do this in SQL itselves? You can also do this in SSRS. At the group level use,

Sum(Fields!Homes.value), Sum(Fields!Schools.value) -- these will retun the counts correctly.

I am not sure as how to concatnate the zip codes. I thought this should work

Join(Fields!Zip.Value) -- but this does not seem to work, I will see if I can come with something else...

|||

Never mind. I could make it work. Thanks to everyone who spent time on this thread

sqlsql

Tuesday, March 20, 2012

Compress/Zip Reporting Services Export options

Hey everyone,

I have an issue where i am sending out files with 30,000+ lines and they are reaching the 11mb, 12mb in size.

This is becoming and issue for us, as we are only allowed to email up to 10mb in size.

I have tried reducing all spaces in the data, removing any graphics etc from the report , but still the excel file is over 11mb. One thing i did find was that, if i export it to excel, then open the file and save as a different file name the file size drops 50% !!

I was wondering if anyone has been able to zip/compress the exported file before it gets emailed?

It would be a great feature for MS to include in the next service pack.. Take advantage of the built in Zip support in Windows..

Look forward to hearing any suggestions that the community may have,


Thanks

Scotty

Hi there-

Unfortunately you'll have to create a custom delivery extension for your report server. You can look at the PrinterDeleiveryExample in the RS samples folder (located wherever you installed RS) to give you an idea of what you'll need to do.

If you are using .Net 2.0, then you can use the System.IO.Compression and System.Net.Mail libraries as part of your delivery extension. I unfortunately have a client that hasn't migrated to .Net 2.0 and I had to use .Net 1.1. I used two open source libraries to accomplish the task: DotNetOpenMail and SharpZipLib. I decided to use DotNetOpenMail, because you can create an attachment from an array of bytes, which you can't do with the System.Web.Mail library in .Net 1.1. So for my custom delivery extension, I zipped my report to a memory stream and then attached that stream's byte array to the e-mail.

Here are some links to get you started:
DotNetOpenMail - http://dotnetopenmail.sourceforge.net
SharpZipLib - http://www.icsharpcode.net/OpenSource/SharpZipLib

Hope that gives you a starting point to a solution. If you have any questions, please respond back here.

Regards,
Scott