SSRS Multiple Field Values Not Passing As Parameters To Drilldown Report
<p>I have not found a suitable answer to my following problem after much searching.</p>

<p>I have a summary report which shows the total number of appointments by month grouped by specialty name and diagnosis.</p>

<pre><code>Specialty_Name  Diagnosis Code   Feb   Mar   Apr

Neurology       G35X             0     3      4
                G379             8     5      7
                Total For Spec   8     8      11

Rheumatology    H051             4     9      2
                M059             6     10     3
                Total For Spec   10    19     5
</code></pre>

<p>When I click on the field (ie 11 for Neurology Total For Spec, Apr), which is <code>=COUNT((Fields!Appointment_ID.Value)</code>, I want to pass each of the Appointment_IDs to a sub query as the parameter to display the associated client details.  However, when I prepare the sub query and drill down report in a manner that I have used before I am only getting the result of the first Appointment_ID; not each one.</p>

<p>The sub query report is set up with the parameter  <code>@Appointment_ID VARCHAR(MAX)</code> and a WHERE clause:</p>

<pre><code>CAST(App.App_ID AS VARCHAR(MAX)) in (
  SELECT * FROM Serve1.dbo.CreateParameterTable(@Appointment_ID,',')
)
</code></pre>

<p>The <code>CreateParameterTable</code> is a function on our server which should handle the string from the summary report and does so on other reports.</p>

<pre><code>(
      @StringInput NVARCHAR(MAX)    
    , @SplitBy  NCHAR(1)
)
RETURNS @OutputTable TABLE ( [String] NVARCHAR(36) )
AS 
BEGIN

    DECLARE @String NVARCHAR(36)

    WHILE LEN(@StringInput) &gt; 0
        BEGIN
            SET @String = LEFT(@StringInput,
                               ISNULL(NULLIF(CHARINDEX(@SplitBy, @StringInput) - 1,
                                             -1), LEN(@StringInput)))
            SET @StringInput = SUBSTRING(@StringInput,
                                         ISNULL(NULLIF(CHARINDEX(@SplitBy, @StringInput),
                                                       0), LEN(@StringInput))
                                         + 1, LEN(@StringInput))

            INSERT INTO @OutputTable ( [String] )
              VALUES ( @String )
        END
</code></pre>

<p>If I manually type in multiple Appointment_IDs to my drill down report I get the expected result.  Therefore it appears that either my Summary Report is not passing out a string or it is not being handled correctly by the function or the sub report does not like the output of the function Which as I have said works on other reports we have written.  I'm stumped.</p>