Archive for December, 2012

Dependent parameters in Reporting Services

Hold onto your butts (a little Samuel L. Jackson from Jurassic Park lingo) because this will be the best post I’ve ever done on a December 27th. Yes, it’s that great.

I was creating a report through SSRS that required the Yes/No parameters of one parameter, StabSelfInEyeball, to be dependent on another. Specifically, if parameter SonnyAndCher was Yes (and our Yes value had an ID of 1, No being 2), then the dependent parameter should only have No – the Yes would disappear completely. Because without Sonny, I just don’t want to hear Cher.

So I created a DataSet that binds to the StabSelfInEyeball parameter is  populated by this query:

SELECT 1 AS YesNoId
,’Yes’ AS YesNoType
WHERE @SonnyAndCher <> 1
UNION
SELECT 2 AS YesNoId
,’No’ AS YesNoType
ORDER BY YesNoType

The first Yes/No parameter, SonnyAndCher, is populated with a similar query, just without the WHERE clause:

SELECT 1 AS YesNoId
,’Yes’ AS YesNoType
UNION
SELECT 2 AS YesNoId
,’No’ AS YesNoType
ORDER BY YesNoType

So how this works is that since the second parameter, StabSelfInEyeball, is dependent on the first, SonnyAndCher , whenever SonnyAndCher is changed, StabSelfInEyeball is refreshed. So, if the user choose ‘No’ for the SonnyAndCher parameter, then I can choose to either stay myself in the eye or not, it’s up to me. But if SonnyAndCher is Yes, the only choice for the StabSelfInEyeball  parameter is “No”. I will not stab myself in the eye – why would I do that? It’s Sonny AND Cher – can’t beat that.

Leave a comment

Reporting Services – HtmlViewer styles not being displayed

There is no shame in it. You make a change to your HtmlViewer.css style sheet, and the changes are not being reflected in Report Manager. It happens to lots of guys. But, help is on the way. Maybe.

This whole situation came about when I made a change to my HtmlViewer stylesheet on my development machine, which worked. When that same change was made to a test server, it didn’t. The style I changed was pretty straight-forward – it just hides the Atom icon in Report Manager:

.ToolbarAtomDataFeed
{
display: none;
}

While poking around, I looked in the rsreportserver.config file, which was located in the “C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer” folder on my machine. I found that in the machine that worked, the “HTMLViewerStyleSheet ” element was there, and the one that didn’t, that same element was missing:

<Configuration>
<HTMLViewerStyleSheet>HtmlViewer</HTMLViewerStyleSheet>
<Dsn>blah</Dsn>
<ConnectionType>Default</ConnectionType>
<LogonUser></LogonUser>

After making this change, the Reporting Services service will need to be restarted.

See your doctor if your style changes don’t last more than four hours.

Leave a comment

Fixing the “Could not load file or assembly AjaxControlToolkit” error with bindingRedirect

I was upgrading a web site to use a later version of the AjaxControlToolkit.dll. The file version of the new DLL was 4.1.60623.0. So I changed the DLL to the new version and rebuilt my project locally, and all was well with my pages, giving me that warm, fuzzy feeling like rum-infused egg nog. But that warm, fuzzy feeling went away when I tried to deploy it on this whole interwebs thing, when I got this error:

Could not load file or assembly ‘AjaxControlToolkit, Version=4.1.50401.0, Culture=neutral, PublicKeyToken=28f01b0e84b6d53e’

The first thing I tried was screaming “WHY AREN’T YOU WORKING?” at the computer, but this did not seem to work. Which is odd – it usually does.

So the next thing I did was search, and I found something interesting: The bindingRedirect element, the documentation of which you can find on MSDN. So I modified my web.config file, adding this code inside the <configuration> element:

   <runtime>
      <assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″>
         <dependentAssembly>
            <assemblyIdentity name=”AjaxControlToolkit”
                              publicKeyToken=”28f01b0e84b6d53e”
                              culture=”neutral” />
            <bindingRedirect oldVersion=”4.1.50401.0″
                             newVersion=”4.1.60623.0″/>
         </dependentAssembly>
      </assemblyBinding>
   </runtime>

Tada! It worked.

Leave a comment

Inserting a blank line in a Reporting Services parameter field

I had a parameter in Reporting Services with a list of user IDs. I needed to add a blank line as the first value instead of the default  value of “<Select a value>”. I messed around with the parameter properties screen, including checking “Allow blank value” and “Allow null value”, but neither did what I wanted. My solution was to tweak the query a little bit.

The UserName parameter was populated with a UserNameDataSet Dataset, and that Dataset was populated with the following query:

SELECT  UserName
FROM [User]
ORDER BY UserID

Nothing fancy – the UserName field holds text user name that is used to log into the system. In order to add a blank line at the top of the list for the user UserName parameter, I had to modify the query to look like the one below, adding a new, dummy “ID” field and UNIONing in a blank space line:

SELECT UserName, 1 AS ID
FROM [User]
UNION
SELECT ‘ ‘ AS UserName, 0 AS ID
ORDER BY UserName

Hey, it met the requirements.

Leave a comment