December 16, 2008 at 10:54 am
Hi
I have a report with a fairly complex (and slow) query populating a matrix.
The matrix performs some aggregations rather than going all the way down to the raw details.
What I would like to do is for any cell in the matrix - expanded or not - click the cell and see the raw data for it. I understand this is straightforward in Enterprise and ReportBuilder (clickthrough) - but there must surely be a way to do it with plain BIDS?
I would be happy just to pass the logical pk for the data out to a multivalued parameter and drillthrough/sub report - but this doesn't seem to work - only taking the first value rather than all of them.
Any ideas gratefully received!
Cheers
Rich
December 17, 2008 at 5:01 am
i had problem as u have like returning only first value of the multi value parameter. It is because when you write the parameter value it will select like parameter!value(0) for mutli value parameter and this means it will take only first string of array. Make sure it doesnt use (0) into parameter value.
Then Create this function.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- This one is for passing a group of string values
CREATE FUNCTION [dbo].[fn_MVParamChar]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(Cast(@Piece AS varchar(100)))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
--------------------------------------------------------------
now use this function as below into your stored procedure when you pass your multi value parameter.
yourfield IN (Select Param FROM [dbo].[fn_MVParamChar](@yourparameter, ','))
regards,
vijay
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply