SSRS Multiple Field Values Not Passing As Parameters To Drilldown Report

Question

I have not found a suitable answer to my following problem after much searching.

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

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

When I click on the field (ie 11 for Neurology Total For Spec, Apr), which is =COUNT((Fields!Appointment_ID.Value), 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.

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

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

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

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

    DECLARE @String NVARCHAR(36)

    WHILE LEN(@StringInput) > 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

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.

In the parameter mapping to the sub report, what does the expression for the value look like? Is it passing the full array of values (i.e. Fields!Appointment_ID.Value), or just the first one (i.e. Fields!Appointment_ID.Value[0])? — R. Richards, May 06 '16 at 13:23
Hi. It is as per your first example and so should be passing the full array of values. Thanks — Gareth, May 09 '16 at 07:06
Can you add the Fields!Appointment_ID.Value on your subreport at the top just to make sure it is passing what you expect ? This will narrow down the issue ... is the problem with the report sending the parameter or with the sub report ? — J Greene, May 09 '16 at 17:30

score 0 · Accepted Answer · answered May 09 '16 at 22:28

How to begin troubleshooting your specific problem

Because you've said you can get the subreport working by supplying a string of values as desired, then it appears the problem is with the report providing the delimited string. Before you can fix the issue, you need to see the delimited string being passed to the subreport - so add it to the tablix. Use the same function you're using to reference the value in the "Go to Report" action and add it as a tooltip to each count. That way you can see the string being prepared and tweak your logic until it comes out correctly.

How I would solve your problem

What you're trying to accomplish as a multi-valued parameter is, as I see it, unnecessarily complicated.

In your tablix, you have a set way of organizing the results. You have specialty name, diagnosis, and month (presumably of the diagnosis or date served).

That's three parameters you can pass through to a sub-report. Using similar sql logic as exists in your main report, you can isolate those 11 simply by knowing the values of those three fields plus the year. Obviously, you will need to do some work to find diagnosis/service dates that fall into a given month, but that's not too hard:

Where Month(Date_of_Service) =  @Month
    and Year(Date_of_Service) = @Year
    and Specialty_Name = @Specialty
    and Diagnosis_Code = @Diagnosis

Many thanks. From initial testing using the 3 parameters Specialty, Month and Year in the sub-report and passing them from the summary report appears to work (the total I'm drilling on is for the service not each individual diagnosis so I didn't include this). What I now don't understand is why passing 3 separate parameters associated with the text box containing the count produces a result, where passing the unique identifier (Appointment_ID) for the records being counted in that text box does not? — Gareth, May 11 '16 at 09:07
Using the delimited string you are introducing additional points of failure in building and then parsing the delimited string, and you appear to have found one of those failure points. If you find my answer to be helpful, don't forget to mark it as such. — Christopher Brown, May 11 '16 at 17:15

SSRS Multiple Field Values Not Passing As Parameters To Drilldown Report

1 Answers1