Can an SSRS report (.rdl) dynamically select its dataset at runtime? Are there equivalent alternatives?

Question

I have prepared a fairly standard report file (.rdl) with a single Tablix populated by a dataset. The dataset consists of a rather simple query, but does perform a JOIN with a WHERE filter.

For the sake of illustration, lets say the dataset query looks something like:

Select FIELD1, FIELD2, FIELD3
From MyView as V
Join MyTable as T on V.FIELD1=T.FIELD1
WHERE ...

I now need to run the same report (layout, data, etc.) against a different dataset. It goes without saying that I expect that the second dataset would necessarily have to match the first in terms field names, types, etc. Initially, I was hoping to simply update the DataSetName property on the Tablix using on a report parameter, but from my research, this doesn't appear to be possible.

I've unsuccessfully tried parameterizing my dataset using 2 approaches.

Approach 1:

Select FIELD1, FIELD2, FIELD3
From @ViewParam as V
Join MyTable as T on V.FIELD1=T.FIELD1
WHERE ...

I then tied the new @ViewParam to a report parameter with the values dbo.MyView and dbo.MyView2 as available options. SSRS Designer didn't care for this much and spat out: "Could not update the list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. Must declare the table variable "@ViewParam". To continue without updating the fields list, click OK."

I clicked OK and continued regardless, but wasn't surprised when prevewing the report didn't work: "Must declare the table variable "@ViewParam".

Approach 2:

On the Dataset properties window, next to the Query field I selected the Fx button and tried to build up a dynamic expression instead based on a hint I found elsewhere. It looked like this:

="Select FIELD1, FIELD2, FIELD3 " &
"From " & Parameters!ViewParam.Value & " as V " &
"Join MyTable as T on V.FIELD1=T.FIELD1 " &
"WHERE ... "

In this case, previewing the report returns the error: "Error during processing of the CommandText expression of dataset 'MyDataset'." I double checked the expression carefully and it seems correct, but without being able to see Report Builder is actually trying to send to SQL Server I am little more than guessing as to the real problem.

Are any of the approaches feasible? If not, is there some equivalent and reasonable approach that will avoid me having 2 nearly duplicate .rdl files in my project?

Thanks!

Tab Alleman Tab Alleman · Accepted Answer · 2015-08-19T19:53:25

Your second approach might be feasible, and you can use Profiler to see what command is actually being sent to SQL Server when you try it, and hopefully that will help you debug it. You will also need to actually EXECUTE the string that you build, you can't just send the string value to SQL Server and expect SQL Server to know what to do with it.

However, this is how I would approach your issue:

Instead of a SQL Statement in your Dataset properties, call a stored procedure. The procedure will expect all the parameters needed to make the WHERE clause, plus the ViewParam parameter.

Inside the stored procedure, use this logic:

IF @ViewParam = 'First Option'
 SELECT (your first query)
ELSE IF @ViewParam = 'Second Option'
 SELECT (your second query)

Or, if you feel it would be easier to maintain, you can use your view parameter to create dynamic sql inside the stored procedure.

Can an SSRS report (.rdl) dynamically select its dataset at runtime? Are there equivalent alternatives?

2 Answers