subreports for each values of multi-values parameters
-
hello!! i have a report that work for single-value parameter. if is possible to generate sub-report for each value selected in multi-valued parameter?
Lets imagine you have multi-valued parameter "parameterA" with four values selected (Value1, Value2, Value3 and Value4). I'd like to generate sub-report for each of these values (passing the value as single parameter). Sure, I can change the procedure to handle multi-valued parameter, but because of performance and caching I really don't want to.
Thanks for any advice.
-
Interesting one. I don't think you can iterate over the values in the multivalued parameter in SSRS. What I would do is to create a dataset that calls a split to table function (example below) passing the parameter in there. You can then use that dataset for a List / Tablix to call your subreports.
Example Dataset:
SELECT * FROM dbo.split(@multivaluedparm, ',')Example Splitter:
ALTER FUNCTION [dbo].[split]
(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), items NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(items)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
Before Jeff Moden has my guts for garters, this isn't the best, check http://www.sqlservercentral.com/articles/Tally+Table/72993/ for better splitters. (You may have one handy anyway for passing multi valued parameters to sprocs.)
-
I don't understand how i will integrate my dataset that must feed each report
-
1. Open your parent report and create the dataset as described above (this dataset returns a table of your selected parameter values).
2. Insert a List into the report and set it's the DataSetName property to the dataset you created.
3. Insert a Subreport container in the Details section of the List.
4. In the Subreport properties, choose your subreport and pass the Fields!Items.Value to you Subreport parameters (i.e. the dataset in your subreport).
Simples.
-
ok, thanks. I will implements this procedure and i would keep you informed
-
Hi Get me? ,
I bigin again to work on my BI personal project, i have some error when i implements the method that you described to me in your last two posts. It appears when i have running the report:
"procedure or function dbo.split has to many arguments specified"
But in the parameters's dataset, There are not any problems. I don't know the reason of this crash
-
Ahh soz, I can see what's happening, my fault. We always use stored procedures for our report datasets so multivalued parameters get passed as a comma-delimited string (e.g. 'a,b,c').
When you use it in a dataset query like
SELECT * FROM dbo.Split(@multivaluedparm, ',')SSRS expands the values out into comma-separated values. ( e.g. 'a', 'b', 'c' ). This is why it is giving the above error.An easy way to fix this is to convert the parameter results back into a Comma-delimited string. If you open your Dataset properties then go to Parameters and change the Parameter-Value expression to =Join(Parameters!multivaluedparm.Value, ",") . This will combine the parameter values into a single string and pass the to the dataset and function. It will not affect the report parameter itself.
Since img links dont work here I've attached the images showing how to do this or you can use this url for the album: http://imgur.com/a/KRqY8
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply