SSRS Requirements in SQL Server

Scenario: My requirement is to bind a Dropdownlist in SSRS with all column names to be returned by a (SQL) Stored Procedure.

At this point maybe you are confused about why we need to do that.

Why we need to do this stuff

I want to sort my report dynamically. I want to provide the feature to the user to sort the report as we choose.

Let's start.

Step 1:

We need to write the following query that returns all the columns names from a specific SQL Stored Procedure. In this query I have called the SQL builtin function:

sys.dm_exec_describe_first_result_set_for_object

Read the MSDN article for this function and also read the limitations of this function https://msdn.microsoft.com/en-us/library/ff878236.aspx

SELECT [Name]
FROM sys.dm_exec_describe_first_result_set_for_object
(
  OBJECT_ID(yourStoredProcedureName),
  NULL
)

I will discuss only one the limitation of this function.

Limitation: This function does not return any column names when the T-SQL Batch contains a temporary table.

Step 2

I will now use this query in my report to satisfy my requiremetns. Go to your Report Solution. This is my report where I need to add the sorting functionality.



In this report I want to add sorting functionality. For this we need to add a new dataset and write a query in this TextBox then click OK.



Then add a dataset to the report data pane.





Step 3

Then we need to add two parameters to this report.

SortBy: Its contain all the columns names in the dropdown.

SortOrder: It contains one of two values (Asc or Desc).

SortBy: Now I will add Sa ortBy parameter. Right-click on the parameter and add it.



Then go to the Available Values tab of this window and then select Get values from the query then select the dataset name dsColumnNames and the corresponding value field.



Now we need to set the default values of this parameter.



Then we need to add a second parameter.

SortOrder: In this we need to select Specify values from this window and then add two values manually. One is Asc and the second one is Desc.



Then we need to set the default value of this parameter. Set the default value here.



Then we can see the parameter result here.



Step 4

Now we need to do one step more to sort the reports. Go to the tablix Properties as in the following:



Then go to the Tablix sorting tab.



Then click on the Add button. Then we need to add an expression. For this click on the Expression button.



Add the following expression here:

=IIF(Parameters!SortOrder.Value="Asc",Fields(Parameters!SortBy.Value).Value,0)

Again click on the Add button then click on the fx button.



Again we need an expression in the expression window.

=IIF(Parameters!SortOrder.Value="Desc",Fields(Parameters!SortBy.Value).Value,0)

Then change the order of the Then by.



Finally select OK. Now we can see the following output: