Reporting Services - Multi-value parameter query returning no results

Question

Using SSRS (SQL Server 2005) I've set up two reports. For ease of explanation, let's call them MAIN_REPORT and SUB_REPORT.

MAIN_REPORT runs a query that returns a set of Areas, with multiple TagNames per area:

AREA    Tagname
----------
A1        T1
A1        T2
A2        T3
A3        T4

If you select one of the Area cells, it opens SUB_REPORT and passes a parameter to @ParentTagNames using Join(Parameters!ResultsOfSearch.Value,","). ResultsOfSearch is equal to the Tagname column above (haven't yet figured out how to limit the Tagnames to only the ones in the selected area, but I'll worry about that later).

SUB_REPORT takes the passed parameter and queries a massive database for the tagname, and returns a min, max, and average of available values:

SELECT
h.TagName as TagName,
Minimum = convert(decimal(38, 2), Min(h.Value)), 
Maximum = convert(decimal(38, 2), Max(h.Value)), 
Average = convert(decimal(38, 2), Avg(h.Value)) 
FROM INSQL.Runtime.dbo.History h 
WHERE h.TagName in (@ParentTagNames)
AND h.wwVersion = 'LATEST'
AND h.wwRetrievalMode = 'Cyclic'
AND h.wwResolution = '60000'
AND h.Value > '-1.0'
AND h.DateTime >= '2009-09-01 12:00:00'
AND h.DateTime <= '2009-09-02 16:00:00'
GROUP BY h.TagName

However, if @ParentTagNames is equal to more than one value, it returns no data. If it's equal to a single tag, it returns data. I figured it must have had something to do with the formatting of the data within @ParentTagNames as it's passed from MAIN_REPORT, but it doesn't seem to matter.

I've tried the following formats:

T1,T2,T3,T4,T5,T6
'T1','T2','T3','T4','T5','T6'

I made sure the multi-value is checked for the parameter @ParentTagNames. Google has successfully failed me.

Anyone?

EDIT: Profiler magic!

exec sp_executesql N'SELECT
    h.TagName as TagName,
    Minimum = convert(decimal(38, 2), Min(h.Value)), 
    Maximum = convert(decimal(38, 2), Max(h.Value)), 
    Average = convert(decimal(38, 2), Avg(h.Value)) 
FROM INSQL.Runtime.dbo.History h 
WHERE h.TagName in (@ParentTagNames)
AND h.wwVersion = ''LATEST''
AND h.wwRetrievalMode = ''Cyclic''
--AND h.wwResolution = @Resolution
AND h.wwResolution = ''60000''
AND h.Value > ''-1.0''
--AND h.DateTime >= @StartTime
--AND h.DateTime <= @EndTime

AND h.DateTime >= ''2009-09-01 12:00:00''
AND h.DateTime <= ''2009-09-02 16:00:00''

GROUP BY h.TagName',N'@ParentTagNames nvarchar(46)',@ParentTagNames=N'M12_CPM_Filling_250.Value,M8_Filling_391.Value'

Is the query you have here in a stored procedure or in plain text in the report? — Nick Heidke, Jun 19 '12 at 19:45
Ooh, maybe my problem is the JOIN itself; is that making it into a single field? — Jimfletcha, Jun 19 '12 at 19:54
Have you tried profiling to see what the query is that's sent to the DB? — Nick Heidke, Jun 19 '12 at 20:02
Profiling. What is this wizardry? I've run the SUB_REPORT query standalone within SSRS with the datasets I mentioned above typed in, but still no dice. — Jimfletcha, Jun 19 '12 at 20:03
Can you run the query directly against the database using Management Studio? Also, do you know what the column definition is for h.TagName? — Nick Heidke, Jun 19 '12 at 20:05
Have you run this query for each, individual `ParentTagName`? It's theoretically possible that not all of these tags exist with the same conditions for which you are filtering in the `WHERE` clause. — swasheck, Jun 19 '12 at 20:09
The query run directly against the database (as in, within Studio Manager) works with substituting @ParentTagNames with 'T1','T2','T3'. The column definition for h.TagName is, to my knowledge, not available. INSQL is a linked server to circular files (bunch of folders on the desktop). — Jimfletcha, Jun 19 '12 at 20:09
Thought of that. I've paired my testing down to only two tags, both of which return values when queries individually (using the SSRS query, by the way). Profiling appends this line to my query: N'@ParentTagNames nvarchar(46)',@ParentTagNames=N'M12_CPM_Filling_250.Value,M8_Filling_391.Value' — Jimfletcha, Jun 19 '12 at 20:14
@user1467368 then your issue is in how you're passing the values to the server — swasheck, Jun 19 '12 at 20:17
Oh I need a username. Right, but how do I fix it! The format of the data I manually enter into @ParentTagNames doesn't matter. How do I parse it externally within the same query? — Jimfletcha, Jun 19 '12 at 20:19
Try unchecking the Multi-Value checkbox for @ParentTagNames. I'm hoping the profiler output is a bit different... — Nick Heidke, Jun 19 '12 at 20:25
Changing @ParentTagNames to read 'T1','T2' caused the Profiler to output the last line as: N'''T1'',''T2'''. So it's still casting it as a single variable. — Jimfletcha, Jun 19 '12 at 20:32
I think you may need a SPLIT call on the subreport to force SSRS to take the incoming parameter and put it into an array. Reference: http://www.stuffthatjustworks.com/HowToPassMultivalueReportParametersToSubreportsInReportBuilder.aspx — Nick Heidke, Jun 19 '12 at 20:41
Even though entering the data manually produces the same result (ie after the point that the JOIN happens)? — Jimfletcha, Jun 19 '12 at 20:43
I'm thinking so, because the SPLIT call should treat 'T1' and 'T2' and separate values and no longer concatenate them as a single variable. — Nick Heidke, Jun 19 '12 at 20:50
No freaking way! Ok. Well. That worked. Thanks, yo! Counterintuitive; I guess there is some formatting that can only happen outside the data query to the parameter. Weird. How do I...you know...cause you to "win" this question! — Jimfletcha, Jun 19 '12 at 20:52
I've added the SPLIT as an answer below. Please accept that :-) Welcome to the stack overflow community! — Nick Heidke, Jun 19 '12 at 20:55
Umm I can't "upvote" you apparently, but I wish you well and cookies! — Jimfletcha, Jun 19 '12 at 20:57

score 1 · Accepted Answer · answered Jun 19 '12 at 20:54

1

I think you may need a SPLIT call on the MAIN_REPORT to force SSRS to take the incoming parameter and put it into an array.

Reference: http://www.stuffthatjustworks.com/HowToPassMultivalueReportParametersToSubreportsInReportBuilder.aspx

answered Jun 19 '12 at 20:54

Nick Heidke

2,787
2
34
58

Err, well, make that on MAIN_REPORT. – Jimfletcha Jun 19 '12 at 20:58
Next step is figuring out how the deuce to only make it pass T1 and T2 when I click on A1, rather than T1, T2, T3, T4. Next hurdle, hopefully won't be too bad. – Jimfletcha Jun 19 '12 at 21:03
Give it a whirl, and if you're struggling feel free to put up a separate question. You did an excellent job of explaining your issue with this question, so I have little doubt you'd get great assistance on further questions. – Nick Heidke Jun 19 '12 at 21:11

Reporting Services - Multi-value parameter query returning no results

1 Answers1

Linked