Multiple sql scripts in dataset selected via option in report

I have an SSRS report that includes aggregated data chart etc utilizing more than one data source. I am in the process of creating a drill down to display person level data for each of the charts so you click on the data label and then view the underlying names etc..

Years ago i was able to create a person level report that incorporated a few different 'scripts' as it were so that you could in effect choose which script (one dataset included a few different scripts) powered the output of the report.

To explain further - when selecting a 'drill through' action to go to report one of the 'parameters' passed would be a number 1,2.3 etc and this would be passed through to the person level report and effectively determone which script powered the user level report.

So a value of 1 would determine that script one fromt he dataset would be used to power the report. This would be totally different to the script that a value of 2 would provide.

Im not sure how the dataset was structured but roughly speaking I believe it worked a bit like a case statement so the dataset would be

case
when value = 1 then

select
from
where

when value = 2 then

select
from
where

etc..

I kind find this report and have struggled to find guidance how this would be done. Any help would be gratefully appreciated.

You can do this as you described but all paths in your script must return the same structure to SSRS, the same column names and data types.

So you cannot do something like

IF @option = 1 
   BEGIN
      SELECT FirstName, LastName from Employees WHERE EmpID = @emp
   END
IF @option = 2
   BEGIN
      SELECT StartDate, Salary, Department from Employees WHERE EmpID = @emp
   END

In the above, the number of columns changes, and even if we only returned StartDate and Slaary in the 2nd option, the datatype would also be different.

Alternatively, if your dataset is small, you can return a list of keys, values and datatypes and the report use a LOOKUP() to retrieve the correct row.

e.g.

IF @option = 1 
   BEGIN
          SELECT 'FirstName' as FieldName, FirstName as FieldValue, 'text' as FieldType FROM Employees WHERE EmpID = @emp 
    UNION
          SELECT 'LastName', LastName, 'text' FROM Employees WHERE EmpID = @emp
       END
    IF @option = 2
       BEGIN
          SELECT 'StartDate' as FieldName, StartDate as FieldValue, 'date' as FieldType FROM Employees WHERE EmpID = @emp
          UNION
          SELECT 'Salary', Salary, 'money' FROM Employees WHERE EmpID = @emp
          UNION 
          SELECT 'Department', Department, 'text' FROM Employees WHERE EmpID = @emp
       END

This way the field names and datatypes are always the same.

In the report, assuming the table format returned from the query was not what you wanted, you would use LOOKUP() to choose the correct value in each textbox on your report e.g. =LOOKUP('Salary', Fields.FieldName.Value, Field.FieldValue.Value, "DataSet1")

You would do the same thing to get the field type and then set the textbox format property accordingly.

Try to create a parameter ScriptSelection in the person-level report to capture the value that determines the script to be used.

Then modify the dataset query in the person-level report to include a conditional statement based on the value of the ScriptSelection parameter using a CASE statement or an IF statement :

SELECT *
FROM (
    SELECT
        -- Columns from script 1
    FROM table1
    WHERE @ScriptSelection = 1

    UNION ALL

    SELECT
        -- Columns from script 2
    FROM table2
    WHERE @ScriptSelection = 2

    -- Add more UNION ALL sections for additional scripts
) AS data;

Then configure the drill-through action in the main report to pass the appropriate value (1, 2, 3, etc.) for the ScriptSelection parameter to the person-level report.

When clicking on a data label in the main report and trigger the drill-through action, the person-level report will receive the selected parameter value and use the corresponding script (dataset) to power the report.