SQL Reporting Services 2008 Spiting String

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 28-04-2012


Just a quick post about splitting strings in reporting services. I had a need today to split up an address into number/street/town that was held in one filed instead of the separate ones. I found a couple of things online about how to do it, but they did’nt seem to work on SQL 2008, so here is the solution:

=Split(Fields!Address.Value, ” “).GetValue(0)

The split function returns an array, so all we are doing here is splitting the address filed up based on a space, and then returning the first item from the array. In my case this was the house number,

Handling Deleted Users Who own SQL Reporting Subscription

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 01-06-2011


I’ve had an issue this week with some SQL reporting services subscriptions. A user contacted me saying her subscription reports were all failing. At first I assumed there was a problem with the user the report was running as, but it turned out to be something else.

When I tried to change and save the settings on one of the reports, I was presented with this error:

The user or group name ‘Dom\User.Name’ is not recognized. (rsUnknownUserName)

Now, it turned out that the user that had originally create this report subscription had recently been deleted out of the active directory, and there was a lot of reports that this user had created. I had a poke around on the web interface and in the management tools, but i couldn’t see anywhere to change the owner of the subscription.

So, as i’ve had to do in the past I started having a look in the reporting service database. I looked first in the Subscriptions table, and there was an OwnerID field, when I linked this table back to the users table I found it was indeed linked to the old user that had been removed. So, I found a valid user in the table, and ran this SQL to update the Subscriptions table pointing to this user:

UPDATE dbo.Subscriptions SET OwnerID = ‘xxx-xxx-xxx-xxx-New-User’ WHERE OwnerID = ‘xxx-xxx-xxx-xxx-Old-User’

When I opened up the subscription again, the owner was now valid, and I could save the report without the error message. I’m glad i did’nt have to go back to the user and tell them they needed to re-create all the subscriptions!

Show/Hide Groups at run time

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 17-07-2009


Recently quite a few of our users have been asking if they can show all of the report details at runtime, instead of having to drill down all the groups, opening and closing as they see fit. Well, the answer is yes, and its very easy to implement.

Parameters are you friend

The first thing to do is create yourself a new parameter in the report, and call it something like par_expand_groups. Give it the following options:

Now, you need to make this parameter control weather or not the group is expanded as the report is ran, you do this by putting an expression into the hidden property of the row.

Normally if you have a group, when you run the report it will probably be hidden and the user has the option of showing the details, so the hidden property will be set to true. We are going to set it to the following expression:

=IIF(Parameters!par_expand_groups.Value = “True”, False, True)

This means that if the parameter is set to yes, the details will be shown, if its set to no they will remain hidden. Add this to each level of grouping on your report, and the user now has control over what they see

Slight Problem with the show/hide icons

Once you have added this to your report, you might notice a slight problem. If you run the report with the option of showing the group details, the little show/hide icon on the left used to toggle the details will be wrong. It will be showing the ‘+’ even though the details are already showing, and if you press it to hide the details it will change to a ‘-‘. They are the wrong way round!

Obviously, this is going to confuse your users, and needs sorting. Well, thanks to a tip from http://www.lukehayler.com/2009/07/custom-visibility-toggling-in-ssrs its easy.

All you need to do is open up the properties of the text box that is the trigger for the show hide, and go into the Visibility tab. At the bottom is anĀ  option to set an expression for the toggle image. just enter:

=IIF(Parameters!par_expand_groups.Value = “True”, True, False)

As before, they is just looking at your parameter and setting the value of the toggle image. Now, when you run the report with the option to display the details, they icons for toggling the details should be correct.

Report Services Printer Control Problem

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 23-12-2008


Not sure how many of you have hit this, but recently we have had a problem with users not being able to print out reports from the browser. When they hit the print icon after runnig a report, they are given the message

Cannot load client print control’

Turns out that one of the patches Microsoft issue out (on a seemingly never ending basis) breaks(well, blocks) the activex. The patch in question is the ActiveX killbit one (i cant remember the kb number), there appears to be a problem with the old version of the print control activex, and when the latest patch is applied to the clients it stops the print control working.

The way to get it working again is basilcly to patch your reports server. This is something i am always a bit cautious of doing, as in the past i have had problems (service pack one removed the ability to do a multi select parameter) but this time i had a virtual server i could snapshot, patch then test.

To get it working again, i installed first of all SQL 2005 service pack 2 (http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&displaylang=en), then the latest hotfix rollup (http://support.microsoft.com/kb/956854). On a couple of the client machines this seemed to fix the problem without having to re-download the control, but on others we had to remove the control first, then re-install it and it worked a treat