SSRS

calvin-and-hobbes

SSRS – Crazy Issues; Stupid Solutions

calvin-and-hobbes-e1328550590232

1. Interactive Sorting Garbling Table Header Text

As per best of my knowledge and googling capabilities there is no way to place the interactive sorting up and down arrows as per your choice and they will always appear on the right hand side of the cell / textbox /column header. And this wrecks havoc rendering your SSRS report if the cell / textbox /column header are not wide enough.  Check the below image :

Interactive Sorting Issue 2

A workaround is to have a dummy row below the header row and turn on the interactive sorting for the columns of this dummy row like below :

image

image

And then the when you run the report it will come out like this :

image

2. Image with Transparent Background Getting Garbled When Exported to pdf

This one is really weird, when the image you want to display in footer had a transparent background like below and the footer has a background (sky blue in my case):

image

then when the report is exported to pdf it looks like below, which is not really sharp:

image

So the workaround for this is to provide the image a background i.e. sky blue instead of transparent and then if you export your report in pdf it should be fine as below :

image

3. Display only the Currency Symbol

Typically we want to show the currency next to the numbers whenever we are displaying money in the report and the currency symbol has to be as per the client machine or browser locale, for example like below :

image

For this what we do is :

1. Set the Language property of report to User!Language

2. Set Format property of cells to C0 or something similar

As you can see, the currency symbol is also going to occupy some cell space and when the numbers has many digits this will cause a line break which will not be nice. So the ideal case is to display the currency symbol next to the column header like below :image

However the is no way to directly display the currency symbol because you need numbers so you can format them in currency but you have text

So the hack is

1. Create a placeholder instead of label for the column header and set the placeholders label as the column header text

2. Write a expression for the placeholder value which is

=”Column Header Text”
+ “<br>”
+ “(” + Replace(FormatCurrency(“0″,0),”0″,””) + “)”

which actually adds “Rs. 0” after the header text and then replaces the 0 with blank space

3. Set the markup type of the placeholder to HTML

4. Expand Drilldown while Exporting or Subscribing a Report

Drilldown is a great feature of SSRS, however if the drilldown is collapsed by default if you export or deliver a report by email by subscriptions then still the report content will be collapsed and for some use cases that’s what you will not want.

A workaround can be to add a report parameter ShowDetails with values Yes / No, default No , the drilldown visibility of groups / UI elements is governed by an expression based on this parameters value and when you subscribe the report, you subscribe it with ShowDetails = Yes

image

image

image

image

Advertisements

Adding Logout Feature in Microsoft Report Manager (SSRS with Forms Authentication)

Once we have configured SSRS to use Custom (Forms) authentication by deploying a custom security extension as described here, we can logon to MS Report Manager (MSRM) using credentials of our custom security framework via a logon web page.

However once you logon you will see that there is no way to logout or to expire the authentication cookie and to do so you need to close the browser. This problem is more pronounced for the “Verification Engineers”  since they have to do so n number of times to test n authentication and authorization scenarios. Also it will be a pain for the end users too since they will surely struggle to find a way to logout.

To fix this we can cook a small hack using jQuery and trusty html/css by manipulating the top right hand breadcrumb of MS Report Manager(MSRM) web pages, which looks like this :

image

Step1 :

If we carefully inspect the html of MSRM, the name of this breadcrumb is msrs-topBreadcrumb. Using some jQuery we can identify this element and append the logout href at the end of its last child. Once you do this on every page, the pages should look something like below:

——————————————————————————————————————–

<%@ Register TagPrefix=”MSRS” Namespace=”Microsoft.ReportingServices.UI” Assembly=”ReportingServicesWebUserInterface” %>
<%@ Page language=”c#” Codebehind=”CacheRefreshProperties.aspx.cs” Inherits=”Microsoft.ReportingServices.UI.CacheRefreshPropertiesPage” EnableEventValidation=”false” %>

<html>
<head>
<title></title>

<script type=”text/Javascript”  src=”jquery-1.7.1.min.js” > </script>

<script type=”text/Javascript” >

$(function() {
var a = $(‘.msrs-topBreadcrumb’);
var d = ‘<span style=”margin-left:2px;”>| <a href=”/Reportserver/logon.aspx?Logout=1″>Logout</a> </span>’;
$(d).appendTo(a.find(‘a’).last().parent());
});

</script>

</head>

</html>

——————————————————————————————————————–

Step 2:

Add the “jquery-1.7.1.min.js” file in  “C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Pages”

We can download the jQuery file from internet.

Step 3:

Add the code to expire the authentication cookie on the logon.aspx page.

Note : Check the name of authcoooki from web.config which in our case is “sqlAuthCookie”

image

and that’s it, now the breadcrumb will look like below and clicking on “Logout” will logout the user:

image