Problem Statement

After upgrading the SQL Server Reporting Service to 2016, the SSRS reports embedded in different applications including SharePoint pages using an iframe tag or page viewer web parts were stopped rendering.




Workaround Tried

I tried to fix this issue by adding the following entries in the rsreportserver.config but no luck.

report config file.png

The rsreportserver.config file is installed in the following location by default:

SharePoint Mode Native Mode
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer


Added rs:Embed=true as a query parameter at the end of the report URL and this fixed the issue and reports started working properly.



How to create SSRS reports by joining multiple SharePoint List columns with foreign key relationship


There are three lookup functions in SQL Server 2008 R2 Reporting Services:

  1. Lookup
  2. LookupSet
  3. MultiLookup

In this article I will explain the functionality of LookupSet Function and provide a simple report to show how it is used.


Returns the set of matching values for the specified name from a dataset that contains name/value pairs.


Lookup(source_expression, destination_expression, result_expression, dataset)



(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ID.Value.


(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!CustomerID.Value.


(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!PhoneNumber.Value.


A constant that specifies the name of a dataset in the report. For example, “ContactInformation”.


Returns a VariantArray, or Nothing if there is no match.


  • Create two Data Sets as shown below(here “ds_Orders” is the main dataset and “ds_Products” is the master dataset):


  • Create a report and drag and drop the necessary columns from the “ds_Orders” dataset as shown below


  • In the fourth column of this report, we have to bring the data from the “ds_Products” dataset with lookup option. To do this write the below expression on the fourth column

=Join(LookupSet(Fields!Product_Code.Value,Fields!Code.Value,Fields!Title.Value,”ds_Products”), “,”)

  • Now your Report will look something similar to this


  • That’s it you are done. Run the report you will see the below output




Hope you liked this article  🙂

Business Case 

When you create Dataset using SharePoint List as datasource the list columns includes duplicate records. There is no direct way to remove duplicates and get only the distinct values.


  • Open Report builder and create a datasource and name it as ‘SPListDS’
  • Go to Credential tab and select ‘Use current windows user’ option. And click Ok.
  • Right click on Datasets on Report Data pane and select Add Dataset. Name it as ‘SharePointListDetails’.
  • Select ‘Use a dataset embedded in my report’ option. Select the SPListDS, which you create at the previous step as the datasource. Use the Query Designer to select whatever the list you want to include in the dataset. Click Ok. I selected the column I have to use in my parameter from SharePoint List for my dataset.
  • Right click on the Parameters at the Report data pane and select Add parameter. Name it as ‘DummyParameter’ select ‘Allow multiple values’ check box, select ‘Hidden’ option. Go to Available Values tab, select Get values from query tab. Select dataset, value field and label field from the drop downs.
  • Go to Default values tab; select ‘Get values from a query option’ and select dataset and value field as follows.
  • Next you need to add the Custom code that will get the duplicate values and return only the distinct values.
  • Right click at the report body and select report properties. Go to Code tab. Add the following code to Custom code box. (Thanks to Mohamed for this code 🙂 )

Public Shared Function RemoveDuplicates(parameter As Parameter) As String()

Dim items As Object() = parameter.Value


Dim k As Integer = 0

For i As Integer = 0 To items.Length – 1

If i > 0 AndAlso items(i).Equals(items(i – 1)) Then

Continue For

End If

items(k) = items(i)

k += 1


Dim unique As [String]() = New [String](k – 1) {}

System.Array.Copy(items, 0, unique, 0, k)

Return unique

End Function

  • And click Ok.
  • Next you are going to add the distinct parameter dropdown.
  • Add a new parameter and name it as ‘OriginalParameter’, go to Available values tab and select Specify values option. Click on the Add button. Add the following expression to Label and value fields.


That’s it done


How to restrict user to select only 3 values from the multi value parameter – SSRS?


To take the count of the number of items selected in your parameter use the below expression in your hidden parameter


Based on the count in the hidden parameter you can check the condition and pop up the error message. Follow this article written by me