SSRS – faultbucket

SSRS – Multi Value Parameter in Jump To Report link

March 8, 2012March 8, 2012 Jeff Miles 1 Comment

I’ve developed a report that displays summary information in a table for a single dataset. I then had a request to display the exact same information, where each line item would be displayed on its own page for printing and filing purposes.

Ok, easy enough, I just made a second report with the same dataset, used a list object instead of a table, and then made a button and specified the “Jump To Report” under the Navigation tab.

The problem is, there is a multi-valued parameter within both these reports, and for some reason it was only passing the first selected value, rather than all of them.

I was using the Expression editor to paste in the parameter value, which added “(0)” to the line:

I tried a few things, such as using a =Join(xx) statement, but that didn’t seem to work.

Turns out this is a very easy solution. The simple answer was to just delete the (0)! It seems the expression editor pastes it in automatically since it recognizes the parameter as having multiple values, but it is detrimental when passing the full set of values along.

ReportViewer doesn’t take full width in Internet Explorer

October 5, 2011August 27, 2015 Jeff Miles 13 Comments

I’ve been struggling with an issue with a ReportViewer control in an aspx page for a while now, and finally dedicated some time to getting it resolved.

My control looks like this:

rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="1em"
          ProcessingMode="Remote" AsyncRendering="False" SizeToReportContent="True" ShowBackButton="True"
          EnableViewState="True" Width="100%"

With these attributes, the report displayed correctly in Firefox, but not in Internet Explorer 9. There was a small area to the right of the report body that the report wouldn’t expand into. I’ve seen this reported elsewhere such as this stackoverflow topic.

Here’s what it looked like:

Comparison of width — Firefox display vs. Internet Explorer display

After digging a little deeper with the developer tools, I’ve found that the report body is broken into two cells in a table.

The first has a unique identifier, with no width specified. The second has a width of 100%, but is empty. It’s this empty TD that is causing the space:

For some reason Firefox ignores width on the second TD, but IE gives it space.

I wasted a lot of time trying to use javascript, report server attributes and c# code to get rid of this, but finally discovered an easy way to fix it using CSS.

That first TD uses a unique identifier, but always ends with “oReportCell”. So I used wildcard CSS selectors to give it a width value:

 td[id*='oReportCell'] {width:100% !important;}

Now the width appears correctly within IE!

SSRS – “Jump to Report” in a different project

May 24, 2011May 24, 2011 Jeff Miles 3 Comments

While working with a report for SSRS 2005, using the Business Intelligence Development Studio, I’ve used the option to “Jump to Report” very often. You can find this by going to the properties of an object (textbox, cell, etc) and the Navigation tab.

The problem I encountered today is that the drop down shown above only displays reports that existing within the current project loaded in Visual Studio. This means if you have a common report used as a drill through, a copy would need to be made inside this project which is obviously a bad idea since multiple copies would need to be updated.

However after a bit of searching I’ve found that you can specify the folder (as it appears on the Report Server) that your sub report exists in, and it will function correctly.

By doing this, you can maintain one copy of your common reports, and just reference them from the folder name.

Note: this won’t work while previewing the master report in Visual Studio unless you have deployed the drill through report to the report server.

As a related aside, I have started organizing my reports all within one Solution in Visual Studio, each under separate projects. Prior to this I had one Solution and one Project for each set of reports, which didn’t scale very well and doesn’t allow for easy referencing. Now, I can get at them all at once:

SSRS – Get date parameter as end of day

April 27, 2011April 27, 2011 Jeff Miles 9 Comments

I’ve built a report within SQL Services Reporting Services 2005 (SSRS) that pulls its data based on a date range, chosen through start and end date parameters.

The problem is, if on those parameters I choose April 21st, it’s actually choosing April 21: 00:00. This means if there’s any data that is relevant during the day on April 21st, it wouldn’t be selected.

I needed a way to have my user select April 21st, but have it return values for end of day April 21st. The solution actually proved easier than I originally thought, as I was making it too complicated.

Full credit to Samson Loo who posted about this (outside of SSRS) here: http://justsamson.com/2010/09/19/date-parameters-and-things-to-consider/

My dataset and linked functions all reference the parameter @EndDate, so I didn’t want to go through and change all of that.

Instead, I created a new report parameter called @EndDateEOD, and set its properties to what @EndDate originally was.

I made sure to place this new parameter above my existing @EndDate.

Then I modified @EndDate to make it hidden, and set this as the default value:

=DateAdd("d",1,Parameters!EndDateEOD.Value)

This is adding one day to what the user selects. At first glance this seems like it would produce incorrect information, however in reality, if a user selects April 21st (in reality April 21 00:00:00), then this will use the date April 22 00:00:00 in the dataset, which is what we want.

SSRS – Multi Value parameter in Linked Report

April 1, 2011April 1, 2011 Jeff Miles 3 Comments

I had been trying to get a multi valued parameter into a linked report for the longest time, and when running the report all I would get was an error, “The %variable% is missing a value”.

I just couldn’t figure it out, but it was just me being dumb. The answer is really quite simple:

I was trying to enter my values like this:

value1, value2, value 3

But what you need to do is enter them separately on a different line:

Then SSRS will make a comma delimited list of them for you.