Archive

Archive for the ‘Reporting Services’ Category

SSRS 2008 R2 – Name Tabs when exporting Reporting Services Report to Excel

5 August, 2010 5 comments

This is a long sought after and requested feature, and finally in 2008 R2 it’s here.

The property that you are looking to set is:

PageName

Set this and then on export to excel the tab will use the page name.
This can be set on certain objects in the report and can use Expressions to set the name.
Objects that contain this property include:

  • Tablix
  • Rectangle

As an example of how it works, I have a report that runs as 2 sub reports. Each subreport has a tablix that has the page name property set.
When the report exports to excel I get 2 tabs each named as defined in the page name property.

Another more complex example is using a tablix with grouping. Set the tablix to page break on the element that you are grouping by and then set that element as the value for your page name. Each time the grouping changes it will render in a new page and if exported to excel each tab will appear with the name of the group.

This post goes into it in more detail
http://blogs.msdn.com/b/robertbruckner/archive/2010/05/16/report-design-naming-excel-worksheets.aspx

Cheers

Advertisements
Categories: Reporting Services

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

SSRS 2008 R2 repeat headers on pages

21 May, 2010 6 comments

As of SQL Server 2008 R2 the way to make headers of tablix’s repeat has changed considerably.

I suggest you read the post here:
http://blogs.msdn.com/robertbruckner/archive/2008/10/13/Repeat-Header-And-Visible-Fixed-Header-Table.aspx

In short all the documentation I found suggested you needed to turn on the “Advanced Mode”.

Advanced mode is a bit hard to find.

See the bottom of your design space you should see the heading:

  • Row Groups
  • Column Groups
  • and keep looking right and you see the innocent little black down triangle. Click that and choose advanced mode.
  • Click on the static elements and it will highlight a cell in the report, this identifies the element you are attempting to change.
    To make a heading repeat. Click on the static member for the header row/s of you tablix, view the properties pane and set these properties:

  • KeepWithGroup: After
  • RepeatOnNewPage: true
  • FixedData: true to keep headers visible
  • Categories: Reporting Services

    SSRS SQL Server Reporting Services – How to reference an external image hosted on the report server

    7 May, 2010 1 comment

    Ok,

    I needed to do this the other day, after an hour or so I finally got it.

    Issue, I have an images directory that is deployed to the report server with my reports, It is hidden in the tile view.

    The problem, how to insert an image into my report that references the images directory hosted on my report server.

    Well after a while of struggling with it I noticed this little perl of information in the warnings for my report;

    [rsWarningFetchingExternalImages] Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access.

    So to make this work do the following

    1. Setup an unattended execution account, (See the Execution pane in the Reporting Services Configuration Tool)
    2. Ensure “everyone” has browse access to the images folder, (or confirm that the user running the report has access to the images folder)
    3. In your report set the URL of your image with an expression like the following:


    =IIf(Globals!ReportServerUrl is nothing, "http://localhost/ReportServer", Globals!ReportServerUrl) + "?%2fimages%2fimage.png"

    Two things of interest in this code:
    1. There is a ? after the http:///ReportServer path
    2. This expression uses the localhost when in the IDE preview window and the full reportserverUrl when deployed to a server. I didn’t want to hard code localhost for a production deployed report.

    And now it all works both in the IDE and the deployed report.

    Cheers

    Categories: Reporting Services

    SSRS – Reporting Services and custom assemblies

    15 April, 2010 Leave a comment

    Just cost myself a few hours on this one.

    I have recently installed the 2008 R2 Nov CTP, I also have RS reports that use a custom assembly.

    (The custom assembly provides a Parameters class that returns a formatted string for the selected parameters on a page. We have staffID’s and client ID etc to identify people in our company, so you can type in a staff ID as a parameter to the report, or even a CSV list of staffID’s. The assembly will interogate the parameters, if it is a drop down it will interogate the webservices and find the display value for the selected value directly from the report definition, if it is a parameter of a given type – like staffID – it does a database lookup and returns a nicely formatted string that is rendered into a report. I am thinking of turning this into an assembly that anyone could purchase and making it configurable via xml… if there is interest?)

    Anyway…

    When it comes to writing and deploying custom assemblies you should read this;
    http://bryantlikes.com/WritingCustomCodeInSQLServerReportingServices.aspx
    Bryant is the man when it comes to this stuff

    The thing was that my report was running successfully when I deployed it but not when running in BIDS – (Business Integration Development Studio).

    I was getting this error

    Failed to load expression host assembly. System.Security.Permissions.SecurityPermission

    When you configure you assembly as per the instructions provided by bryant, you need to configure;

    – rssrvpolicy.config, found generally in a location like D:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer,
    (this is the path for the NOV CTP on a x64 bit machine installed on D drive)

    But there is also a corresponding policy file for BIDS to use and this also needs to be configured. It resides in the same folder as your IDE\PrivateAssemblies where you copied you assembly for BIDS to work;
    RSPreviewPolicy.config – D:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

    Updating this policy file fixed my issues.

    Cheers
    John

    Categories: Reporting Services

    SQL Server Reporting Services (SSRS) – IIF statement divide by zero error

    12 March, 2010 6 comments

    If you use the IIF statement to solve your divide by zero errors, you will most likely find that you still get divide by zero errors, as RS evaluates the false condition and creates a divide by zero error.

    The following problems is trying to divide last weeks total hours worked by the number of days someone worked, which could be zero.

    The follow errors
    =IIF(Sum(Fields!LWWorkingDays.Value) = 0, 0,
    Sum(Fields!LWTotal.Value)/Sum(Fields!LWWorkingDays.Value) )

    The solution:
    =IIF(Sum(Fields!LWWorkingDays.Value) = 0, 0,
    Sum(Fields!LWTotal.Value)/
    IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value)) / 60)

    even though the division is not used, it errors. So by converting the zero to a 1, stops it erroring and allows the calculation to work correctly when it is evaluated.

    That is a work around for a problem that should never of existed, good one Microsoft..

    SQL Server Reporting Services – Access textbox value in expression

    3 March, 2010 Leave a comment

    If you need to progamatically access a control on your report then use the following expression

    ReportItems!textbox1.Value