Archive

Archive for July, 2010

SQL Server Excel and Soft Returns

29 July, 2010 Leave a comment

Many moons before reporting services existed, I was involved in writing a report architecture which was based upon SQL server and MS Excel.

In short sql queries were executed and data dumped into excel and then formatted via embedded macros.

In Excel a Soft Return is done by pressing ‘Alt + Enter’. In some cases you may return a 1:Many relationship as a single string and want to delimit it with a carriage return between each element.

To get this to work in Excel you need to use the Ascii code for {LF} which is Char(10).

So that way your comma delimited string can appear with newlines, and turn on wrap in the column and the data formats nicely in excel.

Probably not useful for anyone else, but I needed to do this again today and do you think I could remember the Ascii code….

Categories: SQL Server

How to Copy Reporting Services Reports off a Reporting Server and Production Deployment

Every now and then in my travels around SSRS I have needed to do this. You may be unsure if the version of the report deployed to production is the version of the report in source control and just want to get latest off the production server.

The way I have normally had to do it is to connect to the Reporting services server, go to the properties of the report and then choose “Edit” or in SQL Server 2008 R2 choose “Download”.

If you have to do this for alot of files this is painful, until now…

I found this tool the other day, and I must say its awsome.

http://www.sqldbatips.com/showarticle.asp?ID=62, its called the “Reporting Services Scripter” and I have a new friend.

Not only can it extract files from a given server if you have the correct credentials, it will also create a deployment script.

So now the pain of releasing files to production one by one can be avoided and having to manually configure things again and again.

This is all scripted into a batch file that can then tweaked to point to the production server and this can then all be wrapped up neatly into a release.

Nice 🙂

Categories: Reporting Services