Solved

pass multiple values as parameters from main report to drill down in SSRS

Posted on 2008-06-09
8
4,477 Views
Last Modified: 2011-09-20
Hi, I am successfully able to set report parameters and use them in the data parameters on a main report I want to pass all the values now to the drilldown report. I went to the properties of the textfield, selected Navigate to the next report using the same parameters. It works but for only one of the multi values. I do not know how to test the string that is being passed in the SQL to see what is happening. Is it possible in the first place to pass multiple values to a drilldown report? I am using the same string as in the first report, in it's own query. I get the message that The report parameter "AcqID" has a DefaultValue or ValidValue that depends on the report parameter "AcqID"
Forward dependancies are not vailid.
  WHERE ((C.AcquisitionID IN(@AcquisitionID)) AND (C.ResultID IN(@ResultID)) )
0
Comment
Question by:Laura2112
  • 4
  • 2
  • 2
8 Comments
 

Author Comment

by:Laura2112
ID: 21746302
I have determined by looking at the paraters above the drilldown report that there is only one parameter being passed even though I have set it up the report parameters to be multi. If that helps. Also, I have it set now as non queried for avaialable values, now not getting the same error as above, just getting the first value.
0
 
LVL 18

Expert Comment

by:PFrog
ID: 21749381
To pass a multi valued parameter to a drill through, when you select the parameter in the expression builder, it will automatically add
          =Parameters!AcqID.Value(0)
This (helpfully!) only passes the first selected item. You need to change it to
          =Parameters!AcqID.Value
And then it will pass through the entire selection
0
 

Author Comment

by:Laura2112
ID: 21751200
Thanks for responding. That makes perfect sense. My text box on main report did have
=Field!AcqID.Value. That was working. When I changed it to  =Parameters!AcqID.Value instead I get the error Parameter is a type and cannot be used as an expression. Where am I suppose to use the parameter value? I do not have the choice to do it that way when I am in the Navigation pane assigning the parameters to the drilldown report .
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Accepted Solution

by:
PFrog earned 500 total points
ID: 21751472
If you want to display the value of a multivalued parameter in a textbox, you should use
   =Join(Parameters!AcqID.Value,",")         [which will join all selected values together into a string, delimited with a comma]

If you want to pass a multivalued parameter from one report to another, when you look at the navigation window, you can't just select the value from the dropdown list (only fields are shown in the list), you have to use the expression builder, with the value of
    =Parameters!AcqID.Value

However, it sounds like in your main report you are using the multi valued parameter to filter a query, then displaying the results of that query on the report. When you click on a report item to run the drill through report, what value do you want to pass through to the drillthrough? is it the single value you have clicked on, or all items selected in the multivalued parameter?

If you want to pass in the single value you have clicked on, then your navigation parameter should be
     =Fields!xxx.value
If you want the same parameter selection as was chosen in the main report, use
     =Parameters!xxx.Value       [again, you will have to type this manually, it doesn't exist in the dropdown]

If this isn't clear, please can you explain in more detail what you have/want and I'll try and clarify.
0
 

Author Comment

by:Laura2112
ID: 21751523
Let me play around with this, you are great! Will let you know how it is coming.
Definately want  the same parameter selection as was chosen in the main report, so will use
     =Parameters!xxx.Value       [again, you will have to type this manually, it doesn't exist in the dropdown]
0
 

Author Closing Comment

by:Laura2112
ID: 31465534
Excellent!!!  I am getting ready to post another question now that I have this report working. Man, the hours spent on this!
0
 
LVL 18

Expert Comment

by:chrismc
ID: 21751939
If you are using multi-value parameters, RS converts this to a comma seperated list.
e.g. 123, 654, 8765
The simplest way is then in your query you say
    Select * From myTable Where Id In (@myParameter)

BUT for best practice, you should be calling a stored procedure not ad hoc queries.
This is when multi-value parameters become a bit sticky but there is a good solution.

Just pass the parameter into the stored procedure as normal, then call the SQL function listed below from the procedure to break the parameter into a usable form. I use this a lot and it works well. I can't take credit for this code though but it's been so widely published, I'm not too sure who to credit.

Here is an example stored procedure that would use this function;
CREATE PROCEDURE SSRS.SalesByGroups (@Groups varchar(8000))
AS
      Select *
            From Sales.InvoiceLine
            Where PostingGroup In (Select Value From dbo.ufn_Split(@Groups))
Go




CREATE FUNCTION [dbo].[ufn_Split](@String nvarchar(4000))
RETURNS @Results TABLE (value nvarchar(4000))
AS
-- ------------------------------------------------------------------------------------------------
-- This function takes a string containing an array and splits the array out and returns it as a 
-- table object. This is particularly useful for handling multi value parameters in Reporting 
-- Services.
--
-- To use it code as follows;
--		SELECT value FROM master.dbo.ufn_Split('3,23,45,2,6', ',')
-- ------------------------------------------------------------------------------------------------
 
-- This function takes two parameters; the first is the delimited string, the second is the delimiter
BEGIN
	Declare @Index Int, @Slice nvarchar(4000), @Delimiter char(1)
 
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    Select @Index = 1, @Delimiter = ','
  
    IF @String IS NULL RETURN
    WHILE @Index != 0
	BEGIN    
		-- Get the Index of the first occurence of the Split character
		SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
 
		-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
		IF @INDEX !=0
			SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
		ELSE
			SELECT @SLICE = @STRING
 
		-- PUT THE ITEM INTO THE RESULTS SET
		INSERT INTO @Results(value) VALUES(@SLICE)
 
		-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
		SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
 
		-- BREAK OUT IF WE ARE DONE
		IF LEN(@STRING) = 0 BREAK
	END
 
	RETURN
END
GO

Open in new window

0
 
LVL 18

Expert Comment

by:chrismc
ID: 21751952
PS: The Database Reporting Zone is a better place for Reporting Services questions
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 73
How to update "part of a text field" without changing the entire contents? 2 55
SQL Server 2012 express 24 39
Email Notifications for SQL 2005 9 28
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question