Category: SQL Server Reporting Services

I was creating a report today for servers with a specific Operating System where I needed to include two parameters (Operating System and Service Pack level.)  The values of the second parameter need to be dynamic based on the value selected for the first parameter.  This can be accomplished using cascading parameters.

1. Create the first parameter (OSVersion) for the Operating System.
2. Create a new Dataset (OSServicePacks) (with a query that references the first prompt) that will be used to populate the values for the second parameter.
   (I created an expression for my query, because I want to search for the word Server and the Windows Version, which is particularly necessary since Windows 7 and Server 2008 R2 are both version 6.1
   =”SELECT DISTINCT v_GS_OPERATING_SYSTEM.CSDVersion0 FROM v_R_System JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID WHERE v_R_System.Operating_System_Name_and0 LIKE ‘%Server%” & Parameters!OSVersion.Value & “%'”)
3. Create the second parameter (OSSP) for the Service Pack selection.  Because I want the values of this parameter to be dynamic based on the various Service Pack levels of servers with the specified Operating System I have gone to the Available Values section of the Parameter Properties window and made the following selections
   • Select from one of the following options: “Get values from a query”
   • Dataset: OSServicePacks
   • Value Field: The field that will contain the value of the parameter
   • Label Field: The field that will contain the label for the parameter – the text that will actually appear in the parameter drop-down list (this can be the same as the Value Field)
4. Create the Dataset that will return the data for the body of the report (that will reference one or both of the parameters.)
5. Run the report.  The second parameter will be greyed out until a selection is made for the first parameter.  Then the values in the second parameter will be dynamically populated.
6. If the value for the first parameter is changed, the values for the second parameter will be re-populated based on the new value for the cirst parameter.

I was setting up SSRS for use with SCOM 2012 and trying to configure reporting Services to use SSL with the actual server name and a more user friendly alias.  We have a naming standard for our servers that includes department, service, and production state.  This results in some user-unfriendly names (such as sys-scomdb-p01) so we end up adding an alias in DNS and adding a Subject Alternate Name to the certificate.

Howeever, when selecting the SSL certificate in Reporting Services Configuration Manager, it only reserved a URL using the CN on the certificate, so attempting to access SSRS using the alias/alternate name did not work.

I could not find any informaiton anywhere  about getting SSRS to work using SSL and an alternate name on the certificate, so I looked around in the Reporting Services configuration file (rsreportserver.config) and discovered for myself how easy it is to get it to work.

First, remember that there are two virtual directories: ReportServer and Reports.  The URLs are reserved for each virtual directory in rsreportserver.config.  If you want to use the alias for both virtual directories, you will need to add a URL for both virtual directories:

1. Look for the <VirtualDirectory>tags.  There will be a <URLS> child tag followed by <URL> and <UrlString>.
   
2. Copy the XML code from <URL> to </URL> and paste it immediately below.  There should now be two <URL> tags inside of the <URLs> tag.   Edit the UrlString in one of the <URL> tags to use the alias.

If you look back in Reporting Services Configuration Manager, both URLs will now be displayed.