Reporting Services in SQL Server 2016

Installing SQL Server 2016 CTP2 (Community Technology Preview) to test Reporting Services (SSRS) is a bit of a deception: the installer does not contain the developer tools! They (SSDT-BI or Report Builder) are also not yet available as a separate download as of June 10th 2015.

This fact is not communicated anywhere by Microsoft, although there is a mention by the SQL Server Team on June 1st 2015  in reaction to user Dave's remark concerning this:

Dave, agree completely, the tools upgrade is coming."

Source:
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx

Or here by another user:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fa8133bd-5cfa-4add-aa20-4c5442760d65/download-ssdtbi-samples-for-sql-server-2016?forum=ssdt

This prevents users from testing e.g. the new configurable parameter box.

For those still interested, the CTP can be found here:
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Reporting Services dynamic multi-column parameter box

The Reporting Services parameter section at the top of a report is generated as a HTML table with two fixed columns. To display SSRS parameters in three columns in the parameter field is not possible using a configuration option or even a CSS style sheet, so element conversion is needed.

The instructions below fixes this for the /reports and /reportserver web entry points and are suited for Reporting Services 2012, but might work for earlier versions as well.

The code:

• Adds the jquery library to every report
• Uses this library to:
• Create a DIV element for each set of TD parameter elements (=label and textbox/dropdown) and add it to the first TD element of the first TR row
• Add all sets of TD parameters to these DIVs
• Remove all other TR rows from the parameter TABLE

The result is that as many parameters as possible are fitted on one horizontal row:

The code has to be appended to the end of the ReportingServices.js file. This file is added automatically to every report generated by Reporting Services.

The file is most probably located in the public javascript folder: c:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\js

Perform the following steps to get a multiple columns in the parameter box:

1) Download and add the publicly available jquery-1.4.4.min.js to the public javascript folder.

2) Code to append:

// helper function to add scripts to head of html
function addHeadElement(headtype, file) {
var head = document.getElementsByTagName('head')[0];
  if (headtype == 'script')
  {
    var script = document.createElement('script');
    script.setAttribute('type', 'text/javascript');
    script.setAttribute('src', file);
    head.appendChild(script); 
  }
}
addHeadElement('script','/Reports/js/jquery-1.4.4.min.js');

// function that converts the parameter table to divs 
function param() {

  // create as many div->table->tr elements in the first column of the first row as there are parameters
  $(".ParamLabelCell").each( function (i, v) {
    var html = "<div style='float: left; padding-right: 10px;'> \
      <table> \
        <tr id='blackninja" + i + "'></tr> \
      </table> \
    </div>";
    $("td.InterParamPadding").filter(":first").append(html);
  });
  
  // move each label to its proper div->table->tr element
  $("td.ParamLabelCell").each( function (i, v) {
    $(this).appendTo("#blackninja" + i);
  });
  
  // move each prompt to its proper div->table->tr element
  $("td.ParamEntryCell").each( function (i, v) {
    $(this).appendTo("#blackninja" + i);
  });
  
  // remove all other parameter rows
  $("tr[isparameterrow='true']").not(':first').remove();

}
// add the convert function to the onload event twice
// this guarantees a cross-browser safe implementation
function pageLoad() {
  param();
}
window[ addEventListener ? 'addEventListener' : 'attachEvent' ]( addEventListener ? 'load' : 'onload', param );

3) Change style sheet

Add the following code to the styles\ReportingServices.css style sheet to make the dropdown boxes and textboxes smaller in width. More parameters will fit on a row:

.ParametersFrame.ParamsGrid.MenuBarBkGnd { padding: 0px; }
.DisabledTextBox { width: 125px; }
td.ParamEntryCell [id$="Value"] { width: 125px; }
td.ParamEntryCell { padding: 0px; }
td.SubmitButtonCell { padding-top: 2px; padding-bottom: 0px; }
td.ParamLabelCell { padding-right: 5px; }
td.InterParamPadding { padding-left: 0px; }
/* IE11 Edge/default profile fix */
tr.MenuBarBkGnd { height: 0px; } 
#ParametersRowctl31 { height: 0px; }

4) Enable in the reportserver

Add the following two elements to the head-element in \ReportServer\Pages\ReportViewer.aspx:

<meta http-equiv="X-UA-Compatible" content="IE=9">
<link href="/Reports/styles/ReportingServices.css" type="text/css" rel="stylesheet">
<script type="text/javascript" src="/Reports/js/ReportingServices.js"></script>
<script type="text/javascript" src="/Reports/js/jquery-1.4.4.min.js"></script>

Configure Reporting Services proxy for Bing maps

Add the following XML element to the web.config of both folders:
...\MSSQL\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\
...\MSSQL\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\

Add at the end between the runtime close tag and configuration close tag:

</runtime>
<system.net>
     <settings>
          <ipv6 enabled="true" />
      </settings>
      <defaultProxy enabled="true" useDefaultCredentials="true">
           <proxy bypassonlocal="True" proxyaddress="http://:"/>
       </defaultProxy>
</system.net>
</configuration>

It might be needed to restart the Microsoft Reporting Services service.

Unable to read exported Excel workbook because of illegal character

After exporting from reporting services, Excel opens the workbook with one of the following errors:

"Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal xml character. Line 1, column 36242."

"Excel found unreadable content in..."

SOLUTION

1. Rename the Excel workbook from *.xlsx to *.zip
2. Within the zip file, open the folder \xl\worksheets and open file sheet1.xml in a text editor, i.e. Notepad++
3. Use Notepad++'s GOTO feature to go to position 36242 and determine the offending character
4. Replace this character in the report's source, i.e. by using the SQL REPLACE function if the source is a plain SQL query
   Note: this is a reactive measure. Each time the error occurs, the character needs to be determined and replaced

For example, these Unicode characters cannot be part of the value in any field in the report:

code: visual representation
26: [SUB]
191: ¿

Dynamic transparent color

The default value of most color properties is "Transparent". If you want to dynamically set a color property, you can use the IIF-statement. This statement has the following signature:

=IIF([boolean],[return value if true],[return value if false])

A problem lies in the fact that both the true and the false part are obligatory. For setting the color the following IIF-statement can be used:

=IIF(ReportItems!txtBox.Value=5, "Palegreen", "Transparent")

But, the value Transparent does not exist! You cannot set it! Visual Studio throws the following warning:

The value of the background color property for the textbox ‘textbox’ is “Transparent”, which is not a valid background color.

It turns out that this property is not exported to the generated XML file, called rdl, if it is not explicitly set. The IIF-statement forces you to set it, but the value Transparent cannot be used. How to dynamically set the color property then?

You could use the value White instead. However, the background color for example gets rendered after the borders, so a white background covers part of a visible cell border. This is clearly visible on the report. Very annoying...

The only solution I could come up with is using a function that does not have an explicit return type. Then, if there is no return value, a non-existing object is returned.

Like this:

function Rank(val1)

if val1=5 then Rank = "Palegreen"

end function

Can you have the IIF-statement return a null (uninitialized object) value? The System.DBNull value that has replace the null value is just a type, not a value. So, this doesn't work.

Cannot decrypt the symmetric key...

Yesterday I got a Windows update of my .Net framework 2.0. After the update the system needed to be rebooted, so I did that. After that, my reportmanager in Internet Explorer returned with the following error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information. (rsReportServerDisabled)

The Reporting Services service (RSS) uses an asymmetric key to decrypt a symmetric key. This symmetric key is used to communicate with its repository in SQL Server. The asymmetric key is tied to the account with which the RSS runs. Change the account and the key is invalidated. Apparently, the update did something to this account, although it was "LocalSystem" and still is "LocalSystem".

Fortunately, the problem is easily solved by issuing the following statement at the command prompt I found on Kate Gregory's blog posted by a guy named Derek:

rsactivate -r -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"

It re-creates the assymetric key for the service account and stores it again in the rsreportserver.config file.