Archive

Archive for the ‘Reporting Services’ Category

SSRS – Default Date Parameter not showing

November 3rd, 2013 No comments

The Problem

I have an SSRS Report with a default date parameter.
The default date parameter appears grayed out and does not allow me to enter the date

The problem in detail

In an SSRS report I wanted to have a Default date parameter.

The report had two parameters:

  1. Person Id – Free Text Number field, Mandatory
  2. End Date – Date Field – default to Now

The default value for the date parameter can be set by a default expression like:
=Today()

When this report is opened the cursor is focused in the “Person ID” field, and the End Date is grayed out.
Enter a PersonId and either tab out of the field or click view report and the End date field will become available.

The solution

The problem can be resolved by changing the order of the parameters.

By changing the order from:

  1. Person ID
  2. End Date

to

  1. End Date
  2. Person ID

It appears that placing a text style parameter that requires user input prior to the default date field, stops the parameter execution and leaves the field grayed out.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: Reporting Services Tags:

SSRS running count across groups

August 11th, 2012 No comments

The problem

You have a SSRS report which is grouping by a category.

Assume you have 6 items in two groups. You want the first column to be a running row count across the groups.

You Want a report that looks like this:

SSRS report with running total accross groups

The solution

In the “detail” cell for the first column use the following expression;

=RunningValue(1, Count, Nothing)

VN:F [1.9.22_1171]
Rating: 7.0/10 (4 votes cast)

Categories: Reporting Services Tags:

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

August 5th, 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

VN:F [1.9.22_1171]
Rating: 4.5/10 (2 votes cast)

Categories: Reporting Services Tags:

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

July 9th, 2010 No comments

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 a lot of files this is painful, until now…

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

http://www.sqldbatips.com/showarticle.asp?ID=62, it’s 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 🙂

EDIT: The site has seemed to have gone down but this link is the direct download which is still working.
http://www.sqldbatips.com/samples/code/RSScripter/RSScripter.zip

EDIT 2: the above link also seems to be broken, here is the last know copy that I have, I am sure they won’t mind me redistributing..

RSScripter

Also here is a link that provides the full process of how to use the tool.

using rs scripter to create a deployment

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: Reporting Services Tags:

SSRS 2008 R2 repeat headers on pages

May 21st, 2010 10 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:

  • FixedData: true (to keep headers visible when scrolling)
  • KeepWithGroup: After
  • RepeatOnNewPage: true
  • Edit – Nov 2011
    So its been a year or so since I wrote this and in a case of dog fooding, I had to fix up a report and I hit the same error as listed by nes, itch and Terrence below.

    Here is what the header Row Groups looked like in my report.

    Each one of the static rows between “table1_eventId” and “rsvp” are rows in a group header in the report. When you click on the static row, the element in the report is highlighted

    So I followed my own instructions and setup the row group properties on the static row immediately above rsvp as per my instructions, ran the report and got a similar error to Terrence.

    Delving into the issue there are two possible solutions:
    Solution 1
    Assuming that you need all the header rows to repeat on each page, then you need to configure EVERY static row in the row groups in the same way, (every row between table1_EventId and Rsvp). Get one of the properties different and you get that error.

    Solution 2
    Assuming you don’t need the entire section above to repeat on every page. I needed to only have the table header row repeat. (the last static row prior to RSVP contained the details of the table column headers).
    I right-clicked on the RSVP row Group, chose Insert Row –> outside Group – Above. This inserted a single blank row. Copy the header columns from the row that is now in the outer group, and paste into the new row that appeard. Delete the previous header row. Now follow the instructions above. As there is only 1 row then the repeating header instructions above will work.

    Here is what the row groups looked like after solution 2. The static row between rsvp and table1_details_group is the header as highlighted and this is where you can setup the single repeat header proper

    The trick is that to have the header repeat you have to update every row at a level in the row group to have the same settings.

    VN:F [1.9.22_1171]
    Rating: 0.0/10 (0 votes cast)

    Categories: Reporting Services Tags:

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

    May 7th, 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. ** Very Important ** Ensure “everyone” has browse access to the images folder, (or confirm that the unatteneded execution user 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

    VN:F [1.9.22_1171]
    Rating: 5.3/10 (4 votes cast)

    Categories: Reporting Services Tags:

    SSRS – Reporting Services and custom assemblies

    April 15th, 2010 No comments

    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

    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 FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer,
    (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 IDEPrivateAssemblies where you copied you assembly for BIDS to work;
    RSPreviewPolicy.config – D:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssemblies

    Updating this policy file fixed my issues.

    Cheers
    John

    VN:F [1.9.22_1171]
    Rating: 10.0/10 (1 vote cast)

    Categories: Reporting Services Tags:

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

    March 12th, 2010 8 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.

    The following example is trying to divide last weeks total hours worked

    The following errors:

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

    The Problem
    Even though the number of days = 0 and it should evaluate to the true condition of 0, SSRS still evaluates the false result, and the false result still throws a divide by zero error because it is evaluating:
    Sum(Fields!LWTotal.Value) / 0

    The solution:
    We need to try to make the false result not error when working days = 0.
    The false result

    Sum(Fields!LWTotal.Value) / Sum(Fields!LWWorkingDays.Value)
    

    Needs to change to the following:

    Sum(Fields!LWTotal.Value) / 
    IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value))
    

    Why does this work?
    When working days = 0 the result is going to be 0. So we don’t really care what the false result is going to be we just don’t want it to error. So the new IIF statement on the denominator returns 1 when the working days are zero.

    This evaluates to:

    Sum(Fields!LWTotal.Value) / 1 
    

    and this does not error.

    Final Code Example

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

    In short by converting the zero to a 1, it stops the false result from 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..

    VN:F [1.9.22_1171]
    Rating: 10.0/10 (3 votes cast)

    Categories: Reporting Services, SQL Server Tags:

    SQL Server Reporting Services – Access textbox value in expression

    March 3rd, 2010 No comments

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

    ReportItems!textbox1.Value
    VN:F [1.9.22_1171]
    Rating: 10.0/10 (1 vote cast)

    Categories: Reporting Services, SQL Server Tags:

    Reporting Service 2008 Install – “Input String was not in a correct format.”

    February 9th, 2010 1 comment

    I got this error the other day whilst trying to install Reporting services.

    After much digging I found this blog entry that fixed it:
    http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/c9cb944d-6264-4530-9e00-ee92da3e939f/

    In short the performance counters on the machine were corrupted, so the following command rebuilt them and fixed the RS Install error.

    c:windowssystem32lodctr /R

    RS was left in a half installed state:

  • it was unable to be installed because it thought it was already installed
  • it was unable to repair cause it said it was not installed
  • Solution was to run the command above, uninstall, (I removed all SQL Server 2008 components) and re-install from scratch.

    VN:F [1.9.22_1171]
    Rating: 0.0/10 (0 votes cast)

    Categories: Reporting Services Tags: