Making SSRS multiselect dropdowns searchable combobox


SSRS Multiselect dropdowns are pretty powerful. Just to add on to the power of it, i felt it would be good to add on a searchable combobox functionality. As and when the user types in a text, the dropdown filters the value and displays only the required results in the dropdown.

Also to ensure that the original functionality is not being affected i have tried using a jQuery code that you can simply port in. Just ensure the element IDs are fine.

Below is the code snippet for this functionality. Please copy them directly onto your webpage HTML section towards the end and verify the element IDs once again.

Ensure that you have jQuery loaded on your webpage.

<script type=”text/javascript” language=”javascript”>
/* Binding functions */
if ($(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).html() != null) { //Check if report dropdown is loaded on the page
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).removeAttr(“readOnly”); //Remove the readonly property or else user’s keypress wont work
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).bind(“keyup”, Searchable);//Search for each key press
$(“#ParameterTable_ctl00_cphMain_rpvMain_ctl00“).bind(“click”,ClearValue);//SSRS parameter area click to reset the dropdown filter
$(“#ParameterTable_ctl00_cphMain_rpvMain_ctl00“).bind(“focusin”,ClearValue);//SSRS parameter area focusin to reset the dropdown filter. This is to track user’s tab key press
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_ddDropDownButton“).bind(“click”,ClearValue);// Dropdown arrow click to reset the dropdown filter
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).click(SetFocus);//When the user click on the multiselect dropdown, the cursor should be in place for him to make text entry.
function SetFocus() { $(this).focus(); }
function ClearValue(){
RestoreValues();
}
function RestoreValues()
{
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_divDropDown table tr td”).each(function() {
$(this).parents(“tr”).show();
});
}
function Searchable(e) {
if(e.keyCode ==8 || e.keyCode==46) //Check for backspace or delete
RestoreValues();
var searchText = $(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).val(); //extract the textbox value (full string the user enters)
searchText = searchText.split(‘ ‘).join(” “);//Replace any special space charectors with normal space charector. It might appear weird, but i found the space in SSRS dropdown values to be different from normal space and my regular expression failed. Hence this code is added.
if(searchText != ”)
{
var reg_Ptrn = new RegExp(“^”+ searchText, “i”);
//Loop through all the elements in the dropdown and show or hide each of them based on the user key inputs.
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_divDropDown table tr td“).each(function() {
var labelText = $(this).text();
labelText = labelText.split(‘ ‘).join(” “);
if(reg_Ptrn.test(labelText))
$(this).parents(“tr”).show();
else
$(this).parents(“tr”).hide();
});
}
}
}
</script>

Known Issues:

1. Regular expression special charectors need to be handled separately. If you dropdown values have regex special charector like “[],^?$” etc.., then they will add their special meaning to the search. Although you can call it a regular expression searchable combobox 😉

But i will get back with the fix for the bug. The best option would be to keep track of their keycodes & prefix them with “\” (without double chords). ex. “[” become “\[“. Then pass it to the regex text function. It will escape these charectors and search them as normal charectors.

2. Need to remove the hardcodings of textboxes and dropdown.

3. Performance although not that bad, could be tweaked a bit more.

Hope this helps.

SSRS Export to PDF breaking the columns in larger reports


This is a wierd problem, one could face in SSRS. The export to PDF or Word, appears to break the columns in larger reports with 10 or more columns.

The main reason being the report size specifications.

You can check your report properties to find the report size specifications. Check the screenshot below:

Check the Width and Height properties of the dialog that pops up (Right-click on the Report -> Report Properties)

The Fix:

Find the width of your report tablix (in inches). This will be a good start. Do not worry about height now. Set the Width to the width property in the above dialog, from your calculation. This is indeed a trial and error process, until you get all the columns in one single page. You will also need to adjust the Margins accordingly.

This should solve the issue of columns being spilled over to the next page, while taking export to PDF or Word.

New Problem that can come up:

In the first page of the exported report, you see only one row in the tablix.

Fix:

Adjust the margins to a lower value, less that 0.5in. They can be real culprits in this case.

Hope this helps with your SSRS issue.

SSRS: Making Excel the default export option for reportviewer


Most of us would always use the export to Excel in the report viewer (90% of the time), rather than having a ‘Select a format’ option in it.  I will be describing a simple method here to make the excel a default option for export in the report viewer.

One quick note: You can go to the rsreportserver.config file in “\Program Files\Microsoft SQL Server\MSRS10.DEV\Reporting Services\ReportServer” directory and move the Excel option to the top. Check the screenshot below:

Unfortunately, this will take the Excel to the top of the dropdown list options, but still the ‘select a format’ option will be the first option in the list. Check the screenshot below:

 

Now lets get back on track and see what the possible solution.

Problem: To set the Excel export as the default export option for the reportviewer on the load. Avoid resetting of dropdown options, after click of Export.

 Solution: Consider the two controls in the context, the dropdown and the Export Link. They are simple html controls that gets rendered on the page. Onload of the page, we will set the selected index to 1 or 3 (depending on the position of Excel option, remember the option count starts from 0). Then make the “Export” link onclick event to null. Register a new set of javascript for the onclick of the “Export” link.

The reason why to bother much about the click of export link button is due to the fact that it will reset the dropdowns “selectedIndex = 0” blindly, which we don’t want.

Let me take you through the steps clearly:

Step 1: In your aspx page, which has reportviewer being loaded, add the script tag at the end. By end, i mean after the reportviewer tag. Do check the id of the rendered controls and modify it accordingly.

       <rsweb:ReportViewer ID=”rpvMain” runat=”server” Font-Names=”Trebuchet MS, arial”
                 BackColor=”#FFFFFF” InternalBorderColor=”#CCCCCC” LinkDisabledColor=”Black” AsyncRendering=”true”
                ProcessingMode=”Remote” SizeToReportContent=”false” ShowPrintButton=”false” ShowFindControls=”false”
                Font-Size=”12px” Width=”970px” PromptAreaCollapsed=”false” Style=”text-align: left;display:inline;”>
      </rsweb:ReportViewer>

     <script language=”javascript” type=”text/javascript”>

        function triggerExport() {
            var formatDropDown = document.getElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl00’);
            if (formatDropDown.selectedIndex == 0)
                return false;
            window.open(document.getElementById(‘ctl00_cphMain_rpvMain’).ClientController.m_exportUrlBase + encodeURIComponent(formatDropDown.value), ‘_blank’)
            formatDropDown.selectedIndex = 1; //Change this to the appropriate index of Excel in the dropdown
            document.getElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl01’).Controller.SetViewerLinkActive(document.getElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl00’).selectedIndex != 0); return false;
        }   

        if (document.getElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl00’) != null) {
            document.getElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl00’).selectedIndex = 1;//Change this to the appropriate index of Excel in the dropdown
            document.getElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl01’).onclick = null;
            document.getElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl01’).onclick = triggerExport;        
   
        }
    </script>

 Step 2: Run the page and check it.

NB: You can make it browser compatible, but i found it of no use, as the SSRS has loads of issues with the other browsers. Also, if you see the click event and other javascripts used in the SSRS controls, they all use document.getElementById. 😦 Quite depressing I know.

If you wish to make the document.getElementById usage safe acrross other browsers, use the following function call in your code:       

function CustomGetElementById(elementId)
{
       return document.getElementById
        ? document.getElementById(elementId)
        : document.all
          ? document.all[elementId]
          : document.layers[elementId];
}

ex: CustomGetElementById(‘ctl00_cphMain_rpvMain_ctl01_ctl05_ctl01’).onclick = null;

If you wish to remove the ‘select a format‘ option you can now go ahead and do it. It would be just a small modification to the above functions. I leave that to you.

Hope this post was useful.

Enabling SSRS remote error


There are two ways to do this:

  • Using rs command
  • Setting the DB properties

Using the rs command:

Step 1: Save the following in a text file and save it as ‘EnableRemoteErrors.rss’.

Public Sub Main()
  Dim P As New [Property]()
  P.Name = “EnableRemoteErrors”
  P.Value = True
  Dim Properties(0) As [Property]
  Properties(0) = P
  Try
    rs.SetSystemProperties(Properties)
    Console.WriteLine(“Remote errors enabled.”)
  Catch SE As SoapException
    Console.WriteLine(SE.Detail.OuterXml)
  End Try
End Sub

Step 2: Now open the command prompt and point to the rss file location (Start -> Run -> Cmd)

Step 3: Now run the rss file using the rs command, as shown below:

rs -i EnableRemoteErrors.rss -s http://servername/ReportServer

[Replace the ‘ReportServer’ with your report server name]

 Setting the DB properties:

This option is simple and direct. Except for the fact that you need the Management Studio.

  1. Start Management Studio and connect to a report server instance.
  2. Right-click the report server node, and select Properties.
  3. Click Advanced to open the properties page.
  4. In EnableRemoteErrors, select True.
  5. Click OK.

Hope this was helpful