Dynamic Row field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Dynamic Row field

Author

Topic

Trybbe
Starting Member

27 Posts

Posted - 2009-07-20 : 07:37:50

Hi

I need help please. I have a matrix report displaying company headcount. The row field shows Region while column shows stafftype.

I have a multivalue parameter for region with the option to select all regions or the region of your choice. What I need to have is that, when a user select a region the my report should display areas that fall under that region. So I tried something like :-

=iif(Parameters!Region.value = "All", Fields!Region.value, Fields!Area.value) -- Report runs and instead of names it return "#error" as a value. So I replaced Parameters!Region.value with Fields!Region.value -- then I get the first area within that region and not all of them.

Please help

bsivel
Starting Member

8 Posts

Posted - 2009-07-23 : 17:56:59

Have you tried passing the parameter value to your stored procedure or you query to filter your dataset? This approach eliminates the need for the expression. I can help if you unsure how to do this.

Brian Sivel

Trybbe
Starting Member

27 Posts

Posted - 2009-07-24 : 02:51:55

Hi Brian

Thanks for the suggestion, I will try it and will let you know how it goes.

Thanks
Thato

Trybbe
Starting Member

27 Posts

Posted - 2009-07-24 : 05:55:19

Hi Brian

I wrote a stored proc and it works fine with one problem, I am unable to pass multivalue parameters to the stored procedure. Please help

shah429
Yak Posting Veteran

52 Posts

Posted - 2009-08-03 : 06:52:00

All the selected parameters are passed as a single string to the stored procedure, so you have to use some kind of a function to split the string using ',' as the delimiter. I use the function below. In the where clause of the stored procedure, use this function to capture multiple parameters and split them to make it seem like an array. I hope this will help you.

CREATE FUNCTION [dbo].[udf_SplitString]

(

@Keyword VARCHAR(5000)

)

RETURNS @SplitKeyword TABLE (Keyword VARCHAR(1000))

AS

BEGIN

DECLARE @Word VARCHAR(500)

DECLARE @TempKeyword TABLE (Keyword VARCHAR(5000))

DECLARE @Delimiter VARCHAR(10)

SET @Delimiter = ','

WHILE (CHARINDEX(@Delimiter, @Keyword, 1)>0)

BEGIN

SET @Word = SUBSTRING(@Keyword, 1 , CHARINDEX(@Delimiter, @Keyword, 1) - 1)

SET @Keyword = SUBSTRING(@Keyword, CHARINDEX(@Delimiter, @Keyword, 1) + 1, LEN(@Keyword))

INSERT INTO @TempKeyword VALUES(@Word)

END

INSERT INTO @TempKeyword VALUES(@Keyword)

INSERT @SplitKeyword

SELECT * FROM @TempKeyword

RETURN

END

Trybbe
Starting Member

27 Posts

Posted - 2009-08-04 : 10:21:51

Thanks for the feedback. I managed to split the values. How do I use "NULL" with cascading parameters. I would like the second parameter to default to null if all values are selected on the first parameter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources